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

Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.93 Optimal W/A Exec %: 100.00
Library Hit %: 99.97 Soft Parse %: 96.88
Execute to Parse %: 99.74 Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: 100.00 % Non-Parse CPU: 99.66
...
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
AQPC idle 1 30 30012 74.6
heartbeat redo informer 4 4 1010 10.0
lreg timer 1 3 3000 7.5
CPU time 3 7.3
log file parallel write 22 0 5 .3

The amount of CPU time is about 2 to 3 times the amount reported by the single-user test case.

■ Due to rounding, the 1 CpU seconds is really anywhere from 0 to 2, and the 3 is really anywhere
from 2 to 4 seconds.

Note

Further, the amount of CPU used by two users with bind variables is far less than half the amount of CPU a single
user not using bind variables required! When I looked at the latch report in this Statspack report, I found there was
so little contention for the shared pool and library cache that it was not even worth reporting. In fact, digging deeper
turned up the fact that the shared pool latch was requested 50,511 times versus well over 2.2 million times in the
preceding two-user test without binds:

Latch Name Requests Misses Sleeps Gets
-------------------------- --------------- ------------ ----------- -----------
shared pool 50,511 48 1 47

Performance/Scalability Comparison
Table 3-1 summarizes the CPU usage by each implementation, as well as the latching results as we increase the
number of users beyond two. As you can see, the solution using fewer latches (binds) will scale much better as the
user load goes up.