7 Replies Latest reply: Jul 18, 2013 3:21 AM by Nikolay Savvinov RSS

    Events in AWR report

    user10274093

      Hi,

      on 11.2.0.3 on Win 2008.

      At the beginig of AWR report we see this :

      Top 5 Timed Foreground Events

      Event                                 Waits     Time(s)   (ms)   time Wait Class

      ------------------------------ ------------ ----------- ------ ------ ----------

      db file sequential read             435,183         574      1   83.5 User I/O

      DB CPU                                               68           9.9

      direct path read                     14,642          30      2    4.3 User I/O

      db file scattered read                1,859           9      5    1.2 User I/O

      log file sync                         1,432           3      2     .4 Commit

      Host CPU (CPUs:    2 Cores:    2 Sockets:    2)

       

      Any query that provides the same results ?

      Thank you.

        • 1. Re: Events in AWR report
          Pavel

          hi

           

          scripts that createas AWR contains inside

           

          $ORACLE_HOME/rdbms/admin/awrrpt.sql

          $ORACLE_HOME/rdbms/admin/awrrpti.sql

          ...

           

          regards,
          Pavel

          • 2. Re: Events in AWR report
            Ankit Ashok Aggarwal

            These are the views we can query for same :

             

            select EVENT, TOTAL_WAITS, TIME_WAITED, WAIT_CLASS from V$SYSTEM_EVENT

            where wait_class != 'Idle'

            order by time_waited desc;

            select sid, EVENT, TOTAL_WAITS, TIME_WAITED, WAIT_CLASS

            from V$SESSION_EVENT

            where WAIT_CLASS != 'Idle'

            order by TIME_WAITED;

            • 3. Re: Events in AWR report
              user10274093

              Great, thank you. Let me try.

              • 4. Re: Events in AWR report
                Nikolay Savvinov

                Hi,

                AnkitAshokAggarwal wrote:

                 

                These are the views we can query for same :

                 

                Wrong. The output from these views is basically a random collection of numbers (they contain cumulative stats since the last instance startup which are meaningless unless used to calculate differences between snapshots).

                 

                Best regards,

                Nikolay

                • 5. Re: Events in AWR report
                  Nikolay Savvinov

                  Hi,

                   

                  you can take event counts and total waits from DBA_HIST views, then you'll need to use window functions to calculate snapshot differences. An untested example is shown below:

                  column event_name format a30
                  column "Average wait (ms)" format 999.99
                  column "Time waited (s)" format 999999.99
                  
                  select event_name, total_waits "Waits", time_waited_micro/1e3/total_waits "Average wait (ms)", time_waited_micro/1e6 "Time waited (s)"
                  from
                  (
                      select *
                      from
                      (
                          select inline.*
                          from
                          (
                          select sn.begin_interval_time, 
                                   sn.snap_id, 
                                   e.event_name, 
                                   e.total_waits - lag(e.total_waits) over (partition by e.event_name order by sn.snap_id) total_waits, 
                                   e.time_waited_micro - lag(e.time_waited_micro) over(partition by e.event_name order by sn.snap_id) time_waited_micro
                          from DBA_HIST_SYSTEM_EVENT e,
                                  DBA_HIST_SNAPSHOT sn
                          where e.snap_id = sn.snap_id
                          and wait_class != 'Idle'
                          ) inline
                      ) 
                      where snap_id = (select max(snap_id) from dba_hist_snapshot)
                      order by time_waited_micro desc
                  )
                  where rownum<=5
                  

                   

                  Best regards,   Nikolay

                   

                  Best regards,

                  Nikolay

                  • 6. Re: Events in AWR report
                    user10274093

                    Thank you Nikolay.

                    The DBA_HIST_SYSTEM_EVENT and DBA_HIST_SNAPSHOT tables are empty. Any reason or explanation ?

                    Regards.

                    • 7. Re: Events in AWR report
                      Nikolay Savvinov

                      Hi,

                       

                      these aren't tables, these are views (based on WRH$ tables). The most likely explanations why they are empty is that AWR snapshot job is not running at your system.

                      Use exec dbms_workload_repository.modify_snapshot_settings to configure it.

                       

                      Best regards,

                      Nikolay