Home » RDBMS Server » Server Administration » SYS object related query taking huge time (Oracle 11.2.0.4.0)
SYS object related query taking huge time [message #631266] Mon, 12 January 2015 04:24 Go to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Hi,

In my Development database, there is a SYS related query that is taking huge amount of time. It slows down the entire database. It is this sql:

SELECT TO_CHAR (CURRENT_TIMESTAMP AT TIME ZONE 'GMT',
                'YYYY-MM-DD HH24:MI:SS TZD')
          AS curr_timestamp,
       COUNT (db_user) AS failed_count
  FROM sys.dba_common_audit_trail
 WHERE     action BETWEEN 100 AND 102
       AND returncode != 0
       AND extended_timestamp >=
              CURRENT_TIMESTAMP - TO_DSINTERVAL ('0 0:30:00');


The AWR report shows that it is run by this Module: Oracle Enterprise Manager.Metric Engine

It took 73,980.28 seconds for a single execution!! I can't even run a count on this table; sys.dba_common_audit_trail

I am not aware of what can cause this issue and will be thankful for suggestion on it.

Thanks,
OrauserN
Re: SYS object related query taking huge time [message #631267 is a reply to message #631266] Mon, 12 January 2015 04:27 Go to previous messageGo to next message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
you probably have muillions of rows in your audit trail table that you have never bothered to look at and do not delete. Please run this:
select count(*) from sys.aud$;
Re: SYS object related query taking huge time [message #631268 is a reply to message #631267] Mon, 12 January 2015 04:36 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... and also (with the times):
set timing on
select count(*) from sys.aud$;
select count(*) from GV$XML_AUDIT_TRAIL;
select count(*) from sys.fga_log$;

Re: SYS object related query taking huge time [message #631271 is a reply to message #631268] Mon, 12 January 2015 04:45 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Thank you John and Michel.

This table is the culprit: GV$XML_AUDIT_TRAIL;

I can't run a select against it ...it just hangs for more than 30 minutes. The other two tables (SYS.AUD$ and SYS.FGA_LOG$) have 0 rows.

Re: SYS object related query taking huge time [message #631276 is a reply to message #631271] Mon, 12 January 2015 05:41 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
I also ran gather fixed stats. But I can't run a select against this table: GV$XML_AUDIT_TRAIL
It just hangs for more than 30 minutes and I dont keep it running further as it may affect the OLTP users. Please suggest if it is safe to truncate this table?
Re: SYS object related query taking huge time [message #631277 is a reply to message #631276] Mon, 12 January 2015 05:46 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You cannot truncate this table.
Check your audit files in <audit_file_dest> and/or <diagnostic_dest> subdirectories and remove those you no more need.

Re: SYS object related query taking huge time [message #631282 is a reply to message #631277] Mon, 12 January 2015 06:09 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Thank you Michel! I just asked the DBAs who have access to it ( I am more like a dev. guy) and they said that there are nearly 1.6M such files. this is dev environment and we don't need any of it. So all can be safely deleted. But Since the slowness is on querying the table (and not related to OS file) how will it help?

Is the view GV$XML_AUDIT_TRAIL related to OS files in some way? Can you please explain? thanks a million for the awesome help!!
Re: SYS object related query taking huge time [message #631288 is a reply to message #631282] Mon, 12 January 2015 07:14 Go to previous message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The view scans all these files to get audit information and display them as table rows.

Previous Topic: DB Server Configuration
Next Topic: Cannot Use DBMS_SCHEDULER.CREATE_PROGRAM even as owner
Goto Forum:
  


Current Time: Sun Feb 25 12:36:49 CST 2018

Total time taken to generate the page: 0.02760 seconds