This discussion is archived
8 Replies Latest reply: May 3, 2012 12:32 PM by 498642 RSS

sql monitoring not capturing all sql

user8363520 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    Whats the value of statistics_level parameter?

    Regards
  • 5. Re: sql monitoring not capturing all sql
    user8363520 Newbie
    Currently Being Moderated
    statistics_level=TYPICAL
  • 6. Re: sql monitoring not capturing all sql
    498642 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    NAME TYPE VALUE DISPLAY_VALUE ISDEFAULT
    timed_statistics 1 TRUE TRUE TRUE
    timed_os_statistics 3 0 0 TRUE

Legend

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