Re: Oracle.exe=high CPU utilization on database logoff? Yes, logoff!

"srivenu" <srivenu_at_hotmail.com> wrote in message news:1a68177.0402050426.4b88957a_at_posting.google.com...
> You can trace a session at level 8 or 12 to see the waits.
> Do you have any logoff triggers ?
> regards
> Srivenu

Srivenu, I don't think I have any logoff triggers... I had searched the all_triggers and dba_triggers views' triggering_event columns, but didn't see any. That covers all the triggers, right? Is there anywhere else they could hide on me?

But you hit the answer! Thanks for the tip on session trace! I connected thru SqlPlus, set the trace level to 8, and disconnected. Then I went to the init.ora's user_dump_dest directory and picked up my trace file- Wow! It's definately the audit table! I have over 1000 'db file scattered read' events!

I did some quick poking around; turns out the poor thing is doing a full table scan of the aud$ table. Apparently when the experts came out to install our database, they moved the audit table out of the SYS tablespace into it's own tablespace. Guess what- there's no index on aud$! My guess is that they didn't know about the 'alter table mytable move tablespace newtblspace' command... so they probably manually created the second aud$ table, inserted all the data into it, and forgot to recreate the index.

So I've got some work ahead... I'm going to kill auditing from the init.ora file, cut down the current aud$ table into the useful stuff (such as the entries at the beginning, when the system was installed- or when any table changes were made). Then I'll create another tablespace, create an audit_old table and copy the data in (probably with nologging). Then I'll mark that new tablespace as read-only and take it offline. Finally I'll truncate the original aud$ table, re-create the index on aud$ for sessionid and sess$tid, and set up explicit auditing instead of auditing the whole darn database. Anyone see any problems with this plan?

Thanks! I have to wonder if this over-extensive auditing was meant "just" for installation, and was supposed to be turned off when the installation was finished.

-Thomas Received on Fri Feb 06 2004 - 09:09:18 CST