Database Reference
In-Depth Information
I was called into a project that was in trouble. The developers were experiencing massive performance
issues—it seemed their system was serializing many transactions, that is to say—so instead of many people working
concurrently, everyone was getting into a really long line and waiting for everyone in front of them to complete. The
application architects walked me through the architecture of their system—the classic three-tier approach. They would
have a web browser talk to a middle tier application server running Java Server Pages (JSPs). The JSPs would in turn
utilize another layer—Enterprise Java Beans (EJBs)—that did all of the SQL. The SQL in the EJBs was generated by a
third-party tool and was done in a database-independent fashion.
Now, in this system it was very hard to diagnose anything, as none of the code was instrumented or traceable.
Instrumenting code is the fine art of making every other line of developed code be debug code of some sort—so when
you are faced with performance or capacity or even logic issues, you can track down exactly where the problem is. In
this case, we could only locate the problem somewhere between the browser and the database—in other words, the
entire system was suspect. The Oracle database is heavily instrumented, but the application needs to be able to turn
the instrumentation on and off at appropriate points—something it was not designed to do.
So, we were faced with trying to diagnose a performance issue with not too many details, just what we could
glean from the database itself. Fortunately, in this case it was fairly easy. When someone who knew the Oracle V$
tables (the V$ tables are one way Oracle exposes its instrumentation, its statistics, to us) reviewed them, it became
apparent that the major contention was around a single table—a queue table of sorts. The application would place
records into this table while another set of processes would pull the records out of this table and process them.
Digging deeper, we found a bitmap index on a column in this table (see the later chapter on indexing for more
information about bitmapped indexes). The reasoning was that this column, the processed-flag column, had only
two values—Y and N. As records were inserted, they would have a value of N for not processed. As the other processes
read and processed the record, they would update the N to Y to indicate that processing was done. The developers
needed to find the N records rapidly and hence knew they wanted to index that column. They had read somewhere
that bitmap indexes are for low-cardinality columns—columns that have but a few distinct values—so it seemed a
natural fit. (Go ahead, use Google to search for
when to use bitmap indexes;
low-cardinality will be there over and over.
Fortunately, there are also many articles refuting that too simple concept today.)
But that bitmap index was the cause of all of their problems. In a bitmap index, a single key entry points to many
rows, hundreds or more of them. If you update a bitmap index key (and thus locking it), the hundreds of records that
key points to are effectively locked as well. So, someone inserting the new record with N would lock the N record
in the bitmap index, effectively locking hundreds of other N records as well. Meanwhile, the process trying to read
this table and process the records would be prevented from modifying some N record to be a Y (processed) record,
because in order for it to update this column from N to Y, it would need to lock that same bitmap index key. In fact,
other sessions just trying to insert a new record into this table would be blocked as well, as they would be attempting
to lock the same bitmap key entry. In short, the developers had created a table that at most one person would be able
to insert or update against at a time! We can see this easily using a simple scenario.
■
i will use autonomous transactions throughout this topic to demonstrate locking, blocking, and concurrency issues.
it is my firm belief that autonomous transactions are a feature that oracle should not have exposed to developers—for the
simple reason that most developers do not know when and how to use them properly. the improper use of an autonomous
transaction can and will lead to logical data-integrity corruption issues. Beyond using them as a demonstration tool, autonomous
transactions have exactly one other use—as an error-logging mechanism. if you wish to log an error in an exception block,
you need to log that error into a table and commit it—without committing anything else. that would be a valid use of an
autonomous transaction. if you find yourself using an autonomous transaction outside the scope of logging an error or
demonstrating a concept, you are almost surely doing something very wrong.
Note
