"XPLAN"의 두 판 사이의 차이
DB CAFE
(→플랜 확인 절차) |
(→DISPLAY_SQLSET 함수) |
||
(사용자 2명의 중간 판 64개는 보이지 않습니다) | |||
1번째 줄: | 1번째 줄: | ||
− | + | == DBMS_XPLAN 패키지 == | |
+ | === COMPARE_PLANS 함수 === | ||
+ | <source lang=sql> | ||
+ | DBMS_XPLAN.COMPARE_PLANS( | ||
+ | reference_plan IN generic_plan_object, | ||
+ | compare_plan_list IN plan_object_list, | ||
+ | type IN VARCHAR2 := 'TEXT', | ||
+ | level IN VARCHAR2 := 'TYPICAL', | ||
+ | section IN VARCHAR2 := 'ALL') | ||
+ | RETURN CLOB; | ||
+ | </source> | ||
+ | === DIFF_PLAN 함수 === | ||
+ | :플랜비교 | ||
+ | <source lang=sql> | ||
+ | DBMS_XPLAN.DIFF_PLAN( | ||
+ | sql_text IN CLOB, | ||
+ | outline IN CLOB, | ||
+ | user_name IN VARCHAR2 := 'NULL') | ||
+ | RETURN VARCHAR2; | ||
+ | </source> | ||
+ | |||
+ | === DISPLAY_AWR 함수 === | ||
+ | :AWR에 저장된 실행플랜 내용 표시 | ||
+ | <source lang=sql> | ||
+ | SELECT A.SNAP_ID, A.BEGIN_INTERVAL_TIME | ||
+ | , A.END_INTERVAL_TIME, B.SQL_ID | ||
+ | FROM (SELECT /*+ NO_MERGE */ | ||
+ | DBID, MIN(SNAP_ID) MIN_SNAP_ID, MAX(SNAP_ID) MAX_SNAP_ID | ||
+ | FROM SYS.WRM$_SNAPSHOT -- DBA_HIST_SNAPSHOT의 원본 | ||
+ | WHERE END_INTERVAL_TIME >= SYSDATE - 7 | ||
+ | AND END_INTERVAL_TIME < SYSDATE | ||
+ | GROUP BY DBID) X, -- DBA_HIST_SQLSTAT과 조인시 해당 파티션만 SCAN하기 위해 만든 집합임 | ||
+ | SYS.WRM$_SNAPSHOT A, -- DBA_HIST_SNAPSHOT의 원본 | ||
+ | SYS.WRH$_SQLSTAT B -- DBA_HIST_SQLSTAT의 원본 | ||
+ | WHERE X.DBID = B.DBID | ||
+ | AND B.SNAP_ID BETWEEN X.MIN_SNAP_ID AND X.MAX_SNAP_ID | ||
+ | AND A.DBID = B.DBID | ||
+ | AND A.SNAP_ID = B.SNAP_ID | ||
+ | AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER | ||
+ | AND A.END_INTERVAL_TIME >= SYSDATE - 7 | ||
+ | AND A.END_INTERVAL_TIME < SYSDATE | ||
+ | AND NVL(B.PARSING_SCHEMA_NAME,'-') NOT IN ('SYS','SYSMAN','DBSNMP','SYSTEM','EXFSYS') | ||
+ | AND NOT REGEXP_LIKE(NVL(B.MODULE,'-'), 'Orange|SQL Developer|SQLGate|Data Pump|TOAD|golden|ERwin|PL.SQL Developer| | ||
+ | SQL Loader|sqlplus|SQL.Plus|oracle|DBMS_SCHEDULER', 'i') | ||
+ | AND B.PLAN_HASH_VALUE > 0 | ||
+ | AND B.MODULE IS NOT NULL | ||
+ | |||
+ | | ||
+ | |||
+ | SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME SQL_ID | ||
+ | --------- -------------------- -------------------- ------------- | ||
+ | 5 2014/05/06 09:06:56 2014/05/06 09:18:16 3972rvxu3knn3 | ||
+ | </source> | ||
+ | # SELECT 권한 필요 | ||
+ | ## DBA_HIST_SQL_PLAN | ||
+ | ## DBA_HIST_SQLTEXT | ||
+ | ## V$DATABASE. | ||
+ | |||
+ | === DISPLAY_PLAN 함수 === | ||
+ | : CLOB 출력 유형을 사용하여 플랜 테이블의 내용을 다양한 형식으로 표시. | ||
+ | <source lang=sql> | ||
+ | DBMS_XPLAN.DISPLAY_PLAN ( | ||
+ | table_name IN VARCHAR2 DEFAULT 'PLAN_TABLE', | ||
+ | statement_id IN VARCHAR2 DEFAULT NULL, | ||
+ | format IN VARCHAR2 DEFAULT 'TYPICAL', | ||
+ | filter_preds IN VARCHAR2 DEFAULT NULL, | ||
+ | type IN VARCHAR2 DEFAULT 'TEXT') | ||
+ | RETURN CLOB; | ||
+ | </source> | ||
+ | |||
+ | === DISPLAY_SQL_PLAN_BASELINE 함수 === | ||
+ | :SQL플랜 BASELINE의 지정된 SQL 핸들에 대한 하나 이상의 실행플랜 표시. | ||
+ | <source lang=sql> | ||
+ | DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE ( | ||
+ | sql_handle IN VARCHAR2 := NULL, | ||
+ | plan_name IN VARCHAR2 := NULL, | ||
+ | format IN VARCHAR2 := 'TYPICAL') | ||
+ | RETURN dbms_xplan_type_table; | ||
+ | </source> | ||
+ | |||
+ | === DISPLAY_SQLSET 함수 === | ||
+ | : SQL튜닝 세트에 저장된 실행된 SQL문의 실행플랜 표시. | ||
+ | |||
+ | # 필요 SELECT 권한 | ||
+ | ## ALL_SQLSET_STATEMENTS | ||
+ | ## ALL_SQLSET_PLANS | ||
+ | <source lang=sql> | ||
+ | DBMS_XPLAN.DISPLAY_SQLSET( | ||
+ | sqlset_name IN VARCHAR2, | ||
+ | sql_id IN VARCHAR2, | ||
+ | plan_hash_value IN NUMBER := NULL, | ||
+ | format IN VARCHAR2 := 'TYPICAL', | ||
+ | sqlset_owner IN VARCHAR2 := NULL) | ||
+ | RETURN DBMS_XPLAN_TYPE_TABLE PIPELINED; | ||
+ | </source> | ||
+ | |||
+ | === DBMS_XPLAN.DISPLAY === | ||
− | == DBMS_XPLAN.DISPLAY_CURSOR == | + | === DBMS_XPLAN.DISPLAY_CURSOR === |
+ | ==== DBMS_XPLAN.DISPLAY_CURSOR 사용을 위한 권한 부여 ==== | ||
+ | 필요권한 | ||
+ | |||
+ | * V$SQL_PLAN | ||
+ | * V$SESSION | ||
+ | * V$SQL_PLAN_STATISTICS_ALL | ||
− | |||
<source lang=sql> | <source lang=sql> | ||
GRANT SELECT ON V_$SESSION TO HR; | GRANT SELECT ON V_$SESSION TO HR; | ||
10번째 줄: | 111번째 줄: | ||
</source> | </source> | ||
− | === 플랜 확인 절차 === | + | ==== 플랜 확인 절차 ==== |
− | + | ===== 선택 권한 부여 ===== | |
<source lang=sql> | <source lang=sql> | ||
GRANT SELECT ON V_$SQL_PLAN_STATISTICS_ALL TO HR; | GRANT SELECT ON V_$SQL_PLAN_STATISTICS_ALL TO HR; | ||
</source> | </source> | ||
− | + | ===== STATISTICS_LEVEL 세션 적용 ===== | |
<source lang=sql> | <source lang=sql> | ||
ALTER SESSION SET STATISTICS_LEVEL = ALL; | ALTER SESSION SET STATISTICS_LEVEL = ALL; | ||
+ | |||
-- 현재 세션 변경 | -- 현재 세션 변경 | ||
ALTER SESSION SET CURRENT_SCHEMA = 스키마명; | ALTER SESSION SET CURRENT_SCHEMA = 스키마명; | ||
− | SELECT * | + | SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST -ROWS')); |
− | + | -- select * from table(dbms_xplan.display_cursor(NULL,NULL,'allstats last -rows +alias +outline +predicate')); | |
+ | |||
</source> | </source> | ||
52번째 줄: | 155번째 줄: | ||
</source> | </source> | ||
− | == DBMS_XPLAN.DISPLAY_CURSOR 결과 | + | === DBMS_XPLAN.DISPLAY_CURSOR 결과 분석 === |
− | 출처 : http://www.bysql.net/index.php?document_srl=18171 | + | (출처 : http://www.bysql.net/index.php?document_srl=18171 ) |
− | === 10046 Event가 갖지 못한 DBMS_XPLAN.DISPLAY, DBMS_XPLAN.DISPLAY_CURSOR의 장점 === | + | ==== 10046 Event가 갖지 못한 DBMS_XPLAN.DISPLAY, DBMS_XPLAN.DISPLAY_CURSOR의 장점 ==== |
+ | ::Query Block Name / Object Alias : 쿼리 블록 정보 | ||
+ | ::Outline Date : 오라클 내부 Hint | ||
+ | ::Predicate Information : Access 조건 및 조인 조건, Filter 조건 | ||
+ | ::Column Projection Information : Operation Id 별로 Select된 컬럼 정보 | ||
+ | ::Format : 자신에게 맞는 Format 설정이 자유로움 | ||
− | + | ==== DBMS_XPLAN ==== | |
− | + | - plan_table에 저장된 실행계획을 출력. EXPLAIN PLAN 구문보다 확장된 정보 출력 | |
− | + | ==== DBMS_XPLAN.DISPLAY_CURSOR ==== | |
− | + | ;Function Parameter | |
− | |||
− | |||
− | + | <source lang=sql> | |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
FUNCTION DISPLAY_CURSOR(SQL_ID VARCHAR2 DEFAULT NULL, | FUNCTION DISPLAY_CURSOR(SQL_ID VARCHAR2 DEFAULT NULL, | ||
CURSOR_CHILD_NO INTEGER DEFAULT 0, | CURSOR_CHILD_NO INTEGER DEFAULT 0, | ||
FORMAT VARCHAR2 DEFAULT 'TYPICAL') | FORMAT VARCHAR2 DEFAULT 'TYPICAL') | ||
− | + | </source> | |
− | + | ||
+ | :실제 수행된 SQL의 SQL_ID 값을 입력하면 해당 SQL의 실행계획을 볼 수 있다. | ||
+ | :SQL_ID 값은 V$SQL, V$SQLAREA에서 확인할 수 있다. | ||
− | + | '''CHILD_NUMBER''' | |
+ | # 해당 SQL_ID의 CHILD NUMBER 값을 지정한다. | ||
+ | # SQL_ID 값이 명시 될 경우에만 CHILD NUMBER 값을 지정할수 있다. | ||
− | + | '''FORMAT''' | |
+ | #저장된 PLAN을 어떤 Format으로 보여줄 지 결정하는 파라미터 | ||
− | + | '''BASIC''' | |
+ | # 가장 기본적인 정보만 보여준다. operation ID, the operation name 과 option. | ||
+ | |||
+ | '''TYPICAL''' | ||
+ | # 기본값 임. | ||
+ | # 계획에서 가장 관련성이 높은 정보를 표시합니다 (operation id, name and option, #rows, #bytes and optimizer cost). | ||
+ | # Pruning, parallel and predicate information 는 적용 가능한 경우에만 표시. | ||
+ | # PROJECTION, ALIAS 및 REMOTE SQL 정보는 제외 됨. | ||
+ | |||
+ | '''SERIAL''' | ||
+ | # TYPICAL 같음 (다른점은 parallel information is not displayed, even if the plan executes in parallel.) | ||
+ | |||
+ | '''ALL''' | ||
+ | # 최대 사용자 레벨 | ||
+ | # TYPICAL 레벨과 추가 정보 (PROJECTION, ALIAS ,REMOTE SQL 정보) | ||
+ | |||
+ | ---- | ||
+ | (추가 키워드) | ||
+ | |||
+ | '''ROWS''' | ||
+ | # 관련이있는 경우 옵티마저가 추정한 ROW수 표시 | ||
+ | # -ROWS 플랜상 E-ROWS 항목 생략 | ||
+ | |||
+ | '''BYTES ''' | ||
+ | # 관련이있는 경우 옵티마저가 추정한 BYTE수 표시 | ||
− | + | '''COST''' | |
+ | # 관련이있는 경우 옵티마이져 COST 정보 출력 | ||
− | |||
− | + | '''OUTLINE''' | |
+ | # TYPICAL FORMAT에 추가적으로 HIDDEN HINT인 OUTLINE GLOBAL HINT를 제공한다. | ||
− | + | '''PARTITION''' | |
+ | # partition pruning information 출력 | ||
− | + | '''PARALLEL''' | |
+ | # PX information (distribution method and table queue information) | ||
− | + | '''PREDICATE''' | |
+ | # predicate section 출력 | ||
− | + | '''PROJECTION''' | |
+ | # projection section | ||
− | + | '''ALIAS''' | |
+ | # "Query Block Name / Object Alias" section | ||
− | + | '''REMOTE''' | |
+ | # distributed query (for example, remote from serial distribution and remote SQL) | ||
− | + | '''NOTE''' | |
+ | # note section of the explain plan | ||
− | + | '''IOSTATS''' | |
− | + | # SQL 실행시 기본 플랜 통계가 수집된다고 가정 한다면(gather_plan_statistics 힌트를 사용하거나 statistics_level 매개 변수를 ALL로 설정하여), 이 포맷은 커서를 수행된 ALL의 IO 통계정보를 보여준다. (혹은 아래와 같이 LAST 만 ) | |
− | + | '''MEMSTATS''' | |
− | + | # PGA 메모리 관리가 활성화 된 경우 (즉, pga_aggregate_target 매개 변수가 0이 아닌 값으로 설정 됨)이 형식을 사용하면 메모리 관리 통계 (예 : 운영자의 실행 모드, 사용 된 메모리 양, 유출 된 바이트 수)를 표시 할 수 있습니다. 디스크 등). 이러한 통계는 해시 조인, sort 또는 일부 bitmap operator 와 같은 메모리 집약적 인 작업에만 적용됩니다. | |
− | |||
− | + | '''ALLSTATS ''' | |
+ | # 'IOSTATS MEMSTATS' 단축키 | ||
− | + | '''LAST''' | |
+ | # 기본값,, 플랜통계는 커서의 모든 실행결과를 보여줌. 마지막 실행된 통계정보 출력 | ||
− | + | '''ADVANCE''' | |
− | + | - ALL FORMAT에 OUTLINE FORMAT를 합친 정보를 제공한다. | |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | ADVANCE | ||
− | ALL FORMAT에 OUTLINE FORMAT를 합친 정보를 제공한다. | ||
− | |||
− | + | '''ALLSTATS''' | |
+ | # 실제 엑세스한 로우수와 수행시간, CP, PR, PW 정보를 보여준다. | ||
+ | # 수행횟수에 따라 누적된 값을 보여준다. | ||
− | ALLSTATS | + | '''ALLSTATS LAST''' |
− | 실제 엑세스한 로우수와 수행시간, CP, PR, PW 정보를 보여준다. | + | # 실제 엑세스한 로우수와 수행시간, CP, PR, PW 정보를 보여준다. |
− | + | # LAST는 마지막 실행된 통계정보 출력 | |
− | |||
− | |||
− | |||
− | |||
− | |||
+ | '''ADVANCED ALLSTATS LAST''' | ||
+ | # DBMS_XPLAN.DISPLAY_CURSOR에서 지원하는 모든 FORMAT의 정보를 보여준다. | ||
− | + | ==== DBMS_XPLAN.DISPLAY_CURSOR 사용방법 ==== | |
1) GATHER_PLAN_STATISTICS' HINT 사용 | 1) GATHER_PLAN_STATISTICS' HINT 사용 | ||
+ | |||
<source lang=sql> | <source lang=sql> | ||
SELECT /*+GATHER_PLAN_STATISTICS*/ | SELECT /*+GATHER_PLAN_STATISTICS*/ | ||
− | + | * | |
FROM (SELECT E.* | FROM (SELECT E.* | ||
FROM EMPLOYEE E | FROM EMPLOYEE E | ||
166번째 줄: | 280번째 줄: | ||
WHERE ROWNUM <= 100 | WHERE ROWNUM <= 100 | ||
</source> | </source> | ||
+ | |||
2) STSTISTICS_LEVEL를 ALL로 설정 | 2) STSTISTICS_LEVEL를 ALL로 설정 | ||
− | + | * _ROWWOURCE_EXECUTION_STATISTICS' 파라미터를 TRUE로 설정 | |
<source lang=sql> | <source lang=sql> | ||
174번째 줄: | 289번째 줄: | ||
</source> | </source> | ||
− | + | or | |
+ | |||
+ | <source lang=sql> | ||
+ | SELECT t.* | ||
+ | FROM (SELECT /*+ ordered */ a.SQL_ID,a.CHILD_NUMBER | ||
+ | FROM V$SQL a | ||
+ | WHERE sql_text like '%CYKIM%' | ||
+ | AND ROWNUM <= 2 | ||
+ | ORDER BY a.last_load_time desc | ||
+ | ) pb | ||
+ | , TABLE(DBMS_XPLAN.DISPLAY_CURSOR(pb.SQL_ID, pb.CHILD_NUMBER,'ADVANCED ALLSTATS LAST')) t; | ||
+ | </source> | ||
+ | |||
+ | ==== Plan 상의 항목들에 대한 설명 ==== | ||
+ | |||
<source lang=sql> | <source lang=sql> | ||
------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ||
240번째 줄: | 369번째 줄: | ||
− | 1) Basics 항목 | + | ;1) Basics 항목 |
Id : 각 Operationd의 ID임. *가 달려있는 경우는 Predicate Informatio에 Access 및 Filter에 관한 정보를 표시함 | Id : 각 Operationd의 ID임. *가 달려있는 경우는 Predicate Informatio에 Access 및 Filter에 관한 정보를 표시함 | ||
Operation : 각각 실행되는 JOB | Operation : 각각 실행되는 JOB | ||
Name : Operationdl 엑세스 하는 Table 및 Index | Name : Operationdl 엑세스 하는 Table 및 Index | ||
− | 2) Query Optimizer Estimations 항목(예상치) | + | |
+ | ;2) Query Optimizer Estimations 항목(예상치) | ||
E-Rows : 각 Operation이 끝났을 때 return 되는 건수. | E-Rows : 각 Operation이 끝났을 때 return 되는 건수. | ||
251번째 줄: | 381번째 줄: | ||
Cost(%CPU) : 각 Operation의 Cost. 괄호 안의 내용은 CPU Cost의 백분율임. 이 값은 Child Operation의 Cost를 합친 누적치. | Cost(%CPU) : 각 Operation의 Cost. 괄호 안의 내용은 CPU Cost의 백분율임. 이 값은 Child Operation의 Cost를 합친 누적치. | ||
E-Time : 수행시간 | E-Time : 수행시간 | ||
− | 3) Runtime Statistics 항목 | + | |
+ | ;3) Runtime Statistics 항목 | ||
Starts : 각 Operation을 반복 수행한 건수 | Starts : 각 Operation을 반복 수행한 건수 | ||
A-Rows : 각 Operation이 Return 한 건수 | A-Rows : 각 Operation이 Return 한 건수 | ||
A-Time : 실제 실행시간. 0.01초까지 나타남(HH:MM:SS.FF). Child Operation의 A-Time을 합친 누적치 | A-Time : 실제 실행시간. 0.01초까지 나타남(HH:MM:SS.FF). Child Operation의 A-Time을 합친 누적치 | ||
− | 4) I/O Statistics | + | |
+ | ;4) I/O Statistics | ||
Buffers : 각 Operation이 memory에서 읽은 Block 수. | Buffers : 각 Operation이 memory에서 읽은 Block 수. | ||
Reads : 각 Operation이 Disk에서 Read한 Block 수. | Reads : 각 Operation이 Disk에서 Read한 Block 수. | ||
Writes : 각 Operation이 Disk에서 White한 Block 수. | Writes : 각 Operation이 Disk에서 White한 Block 수. | ||
− | 5) Memory Utilization Statistics(hash 작업이나 sort 작업 시 사용한 메모리 통계) | + | |
+ | ;5) Memory Utilization Statistics(hash 작업이나 sort 작업 시 사용한 메모리 통계) | ||
OMen : Optimal Execution에 필요한 Memory | OMen : Optimal Execution에 필요한 Memory | ||
276번째 줄: | 409번째 줄: | ||
− | 6) 쿼리블록 정보 : Plan 상의 Id별로 쿼리블럭 및 Alias 정보를 출력. | + | ;6) 쿼리블록 정보 : Plan 상의 Id별로 쿼리블럭 및 Alias 정보를 출력. |
− | 7) Outline Date : 오라클이 내부적으로 사용한 힌트. | + | ;7) Outline Date : 오라클이 내부적으로 사용한 힌트. |
− | 8) Predicate Information : Plan 상의 Id 별로 인덱스 액세스, Filter, 조인정보 등을 표출 | + | ;8) Predicate Information : Plan 상의 Id 별로 인덱스 액세스, Filter, 조인정보 등을 표출 |
− | 9) Column Projection Information : Plan 상의 Id 별로 Select 되는 컬럼의 정보. | + | ;9) Column Projection Information : Plan 상의 Id 별로 Select 되는 컬럼의 정보. |
− | + | ==== 쿼리 변형이 없는 단순 쿼리 튜닝의 경우는 최대한 단순화. ==== | |
<source lang=sql> | <source lang=sql> | ||
SELECT * | SELECT * | ||
293번째 줄: | 426번째 줄: | ||
Format : 'allstats last -rows +predicate'로 설정 | Format : 'allstats last -rows +predicate'로 설정 | ||
+ | |||
예측 Row 수(E-row) 생략. 실행통계와 Predicate Information만 출력 | 예측 Row 수(E-row) 생략. 실행통계와 Predicate Information만 출력 | ||
+ | |||
<source lang=sql> | <source lang=sql> | ||
-------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------- | ||
372번째 줄: | 507번째 줄: | ||
</source> | </source> | ||
+ | ==== 병렬쿼리 사용시 ==== | ||
+ | <source lang=sql> | ||
+ | SELECT * | ||
+ | FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST ADAPTIVE PARTITION PARALLEL OUTLINE')) | ||
+ | </source> | ||
+ | |||
+ | == SQL MONITORING == | ||
+ | === DBMS_SQLTUNE.REPORT_SQL_MONITOR === | ||
+ | |||
+ | {{틀:고지상자 | ||
+ | |제목 = DBMS_SQLTUNE.REPORT_SQL_MONITOR | ||
+ | |내용 =# DBMS_XPLAN.DIPLAY_CURSOR이나 SQL TRACE의 경우는 마지막까지 데이터를 FETCH해야 실행 통계를 볼 수 있음. | ||
+ | # SQL_MONITOR의 경우에는 실행중인 SQL에 대한 I/O등의 통계가 표시됨. | ||
+ | # SQL 모니터링 대상 은 V$SQL_MONITOR로 조회 | ||
+ | ## CPU 또는 I/O 시간이 5초 이상 수행된 경우 | ||
+ | ## PARALLEL로 SQL 수행된 경우 | ||
+ | ## /*+ MONITOR */ 힌트 사용한 경우 | ||
+ | # /*+ NO_MONITOR */ 힌트 사용하면 모니터링이 방지 | ||
+ | # V$SQL_MONITOR, V$SQL_PLAN_MONITOR 로 확인 | ||
+ | # DBMS_TUNE 패키지의 REPORT_SQL_MONITOR() 프로시져로 확인 | ||
+ | }} | ||
+ | |||
+ | <source lang=sql> | ||
+ | SELECT SQL_ID | ||
+ | , STATUS | ||
+ | , MODULE | ||
+ | , ACTION | ||
+ | , LAST_REFRESH_TIME | ||
+ | , ROUND(ELAPSED_TIME/1000000, 2) ELAPSED_TIME | ||
+ | , BUFFER_GETS | ||
+ | FROM V$SQL_MONITOR | ||
+ | WHERE USERNAME = 'APP_USER' | ||
+ | ORDER BY LAST_REFRESH_TIME DESC; | ||
+ | |||
+ | SQL_ID STATUS MODULE ACTION LAST_REFRESH_TIME ELAPSED_TIME BUFFER_GETS | ||
+ | ------------- --------------- ---------------------- ------------------ -------------------- ------------ ----------- | ||
+ | 5yfzxpu5593jw EXECUTING Orange for ORACLE DBA 5.0.6 (Build:258) 2014/05/06 13:57:51 8.27 364216 | ||
+ | </source> | ||
+ | * STATUS가 EXECUTING 은 현재 수행중이라는 의미. 수행 완료가 되었으면 DONE으로 표시 | ||
+ | |||
+ | |||
+ | * ORANGE/TOAD TOOL에서 수행 | ||
+ | <source lang=sql> | ||
+ | SELECT DBMS_LOB.SUBSTR(TEXT1, 3000, 1) TEXT1 | ||
+ | , DBMS_LOB.SUBSTR(TEXT1, 3000, 3001) TEXT2 | ||
+ | , DBMS_LOB.SUBSTR(TEXT1, 3000, 6001) TEXT3 | ||
+ | , DBMS_LOB.SUBSTR(TEXT1, 3000, 9001) TEXT4 | ||
+ | , DBMS_LOB.SUBSTR(TEXT1, 3000, 12001) TEXT5 | ||
+ | , DBMS_LOB.SUBSTR(TEXT1, 3000, 15001) TEXT6 | ||
+ | , DBMS_LOB.SUBSTR(TEXT1, 3000, 19001) TEXT7 | ||
+ | , DBMS_LOB.SUBSTR(TEXT1, 3000, 22001) TEXT8 | ||
+ | FROM ( | ||
+ | SELECT /*+ NO_MERGE */ | ||
+ | DBMS_SQLTUNE.REPORT_SQL_MONITOR('5yfzxpu5593jw') TEXT1 | ||
+ | FROM DUAL); | ||
+ | </source> | ||
+ | | ||
+ | |||
+ | * SQLPLUS에서 수행 | ||
+ | <source lang=sql> | ||
+ | SQL> SET LONG 10000000 | ||
+ | SQL> SET LONGCHUNKSIZE 100000000 | ||
+ | SQL> SET LINESIZE 1000 | ||
+ | SQL> SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR('5yfzxpu5593jw') FROM DUAL; | ||
+ | </source> | ||
+ | |||
+ | == OUTLINE 조회 == | ||
+ | <source lang=sql> | ||
+ | --OutLine 추출 | ||
+ | SELECT EXTRACTVALUE (VALUE (D), '/hint') AS OUTLINE_HINTS | ||
+ | FROM XMLTABLE ('/*/outline_data/hint' | ||
+ | PASSING (SELECT XMLTYPE (OTHER_XML) AS XMLVAL | ||
+ | FROM GV$SQL_PLAN | ||
+ | WHERE SQL_ID = '&SQL_ID' | ||
+ | AND OTHER_XML IS NOT NULL | ||
+ | AND CHILD_NUMBER = &CHILD_NUM | ||
+ | AND ROWNUM = 1)) D; | ||
+ | </source> | ||
+ | |||
+ | == EXPLAIN PLAN == | ||
+ | === 인덱스 생성시 플랜 확인 === | ||
+ | <source lang=sql> | ||
+ | EXPLAIN PLAN FOR | ||
+ | CREATE INDEX IDX_TB_BA_RGLR_99 ON TB_BA_RGLR (YEAR, EMP_NO, AGE) ; | ||
+ | |||
+ | SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY) ; | ||
+ | </source> | ||
[[Category:oracle]] | [[Category:oracle]] |
2023년 12월 15일 (금) 21:47 기준 최신판
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 DBMS_XPLAN 패키지[편집]
1.1 COMPARE_PLANS 함수[편집]
DBMS_XPLAN.COMPARE_PLANS(
reference_plan IN generic_plan_object,
compare_plan_list IN plan_object_list,
type IN VARCHAR2 := 'TEXT',
level IN VARCHAR2 := 'TYPICAL',
section IN VARCHAR2 := 'ALL')
RETURN CLOB;
1.2 DIFF_PLAN 함수[편집]
- 플랜비교
DBMS_XPLAN.DIFF_PLAN(
sql_text IN CLOB,
outline IN CLOB,
user_name IN VARCHAR2 := 'NULL')
RETURN VARCHAR2;
1.3 DISPLAY_AWR 함수[편집]
- AWR에 저장된 실행플랜 내용 표시
SELECT A.SNAP_ID, A.BEGIN_INTERVAL_TIME
, A.END_INTERVAL_TIME, B.SQL_ID
FROM (SELECT /*+ NO_MERGE */
DBID, MIN(SNAP_ID) MIN_SNAP_ID, MAX(SNAP_ID) MAX_SNAP_ID
FROM SYS.WRM$_SNAPSHOT -- DBA_HIST_SNAPSHOT의 원본
WHERE END_INTERVAL_TIME >= SYSDATE - 7
AND END_INTERVAL_TIME < SYSDATE
GROUP BY DBID) X, -- DBA_HIST_SQLSTAT과 조인시 해당 파티션만 SCAN하기 위해 만든 집합임
SYS.WRM$_SNAPSHOT A, -- DBA_HIST_SNAPSHOT의 원본
SYS.WRH$_SQLSTAT B -- DBA_HIST_SQLSTAT의 원본
WHERE X.DBID = B.DBID
AND B.SNAP_ID BETWEEN X.MIN_SNAP_ID AND X.MAX_SNAP_ID
AND A.DBID = B.DBID
AND A.SNAP_ID = B.SNAP_ID
AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER
AND A.END_INTERVAL_TIME >= SYSDATE - 7
AND A.END_INTERVAL_TIME < SYSDATE
AND NVL(B.PARSING_SCHEMA_NAME,'-') NOT IN ('SYS','SYSMAN','DBSNMP','SYSTEM','EXFSYS')
AND NOT REGEXP_LIKE(NVL(B.MODULE,'-'), 'Orange|SQL Developer|SQLGate|Data Pump|TOAD|golden|ERwin|PL.SQL Developer|
SQL Loader|sqlplus|SQL.Plus|oracle|DBMS_SCHEDULER', 'i')
AND B.PLAN_HASH_VALUE > 0
AND B.MODULE IS NOT NULL
SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME SQL_ID
--------- -------------------- -------------------- -------------
5 2014/05/06 09:06:56 2014/05/06 09:18:16 3972rvxu3knn3
- SELECT 권한 필요
- DBA_HIST_SQL_PLAN
- DBA_HIST_SQLTEXT
- V$DATABASE.
1.4 DISPLAY_PLAN 함수[편집]
- CLOB 출력 유형을 사용하여 플랜 테이블의 내용을 다양한 형식으로 표시.
DBMS_XPLAN.DISPLAY_PLAN (
table_name IN VARCHAR2 DEFAULT 'PLAN_TABLE',
statement_id IN VARCHAR2 DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL',
filter_preds IN VARCHAR2 DEFAULT NULL,
type IN VARCHAR2 DEFAULT 'TEXT')
RETURN CLOB;
1.5 DISPLAY_SQL_PLAN_BASELINE 함수[편집]
- SQL플랜 BASELINE의 지정된 SQL 핸들에 대한 하나 이상의 실행플랜 표시.
DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE (
sql_handle IN VARCHAR2 := NULL,
plan_name IN VARCHAR2 := NULL,
format IN VARCHAR2 := 'TYPICAL')
RETURN dbms_xplan_type_table;
1.6 DISPLAY_SQLSET 함수[편집]
- SQL튜닝 세트에 저장된 실행된 SQL문의 실행플랜 표시.
- 필요 SELECT 권한
- ALL_SQLSET_STATEMENTS
- ALL_SQLSET_PLANS
DBMS_XPLAN.DISPLAY_SQLSET(
sqlset_name IN VARCHAR2,
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
format IN VARCHAR2 := 'TYPICAL',
sqlset_owner IN VARCHAR2 := NULL)
RETURN DBMS_XPLAN_TYPE_TABLE PIPELINED;
1.7 DBMS_XPLAN.DISPLAY[편집]
1.8 DBMS_XPLAN.DISPLAY_CURSOR[편집]
1.8.1 DBMS_XPLAN.DISPLAY_CURSOR 사용을 위한 권한 부여[편집]
필요권한
- V$SQL_PLAN
- V$SESSION
- V$SQL_PLAN_STATISTICS_ALL
GRANT SELECT ON V_$SESSION TO HR;
GRANT SELECT ON V_$SQL TO HR;
GRANT SELECT ON V_$SQL_PLAN TO HR;
1.8.2 플랜 확인 절차[편집]
1.8.2.1 선택 권한 부여[편집]
GRANT SELECT ON V_$SQL_PLAN_STATISTICS_ALL TO HR;
1.8.2.2 STATISTICS_LEVEL 세션 적용[편집]
ALTER SESSION SET STATISTICS_LEVEL = ALL;
-- 현재 세션 변경
ALTER SESSION SET CURRENT_SCHEMA = 스키마명;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST -ROWS'));
-- select * from table(dbms_xplan.display_cursor(NULL,NULL,'allstats last -rows +alias +outline +predicate'));
- 1번째 파라메터는 SQL_ID 임 , NULL 일경우 바로 전에 수행한 SQL
- 2번째 파라메터는 CHILD_NUMBER 임
SELECT *
FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ADVANCED ALLSTATS LAST'))
-- ( NULL, NULL, 'ADVANCED ALLSTATS LAST' -rows ) '-rows 추가시 예측 rows 생략
- 참고) 바로전 수행 한 SQL_ID 찾기
SELECT SQL_ID,CHILD_NUMBER,SQL_TEXT
FROM V$SQL
WHERE SQL_ID = (SELECT PREV_SQL_ID FROM V$SESSION
WHERE AUDSID = USERENV('SESSIONID'))
AND ROWNUM =1 ;
- 참고) 메모리 Clear (※ 주의) 실제 운영 및 개발 환경에서 사용시 주의)
ALTER SYSTEM FLUSH BUFFER_CACHE; --데이터 버퍼 캐시 영역을 DBA에 의해 Flushing
ALTER SYSTEM FLUSH SHARED_POOL; --공유 풀 영역을 Clear
1.9 DBMS_XPLAN.DISPLAY_CURSOR 결과 분석[편집]
(출처 : http://www.bysql.net/index.php?document_srl=18171 )
1.9.1 10046 Event가 갖지 못한 DBMS_XPLAN.DISPLAY, DBMS_XPLAN.DISPLAY_CURSOR의 장점[편집]
- Query Block Name / Object Alias : 쿼리 블록 정보
- Outline Date : 오라클 내부 Hint
- Predicate Information : Access 조건 및 조인 조건, Filter 조건
- Column Projection Information : Operation Id 별로 Select된 컬럼 정보
- Format : 자신에게 맞는 Format 설정이 자유로움
1.9.2 DBMS_XPLAN[편집]
- plan_table에 저장된 실행계획을 출력. EXPLAIN PLAN 구문보다 확장된 정보 출력
1.9.3 DBMS_XPLAN.DISPLAY_CURSOR[편집]
- Function Parameter
FUNCTION DISPLAY_CURSOR(SQL_ID VARCHAR2 DEFAULT NULL,
CURSOR_CHILD_NO INTEGER DEFAULT 0,
FORMAT VARCHAR2 DEFAULT 'TYPICAL')
- 실제 수행된 SQL의 SQL_ID 값을 입력하면 해당 SQL의 실행계획을 볼 수 있다.
- SQL_ID 값은 V$SQL, V$SQLAREA에서 확인할 수 있다.
CHILD_NUMBER
- 해당 SQL_ID의 CHILD NUMBER 값을 지정한다.
- SQL_ID 값이 명시 될 경우에만 CHILD NUMBER 값을 지정할수 있다.
FORMAT
- 저장된 PLAN을 어떤 Format으로 보여줄 지 결정하는 파라미터
BASIC
- 가장 기본적인 정보만 보여준다. operation ID, the operation name 과 option.
TYPICAL
- 기본값 임.
- 계획에서 가장 관련성이 높은 정보를 표시합니다 (operation id, name and option, #rows, #bytes and optimizer cost).
- Pruning, parallel and predicate information 는 적용 가능한 경우에만 표시.
- PROJECTION, ALIAS 및 REMOTE SQL 정보는 제외 됨.
SERIAL
- TYPICAL 같음 (다른점은 parallel information is not displayed, even if the plan executes in parallel.)
ALL
- 최대 사용자 레벨
- TYPICAL 레벨과 추가 정보 (PROJECTION, ALIAS ,REMOTE SQL 정보)
(추가 키워드)
ROWS
- 관련이있는 경우 옵티마저가 추정한 ROW수 표시
- -ROWS 플랜상 E-ROWS 항목 생략
BYTES
- 관련이있는 경우 옵티마저가 추정한 BYTE수 표시
COST
- 관련이있는 경우 옵티마이져 COST 정보 출력
OUTLINE
- TYPICAL FORMAT에 추가적으로 HIDDEN HINT인 OUTLINE GLOBAL HINT를 제공한다.
PARTITION
- partition pruning information 출력
PARALLEL
- PX information (distribution method and table queue information)
PREDICATE
- predicate section 출력
PROJECTION
- projection section
ALIAS
- "Query Block Name / Object Alias" section
REMOTE
- distributed query (for example, remote from serial distribution and remote SQL)
NOTE
- note section of the explain plan
IOSTATS
- SQL 실행시 기본 플랜 통계가 수집된다고 가정 한다면(gather_plan_statistics 힌트를 사용하거나 statistics_level 매개 변수를 ALL로 설정하여), 이 포맷은 커서를 수행된 ALL의 IO 통계정보를 보여준다. (혹은 아래와 같이 LAST 만 )
MEMSTATS
- PGA 메모리 관리가 활성화 된 경우 (즉, pga_aggregate_target 매개 변수가 0이 아닌 값으로 설정 됨)이 형식을 사용하면 메모리 관리 통계 (예 : 운영자의 실행 모드, 사용 된 메모리 양, 유출 된 바이트 수)를 표시 할 수 있습니다. 디스크 등). 이러한 통계는 해시 조인, sort 또는 일부 bitmap operator 와 같은 메모리 집약적 인 작업에만 적용됩니다.
ALLSTATS
- 'IOSTATS MEMSTATS' 단축키
LAST
- 기본값,, 플랜통계는 커서의 모든 실행결과를 보여줌. 마지막 실행된 통계정보 출력
ADVANCE - ALL FORMAT에 OUTLINE FORMAT를 합친 정보를 제공한다.
ALLSTATS
- 실제 엑세스한 로우수와 수행시간, CP, PR, PW 정보를 보여준다.
- 수행횟수에 따라 누적된 값을 보여준다.
ALLSTATS LAST
- 실제 엑세스한 로우수와 수행시간, CP, PR, PW 정보를 보여준다.
- LAST는 마지막 실행된 통계정보 출력
ADVANCED ALLSTATS LAST
- DBMS_XPLAN.DISPLAY_CURSOR에서 지원하는 모든 FORMAT의 정보를 보여준다.
1.9.4 DBMS_XPLAN.DISPLAY_CURSOR 사용방법[편집]
1) GATHER_PLAN_STATISTICS' HINT 사용
SELECT /*+GATHER_PLAN_STATISTICS*/
*
FROM (SELECT E.*
FROM EMPLOYEE E
WHERE E.DEPARTMENT_ID = 50
ORDER BY E.EMPLOYEE_ID)
WHERE ROWNUM <= 100
2) STSTISTICS_LEVEL를 ALL로 설정
- _ROWWOURCE_EXECUTION_STATISTICS' 파라미터를 TRUE로 설정
SELECT *
FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ADVANCED ALLSTATS LAST'))
or
SELECT t.*
FROM (SELECT /*+ ordered */ a.SQL_ID,a.CHILD_NUMBER
FROM V$SQL a
WHERE sql_text like '%CYKIM%'
AND ROWNUM <= 2
ORDER BY a.last_load_time desc
) pb
, TABLE(DBMS_XPLAN.DISPLAY_CURSOR(pb.SQL_ID, pb.CHILD_NUMBER,'ADVANCED ALLSTATS LAST')) t;
1.9.5 Plan 상의 항목들에 대한 설명[편집]
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 45 |00:00:00.01 | 3 | | | |
|* 1 | COUNT STOPKEY | | 1 | | | | | 45 |00:00:00.01 | 3 | | | |
| 2 | VIEW | | 1 | 45 | 5985 | 4 (25)| 00:00:01 | 45 |00:00:00.01 | 3 | | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 45 | 3105 | 4 (25)| 00:00:01 | 45 |00:00:00.01 | 3 | 6144 | 6144 | 6144 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 1 | 45 | 3105 | 3 (0)| 00:00:01 | 45 |00:00:00.01 | 3 | | | |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 45 | | 1 (0)| 00:00:01 | 45 |00:00:00.01 | 1 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$2 / from$_subquery$_001@SEL$1
3 - SEL$2
4 - SEL$2 / E@SEL$2
5 - SEL$2 / E@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
INDEX_RS_ASC(@"SEL$2" "E"@"SEL$2" ("EMPLOYEE"."DEPARTMENT_ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=100)
3 - filter(ROWNUM<=100)
5 - access("E"."DEPARTMENT_ID"=50)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "from$_subquery$_001"."EMPLOYEE_ID"NUMBER,22, "from$_subquery$_001"."FIRST_NAME"VARCHAR2,20, "from$_subquery$_001"."LAST_NAME"VARCHAR2,25,
"from$_subquery$_001"."EMAIL"VARCHAR2,25, "from$_subquery$_001"."PHONE_NUMBER"VARCHAR2,20, "from$_subquery$_001"."HIRE_DATE"DATE,7,
"from$_subquery$_001"."JOB_ID"VARCHAR2,10, "from$_subquery$_001"."SALARY"NUMBER,22, "from$_subquery$_001"."COMMISSION_PCT"NUMBER,22,
"from$_subquery$_001"."MANAGER_ID"NUMBER,22, "from$_subquery$_001"."DEPARTMENT_ID"NUMBER,22
2 - "from$_subquery$_001"."EMPLOYEE_ID"NUMBER,22, "from$_subquery$_001"."FIRST_NAME"VARCHAR2,20, "from$_subquery$_001"."LAST_NAME"VARCHAR2,25,
"from$_subquery$_001"."EMAIL"VARCHAR2,25, "from$_subquery$_001"."PHONE_NUMBER"VARCHAR2,20, "from$_subquery$_001"."HIRE_DATE"DATE,7,
"from$_subquery$_001"."JOB_ID"VARCHAR2,10, "from$_subquery$_001"."SALARY"NUMBER,22, "from$_subquery$_001"."COMMISSION_PCT"NUMBER,22,
"from$_subquery$_001"."MANAGER_ID"NUMBER,22, "from$_subquery$_001"."DEPARTMENT_ID"NUMBER,22
3 - (#keys=1) "E"."EMPLOYEE_ID"NUMBER,22, "E"."DEPARTMENT_ID"NUMBER,22, "E"."FIRST_NAME"VARCHAR2,20, "E"."LAST_NAME"VARCHAR2,25,
"E"."EMAIL"VARCHAR2,25, "E"."PHONE_NUMBER"VARCHAR2,20, "E"."HIRE_DATE"DATE,7, "E"."JOB_ID"VARCHAR2,10, "E"."SALARY"NUMBER,22,
"E"."COMMISSION_PCT"NUMBER,22, "E"."MANAGER_ID"NUMBER,22
4 - "E"."EMPLOYEE_ID"NUMBER,22, "E"."FIRST_NAME"VARCHAR2,20, "E"."LAST_NAME"VARCHAR2,25, "E"."EMAIL"VARCHAR2,25, "E"."PHONE_NUMBER"VARCHAR2,20,
"E"."HIRE_DATE"DATE,7, "E"."JOB_ID"VARCHAR2,10, "E"."SALARY"NUMBER,22, "E"."COMMISSION_PCT"NUMBER,22, "E"."MANAGER_ID"NUMBER,22,
"E"."DEPARTMENT_ID"NUMBER,22
5 - "E".ROWIDROWID,10, "E"."DEPARTMENT_ID"NUMBER,22
- 1) Basics 항목
Id : 각 Operationd의 ID임. *가 달려있는 경우는 Predicate Informatio에 Access 및 Filter에 관한 정보를 표시함 Operation : 각각 실행되는 JOB Name : Operationdl 엑세스 하는 Table 및 Index
- 2) Query Optimizer Estimations 항목(예상치)
E-Rows : 각 Operation이 끝났을 때 return 되는 건수. E-Bytes : 각 Operation이 Temporany Space를 사용한 양 Cost(%CPU) : 각 Operation의 Cost. 괄호 안의 내용은 CPU Cost의 백분율임. 이 값은 Child Operation의 Cost를 합친 누적치. E-Time : 수행시간
- 3) Runtime Statistics 항목
Starts : 각 Operation을 반복 수행한 건수 A-Rows : 각 Operation이 Return 한 건수 A-Time : 실제 실행시간. 0.01초까지 나타남(HH:MM:SS.FF). Child Operation의 A-Time을 합친 누적치
- 4) I/O Statistics
Buffers : 각 Operation이 memory에서 읽은 Block 수. Reads : 각 Operation이 Disk에서 Read한 Block 수. Writes : 각 Operation이 Disk에서 White한 Block 수.
- 5) Memory Utilization Statistics(hash 작업이나 sort 작업 시 사용한 메모리 통계)
OMen : Optimal Execution에 필요한 Memory SQL 실행 메모리가 최적의 크기를 가졌을때의 메모리. 여기서 메모리가 최적의 크기를 갖는다는 것은 예를 들어, disk에 write하지 않고 sort 작업을 수행하는 경우를 의미한다. 1Mem : One-pass Execution에 필요한 Momory SQL 실행 메모리가 1 pass의 크기를 가졌을 때의 메모리. 여기서 메모리가 1 pass의 크기를 갖는다는 의미는, 예를 들어 sort의 경우 disk에 임시 결과를 한번은 저장하고 결과를 merge해서 sort 작업을 마치는 경우를 의미한다. O/1/M : 각 Operation이 실행한 Optmal/One-pass/Multipass 횟수가 순서대로 표시됨
O 일 경우 메모리공간(hash_area_size)이 부족하지 않아 temp 영역(disk)을 사용하지 않고 처리 되었다는 의미임.
multipass 횟수 : SQL 실행 메모리가 2 pass 이상의 크기를 가졌던 횟수. Used-mem : 마지막 실행 시 사용한 PGA -Memory Used-Tmp : 마지막 실행 시 메모리가 부족하여 Temporary Space를 대신 사용할 때 나타남. 보이는 값에 1024를 곱해야 함. Max-Tmp : 메모리가 부족하여 Temporary Space를 사용할 때 최대 Temp 사용량. Used-Tmp와 다른 점은 마지막 수행시가 아니라 SQL을 여러 번 수행했을 경우에 항상 최대값만 보인다는 것. 보이는 값에 1024를 곱해야 함.
- 6) 쿼리블록 정보
- Plan 상의 Id별로 쿼리블럭 및 Alias 정보를 출력.
- 7) Outline Date
- 오라클이 내부적으로 사용한 힌트.
- 8) Predicate Information
- Plan 상의 Id 별로 인덱스 액세스, Filter, 조인정보 등을 표출
- 9) Column Projection Information
- Plan 상의 Id 별로 Select 되는 컬럼의 정보.
1.9.6 쿼리 변형이 없는 단순 쿼리 튜닝의 경우는 최대한 단순화.[편집]
SELECT *
FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST -ROWS +PREDICATE'))
Format : 'allstats last -rows +predicate'로 설정
예측 Row 수(E-row) 생략. 실행통계와 Predicate Information만 출력
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 |00:00:00.01 | 3 | | | |
|* 1 | COUNT STOPKEY | | 1 | 45 |00:00:00.01 | 3 | | | |
| 2 | VIEW | | 1 | 45 |00:00:00.01 | 3 | | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 45 |00:00:00.01 | 3 | 6144 | 6144 | 6144 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 1 | 45 |00:00:00.01 | 3 | | | |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 45 |00:00:00.01 | 1 | | | |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=100)
3 - filter(ROWNUM<=100)
5 - access("E"."DEPARTMENT_ID"=50)
7.쿼리 변형이 발생하거나 복잡한 쿼리 튜닝시 쿼리블럭과 힌트정보를 추가로 출력
SELECT *
FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST -ROWS +ALIAS +OUTLINE +PREDICATE'))
Format : 'allstats last -rows +alias +outline +predicate'로 설정 Query Block Name / Object Alias 정보와 Outline Data 정보가 추가로 출력 +alias : 쿼리블록 추가 +outline : 오라클리 내부적으로 사용한 힌트정보를 출력
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 |00:00:00.01 | 3 | | | |
|* 1 | COUNT STOPKEY | | 1 | 45 |00:00:00.01 | 3 | | | |
| 2 | VIEW | | 1 | 45 |00:00:00.01 | 3 | | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 45 |00:00:00.01 | 3 | 6144 | 6144 | 6144 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 1 | 45 |00:00:00.01 | 3 | | | |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 45 |00:00:00.01 | 1 | | | |
--------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$2 / from$_subquery$_001@SEL$1
3 - SEL$2
4 - SEL$2 / E@SEL$2
5 - SEL$2 / E@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
INDEX_RS_ASC(@"SEL$2" "E"@"SEL$2" ("EMPLOYEE"."DEPARTMENT_ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=100)
3 - filter(ROWNUM<=100)
5 - access("E"."DEPARTMENT_ID"=50)
1.9.7 병렬쿼리 사용시[편집]
SELECT *
FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST ADAPTIVE PARTITION PARALLEL OUTLINE'))
2 SQL MONITORING[편집]
2.1 DBMS_SQLTUNE.REPORT_SQL_MONITOR[편집]
SELECT SQL_ID
, STATUS
, MODULE
, ACTION
, LAST_REFRESH_TIME
, ROUND(ELAPSED_TIME/1000000, 2) ELAPSED_TIME
, BUFFER_GETS
FROM V$SQL_MONITOR
WHERE USERNAME = 'APP_USER'
ORDER BY LAST_REFRESH_TIME DESC;
SQL_ID STATUS MODULE ACTION LAST_REFRESH_TIME ELAPSED_TIME BUFFER_GETS
------------- --------------- ---------------------- ------------------ -------------------- ------------ -----------
5yfzxpu5593jw EXECUTING Orange for ORACLE DBA 5.0.6 (Build:258) 2014/05/06 13:57:51 8.27 364216
- STATUS가 EXECUTING 은 현재 수행중이라는 의미. 수행 완료가 되었으면 DONE으로 표시
- ORANGE/TOAD TOOL에서 수행
SELECT DBMS_LOB.SUBSTR(TEXT1, 3000, 1) TEXT1
, DBMS_LOB.SUBSTR(TEXT1, 3000, 3001) TEXT2
, DBMS_LOB.SUBSTR(TEXT1, 3000, 6001) TEXT3
, DBMS_LOB.SUBSTR(TEXT1, 3000, 9001) TEXT4
, DBMS_LOB.SUBSTR(TEXT1, 3000, 12001) TEXT5
, DBMS_LOB.SUBSTR(TEXT1, 3000, 15001) TEXT6
, DBMS_LOB.SUBSTR(TEXT1, 3000, 19001) TEXT7
, DBMS_LOB.SUBSTR(TEXT1, 3000, 22001) TEXT8
FROM (
SELECT /*+ NO_MERGE */
DBMS_SQLTUNE.REPORT_SQL_MONITOR('5yfzxpu5593jw') TEXT1
FROM DUAL);
- SQLPLUS에서 수행
SQL> SET LONG 10000000
SQL> SET LONGCHUNKSIZE 100000000
SQL> SET LINESIZE 1000
SQL> SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR('5yfzxpu5593jw') FROM DUAL;
3 OUTLINE 조회[편집]
--OutLine 추출
SELECT EXTRACTVALUE (VALUE (D), '/hint') AS OUTLINE_HINTS
FROM XMLTABLE ('/*/outline_data/hint'
PASSING (SELECT XMLTYPE (OTHER_XML) AS XMLVAL
FROM GV$SQL_PLAN
WHERE SQL_ID = '&SQL_ID'
AND OTHER_XML IS NOT NULL
AND CHILD_NUMBER = &CHILD_NUM
AND ROWNUM = 1)) D;