The Cost Of Database Independence

November 24, 2004 Oracle Database

Reading through the
following on from my "Is
Oracle A Legacy Technology"
posting, and in particular
N rgaard’s reference
to Jonathan Lewis’ comments about database
independence, made me think about another client I recently worked with.

This client had an application that, when a transaction was about to take
place, would request from the database a set of sequence numbers that would be
used to record the transaction. These sequence numbers would be used to record
the customer, the account, the product, the individual order items and the
address that the order was to be sent to. The problem they were having was that,
as the number of concurrent users began to scale up, the application slowed down
dramatically, and around once each day the application actually froze, with
users locked out and no action possible except to restart the application
server. They had a suspicion it was down to a locking issue, and asked us to
take a look and make some recommendations.

As we had access to the application coders (it was an in-house application)
and they had a development environment, it was fairly easy to insert

event 10046 extended trace
code into the bit of the application that
processed orders, process a few sample orders using a single logged in user,
then examine the TKPROF formatted trace file and take a look at the SQL being
emitted by the application, and any wait events that slowed down the code. Next,
we ran the same process with 100 simulated concurrent users, to see if we could
spot any lock contention or other wait events that came when multiple users
tried to use the system.

Looking first at the SQL used by the application, it soon became clear where
the problems might be occurring. The application used a table, which will call
SEQUENCE_NUMBERS, which had one row per sequence number used and stored the
highest value used by the sequence;

CREATE TABLE sequence_numbers
seq_num_type varchar2(50),
seq_num_value number

The table had around 20 seq_num_types,
one for customer_id, one for
product_id, and so on. When the
application required a new customer ID, it would issue code along the following

UPDATE sequence_numbers SET seq_num_value
= seq_num_value WHERE seq_num_type = ‘CUSTOMER’;
SELECT  seq_num_value FROM sequence_numbers WHERE seq_num_type =

Ignoring at this point alternative ways to do this, this first things that
came to mind when I looked at this were – firstly, we’re going to have a lot of
contention for this particular table and it’s associated block within the
datafile, and all of the requests are going to be for the same few rows. Once we
start updating our particular rows Oracle is going to block any further updates
until that change is committed or rolled back, so we’re going to get some issues
around locking, and we’re also going to have issues about multiple processes all
trying to access this particular block in the buffer. Also, all of these commits
are going to (correct me if I’m wrong here) be causing the database to be
writing the redo log to disk because of all the checkpoints, which is going to
effectively serialise our work whilst this goes on.

Looking at the relevant bit from the TKPROF report, we found the following

Elapsed times include waiting on following events:

Event waited on                             Times   Max. Wait  Total Waited
  —————————————-   Waited  ———-  ————
  buffer busy waits                              11        0.06          0.13
  latch free                                      3        0.01          0.04
1        0.00          0.00

What this showed was that, whilst we did get one lock wait, most of our waits
were in fact down to the same rows within the same table being "fought over" by
all our processes at the same time.

Just afterwards, we had a call through from the customer help desk saying
that the application had locked up again, and from running

some code we found over on AskTom
, we quickly found out that one of our
application users was blocking another through looking at the V$LOCK view:

       (select username from v$session where sid=a.sid)
       ‘ is blocking ‘,
        (select username from v$session where
sid=b.sid) blockee,
   from v$lock a, v$lock b
  where a.block = 1
    and b.request > 0
    and a.id1 = b.id1
    and a.id2 = b.id2

——– ———- ————- ——– ———-
APPUSER            8 
is blocking APPUSER          

Speaking again to the application developer, and looking at some logging code
he’d put into the application, it appeared that what was happening was that one
part of the code was requesting a new sequence number somewhere, then failing to
complete the transaction, but leaving the SEQUENCE_NUMBER row locked. All other
users then eventually ground to a halt waiting for this transaction to
eventually commit.

The customer’s next idea was to wrap up the request of these sequence numbers
into a function, which would declare a cursor for update, retrieve the number
and commit the transaction, all in one atomic process that the application
couldn’t muck up. The code for the function looked something like:

create function GET_CUSTOMER_ID
returns number
cursor SEQUENCE_CUR is
        SELECT seq_num_val FROM
