Skip to Main Content


Question and Answer

Chris Saxon

Thanks for the question, hugo.

Asked: May 09, 2016 - 3:41 pm UTC

Last updated: February 20, 2024 - 3:06 pm UTC


Viewed 10K+ times! This question is

You Asked

Hi Tom,

I'm facing an issue somewhat strange and to which a have no clear answer.

The database version that I'm using is on Windows 64 (both Standard and EE).

With 11G (I believe) Oracle started using the Unified Audit Trail. The default "rules" for my version (12c) inserts a new record in the audit table every time a user connects to the DB.
Because these databases are not mine, the size of the tablespace (SYSAUX), was not under surveillance and has grown to a considerable size without anyone noticing it.
At the moment the tablespace and table are around 16GB and 14GB.

We have tried to remove the information from the table and that process is ongoing.

My question is:

In the DBs where this table and tablespace have grown to such sizes, any (or many) query run against a object in that tablespace take a huge amount of time. Of course, I know that if a object is very large, then that means it's going to take a long time to read, but here we are talking about a really large amount of time.

When I'm executing a query against that tablespace the disk subsystem (SSD disks) starts to read about 130 MBs a second. In a symplistic way, one could say that it should read the necessary 16GB in a bit less than 200 seconds, but the system takes more than 10 minutes (I never allowed it to actually finish because these are PROD systems).

What I would like to know is if there is anything specific about objects inside the SYSAUX tablespace (namely the unified audit trail objects and the scheduler job objects) that could explain such a delay in execution while having such a huge disk access (reads).

Thank you very much,

and Connor said...

We'd need to see the actual queries you are running, but something to keep in mind is that when you query dictionary objects, then whilst they all look like just plain database views, in reality:

- sometimes, you are querying a table
- sometimes, you are querying the controlfiles
- sometimes, you are querying a memory structure
- sometimes, you are querying a directory of files

or any combination of the above.

That said, a common cause of slow dictionary queries is when dictionary stats have not been gathered or updated. Perhaps ask your DBA to do that first, and then have another go.

Hope this helps.


  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment


A little more information

hugo alves, May 10, 2016 - 4:30 pm UTC

First of all, thank you very much for your quick response.

The query executed was a "simple" 'select count(*) from UNIFIED_AUDIT_TRAIL;' (as sys).
I know that "UNIFIED_AUDIT_TRAIL" is a view and not a table, but I wasn't expecting the query to take so long.

Like I said before, reading the entire table should take around 200 seconds, a little over three minutes. Under the covers, this view is in fact made of a combination of partitioned tables, indexes and LOBs. Could this be a explanation for the behavior I'm seeing?

Thank you very much,
Chris Saxon
May 11, 2016 - 1:12 am UTC

Looks like you've hit a bug:

From MOS:

Performance Issues While Monitoring the Unified Audit Trail of an Oracle12c Database (Doc ID 2063340.1)


While using EM Cloud Control 12c to monitor the Unified audit trail of an Oracle12c database, you observe high CPU usage.

The problem may also be observed from SQL*Plus when querying the UNIFIED_AUDIT_TRAIL view. If there are a large number of audit records in the Unified audit trail then query performance can be really slow.

After the Unified audit trail is purged using the DBMS_AUDIT_MGMT package, the CPU usage is much less.


This is a known issue:



Development have investigated the issue and state that the bug is not feasible to fix in 12.1, but it will be fixed in 12.2

Thank you very much

hugo alves, May 11, 2016 - 2:23 pm UTC

Thank you very much the information.

Revoke privileges from Public took too long

Youheng Chhieng, February 16, 2024 - 9:13 am UTC

I have using query to revoke execute from public but it took too long
Chris Saxon
February 20, 2024 - 3:06 pm UTC

What do you mean "took too long"? What exactly happened?

Note that if you have APEX installed, it expects PUBLIC to have access to UTL_HTTP. In this case you either need to leave it granted to public or grant the APEX users access to it (FLOWS_NNNNNN / APEX_NNNNNN)

More to Explore


Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.