DB performance is slow due to sys.dba_audit_session job

All posts relating to Oracle database administration.

Moderator: Tim...

DB performance is slow due to sys.dba_audit_session job

Postby royalyogi » Sat May 25, 2013 3:29 pm

I am using 11gR2 on windows server. This is the query that runs many times a day and effect badly the performance of database.I don't have much idea about this query. Please explain about below query and help me to disable this job if no harm.



SELECT TO_CHAR(current_timestamp AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') AS curr_timestamp, COUNT(username) AS failed_count
FROM sys.dba_audit_session
WHERE returncode != 0 AND TO_CHAR(timestamp, 'YYYY-MM-DD HH24:MI:SS') >= TO_CHAR(current_timestamp - TO_DSINTERVAL('0 0:30:00'), 'YYYY-MM-DD HH24:MI:SS')
royalyogi
Senior Member
 
Posts: 166
Joined: Tue Apr 23, 2013 3:59 pm

Re: DB performance is slow due to sys.dba_audit_session job

Postby Tim... » Sat May 25, 2013 7:14 pm

Hi.

This looks like something your application is doing, not one of the background processes. Just look at the way the timestamps are being handled. Converting both to strings then trying to compare them. That is a really silly thing to do, and certainly not something I would expect Oracle to do.

I suggest you speak to the developers on your system and ask them what it is for, and also ask them to read about dates and timestamps so they can use them properly. :)

http://www.oracle-base.com/articles/mis ... ervals.php

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: DB performance is slow due to sys.dba_audit_session job

Postby royalyogi » Sun May 26, 2013 5:06 am

Thanks a lot Tim.
Please explain me, how can we disable sys.dba_audit_session and what will be impact of this on application & database . If we disabled it.
royalyogi
Senior Member
 
Posts: 166
Joined: Tue Apr 23, 2013 3:59 pm

Re: DB performance is slow due to sys.dba_audit_session job

Postby Tim... » Sun May 26, 2013 6:16 am

Hi.

It is a data dictionary view. You can't disable or remove it. You can prevent your application query I g it, which is what you need to do.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 10 guests