Database Reference
In-Depth Information
STAT...parse count (total) 18 20,005 19,987
LATCH.call allocation 4 20,016 20,012
LATCH.enqueue hash chains 70 20,211 20,141
STAT...consistent gets 266 40,093 39,827
STAT...consistent gets from ca 266 40,093 39,827
STAT...consistent gets pin (fa 219 40,067 39,848
STAT...consistent gets pin 219 40,067 39,848
STAT...calls to kcmgcs 117 40,085 39,968
STAT...session logical reads 10,713 70,469 59,756
STAT...recursive calls 10,058 70,005 59,947
STAT...KTFB alloc space (block 196,608 131,072 -65,536
LATCH.cache buffers chains 51,835 171,570 119,735
LATCH.row cache objects 206 240,686 240,480
LATCH.shared pool 20,090 289,899 269,809
STAT...session pga memory 65,536 -262,144 -327,680
STAT...logical read bytes from 87,760,896 577,282,048 489,521,152
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
73,620 784,913 711,293 9.38%
PL/SQL procedure successfully completed.
The runstats utility produces a report that shows differences in latch utilization as well as differences in statistics.
Here I asked runstats to print out anything with a difference greater than 9,500. You can see that we
hard parsed
two
times in the first approach using bind variables, and that we hard parsed 10,000 times without bind variables (once for
each of the inserts). But that difference in hard parsing is just the tip of the iceberg. You can see here that we used an
order of magnitude as many “latches” in the nonbind variable approach as we did with bind variables. That difference
might beg the question “What is a latch?”
Let's answer that question. A latch is a type of lock that is used to serialize access to shared data structures used
by Oracle. The shared pool is an example; it's a big, shared data structure found in the System Global Area (SGA),
and this is where Oracle stores parsed, compiled SQL. When you modify anything in this shared structure, you must
take care to allow only one process in at a time. (It is very bad if two processes or threads attempt to update the same
in-memory data structure simultaneously—corruption would abound). So, Oracle employs a
latching
mechanism,
a lightweight locking method to serialize access. Don't be fooled by the word lightweight. Latches are serialization
devices, allowing access (to a memory structure) one process at a time. The latches used by the hard-parsing
implementation are some of the most used latches out there. These include the latches for the shared pool and for
the library cache. Those are “big time” latches that people compete for frequently. What all this means is that as we
increase the number of users attempting to hard parse statements simultaneously, our performance gets progressively
worse over time. The more people parsing, the more people waiting in line to latch the shared pool, the longer the
queues, the longer the wait.
Executing SQL statements without bind variables is very much like compiling a subroutine before each method
call. Imagine shipping Java source code to your customers where, before calling a method in a class, they had to
invoke the Java compiler, compile the class, run the method, and then throw away the bytecode. Next time they
wanted to execute the same method, they would do the same thing: compile it, run it, and throw it away. You would
never consider doing this in your application; you should never consider doing this in your database either.
