6 Replies Latest reply: Nov 20, 2012 9:38 AM by Dom Brooks RSS

    help with sql monitoring

    user9542267
      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
          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
            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
              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
                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
                  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
                    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