Hi there!
For some corporate reasons 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 additional conditions (e.g. action_name), also I put some hints (paraller, result_cache and some other) but nothing seem to work. My aud$ is pretty large due to company restrictions and regulations so my question here:. Is it possible at all to optimize that query by forcing oracle to use indexes here? I would be grateful for any help&tips.