Database Reference
In-Depth Information
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
AQPC idle 1 30 30010 36.1
heartbeat redo informer 15 15 1004 18.1
LGWR worker group idle 14 15 1055 17.8
lreg timer 4 12 3001 14.4
CPU time 11 13.4
I included the SGA configuration for reference, but the relevant statistics are as follows:
Elapsed (DB time) time of approximately 15 seconds (0.25 of a minute)
•
•
1,667 hard parses per second
•
Now, if we were to run two of these programs simultaneously, we might expect the hard parsing to jump to about
3,300 per second (we have two CPUs available, after all) and the CPU time to double to perhaps 22 CPU seconds. Let's
take a look:
11 CPU seconds used
Elapsed: 0.27 (mins) Av Act Sess: 1.6
DB time: 0.44 (mins) DB CPU: 0.41 (mins)
...
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
...
Parses: 6,259.8 33,385.3
Hard parses: 3,125.6 16,669.7
...
Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.99 Optimal W/A Exec %: 100.00
Library Hit %: 60.03 Soft Parse %: 50.07
Execute to Parse %: 0.06 Latch Hit %: 98.41
Parse CPU to Parse Elapsd %: 96.28 % Non-Parse CPU: 15.06
...
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
CPU time 23 32.7
LGWR worker group idle 18 16 876 22.8
heartbeat redo informer 15 15 1005 21.8
lreg timer 5 15 3001 21.7
latch: shared pool 15,076 0 0 .6
What we discover is that the hard parsing goes up somewhat, and the CPU time more than doubles. How could
that be? The answer lies in Oracle's implementation of latching. On this multi-CPU machine, when we could not
immediately get a latch, we spun. The act of spinning itself consumes CPU. Process 1 attempted many times to get
a latch onto the shared pool only to discover that process 2 held that latch, so process 1 had to spin and wait for it
(consuming CPU). The converse would be true for process 2; many times it would find that process 1 was holding
