The Response - Page 4
September 10, 2001
They had no idea. When asked, 'OK, when we need to tune a generated query, who can help me rewrite the code in the EJB?' The answer was, 'Oh, you cannot tune that code, you have to do it all in the database'. The application was to remain untouched. At that point, I was ready to walk away from the project — it was already clear to me that there was no way this application would work:
That was shown to be the case in the first hour of testing. As it turns out, the first thing the application did was a:
What this did was to force a serialization of all work. The model implemented in the database was such that before any significant work could proceed, you had to lock an extremely scarce resource. That immediately turned this application into a very large single user system. The developers did not believe me (in another database, employing a shared read lock, the observed behavior was different). After spending ten minutes with a tool called TKPROF (you'll hear a lot more about this in Tuning Strategies and Tools, Chapter 10) I was able to show them that, yes, in fact this was the SQL executed by the application (they had no idea — they had never seen the SQL). Not only was it the SQL executed by the application but by using two SQL*PLUS sessions I was able to show them that session two will wait for session one to completely finish its work before proceeding.
So, instead of spending a week benchmarking the application, I
spent the time teaching them about tuning, database locking,
concurrency control mechanisms, how it worked in Oracle versus
Informix versus SQL Server versus DB2 and so on (it is different
in each case). What I had to understand first, though, was the
reason for the
Repeatable read is a database term that says if I read a row once in my transaction and I read the row again later in the same transaction, the row will not have changed — the read is repeatable.
Why did they want this? They had heard it was a 'good thing'. OK, fair enough, you want repeatable read. The way to do that in Oracle is to set the isolation level to serializable (which not only gives you a repeatable read for any row of data, it gives you a repeatable read for a query — if you execute the same query two times in a transaction, you'll get the same results). To get a repeatable read in Oracle, you do not want to use SELECT FOR UPDATE, which you only do when you want to physically serialize access to data. Unfortunately, the tool they utilized did not know about that — it was developed primarily for use with another database where this was the way to get a repeatable read.
So, what we had to do in this case, in order to achieve serializable transactions, was to create a logon trigger in the database that altered the session for these applications and set the isolation level to serializable. We went back to the tool they were using and turned off the switches for repeatable reads and re-ran the application. Now, with the FOR UPDATE clause removed, we got some actual concurrent work done in the database.
That was hardly the end of the problems on this project. We had to figure out:
At the end of the week the developers, who had been insulated from the database, were amazed at what the database could actually provide for them, how easy it was to get that information and, most importantly, how big a difference it could make to the performance of their application. We didn't do the benchmark that week (they had some reworking to do!) but in the end they were successful — just behind schedule by a couple of weeks.
This is not a criticism of tools or technologies like EJBs and container managed persistence. This is a criticism of purposely remaining ignorant of the database and how it works and how to use it. The technologies used in this case worked well — after the developers got some insight into the database itself.
The bottom line is that the database is typically the cornerstone of your application. If it does not work well, nothing else really matters. If you have a black box and it does not work well — what are you going to do about it? About the only thing you can do is look at it and wonder why it is not doing so well. You cannot fix it, you cannot tune it, you quite simply do not understand how it works — and you made the decision to be in this position. The alternative is the approach that I advocate: understand your database, know how it works, know what it can do for you, and use it to its fullest potential.
How (and how not) to Develop Database Applications
That's enough hypothesizing, for now at least. In the remainder of this chapter, I will take a more empirical approach, discussing why knowledge of the database and its workings will definitely go a long way towards a successful implementation (without having to write the application twice!). Some problems are simple to fix as long as you understand how to find them. Others require drastic rewrites. One of the goals of this book is to help you avoid the problems in the first place.
In the following sections, I discuss certain core Oracle features without delving into exactly what these features are and all of the ramifications of using them. For example, I discuss just one of the implications of using Multi-Threaded Server (MTS) architecture— a mode in which you can (and sometimes have to) configure Oracle in order to support multiple database connections. I will not, however, go fully into what MTS is, how it works and so on. Those facts are covered in detail in the Oracle Server Concepts Manual (with more information to be found in the Net8 Administrators Guide).