Buffer Nowait – shows how often buffer cache were accessed with no wait time.
Buffer Hit – shows how often a requested block has been found in the buffer cache without requiring disk access.
Redo NoWait – shows if log_buffer size is set correctly. Preemptive redolog switches in Oracle 11.2
Parse CPU to Parse Elapsd – shows how much time was spent on parsing while waiting for resources.
Non-Parse CPU – in the following example the the figure is close to 100% meaning that the overall CPU usage is only 0.15 % for statement parsing.
5 Shared Pool Statistics – shows if there is an overhead on the system regarding shared pool.
The values should not be very high (preferably less than 75%).
6 Top 5 Timed Foreground Events – Shows top 5 wait events that are taking the most of time. The exact meaning of each and every event can be found here.
Based on what we found in here 2 other sections in the report must be checked.
In the following example a long time (70%) is spent for waiting on I/O related reads. There are 12,727 waits in 265 seconds (4 minutes) which is more significant than 3 million waits in 36,388 second (10 hours).
As well only 8% of the time has been spent on DB CPU. If this time is significant check the following points:
7 SQL ordered by Elapsed Time – Shows which SQL statement runs for a longer time. Those statement needs to be focused that have less numbers of execution with high Elapsed Time per Exec (s). More over check if the % total is significant.
8 SQL ordered by CPU Time – contains information on which SQL statement takes the most CPU time
Total DB time
9 &10 IO Stats – Check if read and write of the datafiles /logs is taking longer. Section 6 reveals some information about the waits for I/O, wether this is a good number or not is dependent on the Hardware/OS. If the I/O is slow check "Tablespace IO Stats" and "File IO stats". "Rd(ms)" columns must not exceed 20 otherwise it worths involving your OS team and hardware vendor to investigate the I/O bottleneck.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
18 | |
12 | |
11 | |
8 | |
7 | |
6 | |
6 | |
6 | |
6 | |
6 |