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

Let's look at the single and dual user Statspack reports, as we did for the no bind variable example. We'll see
dramatic differences here. Here is the single-user report:
Elapsed: 0.07 (mins) Av Act Sess: 0.6
DB time: 0.04 (mins) DB CPU: 0.03 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 2,656M Std Block Size: 8K
Shared Pool: 640M Log Buffer: 14,808K
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
...
Parses: 158.5 317.0
Hard parses: 29.8 59.5
...
Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 98.99 Optimal W/A Exec %: 100.00
Library Hit %: 96.14 Soft Parse %: 81.23
Execute to Parse %: 97.72 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 87.10 % Non-Parse CPU: 71.58
...
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
AQPC idle 1 30 30004 66.6
lreg timer 2 6 3004 13.3
heartbeat redo informer 4 4 1006 8.9
LGWR worker group idle 12 4 331 8.8
CPU time 1 2.1
That is quite dramatic: from 11 CPU seconds in the no bind variables example to 1 CPU second here. From
1,667 hard parses per second to about 29 per second (and based on my knowledge of how Statspack works, most of those
were from running Statspack). Even the elapsed time was dramatically reduced from about 15 seconds down to 4 seconds
(0.07 minutes). When not using bind variables, we spent ten-elevenths of our CPU time parsing SQL (1 second versus 11).
This was not entirely latch related, as much of the CPU time incurred without bind variables was spent parsing and
optimizing the SQL. Parsing SQL is very CPU intensive, but to expend ten-elevenths of our CPU doing something (parsing)
that doesn't really do useful work for us—work we didn't need to perform—is pretty expensive.
When we get to the two-user test, the results continue to look better:
Elapsed: 0.08 (mins) Av Act Sess: 0.9
DB time: 0.07 (mins) DB CPU: 0.07 (mins)
...
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
...
Parses: 25.6 42.7
Hard parses: 0.8 1.3
...