CODEWITHSUNDEEP
oracle-database
mc88
mc88

Reputation: 81

Oracle sys.aud$ / dba_audit_session monitoring - optimize SQL performance

I have the following query which monitors if anyone tried to logon with a technical users on database:

SELECT COUNT (OS_USERNAME)
FROM DBA_AUDIT_SESSION
WHERE USERNAME IN ('USER1','USER2','USER3') 
AND TIMESTAMP>=SYSDATE - 10/(24*60) 
AND RETURNCODE !='0'

Unfortunately the performance of this SQL is quite poor since it does TABLE ACCESS FULL on sys.aud$. I tried to narrow it with:

SELECT COUNT (sessionid)
FROM sys.aud$
WHERE userid         IN ('USER1','USER2','USER3') 
AND ntimestamp#  >=SYSDATE - 10/(24*60)
AND RETURNCODE !='0'
and action# between 100 and 102;

And it is even worse. Is it possible at all to optimize that query by forcing oracle to use indexes here? I would be grateful for any help&tips.

Upvotes: 1

Views: 3905

Answers (3)

Jon Heller
Jon Heller

Reputation: 36872

SYS.AUD$ does not have any default indexes but it is possible to create one on ntimestamp#.

But proceed with caution. The support document "The Effect Of Creating Index On Table Sys.Aud$ (Doc ID 1329731.1)" includes this warning:

Creating additional indexes on SYS objects including table AUD$ is not supported.

Normally that would be the end of the conversation and you'd want to try another approach. But in this case there are a few reasons why it's worth a shot:

  1. The document goes on to say that an index may be helpful, and to test it first.
  2. It's just an index. The SYS schema is special, but we're still just talking about an index on a table. It could slow things down, or maybe cause space errors, like any index would. But I doubt there's any chance it could do something crazy like cause wrong results bugs.
  3. It's somewhat common to change the tablespace of the audit trail, so that table isn't sacred.
  4. I've seen indexes on it before. 2 of the 400 databases I manage have an index on the columns SESSIONID,SES$TID (although I don't know why). Those indexes have been there for years, have been through an upgrade and patches, and haven't caused problems as far as I know.

Creating an "unsupported" index may be a good option for you, if you're willing to test it and accept a small amount of risk.

Upvotes: 1

mc88
mc88

Reputation: 81

NumRows: 8080019

So it is pretty large due to company regulations. Unfortunately using /*+Parallel*/ here makes it run longer, so the performance is still worse.

Any other suggestions?

Upvotes: 0

XING
XING

Reputation: 9886

Oracle 10g onwards optimizer would choose the best plan for your query, provided you write proper joins. Not sure how many recocds exists in your DBA_AUDIT_SESSION , but you can always use PARALLEL hints to somewhat speed up the execution.

SELECT /*+Parallel*/ COUNT (OS_USERNAME) 
--select COUNT (OS_USERNAME) 
FROM DBA_AUDIT_SESSION
WHERE USERNAME IN ('USER1','USER2','USER3') 
AND TIMESTAMP>=SYSDATE - 10/(24*60) 
AND RETURNCODE !='0'

Query Cost reduces to 3 than earlier.

Upvotes: 0

Related Questions