1 2 3 Previous Next 30 Replies Latest reply: Nov 29, 2012 8:58 AM by Mark D Powell RSS

    Updating AUD$ consumes most of the time in AWR report.

    940856
      Hi All,

      It's really good to see, great people passing their help to folks like us and making our life easier. Going forward,I am investigating on of the performance issue and analyzing the AWR report. By looking AWR, I did find updating aud$ taking most of times in AWR report. Following are the information , I extracted from the database and AWR report. Please see, what can be done to take away the bottlenecks.
      Version --  11.1.0.6.0 
      OS      --  HPUXX Itanium
      
      Event                                 Waits     Time(s)   (ms)   time Wait Class
      ------------------------------ ------------ ----------- ------ ------ ----------
      enq: BF - allocation contentio        9,007       6,893    765   45.5 Other
      DB CPU                                            2,565          16.9
      db file scattered read              555,031       2,428      4   16.0 User I/O
      read by other session               288,910       1,428      5    9.4 User I/O
      PX Deq Credit: Session Stats         22,650         231     10    1.5 Other
      
      
      
      209fr01svbb5s
      
                                                                wait   % DB
      Event                                 Waits     Time(s)   (ms)   time Wait Class
      ------------------------------ ------------ ----------- ------ ------ ----------
      db file scattered read              291,023       1,973      7   61.6 User I/O
      DB CPU                                              890          27.8
      read by other session                81,495         340      4   10.6 User I/O
      log file sync                         1,210          21     17     .6 Commit
      db file sequential read              30,452          15      0     .5 User I/O
      
      
      --------------------
      
        Elapsed      CPU                  Elap per  % Total
        Time (s)   Time (s)  Executions   Exec (s)  DB Time    SQL Id
      ---------- ---------- ------------ ---------- ------- -------------
           3,134        833          118       26.6    97.9 209fr01svbb5s
      update sys.aud$ set action#=:2, returncode=:3, logoff$time=cast(SYS_EXTRACT_UTC(
      systimestamp) as date), logoff$pread=:4, logoff$lread=:5, logoff$lwrite=:6, logo
      ff$dead=:7, sessioncpu=:8 where sessionid=:1 and entryid=1 and action#=100     
      
      -------------------------- Plan from Cursor ---------------------------------------
      
      SQL_ID  209fr01svbb5s, child number 0
      -------------------------------------
      update sys.aud$ set action#=:2, returncode=:3,
      logoff$time=cast(SYS_EXTRACT_UTC(systimestamp) as date),
      logoff$pread=:4, logoff$lread=:5, logoff$lwrite=:6, logoff$dead=:7,
      sessioncpu=:8 where sessionid=:1 and entryid=1 and action#=100
      
      Plan hash value: 1651467381
      
      ---------------------------------------------------------------------------
      | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
      ---------------------------------------------------------------------------
      |   0 | UPDATE STATEMENT   |      |       |       |     2 (100)|          |
      |   1 |  UPDATE            | AUD$ |       |       |            |          |
      |*  2 |   TABLE ACCESS FULL| AUD$ |     1 |   139 |     2   (0)| 00:00:01 |
      ---------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         2 - filter(("ENTRYID"=1 AND "ACTION#"=100 AND "SESSIONID"=:1 AND
                    ("SPARE2" IS NULL OR USERENV('ISDBA')='TRUE')))
      
      ++++ Last Anylzsed +++++
      TABLE_NAME                     LAST_ANAL
      ------------------------------ ---------
      AUD$                           08-NOV-07
      
      
      ++++++++ Table Size ++++++++++++++++++++++
      SQL> select sum(bytes)/1024/1024 "Audit Size" from dba_segments where segment_name='AUD$';
      
      Audit Size
      ----------
            2469
      Regards
        1 2 3 Previous Next