Hi,
My end users are claim for performance problem during execution of batch process.
As you can see there are 1,745 statement executing each second.
Awr report shows 98.1% of the time , waits on CPU .
Also Awr report shows that Host CPU is :79.9% Idel.
The second wait event shows only 212 seconds waits on db file sequential read.
Yet , 4 minute in 1 hour period is seems not an issue.
Please advise
DB Name DB Id Instance Inst Num Startup Time Release RAC
------------ ----------- ------------ -------- --------------- ----------- ---
QERP xxx erp 1 21-Jan-13 15:40 11.2.0.2.0 ; NO
Host Name Platform CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
erptst HP-UX IA (64-bit) 16 16 4 127.83
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 40066 22-Jan-13 20:00:52 207 9.6
End Snap: 40067 22-Jan-13 21:00:05 210 9.6
Elapsed: 59.21 (mins)
DB Time: 189.24 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 8,800M 8,800M Std Block Size: 8K
Shared Pool Size: 1,056M 1,056M Log Buffer: 49,344K
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ --------------- --------------- ---------- ----------
DB Time(s): 3.2 ; 0.1 ; 0.00 ; 0.05
DB CPU(s): 3.1 ; 0.1 ; 0.00 ; 0.05
Redo size: 604,285.1 ; 27,271.3
Logical reads: 364,792.3 ; 16,463.0
Block changes: 3,629.5 ; 163.8
Physical reads: 21.5 ; 1.0
Physical writes: 95.3 ; 4.3
User calls: 68.7 ; 3.1
Parses: 212.9 ; 9.6
Hard parses: 0.3 ; 0.0
W/A MB processed: 1.2 ; 0.1
Logons: 0.3 ; 0.0
Executes: 1,745.2 ; 78.8
Rollbacks: 1.2 ; 0.1
Transactions: 22.2
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 ; Redo NoWait %: 100.00
Buffer Hit %: 99.99 ; In-memory Sort %: 100.00
Library Hit %: 99.95 ; Soft Parse %: 99.85
Execute to Parse %: 87.80 ; Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: 74.76 ; % Non-Parse CPU: 99.89
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 75.37 ; 76.85
% SQL with executions>1: 95.31 ; 85.98
% Memory for SQL w/exec>1: 90.33 ; 82.84
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU 11,144 98.1
db file sequential read 52,714 214 4 1.9 User I/O
SQL*Net break/reset to client 29,050 6 0 .1 Applicatio
log file sync 2,536 6 2 .0 Commit
buffer busy waits 4,338 2 1 .0 Concurrenc
Host CPU (CPUs: 16 Cores: 16 Sockets: 4)
~~~~~~~~ Load Average
Begin End %User %System %WIO %Idle
--------- --------- --------- --------- --------- ---------
0.34 ; 0.33 ; 19.7 ; 0.4 ; 1.8 ; 79.9