This discussion is archived
1 2 3 Previous Next 30 Replies Latest reply: Nov 29, 2012 6:58 AM by Mark D Powell RSS

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

940856 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points