8 Replies Latest reply: May 3, 2012 2:32 PM by 498642 RSS

    sql monitoring not capturing all sql

    user8363520
      Hello all, I am on 11.2.0.1 and using Grid control to monitor activity on my DB. There are quite a few DB session active....one session has over 80K logical reads and about 800+ cpu seconds....and its been goin for about couple of mins...running sql query from command prompt....and it shows up in TOP ACTIVTY in grid control as well...but when i go in SQL Monitoring ...there are no sql query that is active...it says "NO SQL EXECUTION WERE MONITORED SO FAR".... if i click on the drop down list and says show me sql for last 6 hrs or 12 hours it pops up...but if i do last 10 mins...nothing shows up....but as i mentioned session are active on the server for a while...so why is it not being captured by sql monitoring ?
        • 1. Re: sql monitoring not capturing all sql
          sb92075
          user8363520 wrote:
          Hello all, I am on 11.2.0.1 and using Grid control to monitor activity on my DB. There are quite a few DB session active....one session has over 80K logical reads and about 800+ cpu seconds....and its been goin for about couple of mins...running sql query from command prompt....and it shows up in TOP ACTIVTY in grid control as well...but when i go in SQL Monitoring ...there are no sql query that is active...it says "NO SQL EXECUTION WERE MONITORED SO FAR".... if i click on the drop down list and says show me sql for last 6 hrs or 12 hours it pops up...but if i do last 10 mins...nothing shows up....but as i mentioned session are active on the server for a while...so why is it not being captured by sql monitoring ?
          SQL Monitoring is initiated when the SQL first starts.
          You can not "monitor" any SQL that started before the monitoring is commenced.
          • 2. Re: sql monitoring not capturing all sql
            912595
            Hi,

            As sb92075 has said oracle will record its stats when session start its sql query.
            In 11g real time sql monitoring will help you dignosing the sql query. Below is one of the blog for this.

            http://askdba.org/weblog/2009/09/11gr2-monitoring-real-time-sql-plan-execution-from-oracle-enterprise-manager/

            Regards
            • 3. Re: sql monitoring not capturing all sql
              user8363520
              Right, I do understand that....but even when something new is started...wouldnt it pick it up ?? The app fired up a new query and lets say sql monitroing is initiated...even when i am not logged onto that page...but when i do go to that page after lets say 2 mins and the sql is still running...wouldnt it show up/capture it ?
              • 4. Re: sql monitoring not capturing all sql
                912595
                Whats the value of statistics_level parameter?

                Regards
                • 5. Re: sql monitoring not capturing all sql
                  user8363520
                  statistics_level=TYPICAL
                  • 6. Re: sql monitoring not capturing all sql
                    498642
                    I am also facing the simillar issue.

                    I have started Reports which usually runs for 20 minutes.

                    When i view the Sql Monitoring page , its showing " No Sql Executions Monitored So far "
                    The Top Activity page sometimes show these sql's

                    My db is Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit

                    Statisticts Level is set to TYPICAL

                    Is this a OEM configuration issue ? Or we need to set any criteria for Monitoring all the SQL's running in db.

                    When i ran the report/qruy there was no load in the system.
                    • 7. Re: sql monitoring not capturing all sql
                      sb92075
                      user495639 wrote:
                      I am also facing the simillar issue.

                      I have started Reports which usually runs for 20 minutes.

                      When i view the Sql Monitoring page , its showing " No Sql Executions Monitored So far "
                      The Top Activity page sometimes show these sql's

                      My db is Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit

                      Statisticts Level is set to TYPICAL

                      Is this a OEM configuration issue ? Or we need to set any criteria for Monitoring all the SQL's running in db.

                      When i ran the report/qruy there was no load in the system.
                      is
                      TIMED_STATISTICS=TRUE?
                      • 8. Re: sql monitoring not capturing all sql
                        498642
                        NAME TYPE VALUE DISPLAY_VALUE ISDEFAULT
                        timed_statistics 1 TRUE TRUE TRUE
                        timed_os_statistics 3 0 0 TRUE