Lock Types - Oracle Database Transactions and Locking Revealed - page 48

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());
Connection
conn = DriverManager.getConnection
("jdbc:oracle:thin:@heesta:1521:ORA12CR1", "scott","tiger");
conn.setAutoCommit( false );
PreparedStatement pstmt =
conn.prepareStatement
("insert into "+ args[0] + " (x) values(?)" );
for( int i = 0; i < 25000; i++ )
{
pstmt.setInt( 1, i );
pstmt.executeUpdate();
}
conn.commit();
conn.close();
System.out.println( "done" );
}
}