The appearance of all the ‘S’ markers in my output is because I actually ran the two earlier commands in the order shown, so the ‘by session’ command after the ‘by access’ command. If I’d run the commands the other way round then you would have seen ‘A’s everywhere.

Each of the columns (‘ALT’,’SEL’, etc.) represents an action that can be audited, and the column content shows when and why it will be audited. The significance of the “X/X” is that the default audit is “always” but could be limited to “whenever successful” (the first ‘X’) or “whenever not successful” (the second ‘X’).

The question is this: is auditing by session cheaper than auditing by action ? Your initial thought is that it probably ought to be because you only need one row per audited object per session when auditing by session, not one row per session per access – but how much do you really save ? When in doubt, one of the first tests is to run a little code with sql_trace enabled.

Here, from 10.2.0.3, is the interesting bit from tkprof after running “select * from t1″ eleven times – enabling sql_trace after the first select so that I can see the work done by auditing for the last ten selects:

If you thought that auditing by session was a light-weight operation, think again. Interestingly, the auditing anomaly that I reported recently re-appears in the “audit by session” option. You will recall that when auditing is enabled, Oracle turns “update row” into “lock row, update row” – this happens to the audit table (aud$) as well, so every update to an aud$ row generates two redo entries. Here’s a comparison of the redo generated (by 10.2.0.3) for the two tests:

The results are startlingly similar. For “audit by access” we see one redo entry for the table insert, one for the index insert, one for the commit; for “audit by session” we see one redo entry for the “lock row”, one for the “update row”, one for the commit. You may recall, by the way, that one of the comments on my blog about the lock/update anomaly pointed out that this bug was limited to 10.2.0.1 – 10.2.0.3; that may be true most of the time, but when you get to the aud$table the same lock/update pattern reappears.

And here’s a final kicker when you think about auditing: sometimes it seems like a good idea to write a pl/sql function with an embedded query that you can use in a select list rather than doing an outer join to a table in the from clause. If you enable auditing on the embedded table, every call to the pl/sql function results in an insert (or update) of a row in the audit table.

Footnote: If you want to see what the three-letter column names in the user_obj_audit_opts view mean, you can run a query like this:

Update 26th Nov:

For news of a threat on upgrading from 10g to 11g (post 11.1.0.6) – see this thread on OTN. The table aud$ loses its index in 11.1.0.7, and any updates are (probably) replaced by an insert – so audit all on t1 by session turns into an insert for every access to t1; the impact on undo and redo doesn’t change all that much, but some people may see the size of the aud$ table increase dramatically.