Auditing vs. Performance in the Oracle Database

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 🙂

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.

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:

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?

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?

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

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.

Julian is the Global Database Lead of Accenture. His primary responsibility is managing and leading the Global Oracle Technology Practice which includes Autonomous Cloud, IaaS, PaaS, Database Services, Engineered Systems, Java, Middleware, Security and all other areas falling under Oracle Technology. He is also the Accenture-Enkitec Group Managing Director for ... Continue reading →