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
- The application was built without a single consideration for
scaling the database level.
- The application itself could not be tuned or touched.
- Experience shows that 80 to 90 percent of all tuning is done
at the application level, not at the database level.
- The developers had no idea what the beans did in the database
or where to look for potential problems.
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:
select * from t for update;
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
SELECT FOR UPDATE. It turned out the developers
wanted a repeatable read.
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
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:
- How to tune SQL without changing the SQL (that's hard, we'll
look at some methods in Chapter 11 on Optimizer Plan Stability).
- How to measure performance.
- How to see where the bottlenecks were.
- How and what to index. And so on.
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).