Oracle DBA – A lifelong learning experience

aud$ and ASSM – a good combination

Posted by John Hallas on March 6, 2014

We had a 8 node RAC cluster that was showing evidence of connections timing out. The following AWR report segments indicates a high level of login activity and blocking on the AUD$ table and segments.

ex3

ex1

ex2

None of it conclusive but what was the kicker was the fact that the aud$ table was still in the system tablespace which is manually  managed and therefore automatic segment space management is not coming into play.

Over a 24 hour period there were over 50K connections, all being audited and the aud$ table was on the system tablespace which was manual and not running ASSM.

On all our systems we migrate the aud$ table to an ASSM managed tablespace (normally SYSAUX) after the build but this database had been delivered by a 3rd party and was not under our direct control.

I was pretty certain that moving that table would relieve the symptons of contention on the aud$ segments, which were being exaggerated by the 8 RAC nodes.

The following test case indicates the differences. I have taken the code used from a site which discusses freelist contention and ASSM

Create a test table in  TEST a manually managed tablespace, run some throughput through and monitor through AWR.

Create a new table in an auto managed tablespace, repeat the tests and compare results

drop table test;
 create table test (
   x date,
   y char(255) default 'x'
 )
 storage (freelists 1)
 tablespace test;

exec dbms_workload_repository.create_snapshot();

declare
     l_job number;
 begin
     for i in 1 .. 15
     loop
         dbms_job.submit( l_job, 'do_insert;' );
     end loop;
     commit;
 end;
 /

select substr(job,1,4) "job",
        substr(schema_user,1,10) "user",
        substr(to_char(last_date,'dd.mm.yyyy hh24:mi'),1,16) "last date",
        substr(to_char(next_date,'dd.mm.yyyy hh24:mi'),1,16) "next date",
        substr(broken,1,2) "b",
        substr(failures,1,6) "failed",
        substr(what,1,32) "command"
   from dba_jobs;

The AWR top events from the first run were

Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
buffer busy waits                 1,876,080       2,873      2   63.7 Concurrency
enq: HW - contention                227,951       1,091      5   24.2 Configuration
DB CPU                                              469          10.4
db file sequential read             118,194         108      1    2.4 User I/O
log file switch (checkpoint in           60          10    166     .2 Configuration

followed by the second run using  the tablespace that was ASSM managed

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU                                            1,696          24.4
log file switch (checkpoint in        1,775         938    528   13.5 Configuration
db file sequential read             724,504         630      1    9.1 User I/O
log file switch completion            2,344         434    185    6.3 Configuration
buffer busy waits                   987,073         260      0    3.7 Concurrency

The overall result was that we had a similar number of logins, no busy segments on the aud$ table and no timeouts. Job done

About these ads

6 Responses to “aud$ and ASSM – a good combination”

  1. Paul Matuszyk said

    Indeed – if you have auditing enabled having it in system tablespace is a very bad idea. oracle warns about it.

    • John Hallas said

      True, but originally they did not support moving the aud$ table – they do now and in 11GR2 have brought in the DBMS_AUDIT_MGMT package to do so.

      • Paul Matuszyk said

        don’t remember when this feature was introduced but when you upgrade to 11gr2 they ask you to move it to sysaux.

  2. SutoCom said

    Reblogged this on Sutoprise Avenue, A SutoCom Source.

  3. DBA said

    Nice One……

    http://exadatacertification.blogspot.com

  4. Nabil Jamaleddin said

    aud$ and ASSM – a good combination? I am not so sure. We just moved aud$ from system to an ASSM tablespace and when we purge aud$ our production system has severe performance issues. We are using Linux 5 on an oracle 11.2.0.3 RAC database. I am getting ready to move aud$ back to a MSSM tablespace to see if that helps. Has anyone else seen this type of behavior?

    Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 203 other followers

%d bloggers like this: