I have trouble interpreting the following AWR.
DB Time is 82.85 min within a 1h snapshot interaval.
- If I check CPU: 1447sec/60 = 24min ahve be consumed. Around 30% of DB Time
- Top Timed Events are reads with 273sec + 168sec + 34sec = 475sec = 8min. Just a small percentage of DB Time
So what has the database done in the remaining 82min -24min - 8min = 50min?
DB Name DB Id Instance Inst Num Startup Time Release RAC
------------ ----------- ------------ -------- --------------- ----------- ---
1 11-Jan-15 19:09 11.2.0.3.0 NO
Host Name Platform CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
xxxxxxxxx AIX-Based Systems (64-bit) 8 2 8.00
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 20849 03-Mar-16 06:00:43 50 2.4
End Snap: 20850 03-Mar-16 07:00:46 48 2.0
Elapsed: 60.04 (mins)
DB Time: 82.85 (mins)
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ --------------- --------------- ---------- ----------
DB Time(s): 1.4 3.7 0.00 0.00
DB CPU(s): 0.4 1.1 0.00 0.00
Redo size: 2,454,102.5 6,592,682.0
Logical reads: 30,625.6 82,272.5
Block changes: 5,082.7 13,654.0
Physical reads: 875.1 2,350.9
Physical writes: 625.5 1,680.4
User calls: 1,166.3 3,133.2
Parses: 7.5 20.2
Hard parses: 2.1 5.5
W/A MB processed: 5.0 13.5
Logons: 0.1 0.4
Executes: 5,213.9 14,006.4
Rollbacks: 0.0 0.0
Transactions: 0.4
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 98.49 In-memory Sort %: 99.98
Library Hit %: 99.89 Soft Parse %: 72.55
Execute to Parse %: 99.86 Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: 28.99 % Non-Parse CPU: 98.94
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU 1,447 29.1
db file scattered read 82,816 273 3 5.5 User I/O
db file sequential read 383,558 168 0 3.4 User I/O
direct path read temp 25,244 34 1 .7 User I/O
library cache lock 438 8 19 .2 Concurrenc
Host CPU (CPUs: 8 Cores: 2 Sockets: )
~~~~~~~~ Load Average
Begin End %User %System %WIO %Idle
--------- --------- --------- --------- --------- ---------
0.61 1.18 10.1 4.0 0.5 85.9
Time Model Statistics DB/Inst: SABADWH/SABADWH Snaps: 20849-20850
-> Total time in database user-calls (DB Time): 4971.2s
-> Statistics including the word "background" measure background process
time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name
Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time 3,701.4 74.5
DB CPU 1,446.6 29.1
PL/SQL execution elapsed time 101.6 2.0
parse time elapsed 53.6 1.1
hard parse elapsed time 28.7 .6
RMAN cpu time (backup/restore) 4.8 .1
PL/SQL compilation elapsed time 3.7 .1
hard parse (sharing criteria) elapsed time 3.2 .1
hard parse (bind mismatch) elapsed time 3.1 .1
failed parse elapsed time 1.1 .0
repeated bind elapsed time 0.5 .0
connection management call elapsed time 0.4 .0
sequence load elapsed time 0.1 .0
inbound PL/SQL rpc elapsed time 0.0 .0
DB time 4,971.2
background elapsed time 667.9
background cpu time 73.0
-------------------------------------------------------------