Dontcheff

Auditing vs. Performance in the Oracle Database

In Bugs, Database tuning, DBA, Oracle database, Security and auditing on May 12, 2011 at 02:24

You show this (part of a) AWR report to the DBA and he proudly concludes: disable auditing, it is killing the performance! And thus, quite often Oracle database auditing is not enabled. And here are the 3 main reasons why auditing is not turned on:

- DBAs, developers, etc. are not familiar with this feature: For those who are not familiar with auditing, I suggest Tim Hall’s and Pete Finnigan’s articles: Auditing in Oracle 10g Release 2 and Introduction to Simple Oracle Auditing.
- Security is not considered important and necessary: For those who do not consider auditing important, I wish them luck. They are anyway not interested in what I have to say..
- Performance is being hit by enabling auditing: For the ones having issues with performance when auditing is enabled, here is something.

There are 3 major reasons why performance suffers when auditing is enabled: too much is being audited, AUD$ still hangs in the SYSTEM tablespace and surprise, surprise: the Oracle bugs.

1. Too much is being audited. If it is a new database, spend some time with all parties involved on what to audit. The reality however is something like that: go-live day is getting closer, oh do we have auditing enabled? How do you enable it, can you give me the command please. And it should not go like that. You first decide on the value of audit_trail and then audit what is really needed, do not audit repetitive commands that generate too many inserts into the AUD$ table for it can grow very fast indeed.

Have a look at this thread from Pete Finnigan’s site called Performance Impact of Auditing.

If it is an existing database, check first what is being audited. To find out system audited stuff run the following:

select * from DBA_PRIV_AUDIT_OPTS
union all
select * from DBA_STMT_AUDIT_OPTS;

Note that the difference between the two views above is very small and I have not found yet a place with explanation about the difference. The documentation says that DBA_STMT_AUDIT_OPTS describes the current system auditing options across the system and by user while DBA_PRIV_AUDIT_OPTS describes the current system privileges being audited across the system and by user. Puzzled? Me too.

For example, AUDIT SYSTEM belongs only to DBA_PRIV_AUDIT_OPTS while PROFILE, PUBLIC SYNONYM, DATABASE LINK, SYSTEM AUDIT, SYSTEM GRANT and ROLE belong only to DBA_STMT_AUDIT_OPTS.

On the other hand, CREATE PUBLIC DATABASE LINK, EXEMPT ACCESS POLICY, CREATE EXTERNAL JOB, DROP USER and ALTER DATABASE belong to both views, get it :-)

For the auditing options on all objects, check DBA_OBJ_AUDIT_OPTS.

Check the Oracle 11gR2 documentation for the Recommended Audit Settings.

2. AUD$ still hangs in the SYSTEM tablespace. The system tablespace might be fragmented. Starting 11gR2, Oracle supports moving the AUD$ table out of the SYSTEM tablespace. But first, noaudit your policy or stop the auditing.

If using 11.2.0 and above follow the documentation instruction.

If still running 11.1.0 or a below, here is how to do it:

create tablespace AUDIT_DATA datafile ...;
create table AUDX tablespace AUDIT_DATA as select * from AUD$;
rename AUD$ to AUD$$;
rename AUDX to AUD$;
create index i_aud2 on AUD$(sessionid, ses$tid) tablespace AUDIT_DATA;

Remember to purge the records on regular basis. Do not just delete them but move them to a centralized auditing repository. Use the new DBMS_AUDIT_MGMT package. Check Tim Hall’s instructionon how to purge audit trail records. In urgent cases, it is safe to run truncate table AUD$;

If you use FGA, remember to move also FGA_LOG$ away from the SYSTEM tablespace:

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
    audit_trail_location_value => 'AUDIT_DATA');
END;
/

And I would recommend this article by Martin Widlake: Why is my SYSTEM Tablespace so Big?! The 3rd SYSTEM table after SYS.AUD$ and SYS.HISTGRM$ that I have seen to grow up is SYS.IDL_UB1$.

3. Oracle bugs. If you enable auditing you might get several bugs for free, most old ones should be fixed in 11.2.0.2, don’t know about the new ones :-)

20 years ago, Bug 52646: audit trail degrades performances too much was solved in Oracle 6:

Well, you still have many similar ones. As of today, all these bugs have empty field for “Fixed in Product Version”. And this is not the complete list!

Bug 10347785: huge version count for insert into sys.aud$ causing library cache: mutex x/hang
Bug 504968: ora-600[17051] and database crash when writing to audit table
Bug 11901734: dbms_audit_mgmt audit trail cleanup cannot keep up with aud$ volume
Bug 8236755: ora-00060 occurs while updating aud$ table.
Bug 6159102: export session spins when logging off in repeated update sys.aud$ statements
Bug 6334058: deadlock with ora-00060 while updating sys.aud$ and auditing on select is enable
Bug 4405301: too many entries in aud$ when sequence is queried and audit is by session
Bug 1257564: noaudit does not turn off auditing of database (very nice indeed!)

I wish Oracle will create one bug called “Performance issues with the AUD$ table”, solve it so finally no one complains about the performance of one simple table which in my opinion is not even a real dictionary table.

Question: In RAC, sessions from both/all nodes are being audited. Will AUD$ hot blocks “stuffed” with new data be ping-ponged via the interconnect?

About these ads
  1. Answer: I guess – not too much, at least with ASSM. After all, the auditing information is only written, (almost) never read by the database.

  2. Can the SYSTEM tablespace be ASSM :-)

  3. It’s not supported, AFAIK. But if you are using auditing in a busy production system, you have to move AUD$ out of the SYSTEM tablespace anyway.

  4. Yavor sent me the following quote: “ASSM has been designed specifically for a RAC environment. The bitmap hierarchy is structured so that each instance can use a different first level bitmap block to allocate and deallocate space within blocks, thus reducing contention. In addition, each instance will also insert new rows into a different range of blocks, also reducing contention for blocks.”

    Now, how about if AUD$ is still in the SYSTEM tablespace and nobody modified the default value of 1 for freelists and freelist groups?

  5. Hi Dontcheff,

    Thanks for your post. It’s excellent and very clear.
    It helped me while I’m designing the Auditing in my Oracle database.

    Cheers,
    Roni.

  6. Thanks for the blog.
    I thought you may be interested in a new bug that was introduced in 11g and will only be fixed in 11.2.0.4. When auditing is turned on, it slows down inserts by 50 times. Bug 13524613. The only way not to be impacted by this bug in 11g is to set autit_trail=none, or request an interim patch

  7. Julian, thank you for this article that could have come from the bottom of my heart! Just the other week, I was called by a customer to do performance firefighting on a production DB and found the exact scenario you described above.
    The production DBA claimed that he only enabled 11.2′s default auditing, but I found that nearly everything was audited, including every “delete” and “update” statement (and there were lots of them). We haven’t found the root cause of the overblown audit options yet, but I suppose they were introduced by some patch or security fix. 50 GB of audit data in 3 days and no purging active…

    #2 in the Top SQL, by the way, was a “select from sys.aud$” that is issued regularly by Oracle Enterprise Manager, probably to generate security alerts on its dashboard. In the end, the instance was more concerned with auditing than with its applications.

    Cheers,
    Uwe

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 246 other followers

%d bloggers like this: