The more people waiting line latch the shared pool

Chapter 1 ■ Developing SuCCeSSful oraCle appliCationS

Now, the only difference between the two is that one uses a bind variable and the other does not. Both are using dynamic SQL and the logic is otherwise identical. The only difference is the use of a bind variable in the first.

EODA@ORA12CR1> exec proc2
PL/SQL procedure successfully completed.

EODA@ORA12CR1> exec runstats_pkg.rs_stop(9500)
Run1 ran in 34 cpu hsecs
Run2 ran in 432 cpu hsecs
run 1 ran in 7.87% of the time

Now, the preceding result clearly shows that based on CPU time, it took significantly longer and significantly more resources to insert 10,000 rows without bind variables than it did with them. In fact, it took more than a magnitude more CPU time to insert the rows without bind variables. For every insert without bind variables, we spent the vast preponderance of the time to execute the statement simply parsing the statement! But it gets worse. When we look at other information, we can see a significant difference in the resources utilized by each approach:

Name Run1 Run2 Diff STAT...CCursor + sql area evic 2 9,965 9,963 STAT...enqueue requests 35 10,012 9,977 STAT...enqueue releases 34 10,012 9,978 STAT...execute count 10,020 20,005 9,985 STAT...opened cursors cumulati 10,019 20,005 9,986 STAT...table scans (short tabl 3 10,000 9,997 STAT...sorts (memory) 3 10,000 9,997 STAT...parse count (hard) 2 10,000 9,998 LATCH.session allocation 5 10,007 10,002 LATCH.session idle bit 17 10,025 10,008 STAT...db block gets 10,447 30,376 19,929 STAT...db block gets from cach 10,447 30,376 19,929 STAT...db block gets from cach 79 20,037 19,958 LATCH.shared pool simulator 8 19,980 19,972 STAT...calls to get snapshot s 22 20,003 19,981

PL/SQL procedure successfully completed.

The runstats utility produces a report that shows differences in latch utilization as well as differences in statistics.