Author: Hemant K Chitale
Page:
1
2
Operating System statistics in AWR Report:
Oracle 10g made the life of database easy by providing operating system statistics in AWR Reports.
Consider below report for a snap started at 19:48:18 and ended at 20:00:06.
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 211 29-Feb-08 19:48:18 21 3.1
End Snap: 212 29-Feb-08 20:00:06 18 1.9
Elapsed: 11.80 (mins)
DB Time: 0.31 (mins)
The top five timed events are as under
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
RMAN backup & recovery I/O 1,681 82 49 437.9 System I/O
control file sequential read 3,800 20 5 109.4 System I/O
control file parallel write 526 5 9 24.2 System I/O
CPU time 3 16.2
db file sequential read 236 2 8 9.6 User I/O
Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time 15.5 82.7
inbound PL/SQL rpc elapsed time 3.5 18.6
PL/SQL execution elapsed time 3.3 17.5
DB CPU 3.0 16.2
RMAN cpu time (backup/restore) 2.0 10.8
parse time elapsed 0.5 2.9
hard parse elapsed time 0.5 2.6
We can see that the CPU time is mentioned as 3 seconds. This means that server spent time in some other job as well. Below snippet shows the operating system statistics from the AWR Report. The operating system CPU statistics are for 709.76seconds and include the busy as well as idle time. As mentioned above Oracle took only 3seconds of CPU time and hence some other processes utilized about 381.68 seconds that is (384.68-3). The operating system statistics show that the CPU utilisation [including %sys] was 61.21% (434.45 of 709.76 seconds).
Statistic Total
-------------------------------- --------------------
AVG_BUSY_TIME 43,445
AVG_IDLE_TIME 27,531
AVG_SYS_TIME 4,977
AVG_USER_TIME 38,468
BUSY_TIME 43,445
IDLE_TIME 27,531
SYS_TIME 4,977
USER_TIME 38,468
...
NUM_CPUS 1
Lets consider another report of three hours for a snap started at 02:05:00 and ended at 05:04:40.
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 213 25-Feb-08 02:05:00 18 1.9
End Snap: 216 25-Feb-08 05:04:40 18 1.9
Elapsed: 179.67 (mins)
DB Time: 0.09 (mins)
Below top five timed events show that the CPU time for Oracle is 3 seconds.
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
control file sequential read 6,096 7 1 124.0 System I/O
control file parallel write 3,593 4 1 77.7 System I/O
CPU time 3 50.4
db file parallel write 1,790 1 1 25.7 System I/O
db file sequential read 49 1 13 12.0 User I/O
Below operating system statistics show that CPU %utilisation [including %sys]
on the server is 66.90% as it consumed 7212.22 seconds out of 10,779.97seconds. The server seem to be quite busy and we saw that these CPU cycles are consumed by some other application other than Oracle. Statistic Total
-------------------------------- --------------------
AVG_BUSY_TIME 721,220
AVG_IDLE_TIME 356,777
AVG_SYS_TIME 5,531
AVG_USER_TIME 715,689
BUSY_TIME 721,220
IDLE_TIME 356,777
SYS_TIME 5,531
USER_TIME 715,689
NUM_CPUS 1 Read Again!!
Page:
1
2
More Database Articles
Database Security: Step by step guideline
Avoiding Rollback Generation in Bulk Delete!!
Oracle – SQL: Performance Boost with Collections
The Power of Oracle Virtual Column!!
Oracle Multitable Inserts, Important Concerns!!
|
