2 Replies Latest reply: Mar 27, 2012 3:06 AM by kuljeet singh - RSS

    RAC executing system level queries mostly!

    misterimran
      Dear All,

      My Database is 11gR2 RAC on Linux operating system.

      I have observed that my RAC is busy in running system level queries all the time. In the top SQL of OEM I can see the flowing queries very frequently.

      Due to this my application queries are given less priority and it seems that there is slow response from the database. I don't remember enabling any kind of auditing or analysis.

      Kindly help me so I can stop the execution of unnecessary queries that seems to be an extra burden on production database.

      Here are few of them:

      SELECT event, sql_id, sql_plan_hash_value, sql_opcode, session_id, session_serial#, module, action, client_id, DECODE(wait_time, 0, 'W', 'C'), 1, time_waited, service_hash, user_id, program, sample_time, p1, p2, p3, current_file#, current_obj#, current_block#, qc_session_id, qc_instance_id, INST_ID,REMOTE_INSTANCE#
      FROM gv$active_session_history
      WHERE sample_time >= :1 AND sample_time <= :2

      SELECT (CASE WHEN PERCENT_USED> 100 THEN 0 ELSE (100-PERCENT_USED) END) PERCENT_FREE
      FROM
      (SELECT (SUM(PERCENT_SPACE_USED)-SUM(PERCENT_SPACE_RECLAIMABLE)) PERCENT_USED
      FROM V$FLASH_RECOVERY_AREA_USAGE)

      SELECT sql_id,sql_text
      from v$sql
      WHERE sql_id in (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10)

      SELECT DISTINCT JI.JOB_TYPE, JI.EDITABLE
      FROM MGMT_JOB_SINGLE_TARGET_TYPES STT, MGMT_JOB_TYPE_DISPLAY_INFO DI, MGMT_JOB_TYPE_INFO JI, MGMT_JOB_TYPE_MAX_VERSIONS MV
      WHERE DI.JOB_TYPE_ID=STT.JOB_TYPE_ID AND JI.JOB_TYPE_ID=DI.JOB_TYPE_ID AND JI.JOB_TYPE=MV.JOB_TYPE AND
      MV.MAJOR_VERSION=(SELECT MAX(MAJOR_VERSION)
      FROM MGMT_JOB_TYPE_MAX_VERSIONS
      WHERE JOB_TYPE=JI.JOB_TYPE) AND DI.USE_DEFAULT_CREATE_UI=1 AND STT.SINGLE_TARGET_TYPE IN (
      SELECT DISTINCT MEMBER_TARGET_TYPE
      FROM MGMT_FLAT_TARGET_MEMBERSHIPS
      WHERE COMPOSITE_TARGET_NAME = :B2 AND COMPOSITE_TARGET_TYPE = :B1 UNION
      SELECT :B1
      FROM DUAL)

      SELECT TO_CHAR(CAST(md.end_time AS TIMESTAMP) AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') time, md.user_wait_time_pct, md.db_time_ps db_time_users, md.cpu_time_ps db_cpu_users, NVL(md.host_cpu_usage_pct, 0)/inst.instCount host_cpu_usage_pct, wcd.users userio_users, md.load_average load_average, p.cpu max_cpu_cnt
      FROM
      (SELECT DISTINCT wait_class_id
      FROM v$event_name, v$parameter p
      WHERE wait_class = 'User I/O' AND p.name='timed_statistics' and p.value='TRUE') wcn,
      (SELECT wait_class_id, min(end_time) end_time, sum(time_waited / intsize_csec) users
      FROM gv$waitclassmetric
      WHERE intsize_csec > 5900 GROUP BY wait_class_id ) wcd,
      (SELECT sum(value) cpu
      FROM gv$parameter
      WHERE name='cpu_count' AND inst_id in
      (SELECT min(inst_id)
      FROM gv$instance GROUP BY host_name)) p,
      (SELECT count(*) instCount
      from gv$instance) inst,
      (SELECT min(end_time) end_time, SUM(CASE WHEN metric_name = 'Database Wait Time Ratio' THEN value ELSE 0 END) user_wait_time_pct, SUM(CASE WHEN metric_name = 'Database Time Per Sec' THEN value / 100 ELSE 0 END) db_time_ps, SUM(CASE WHEN metric_name = 'CPU Usage Per Sec' THEN value / 100 ELSE 0 END) cpu_time_ps, SUM(CASE WHEN metric_name = 'Host CPU Utilization (%)' THEN value ELSE 0 END) host_cpu_usage_pct, SUM(CASE WHEN metric_name = 'Current OS Load' THEN value else 0 END) load_average
      FROM gv$sysmetric
      WHERE metric_name IN ('Database Wait Time Ratio', 'Database Time Per Sec', 'CPU Usage Per Sec', 'Host CPU Utilization (%)', 'Current OS Load') AND group_id = 2 AND intsize_csec > 5900 ) md, v$parameter pp
      WHERE wcn.wait_class_id = wcd.wait_class_id AND pp.name='statistics_level' and pp.value!='BASIC'

      select end_time, wait_class#, (time_waited_fg)/(intsize_csec/100), (time_waited)/(intsize_csec/100), 0
      from v$waitclassmetric union all
      select fg.end_time, -1, fg.value, bg.value, dbtime.value
      from v$sysmetric fg, v$sysmetric bg, v$sysmetric dbtime
      where bg.metric_name = 'Background CPU Usage Per Sec' and bg.group_id = 2 and fg.metric_name = 'CPU Usage Per Sec' and fg.group_id = 2 and dbtime.metric_name = 'Average Active Sessions' and dbtime.group_id = 2 and bg.end_time = fg.end_time and fg.end_time = dbtime.end_time order by end_time,wait_class#

      Thanks for your time.

      Regards,
      Imran
        • 1. Re: RAC executing system level queries mostly!
          kuljeet singh -
          these queries generated from OEM itself to monitor the health of database ,please find the top user in OEM for this database ,is it DBSNMP ?
          • 2. Re: RAC executing system level queries mostly!
            912595
            misterimran wrote:
            Dear All,

            My Database is 11gR2 RAC on Linux operating system.

            I have observed that my RAC is busy in running system level queries all the time. In the top SQL of OEM I can see the flowing queries very frequently.

            Due to this my application queries are given less priority and it seems that there is slow response from the database. I don't remember enabling any kind of auditing or analysis.

            Kindly help me so I can stop the execution of unnecessary queries that seems to be an extra burden on production database.

            Here are few of them:

            SELECT event, sql_id, sql_plan_hash_value, sql_opcode, session_id, session_serial#, module, action, client_id, DECODE(wait_time, 0, 'W', 'C'), 1, time_waited, service_hash, user_id, program, sample_time, p1, p2, p3, current_file#, current_obj#, current_block#, qc_session_id, qc_instance_id, INST_ID,REMOTE_INSTANCE#
            FROM gv$active_session_history
            WHERE sample_time >= :1 AND sample_time <= :2

            SELECT (CASE WHEN PERCENT_USED> 100 THEN 0 ELSE (100-PERCENT_USED) END) PERCENT_FREE
            FROM
            (SELECT (SUM(PERCENT_SPACE_USED)-SUM(PERCENT_SPACE_RECLAIMABLE)) PERCENT_USED
            FROM V$FLASH_RECOVERY_AREA_USAGE)

            SELECT sql_id,sql_text
            from v$sql
            WHERE sql_id in (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10)

            SELECT DISTINCT JI.JOB_TYPE, JI.EDITABLE
            FROM MGMT_JOB_SINGLE_TARGET_TYPES STT, MGMT_JOB_TYPE_DISPLAY_INFO DI, MGMT_JOB_TYPE_INFO JI, MGMT_JOB_TYPE_MAX_VERSIONS MV
            WHERE DI.JOB_TYPE_ID=STT.JOB_TYPE_ID AND JI.JOB_TYPE_ID=DI.JOB_TYPE_ID AND JI.JOB_TYPE=MV.JOB_TYPE AND
            MV.MAJOR_VERSION=(SELECT MAX(MAJOR_VERSION)
            FROM MGMT_JOB_TYPE_MAX_VERSIONS
            WHERE JOB_TYPE=JI.JOB_TYPE) AND DI.USE_DEFAULT_CREATE_UI=1 AND STT.SINGLE_TARGET_TYPE IN (
            SELECT DISTINCT MEMBER_TARGET_TYPE
            FROM MGMT_FLAT_TARGET_MEMBERSHIPS
            WHERE COMPOSITE_TARGET_NAME = :B2 AND COMPOSITE_TARGET_TYPE = :B1 UNION
            SELECT :B1
            FROM DUAL)

            SELECT TO_CHAR(CAST(md.end_time AS TIMESTAMP) AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') time, md.user_wait_time_pct, md.db_time_ps db_time_users, md.cpu_time_ps db_cpu_users, NVL(md.host_cpu_usage_pct, 0)/inst.instCount host_cpu_usage_pct, wcd.users userio_users, md.load_average load_average, p.cpu max_cpu_cnt
            FROM
            (SELECT DISTINCT wait_class_id
            FROM v$event_name, v$parameter p
            WHERE wait_class = 'User I/O' AND p.name='timed_statistics' and p.value='TRUE') wcn,
            (SELECT wait_class_id, min(end_time) end_time, sum(time_waited / intsize_csec) users
            FROM gv$waitclassmetric
            WHERE intsize_csec > 5900 GROUP BY wait_class_id ) wcd,
            (SELECT sum(value) cpu
            FROM gv$parameter
            WHERE name='cpu_count' AND inst_id in
            (SELECT min(inst_id)
            FROM gv$instance GROUP BY host_name)) p,
            (SELECT count(*) instCount
            from gv$instance) inst,
            (SELECT min(end_time) end_time, SUM(CASE WHEN metric_name = 'Database Wait Time Ratio' THEN value ELSE 0 END) user_wait_time_pct, SUM(CASE WHEN metric_name = 'Database Time Per Sec' THEN value / 100 ELSE 0 END) db_time_ps, SUM(CASE WHEN metric_name = 'CPU Usage Per Sec' THEN value / 100 ELSE 0 END) cpu_time_ps, SUM(CASE WHEN metric_name = 'Host CPU Utilization (%)' THEN value ELSE 0 END) host_cpu_usage_pct, SUM(CASE WHEN metric_name = 'Current OS Load' THEN value else 0 END) load_average
            FROM gv$sysmetric
            WHERE metric_name IN ('Database Wait Time Ratio', 'Database Time Per Sec', 'CPU Usage Per Sec', 'Host CPU Utilization (%)', 'Current OS Load') AND group_id = 2 AND intsize_csec > 5900 ) md, v$parameter pp
            WHERE wcn.wait_class_id = wcd.wait_class_id AND pp.name='statistics_level' and pp.value!='BASIC'

            select end_time, wait_class#, (time_waited_fg)/(intsize_csec/100), (time_waited)/(intsize_csec/100), 0
            from v$waitclassmetric union all
            select fg.end_time, -1, fg.value, bg.value, dbtime.value
            from v$sysmetric fg, v$sysmetric bg, v$sysmetric dbtime
            where bg.metric_name = 'Background CPU Usage Per Sec' and bg.group_id = 2 and fg.metric_name = 'CPU Usage Per Sec' and fg.group_id = 2 and dbtime.metric_name = 'Average Active Sessions' and dbtime.group_id = 2 and bg.end_time = fg.end_time and fg.end_time = dbtime.end_time order by end_time,wait_class#

            Thanks for your time.

            Regards,
            Imran
            Here are some guidliness

            1) Check if awr snapshot is captured too frequent.
            2) Ensure that no resource plan is active during day time(which can give low resource to user query) . show parameter resource_manager_plan
            3) Turn off the db cache advosor if you are not using it
            4) Ensure not to gather stats during business hours
            5) Gather fixed table stats to increase perforance for fixed tables. exec dbms_stats.gather_fixed_objects_stats
            6) Gather dictionary stats once to increase performance on dictionary queires. exec dbms_stats.gather_dictionary_stats

            Also as Kuljeet said, check if you are monitoring through OEM