This discussion is archived
6 Replies Latest reply: Nov 20, 2012 7:38 AM by Dom Brooks RSS

help with sql monitoring

user9542267 Newbie
Currently Being Moderated
Hi all, ( Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 )
I try the next select statement and I see many rows showing the same sql text.
select * from (
select a.sid session_id, a.SQL_ID, a.status, a.CPU_TIME/1000000 cpu_sec, a.BUFFER_GETS, a.DISK_READS, b.SQL_TEXT
from v$sql_monitor a, v$sql b
where a.SQL_ID = b.SQL_ID
order by a.CPU_TIME desc)
sql_text
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')

what this text mean?
thanks
  • 1. Re: help with sql monitoring
    Manik Expert
    Currently Being Moderated
    It means audit is enabled..

    Check this
    SHOW PARAMETER AUDIT
    http://www.oracle-base.com/articles/10g/auditing-10gr2.php

    Cheers,
    Manik.
  • 2. Re: help with sql monitoring
    928466 Newbie
    Currently Being Moderated
    here is one more Quick doubt i have:

    CAN we over write the CPU_TIME ...?

    say we are running the above mentioned query with .002 secs., can we shorten the execution time by maintaining the same Query?

    Thanks..............Pamod
  • 3. Re: help with sql monitoring
    user9542267 Newbie
    Currently Being Moderated
    Thanks for you reply and help Manik!
    I see that we have audit enable.
    Now I see another row with a familiar select statement:
    1     170     4t3qn75qda9wu     DONE (ALL ROWS)     5.359375     1460843     8161     "select .... "

    It is a way to figure out where is this statement coming from? We have many procedures, is this can come from a query outside of oracle?
    Best,
  • 4. Re: help with sql monitoring
    user9542267 Newbie
    Currently Being Moderated
    One more thing Manik,
    I see about 75 sessions showing this audit select statement with DONE status.
    Is this ok? why so many sessions?
    Best,
  • 5. Re: help with sql monitoring
    user648708 Explorer
    Currently Being Moderated
    Hello,

    the first query against the view v$sql_monitor has nothing to do with audit!

    Here you will see all SQL queries based on Real-time SQL monitoring
    ( e.g. http://www.oracle-base.com/articles/11g/real-time-sql-monitoring-11gr1.php ).
    It is a way to figure out where is this statement coming from? We have many procedures, is this can come from a query outside of oracle?
    It is easy to figure out, where these statements are coming from:
    the columns USER_NAME, MODULE, CLIENT_INFO, PROGRAM in v$sql_monitor will tell you the origin. These sessions could be
    external sessions from outside via SQL*Net.
    I see about 75 sessions showing this audit select statement with DONE status.
    Is this ok? why so many sessions?
    Obviously, you have an active session which execute some sql queries. If they are finished, the status will be "DONE".
    This is ok.

    Kind regards,

    WoG
  • 6. Re: help with sql monitoring
    Dom Brooks Guru
    Currently Being Moderated
    You sql statement will also be influenced by multiple child cursors. sql id is not unique in either table.

    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') 
    
    what this text mean?
    This is quite a well known badly performing query from, I think, OEM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points