12.1 버전부터 SQL Monitor Report 데이터가 AWR에 저장됩니다.
아래와 같이 DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL 함수로 Historical SQL Monitor Report를 생성할 수 있습니다. rid에 리포트 ID, type에 TEXT 또는 ACTIVE를 입력합니다.
-- 1
SELECT DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL (rid => :v_rid, type => 'TEXT') AS report
FROM DUAL;
아래 쿼리로 Historical SQL Monitor Report의 리포트 ID를 조회할 수 있습니다. DBA_HIST_REPORTS 뷰는 AWR로 캡처된 XML 보고서에 대한 정보를 표시합니다.
SELECT a.snap_id
, a.dbid
, a.instance_number
, a.report_id
, b.*
FROM dba_hist_reports a
, XMLTABLE ('/report_repository_summary/sql'
PASSING XMLTYPE (a.report_summary)
COLUMNS
sql_id VARCHAR2(256) PATH '@sql_id'
, sql_exec_start VARCHAR2(256) PATH '@sql_exec_start'
, sql_exec_id NUMBER PATH '@sql_exec_id'
, status VARCHAR2(256) PATH 'status'
, sql_text VARCHAR2(4000) PATH 'sql_text'
, first_refresh_time VARCHAR2(256) PATH 'first_refresh_time'
, last_refresh_time VARCHAR2(256) PATH 'last_refresh_time'
, refresh_count NUMBER PATH 'refresh_count'
, inst_id NUMBER PATH 'inst_id'
, sid NUMBER PATH 'session_id'
, session_serial# NUMBER PATH 'session_serial'
, user_id NUMBER PATH 'user_id'
, username VARCHAR2(256) PATH 'user'
, con_id NUMBER PATH 'con_id'
, con_name VARCHAR2(256) PATH 'con_name'
, module VARCHAR2(256) PATH 'module'
, action VARCHAR2(256) PATH 'action'
, service_name VARCHAR2(256) PATH 'service'
, program VARCHAR2(256) PATH 'program'
, plan_hash NUMBER PATH 'plan_hash'
, plsql_entry_object_id NUMBER PATH 'plsql_entry_object_id'
, plsql_entry_subprogram_id NUMBER PATH 'plsql_entry_subprogram_id'
, plsql_object_id NUMBER PATH 'plsql_object_id'
, plsql_subprogram_id NUMBER PATH 'plsql_subprogram_id'
, is_cross_instance VARCHAR2(256) PATH 'is_cross_instance'
, px_maxdop NUMBER PATH 'dop'
, instances NUMBER PATH 'instances'
, px_servers_requested NUMBER PATH 'px_servers_requested'
, px_servers_allocated NUMBER PATH 'px_servers_allocated'
, duration NUMBER PATH 'stats/stat[@name="duration"]' DEFAUlT 0
, elapsed_time NUMBER PATH 'stats/stat[@name="elapsed_time"]' DEFAUlT 0
, cpu_time NUMBER PATH 'stats/stat[@name="cpu_time"]' DEFAUlT 0
, user_io_wait_time NUMBER PATH 'stats/stat[@name="user_io_wait_time"]' DEFAUlT 0
, application_wait_time NUMBER PATH 'stats/stat[@name="application_wait_time"]' DEFAUlT 0
, concurrency_wait_time NUMBER PATH 'stats/stat[@name="concurrency_wait_time"]' DEFAUlT 0
, cluster_wait_time NUMBER PATH 'stats/stat[@name="cluster_wait_time"]' DEFAUlT 0
, plsql_exec_time NUMBER PATH 'stats/stat[@name="plsql_exec_time"]' DEFAUlT 0
, other_wait_time NUMBER PATH 'stats/stat[@name="other_wait_time"]' DEFAUlT 0
, fetches NUMBER PATH 'stats/stat[@name="user_fetch_count"]' DEFAUlT 0
, buffer_gets NUMBER PATH 'stats/stat[@name="buffer_gets"]' DEFAUlT 0
, physical_read_requests NUMBER PATH 'stats/stat[@name="read_reqs"]' DEFAUlT 0
, physical_read_bytes NUMBER PATH 'stats/stat[@name="read_bytes"]' DEFAUlT 0
, physical_write_requests NUMBER PATH 'stats/stat[@name="write_reqs"]' DEFAUlT 0
, physical_write_bytes NUMBER PATH 'stats/stat[@name="write_bytes"]' DEFAUlT 0
) b
WHERE a.component_name = 'sqlmonitor';
DBA_HIST_REPORTS_DETAILS 뷰는 AWR로 캡처된 보고서에 대한 세부 정보를 표시합니다.
-- 2
SELECT a.snap_id
, a.dbid
, a.instance_number
, a.report_id
, a.session_id
, a.session_serial#
, b.*
FROM dba_hist_reports_details a
, XMLTABLE ('/report/sql_monitor_report/parallel_info/sessions/session'
PASSING XMLTYPE (a.report)
COLUMNS
inst_id NUMBER PATH '@inst_id'
, process_name VARCHAR(256) PATH '@process_name'
, session_id NUMBER PATH '@session_id'
, session_serial NUMBER PATH '@session_serial'
, elapsed_time NUMBER PATH 'stats/stat[@name="elapsed_time"]' DEFAUlT 0
, cpu_time NUMBER PATH 'stats/stat[@name="cpu_time"]' DEFAUlT 0
, user_io_wait_time NUMBER PATH 'stats/stat[@name="user_io_wait_time"]' DEFAUlT 0
, application_wait_time NUMBER PATH 'stats/stat[@name="application_wait_time"]' DEFAUlT 0
, concurrency_wait_time NUMBER PATH 'stats/stat[@name="concurrency_wait_time"]' DEFAUlT 0
, cluster_wait_time NUMBER PATH 'stats/stat[@name="cluster_wait_time"]' DEFAUlT 0
, plsql_exec_time NUMBER PATH 'stats/stat[@name="plsql_exec_time"]' DEFAUlT 0
, other_wait_time NUMBER PATH 'stats/stat[@name="other_wait_time"]' DEFAUlT 0
, fetches NUMBER PATH 'stats/stat[@name="user_fetch_count"]' DEFAUlT 0
, buffer_gets NUMBER PATH 'stats/stat[@name="buffer_gets"]' DEFAUlT 0
, physical_read_requests NUMBER PATH 'stats/stat[@name="read_reqs"]' DEFAUlT 0
, physical_read_bytes NUMBER PATH 'stats/stat[@name="read_bytes"]' DEFAUlT 0
, physical_write_requests NUMBER PATH 'stats/stat[@name="write_reqs"]' DEFAUlT 0
, physical_write_bytes NUMBER PATH 'stats/stat[@name="write_bytes"]' DEFAUlT 0
) b;