Skip to Main Content

Breadcrumb

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

Version: 12.1.0.1.0

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 12.1.0.1.0 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,
hugo

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.

Rating

  (3 ratings)

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

Comments

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,
hugo
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)

SYMPTOMS

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.

CAUSE

This is a known issue:

bug 21119008 "POOR QUERY PERFORMANCE ON UNIFIED_AUDIT_TRAIL"

SOLUTION

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
REVOKE EXECUTE ON UTL_HTTP FROM PUBLIC;
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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

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