Database Reference
In-Depth Information
The logic is to try to get the latch and, failing that, to increment the miss count, a statistic we can see in a
Statspack report or by querying the
V$LATCH
view directly. Once the process misses, it will loop some number of times
(an undocumented parameter controls the number of times and is typically set to 2,000), attempting to get the latch
over and over. If one of these get attempts succeeds, then it returns and we continue processing. If they all fail, the
process will go to sleep for a short duration of time, after incrementing the sleep count for that latch. Upon waking
up, the process begins all over again. This implies that the cost of getting a latch is not just the “test and set”-type
operation that takes place, but also a considerable amount of CPU while we try to get the latch. Our system will appear
to be very busy (with much CPU being consumed), but not much work is getting done.
Measuring the Cost of Latching a Shared Resource
As an example, we'll study the cost of latching the shared pool. We'll compare a well-written program (one that uses
bind variables) and a program that is not so well written (it uses literal SQL, or unique SQL for each statement). To
do this, we'll use a very small Java program that simply logs into Oracle, turns off auto-commit (as all Java programs
should do immediately after connecting to a database), and executes 25,000 unique
INSERT
statements in a loop. We'll
perform two sets of tests: our program will not use bind variables in the first set, and in the second set it will.
To evaluate these programs and their behavior in a multiuser environment, I opted to use Statspack to gather the
metrics, as follows:
1.
Execute a Statspack snapshot to gather the current state of the system.
2.
Run N copies of the program, having each program
INSERT
into its own database table so
as to avoid the contention associated with having all programs trying to insert into a single
table.
3.
Take another snapshot immediately after the last copy of the program finishes.
Then it is a simple matter of printing out the Statspack report and finding out how long it took N copies of the
program to complete, how much CPU was used, what major wait events occurred, and so on.
■
Why not use aWr (automatic Workload repository) to perform this analysis? the answer to that is because
everyone has access to Statspack, everyone. it might have to be installed by your dBa, but every oracle customer has
access to it. i want to present results that are reproducible by everyone.
Note
These tests were performed on a dual-CPU machine with hyperthreading enabled (making it appear as if there
were four CPUs). Given that there were two physical CPUs, you might expect very linear scaling here—that is, if one
user uses 1 unit of CPU to process her inserts, then you might expect that two users would require 2 units of CPU.
You'll discover that this premise, while sounding plausible, may well be inaccurate (just how inaccurate depends
on your programming technique, as you'll see). It would be correct if the processing we were performing needed no
shared resource, but our process will use a shared resource, namely the shared pool. We need to latch the shared pool
to parse SQL statements, and we need to latch the shared pool because it is a shared data structure, and we cannot
modify it while others are reading it and we cannot read it while it is being modified.
■
i've performed these tests using Java, pL/SQL, pro*C, and other languages. the end results are very much the
same every time. this demonstration and discussion applies to all languages and all interfaces to the database. i chose
Java for this example as i find Java and Visual Basic applications are most likely to not use bind variables when working
with the oracle database.
Note
