XPLAN
DB CAFE
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
오라클 XPLAN 튜닝 툴
목차
1 DBMS_XPLAN.DISPLAY_CURSOR[편집]
1.1 DBMS_XPLAN.DISPLAY_CURSOR 사용을 위한 권한 부여[편집]
GRANT SELECT ON V_$SESSION TO HR;
GRANT SELECT ON V_$SQL TO HR;
GRANT SELECT ON V_$SQL_PLAN TO HR;1.2 플랜 확인 절차[편집]
1. 선택 권한 부여
GRANT SELECT ON V_$SQL_PLAN_STATISTICS_ALL TO HR;2. STATISTICS_LEVEL 세션 적용
ALTER SESSION SET STATISTICS_LEVEL = ALL;
-- 현재 세션 변경
ALTER SESSION SET CURRENT_SCHEMA = 스키마명;
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ADVANCED'));- 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; --공유 풀 영역을 Clear2 DBMS_XPLAN.DISPLAY_CURSOR 결과 보는 법[편집]
출처 : http://www.bysql.net/index.php?document_srl=18171
2.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 설정이 자유로움
2. DBMS_XPLAN
- plan_table에 저장된 실행계획을 출력. EXPLAIN PLAN 구문보다 확장된 정보 출력
- 제공 Function
- DISPLAY(9i) : 예상실행 계획
- DISPLAY_CURSOR(10g) : SHARED POOL에 올라가 있는 실제 실행계획
- DISPLAY_AWR(10g) : 과거의 실행계획이 저장
- DISPLAY_SQLSET(10g) : 튜닝된 실행계획을 보관
- DISPLAY_SQL_PLAN_BASELINE(11g) : 실행가능한 후보 실행계획
- DISPLAY_PLAN(11g) : Return the last plan, or a named plan, explained as CLOB
- BUILD_PLAN_XML(11g) : Return the last plan, or a named plan, explained as XML
- 사용하기위해 필요한 권한
- V$SESSION
- V$SQL_PLAN
- V$SQL(optional)
- V$SQL_PLAN_STATISTICS_ALL
- 참조
- 오라클 성능 고도화원리와 해법 CHAPTER3 - 04 DBMS_XPLAN 패키지
- 오라클클럽 문서(http://www.gurubee.net/pages/viewpage.action?pageId=4784265&)
- http://www.oracle-base.com/articles/9i/DBMS_XPLAN.php
- http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_xplan.htm
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에서 확인할 수 있다.
SQL_ID 값을 명시하지 않으면 해당 세션의 마지막에 실행된 문장을 의미한다.
CHILD_NUMBER
해당 SQL_ID의 CHILD NUMBER 값을 지정한다.
CURSOR_CHILD_NO 값을 명시하지 않으면 해당 SQL_ID의 첫번째 CURSOR_CHILD_NO 값을 가져온다.
FORMAT
저장된 PLAN을 어떤 Format으로 보여줄 지 결정하는 파라미터
BASIC
- 가장 기본적인 정보만 보여준다.
TYPICAL - FORMAT의 DEFAULT 값인 TYPICAL은 SQL 투닝에 필요한 NORMAL한 정보를 보여 준다. SQL 튜닝에 가장 유용하게 사용되는 PREDICATE INFORMATION이 제공된다.
ALL - TYPICAL FORMAT에 QUERY BLOCK NAME과 COLUMN PROJECTION INFORMATION이 추가로 제공된다.
OUTLINE - TYPICAL FORMAT에 추가적으로 HIDDEN HINT인 OUTLINE GLOBAL HINT를 제공한다.
ADVANCE - ALL FORMAT에 OUTLINE FORMAT를 합친 정보를 제공한다.
ALLSTATS - 실제 엑세스한 로우수와 수행시간, CP, PR, PW 정보를 보여준다. - 수행횟수에 따라 누적된 값을 보여준다.
ALLATATS LAST - 실제 엑세스한 로우수와 수행시간, CP, PR, PW 정보를 보여준다. - 마지막에 수행된 값을 보여준다.
ADVANCED ALLSTATS LSAT - DBMS_XPLAN.DISPLAY_CURSOR에서 지원하는 모든 FORMAT의 정보를 보여준다.
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 <= 1002) STSTISTICS_LEVEL를 ALL로 설정 '_ROWWOURCE_EXECUTION_STATISTICS' 파라미터를 TRUE로 설정\
SELECT *
FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ADVANCED ALLSTATS LAST'))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 되는 컬럼의 정보.
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)