반응형
SQL PLAN을 확인하는 툴 중 하나인 SQL_MONITOR의 사용법에 대해서 정리하겠습니다. 매번 인터넷에서 찾기 어려워서 제가 정리해서 보려고 생각중입니다
1. SQL PLAN을 확인하고 싶은 SQL에 /*+ MONITOR */ 힌트를 추가하여 수행한다
SELECT /*+ MONITOR */*
FROM (SELECT org.*,
ROWNUM
....생략
2.v$sql_monitor 딕셔너리뷰를 조회하여 SQL_ID를 찾아낸다
select * from v$sql_monitor
where username='유저명'
or
select * from v$sql_monitor
where sql_text like '%MONITOR%'
3.찾은 SQL_ID를 활용하여 DBMS_SQLTUNE.REPORT_SQL_MONITOR 패키지를 수행한다.
저는 오렌지 툴보다 SQLPLUS에서 명령어를 수행하는 것이 잘 보이더라구요. SQLPLUS에서 수행하여 확인하는 것을 추천드립니다.
예시
select dbms_sqltune.report_sql_monitor(
sql_id=>'asqfx5s7bjq63',
type=>'TEXT',
report_level=>'ALL') from dual;
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
SQL> select dbms_sqltune.report_sql_monitor(sql_id=>'27jufc228v333',type=>'TEXT',report_level=>'ALL') from dual;
SQL Monitoring Report
SQL Text
------------------------------
SELECT /*+ MONITOR */ * FRO... 생략
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : IBMDBA04 (2261:20705)
SQL ID : 27jufc228v333
SQL Execution ID : 16777216
Execution Started : 11/19/2021 13:09:02
First Refresh Time : 11/19/2021 13:09:02
Last Refresh Time : 11/19/2021 13:09:08
Duration : 6s
Module/Action : Trusted Orange for ORACLE (Unicode) DBA /7.0.2 (Build:163,S)
Service : DBCORT
Program : TOrangeV7.1U.exe
Fetch Calls : 2
Binds
========================================================================================================================
| Name | Position | Type | Value |
========================================================================================================================
| :1 | 1 | VARCHAR2(128) | 20914800 |
| :2 | 2 | VARCHAR2(32) | N |
| :3 | 3 | VARCHAR2(32) | 101 |
========================================================================================================================
Global Stats
============================================================================================================================================
| Elapsed | Cpu | IO | Application | Concurrency | Cluster | PL/SQL | Other | Fetch | Buffer | Read | Read | Write | Write |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Time(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes |
============================================================================================================================================
| 6.13 | 1.43 | 3.08 | 0.00 | 0.02 | 0.71 | 0.03 | 0.89 | 2 | 76542 | 3201 | 536MB | 3 | 2MB |
============================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=550618213)
=========================================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (%) | (# samples) |
=========================================================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 3 | +4 | 30 | 30 | | | | | . | | |
| 1 | INDEX RANGE SCAN | IX_OGO0006_08 | 1 | 3 | 3 | +4 | 30 | 30 | 31 | 248KB | | | . | | |
| 2 | VIEW | | 1 | 14M | 1 | +6 | 1 | 30 | | | | | . | | |
| 3 | COUNT STOPKEY | | | | 1 | +6 | 1 | 30 | | | | | . | | |
| 4 | VIEW | | 1 | 14M | 1 | +6 | 1 | 30 | | | | | . | | |
| 5 | TEMP TABLE TRANSFORMATION | | | | 1 | +6 | 1 | 30 | | | | | . | | |
| 6 | LOAD AS SELECT | SYS_TEMP_0FD9D6A11_D29BF6D1 | | | 1 | +1 | 1 | 2 | | | 3 | 2MB | . | | |
| 7 | HASH JOIN | | 6905 | 994 | 1 | +1 | 1 | 14854 | | | | | 12MB | | |
| 8 | TABLE ACCESS FULL | TB_OGO0009 | 6905 | 572 | 1 | +1 | 1 | 64767 | 104 | 25MB | | | . | | |
| 9 | TABLE ACCESS FULL | TB_OGO0003 | 87814 | 421 | 1 | +1 | 1 | 15010 | 166 | 18MB | | | . | 16.67 | db file scattered read (1) |
| 10 | SORT ORDER BY STOPKEY | | 1 | 14M | 1 | +6 | 1 | 30 | | | | | 4096 | | |
| 11 | HASH GROUP BY | | 1 | 14M | 3 | +4 | 1 | 30 | | | | | 1MB | | |
| 12 | VIEW | | 1 | 14M | 3 | +4 | 1 | 679 | | | | | . | | |
| 13 | FILTER | | | | 3 | +4 | 1 | 679 | | | | | . | | |
| 14 | HASH JOIN | | 2M | 2M | 4 | +3 | 1 | 1586 | | | | | 2MB | 33.33 | Cpu (2) |
| 15 | NESTED LOOPS | | 114K | 2M | 1 | +4 | 1 | 583 | | | | | . | | |
| 16 | NESTED LOOPS | | 576K | 2M | 1 | +4 | 1 | 659 | | | | | . | | |
| 17 | NESTED LOOPS | | 288K | 585K | 1 | +4 | 1 | 321 | | | | | . | | |
| 18 | VIEW | | 13919 | 29 | 1 | +4 | 1 | 1 | | | | | . | | |
| 19 | SORT UNIQUE | | 13919 | 28 | 3 | +2 | 1 | 87350 | | | | | 10MB | | |
| 20 | CONNECT BY NO FILTERING WITH SW (UNIQUE) | | | | 3 | +2 | 1 | 87350 | | | | | 4MB | | |
| 21 | VIEW | | 6905 | 26 | 1 | +2 | 1 | 14854 | | | | | . | | |
| 22 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6A11_D29BF6D1 | 6905 | 26 | 1 | +2 | 1 | 14854 | 3 | 2MB | | | . | | |
| 23 | TABLE ACCESS BY INDEX ROWID BATCHED | TB_OGO0006 | 21 | 42 | 1 | +4 | 1 | 321 | 208 | 2MB | | | . | | |
| 24 | INDEX RANGE SCAN | IX_OGO0006_02 | 41 | 2 | 1 | +4 | 1 | 321 | 5 | 40960 | | | . | | |
| 25 | INDEX RANGE SCAN | IX_UDZ2401_01 | 2 | 2 | 1 | +4 | 593 | 659 | 56 | 448KB | | | . | | |
| 26 | TABLE ACCESS BY INDEX ROWID | TB_UDZ2401 | 1 | 4 | 3 | +2 | 1094 | 583 | 173 | 1MB | | | . | 16.67 | db file sequential read (1) |
| 27 | TABLE ACCESS FULL | TB_UDZ2406 | 870K | 10565 | 3 | +4 | 1 | 2M | 495 | 472MB | | | . | | |
| 28 | SORT AGGREGATE | | 1 | | 3 | +4 | 882 | 882 | | | | | . | | |
| 29 | TABLE ACCESS BY INDEX ROWID BATCHED | TB_UDZ2406 | 1 | 6 | 3 | +4 | 882 | 2053 | 1320 | 10MB | | | . | 33.33 | Cpu (1) |
| | | | | | | | | | | | | | | | db file parallel read (1) |
| 30 | INDEX RANGE SCAN | IX_UDZ2406_01 | 3 | 3 | 3 | +4 | 882 | 2053 | 595 | 5MB | | | . | | |
=========================================================================================================================================================================================================================================
출처 :https://estenpark.tistory.com/362
[Oracle] Oracle Real-Time SQL Monitoring(쿼리 모니터링)
Oracle 11g 이상부터 Real-Time SQL Monitoring(v$sql_monitor, v$sql_plan_monitor) 기능을 사용할 수 있습니다. Oracle 11g에서 추가된 Real-Time SQL Monitoring 기능은 5초 이상인 모든 쿼리의 실행 이력 정보..
estenpark.tistory.com
반응형
'Oracle > 운영' 카테고리의 다른 글
| TEMP TABLESPACE 사용량 조회 (0) | 2022.01.03 |
|---|---|
| 테스트로 확인하는 V$ASM_OPERATION (0) | 2021.11.23 |
| 통계정보 복사하기 (partition table) (0) | 2021.11.19 |
| BULK INSERT (대량 INSERT 작업) 일반INSERT 작업과 비교 (0) | 2021.11.18 |
| 통계정보 관련 딕셔너리 뷰 (0) | 2021.11.11 |
댓글