user::f158fe25-5652-4bc3-b247-b1c0f1c371b4

V$SQL_MONITOR 활용 방안 구상을 위한 자료 조사

 

관련 뷰

v$sql_monitor
v$sql_plan_monitor

 

contents

control_management_pack_access 파라미터가 DIAGNOSTIC+TUNING 로, statistics_level 파라미터가 ALL 또는 TYPICAL으로 설정되어 있어야 한다.
수행시간이 5초(정확히는 CPU 또는 I/O Time) 이상인 모든 쿼리의 실행이력 정보를 뷰를 통해 확인할 수 있다.


_sqlmon_threshold 파라미터를 통해 5초 값을 조정할 수 있다.
(_sqlmon_max_plan, _sqlmon_max_planline)
dbms_sqltune.report_sql_monitor 함수를 이용해서 정보를 조회할 수 있다.
monitor 힌트를 사용해 강제로 추적하게 할 수 있다. 단 no_monitor 힌트는 강제로 추적을 방지한다.

dbms_sqltune.report_sql_monitor를 통해 HTML 형식의 리포트를 작성할 수 있다.

 

 

참조

v$sql_monitor. elapsed_time
v$sql_monitor. cpu_time
v$sql_monitor. fetches
v$sql_monitor. buffer_gets
v$sql_monitor. disk_reads
v$sql_monitor. direct_writes
v$sql_monitor. application_wait_time
v$sql_monitor. concurrency_wait_time
v$sql_monitor. cluster_wait_time
v$sql_monitor. user_io_wait_time
v$sql_monitor. plsql_exec_time
v$sql_monitor. java_exec_time

dbms_sql_monitor.begin_operation
dbms_sql_monitor.end_operation
dbms_sqltune.report_sql_monitor_list
dbms_sqltune.report_sql_detail

-- em의 Active SQL Monitor Report ----------------------------------------------------------------------
set pagesize 0 echo off timing off linesize 1000 trimspool on trim on long 2000000 longchunksize 2000000 feedback off
spool sqlmon_4vbqtp97hwqk8.html
select dbms_sqltune.report_sql_monitor(type=>'EM', sql_id=>'4vbqtp97hwqk8') monitor_report from dual;
spool off


-- 참조 12c---------------------------------------------------------------------------------------------
v$sql_monitor_sesstat
v$sql_monitor_statname
dba_hist_reports
dba_hist_reports_detail

-- 참고용 SQL ------------------------------------------------------------------------------------------
SELECT *
     FROM
       (SELECT status,
         --username,
         sql_id,
         sql_exec_id,
         TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start,
         ROUND(elapsed_time/1000000)                      AS "Elapsed (s)",
         ROUND(cpu_time    /1000000)                      AS "CPU (s)",
         buffer_gets,
         ROUND(physical_read_bytes /(1024*1024)) AS "Phys reads (MB)",
         ROUND(physical_write_bytes/(1024*1024)) AS "Phys writes (MB)"
       FROM v$sql_monitor
       ORDER BY elapsed_time DESC
       )
     WHERE rownum<=20;

SELECT ROUND(elapsed_time    /1000000)     AS "Elapsed (s)",
     ROUND(cpu_time             /1000000,3)   AS "CPU (s)",
     ROUND(queuing_time         /1000000,3)   AS "Queuing (s)",
     ROUND(application_wait_time/1000000,3)   AS "Appli wait (s)",
     ROUND(concurrency_wait_time/1000000,3)   AS "Concurrency wait (s)",
     ROUND(cluster_wait_time    /1000000,3)   AS "Cluster wait (s)",
     ROUND(user_io_wait_time    /1000000,3)   AS "User io wait (s)",
     ROUND(physical_read_bytes  /(1024*1024)) AS "Phys reads (MB)",
     ROUND(physical_write_bytes /(1024*1024)) AS "Phys writes (MB)",
     buffer_gets                              AS "Buffer gets",
     ROUND(plsql_exec_time/1000000,3)         AS "Plsql exec (s)",
     ROUND(java_exec_time /1000000,3)         AS "Java exec (s)"
     FROM v$sql_monitor
     WHERE sql_id = '81c1xvnmsyr64'
     AND sql_exec_id = 16777270
     AND sql_exec_start=TO_DATE('11-jan-2012 05:59:27','dd-mon-yyyy hh24:mi:ss');

SELECT NVL(wait_class,'CPU') AS wait_class, NVL(event,'CPU') AS event, COUNT(*)
     FROM v$active_session_history a
     WHERE sql_id = '81c1xvnmsyr64'
     AND sql_exec_id = 16777270
     AND sql_exec_start=TO_DATE('11-jan-2012 05:59:27','dd-mon-yyyy hh24:mi:ss')
     GROUP BY wait_class, event;

SELECT DISTINCT NVL(wait_class,'CPU') AS wait_class FROM v$active_session_history ORDER BY 1;

SELECT
     RPAD('(' || p.plan_line_ID || ' ' || NVL(p.plan_parent_id,'0') || ')',8) || '|' ||
     RPAD(LPAD (' ', 2*p.plan_DEPTH) || p.plan_operation || ' ' || p.plan_options,60,'.') ||
     NVL2(p.plan_object_owner||p.plan_object_name, '(' || p.plan_object_owner|| '.' || p.plan_object_name || ') ', '') ||
     NVL2(p.plan_COST,'Cost:' || p.plan_COST,'') || ' ' ||
     NVL2(p.plan_bytes||p.plan_CARDINALITY,'(' || p.plan_bytes || ' bytes, ' || p.plan_CARDINALITY || ' rows)','') || ' ' ||
     NVL2(p.plan_partition_start || p.plan_partition_stop,' PStart:' ||  p.plan_partition_start || ' PStop:' || p.plan_partition_stop,'') ||
     NVL2(p.plan_time, p.plan_time || '(s)','') AS PLAN
     FROM v$sql_plan_monitor p
     WHERE sql_id = '81c1xvnmsyr64'
     AND sql_exec_id = 16777270
     AND sql_exec_start=TO_DATE('11-jan-2012 05:59:27','dd-mon-yyyy hh24:mi:ss')
     ORDER BY p.plan_line_id, p.plan_parent_id;

SELECT dbms_sqltune.report_sql_monitor_list(sql_id=>'81c1xvnmsyr64',report_level=>'ALL') AS report FROM dual;