OraFAQ Forum: Server Administration » SYS object related query taking huge time

Home » RDBMS Server » Server Administration » SYS object related query taking huge time (Oracle 11.2.0.4.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Mon, 12 January 2015 04:24

orausern
Messages: 826
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

Report message to a moderator

Re: SYS object related query taking huge time [message #631267 is a reply to message #631266]

Mon, 12 January 2015 04:27

John Watson
Messages: 8461
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$;

Report message to a moderator

Re: SYS object related query taking huge time [message #631268 is a reply to message #631267]

Mon, 12 January 2015 04:36

Michel Cadot
Messages: 67667
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$;

Report message to a moderator

Re: SYS object related query taking huge time [message #631271 is a reply to message #631268]

Mon, 12 January 2015 04:45

orausern
Messages: 826
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.

Report message to a moderator

Re: SYS object related query taking huge time [message #631276 is a reply to message #631271]

Mon, 12 January 2015 05:41

orausern
Messages: 826
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?

Report message to a moderator

Re: SYS object related query taking huge time [message #631277 is a reply to message #631276]

Mon, 12 January 2015 05:46

Michel Cadot
Messages: 67667
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.

Report message to a moderator

Re: SYS object related query taking huge time [message #631282 is a reply to message #631277]

Mon, 12 January 2015 06:09

orausern
Messages: 826
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!!

Report message to a moderator

Re: SYS object related query taking huge time [message #631288 is a reply to message #631282]

Mon, 12 January 2015 07:14

Michel Cadot
Messages: 67667
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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jan 27 02:24:05 CST 2021