This discussion is archived
1 2 3 Previous Next 30 Replies Latest reply: Nov 29, 2012 6:58 AM by Mark D Powell Go to original post RSS
  • 15. Re: Updating AUD$ consumes most of the time in AWR report.
    nnarimanov Newbie
    Currently Being Moderated
    Don't you think that transactions will be better on assm tablespace than on locally managed system tbs?
  • 16. Re: Updating AUD$ consumes most of the time in AWR report.
    nnarimanov Newbie
    Currently Being Moderated
    Maybe aud$ is being defragmented? If yes, then I think shrink might help. Aslo you can not shrink object while it resides in locally managet tablespace.
  • 17. Re: Updating AUD$ consumes most of the time in AWR report.
    Mark D Powell Guru
    Currently Being Moderated
    Based on limited information it is difficult to be 100% sure of anything however looking at the update in question and its plan from the initial post the problem appears to be a poorly performing query, i.e., a bad plan. The update where clause has an equality on the sessionid which is the leading column of the index. On my system I have about 2 rows per sessionid. Normally I would expect this query to use the index. If the index was in use the table being fragmented would make no difference to the performance of the query via the index. The real question is why the index is not being used?

    You are welcome to have a difference of opinion, but moving the table out of the system tablespace is officailly per Oracle an unsupported operation. Why perform an unsupported operation when it is not the best fix to your issue?

    IMHO -- Mark D Powell --
  • 18. Re: Updating AUD$ consumes most of the time in AWR report.
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    >
    There used to be an index i_aud1 on sessionid but this was dropped in 11.x because the logoff UPDATE on AUD$ was removed and replaced by an INSERT....
    So how come your version is updating?
    The OP is running 11.1.0.6. It's possible that the change you describe appeared in 11.1.0.7 - I certainly have access to an 11.1.0.7 database that was originally 11.1.0.6 that shows some rows where entryid=1 and logoff$time non-null (i.e. the logon row has been updated with the logoff time) - and the dates suggest that this happened when the database was 11.1.0.6.

    Mark's comment about stats making the tablescan look like a good idea to the optimizer may be the basic problem.

    Regards
    Jonathan Lewis
  • 19. Re: Updating AUD$ consumes most of the time in AWR report.
    jgarry Guru
    Currently Being Moderated
    Depends. If the problem is there are millions of rows being scanned, it doesn't matter where the table is. Moving the table was explicitly not supported, even though there are scripts on MOS, until Audit Vault required it. So it depends on what exact version and patch level you have as to whether you can use the DBMS_AUDIT_MGMT purge procedures, which may have their own bugs, and there are additional considerations on adding an index. Starting with 10.2 system is autoalocate LMT, so no, ASSM is not a reason to move it. In fact, if you use a db or tablespace block size other than 8K you run the risk of running into ASSM excessive space allocation bugs. In the end, each site needs someone to manage all this as per local requirements, it is silly to just say "move the table" or "reorganize the table," especially when you have evidence as to what the actual problem is, and there is sometimes conflicting MOS advice. Listen to Mark and John.

    On top of all that, you never know when itanium is going to have some weird platform specific issues.
  • 20. Re: Updating AUD$ consumes most of the time in AWR report.
    andrewmy Journeyer
    Currently Being Moderated
    There can be a significant performance hit, depending on your level of auditing, especially if:
    a) AUD$ has millions of rows
    b) Auditing option is by session instead of by access

    It may be good policy to practice a scheduled housekeep to purge or archive the old audit data.
  • 21. Re: Updating AUD$ consumes most of the time in AWR report.
    nnarimanov Newbie
    Currently Being Moderated
    This is a good advice, we practice it about 5 years in 10g. There's a cron script which runs once in 5 hours and moves archive data to archive table created by me.
  • 22. Re: Updating AUD$ consumes most of the time in AWR report.
    Mark D Powell Guru
    Currently Being Moderated
    andrewmy, I think you got item b backwards. Auditing by access, which creates an audit entry for every touch to an objects, is more expensive than auditing by session which creates just one entry per object per session.

    We have found auditing DDL operations to be extremely cheap. One, in a production environment you should not normally perform DDL other than truncate so there is not much to audit and considering the time it takes to add a column with a default value or rebuild an index the insertion of a single audit row is insignificant. Auditing DML on the other hand is much more expensive. We prefer to use table row triggers and history tables where a record of row data value changes needs to be kept. The only DML auditing we currently perform is on the emergency ID used by developers to change production data. Here for performance reasons we audit only by session.

    HTH -- Mark D Powell --
  • 23. Re: Updating AUD$ consumes most of the time in AWR report.
    andrewmy Journeyer
    Currently Being Moderated
    Speaking only from my (admittedly limited) experience, I found auditing by session to be resource heavy compared to auditing by access if you have DML audits (insert, update, delete) turned on. Analysis of the AWR reports showed that most of the time was taken by updates to the audit table, possibly because every audited DML in the same session triggered an update of the session audit record compared to by access audit trails which are inserts. Keeping the audit table purged or archived every couple of months kept the overhead under control.
  • 24. Re: Updating AUD$ consumes most of the time in AWR report.
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    andrewmy wrote:
    Speaking only from my (admittedly limited) experience, I found auditing by session to be resource heavy compared to auditing by access if you have DML audits (insert, update, delete) turned on. Analysis of the AWR reports showed that most of the time was taken by updates to the audit table, possibly because every audited DML in the same session triggered an update of the session audit record compared to by access audit trails which are inserts. Keeping the audit table purged or archived every couple of months kept the overhead under control.
    I wrote about this after visiting a client running 10g some time last year: http://jonathanlewis.wordpress.com/2011/06/07/audit-excess/ - the first thing I thought of when I read the comment above about "no more indexes" was whether the code had changed in 11g, which version were you running ?

    Regards
    Jonathan Lewis
  • 25. Re: Updating AUD$ consumes most of the time in AWR report.
    Mark D Powell Guru
    Currently Being Moderated
    Checking my 10.2.0.5 environment I see the index I_AUD1 exists. The only 11.2 system I currently have access to does not show the index but auditing is not configured to be in use on that system so it may not be a good check. It would seem strange that no indexes would be available on aud$. It looks like adding a check to what audit rules are in use may be necessary before upgrading to 11g.

    HTH -- Mark D Powell --
  • 26. Re: Updating AUD$ consumes most of the time in AWR report.
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Mark D Powell wrote:
    Checking my 10.2.0.5 environment I see the index I_AUD1 exists. The only 11.2 system I currently have access to does not show the index but auditing is not configured to be in use on that system so it may not be a good check. It would seem strange that no indexes would be available on aud$. It looks like adding a check to what audit rules are in use may be necessary before upgrading to 11g.
    I've just done a quick check on an 11.1.0.7 which doesn't have the index.
    audit all on t1 by session 
    select count(*) from t1;
    /
    /
    /
    /
    Each execution results in and audit record being inserted into aud$ and becoming visible in dba_audit_object with action_name = 'SESSION REC'.
    Not good news for anyone who is on 10g, with object audit by session, and expecting to upgrade to 11g

    Regards
    Jonathan Lewis
  • 27. Re: Updating AUD$ consumes most of the time in AWR report.
    jgarry Guru
    Currently Being Moderated
    Intended. See MOS Known Issues When Using: DBMS_AUDIT_MGMT [ID 804624.1]

    Edit: Also see MOS The Effect Of Creating Index On Table Sys.Aud$ [ID 1329731.1]

    If I had this issue, I would create the index for the purge, then drop it.

    Edit 2: I'd also log a support call and ask why an update is taking place when there aren't supposed to be updates any more. That would be a bug.

    Edited by: jgarry on Nov 28, 2012 9:42 AM

    Edited by: jgarry on Nov 28, 2012 9:43 AM
  • 28. Re: Updating AUD$ consumes most of the time in AWR report.
    Mark D Powell Guru
    Currently Being Moderated
    Joel, I notice the first note states that updates are no longer performed on aud$ but later discusses the condition where updates still exist and stating the problem can be fixed by running: DBMS_AUDIT_MGMT.INIT_CLEANUP.

    Looking at your new addition to your post I read that "Creating additional indexes on SYS objects including table AUD$ is not supported," however, "creating a new index may be beneficial" as the dbms_audit_mgmt purge process can benefit from having one.

    From the scarse amount of information on the init_cleanup this might be an issue only if you upgrade an existing database instead of create a new one via the DBCA with auditing enabled.


    HTH -- Mark D Powell --
  • 29. Re: Updating AUD$ consumes most of the time in AWR report.
    andrewmy Journeyer
    Currently Being Moderated
    Jonathan Lewis wrote:
    I wrote about this after visiting a client running 10g some time last year: http://jonathanlewis.wordpress.com/2011/06/07/audit-excess/ - the first thing I thought of when I read the comment above about "no more indexes" was whether the code had changed in 11g, which version were you running ?
    >

    At the time the testing was done, it was on 10.2.0.4.

Legend

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