Database Reference
In-Depth Information
the latch to the resource it needed. So, much of our processing time was spent not doing real work, but waiting for a
resource to become available. If we page down through the Statspack report to the “Latch Sleep Breakdown” report,
we discover the following:
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------- ------------ ----------- -----------
shared pool 2,296,041 75,240 15,267 60,165
Note how the number 15,267 appears in the
SLEEPS
column here? That number corresponds very closely to the
number of
WAITS
reported in the preceding “Top 5 Timed Events” report.
■
the number of sleeps corresponds
closely
to the number of waits; this might raise an eyebrow. Why not
exactly
? the reason is that the act of taking a snapshot is not atomic; a series of queries are executed gathering statistics
into tables during a Statspack snapshot, and each query is
as of
a slightly different point in time. So, the wait event
metrics were gathered at a time slightly before the latching details were.
Note
Our “Latch Sleep Breakdown” report shows us the number of times we tried to get a latch and failed in the
spin loop. That means the Top 5 report is showing us only the tip of the iceberg with regard to latching issues—the
75,240 misses (which means we spun trying to get the latch) are not revealed in the Top 5 report for us. After
examination of the Top 5 report, we might not be inclined to think we have a hard parse problem here, even though
we have a very serious one. To perform 2 units of work, we needed to use more than 2 units of CPU. This was due
entirely to the fact that we need that shared resource, the shared pool. Such is the nature of latching.
You can see that it can be very hard to diagnose a latching-related issue, unless you understand the mechanics of
how they are implemented. A quick glance at a Statspack report, using the Top 5 section, might cause us to miss the
fact that we have a fairly bad scaling issue on our hands. Only by deeper investigation in the latching section of the
Statspack report will we see the problem at hand.
Additionally, it is not normally possible to determine how much of the CPU time used by the system is due to
this spinning—all we know in looking at the two-user test is that we used 23 seconds of CPU time and that we missed
getting a latch on the shared pool 75,240 times. We don't know how many times we spun trying to get the latch each
time we missed, so we have no real way of gauging how much of the CPU time was spent spinning and how much was
spent processing. We need multiple data points to derive that information.
In our tests, because we have the single-user example for comparison, we can conclude that about 1 CPU
seconds or so was spent spinning on the latch, waiting for that resource. We can come to this conclusion because we
know that a single user needs only 11 seconds of CPU time so two single users would need 22 seconds, and 23
(total CPU seconds) minus 22 is 1.
With Bind Variables
Now I'd like to look at the same situation as presented in the previous section, but this time using a program that
uses significantly less latches during its processing. We'll take that Java program and code it using bind variables. To
accomplish this, we'll change the
Statement
into a
PreparedStatement
, parse a single
INSERT
statement, and then
bind and execute that
PreparedStatement
repeatedly in the loop:
import java.sql.*;
public class instest
{
static public void main(String args[]) throws Exception
{
System.out.println( "start" );
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