sequence_numbers WHERE seq_num_type = ‘CUSTOMER’
          FOR UPDATE;
         {logic to retrieve sequence

However, when we ran this concurrently with 100 users and took a look at the
TKPROF results, the performance was even worse:

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  —————————————-   Waited  ———-  ————
  enqueue                                        12        0.98          2.03
  latch free                                      6        0.01          0.10
  buffer busy waits                               5        0.02          0.05

Now we were really having problems with locks. The SELECT
… FOR UPDATE was locking the table even more aggressively than our previous
code, such that the whole process was now taking much longer just to do the same
job. We might solve our system deadlock problem, but the application definitely
won’t scale.

Of course at this point anyone who’s familiar with Oracle
will know that the customer should really have been using sequences instead of
maintaining a list of values in a table, and manually updating, selecting and
committing each request. Sequence values (or infact, the next value for a
particular sequence) are held in the SGA, only require disk I/O when the
sequence cache has been exhausted, and don’t involve any locks, buffer busy
waits or table contention. Sequences are one of the features within Oracle that
allow applications to scale, and their use in this situation is described in
Jonathan Lewis’ chapter in

Oracle Insights : Tales Of The Oaktable

"The primary purpose of Oracle sequences is to supply
unique numbers rapidly … So what do you do if you don’t want to use Oracle
sequences? Typically,something like this:

select seq_value
into :local_variable
from seq_table
where seq_name = ‘SLOW_REQ’
for update;

update seq_table
set seq_value = seq_value + 1
where seq_name = ‘SLOW_SEQ’;

The commit is necessary to reduce the time that other
sessions have to sit waiting for you to get out of the way … More
significantly, you have reduced your scalability quite dramatically. Sessions
will be queueing, and you’ve now achieved three round trips, three redo
records, and six block changes, something that Oracle could do in one (or
zero) round trips, no redo records, and no block changes"

The obvious step then was to recommend that all of this
code was replaced by simple sequence lookups, which would get rid of the segment
contention, contention for block buffers, locking and extra redo. However, the
problem was that the customer’s management required that all applications be
"database independent", so that they weren’t locked into a single database

The reasoning behind this was that the client had some
associated companies, not all of which used Oracle as their RDBMS, and the
software might possibly be used by these other companies on their database. In
addition, the company had got a particularly good deal from Oracle when they
last licensed the database, and migrated from Sybase onto Oracle because of this
deal. If they didn’t get the same deal when they came to renew their license,
they might well move to a different database vendor, or even look at an open
source database such as mySQL.

If you read AskTom or have got Tom Kyte’s

Effective Oracle By Design
, you’ll know Tom’s view on database independence:

"Here’s something that you might find controversial :
Database dependence (regardless of the database you are using, this does not
apply just to Oracle) should be your goal, not something to avoid. You want to
maximise the investment in your database. You want to develop the best
software in the least amount of time against that database. The only way to do
that is to fully exploit what the database has to offer.

The truth is that, with the exception of trivial
applications, achieving database independence is not only extremely hard, but
is also extremely costly and consumes a huge amount of resources … your
application would not use SQL beyond simple "keyed reads" … in short, you
would end up writing your own database."

Of course a cynic would say that "He would say that,
wouldn’t he, working for a database vendor" but at the same time, you’ve paid a
lot of money for Oracle, and it seems a shame not to use the very features that
Oracle can charge a lot of money for. Jonathan Lewis, in the same Oaktable book
chapter, warns however that money aside, if you don’t use these features, your
application just won’t scale:

"An application that is database independent is
automatically an application that will perform badly at high levels of
concurrency or large data volumes. It may do the job adequately, but you may
spend all your time and money struggling to push the software into meeting
Service Level Agreements (SLAs) and the users’ expectations … Database
developers put a lot of effort into making their technology efficient, and
basically assume that if they design a good feature into their systems you
will be prepared to pay a small performance penalty in one part of their code
in order to win a big performance gain elsewhere. Database-independent code
doesn’t use features, it uses vanilla-flavored, lowest common denominator
elements – so you pay every penalty, but never pick up the benefits."

Also, at what point do you declare database independence –
do you allow stored procedures? (most databases have them, but their syntax is
different). What about analytic functions? They’re in SQL-99, but not everyone
implements them. SQL Server has identity columns, and Access has autonumber
fields – can we use sequences now, if we equate them with these other
implementation methods? It’s difficult to come up with a workable definition of
database independence that doesn’t just leave us, as Tom Kyte suggests, with
just tables and indexes.

So, effectively, we were stuck with this sequence
workaround, which we had to keep in place to satisfy the requirement for
database independence, but which effectively limited the scalability of the
application and had Oracle working at only a fraction of it’s possible capacity.
The irony of the situation was that the client could end up paying more for
hardware to support the inefficient application, and was already paying out
additional money to have someone like myself take a look at the application.
But, by doing this they were able to switch from vendor to vendor whenever they
wished, other companies within the group could use it on their own database
platforms, and perhaps the deal they might get in future would outweigh the
additional cost of running the application in this way.

Anyway, I thought this was an interesting bit of work in
the end, as it illustrated the impact of not doing things the Oracle-optimised
way, and also showed why a customer might choose, for logical business reasons,
to go down the database independence route. I’m due to go back in there early
next year, so it’ll be interesting to see if sequences were eventually
considered, what impact they might have had, if they weren’t what additional
cost and effort was incurred in making the application scalable by other means.