1 Reply Latest reply: Nov 16, 2012 2:19 AM by Mohamed Houri RSS

    AWR report

    user10274093
      Hi,
      please help me to understand my AWR report.

      First the waits :
       
      Top 5 Timed Foreground Events 
      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
                                                                 Avg 
                                                                wait   % DB 
      Event                                 Waits     Time(s)   (ms)   time Wait Class 
      ------------------------------ ------------ ----------- ------ ------ ---------- 
      db file sequential read              13,779         120      9   31.4 User I/O 
      DB CPU                                              113          29.6 
      direct path read                     13,402          62      5   16.2 User I/O 
      db file scattered read                1,616          25     15    6.4 User I/O 
      Disk file operations I/O                365           9     25    2.4 User I/O 
      Host CPU (CPUs:    1 Cores:    1 Sockets:    1) 
      Does it mean :
      1-the longest wait was for db file sequential read (then using index) and it has taken 120 seconds ? And it happened 13779 times ?

      To find which SQL has result in that (db file sequential read ) I go further in report until :
       
      SQL ordered by User I/O Wait Time    DB/Inst: MYDB/mydb  Snaps: 1391-1393 
      -> Resources reported for PL/SQL code includes the resources used by all SQL 
         statements called by the code. 
      -> %Total - User I/O Time as a percentage of Total User I/O Wait time 
      -> %CPU   - CPU Time      as a percentage of Elapsed Time 
      -> %IO    - User I/O Time as a percentage of Elapsed Time 
      -> Captured SQL account for   76.8% of Total User I/O Wait Time (s): 
      -> Captured PL/SQL account for   32.5% of Total User I/O Wait Time (s): 
      
        User I/O                UIO per           Elapsed 
        Time (s)  Executions    Exec (s) %Total   Time (s)   %CPU    %IO    SQL Id 
      ---------- ------------ ---------- ------ ---------- ------ ------ ------------- 
            43.8 ;       1,347       0.03 ;  15.3 ;      56.6 ;  14.4 ;  77.4 4rc7d8c0mvfm8 
      Module: PSAPPSRV.exe 
      SELECT 
      Is this sql with SQL ID=4rc7d8c0mvfm8 the main have been waiting for db file sequential read above ?

      Thanks and regards.
        • 1. Re: AWR report
          Mohamed Houri
          Top 5 Timed Foreground Events 
          ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
                                                                     Avg 
                                                                    wait   % DB 
          Event                                 Waits     Time(s)   (ms)   time Wait Class 
          ------------------------------ ------------ ----------- ------ ------ ---------- 
          db file sequential read              13,779         120      9   31.4 User I/O 
          DB CPU                                              113          29.6 
          direct path read                     13,402          62      5   16.2 User I/O 
          db file scattered read                1,616          25     15    6.4 User I/O 
          Disk file operations I/O                365           9     25    2.4 User I/O 
          Host CPU (CPUs:    1 Cores:    1 Sockets:    1) 
          You have to know that in an AWR report there will be always a Top 5 Timed Events in case of a system experiencing a performance problem or working perfectly. This is why you should at least post the duration of this AWR snapshot and the Load Profile.
          -the longest wait was for db file sequential read (then using index) and it has taken 120 seconds ? And it happened 13779 times ? 
          Yes.

          You have also an average wait time of single block read of 9 milli-second. This is not very bad but not very good also. It could be better.
          SQL has result in that (db file sequential read ) I go further in report until :
          Generally speaking there is a coherence (a close relation) between db file sequential read and the part of the AWR entitled "SQL Ordered by Gets"


          You have also a relatively high number of direct path read. The section of the AWR report that has a correlation with the direct path read event and which is worth investigating is SQL ordered by reads, Segments by physical reads and (if in 11gR1 or higher), Segments by direct physical reads.

          Some more details can be found here

          http://hourim.wordpress.com/category/trouble-shooting/

          Best Regards

          Mohamed Houri
          www.hourim.wordpress.com