DISPLAY , DISPLAY_CURSOR , DISPLAY_AWR 함수가 많이 쓰이는 DBMS_XPALN
DBMS_XPLAN 패키지는 아래와 같은 함수를 제공합니다.
1. DISPLAY (9i ↑)
table_name :default 'PLAN_TABLE'
statement_id :default NULL
format :default 'TYPICAL'
filter_preds :default NULL
:예상 실행 계획
2. DISPLAY_CURSOR (10G ↑)
sql_id :default null
child_number :default null
fromat :default 'TYPICAL'
:실제 실행계획
3. DISPLAY_AWR (10G ↑)
sql_id
plan_hash_value :default null
db_id :default null
format :default 'TYPICAL'
:AWR 을 통해 얻은 항목별 TOP SQL 의 실행계획
4. DISPLAY_SQLSET (10G ↑)
sqlset_name
sql_id
plan_hash_value :default null
format :default 'TYPICAL'
sqlset_owner :default null
:SQL TUNING ADVISOR 을 통해 얻은 튜닝 실행계획
11G에서는 다음과 같은 함수가 추가 되었습니다.
5. DISPLAY_SQL_PLAN_BASELINE (11G ↑)
:SQL PLAN BASELINE 를 통한 후보 실행계획
--DISPLAY
DBMS_XPLAIN.DISPLAY 함수는
EXPLAIN PLAN FOR SELECT.... 로 분석된 예상 실행계획을 조회 할수 있습니다.
SET AUTOTRACE 와 같은 기능을 수행한다고 생각해도 무방합니다.
EX) SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
기본적으로는 마지막에 실행 분석된 SQL 문에 대한 예상 실행계획을 조회 하지만
STATEMENT_ID 를 지정해 기록되어 있는 실행계획을 확인 하는것또한 가능합니다.
-분석
EXPLAIN PLAN
SET STATEMENT_ID = 'A1' -- STATEMENT_ID 를 지정합니다.
FOR SELECT * FROM EMP;
-조회
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','A1','TYPICAL'));
-------------------{DBMS_XPLAN.DISPLAY('plan table','statement_id'가장마지막 일경우 null , 'option' )}
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
Plan hash value: 3956160932
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10M| 171M| 7639 (6)| 00:01:32 |
| 1 | TABLE ACCESS FULL| EMP | 10M| 171M| 7639 (6)| 00:01:32 |
--------------------------------------------------------------------------
(실행계획을 한줄에 담기는 어렵네요...)
동일한 STATEMENT_ID 를 지정할 경우 마지막에 지정된 분석 결과를 가지고 있으므로 이점 유의 하시길 바랍니다.
DBMS_XPLAN 의 장점중 하나는 보고자 하는 포멧 를 컨트롤 할수 있다는 점이다.
all :
plan 정보는 typical 과 같으나 plan 이외의 정보중에서
outline data 정보를 제외하고 모두 출력한다.
advanced :
all 에서 보여주는 정보와 peeked binds , outline data , note
를 추가로 보여준다
allstats :
operation 별로 query block name 과 object alias 를 control 한다.
last :
가장 마지막에 수행된 수행된 실행통계를 출력하며 명시 하지 않을시
누적된 실행 통계를 출력한다.
이외에도 몇가지 컨트롤 명령어가 더 존재합니다.
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL,'ALL'))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10M| 171M| 7639 (6)| 00:01:32 |
| 1 | TABLE ACCESS FULL| EMP | 10M| 171M| 7639 (6)| 00:01:32 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMP"."EMPNO"[NUMBER,22], "EMP"."JOB"[VARCHAR2,2],
"EMP"."HIREDATE"[DATE,7], "EMP"."DEPTNO"[NUMBER,22]
이런식으로 표시됩니다~
--DISPLAY_CURSOR
DISPLAY 함수가 예상 실행계획 을 조회 했었다면..
DBMS_XPLAIN.DISPLAY_CURSOR 함수는
SHARED POOL 의 실제 실행 계획을 조회 할수 있습니다.
이를 위해 1회 의 실제 SQL문을 실행 하여야 하지만
SHARED POOL 에 남아 있는 한 다시 SQL문을 실행하지 않아도 계속 적인 조회가 가능합니다.
SELECT * FROM EMP WHERE DEPTNO = '10';
과 같이 실제 조회를 실행한후.
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(null,null,'typical'));
DISPLAY 함수 처럼 마지막 실행된 쿼리의 실행계획을 조회하게되면.
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID 9s6dwucfkaq75, child number 0
-------------------------------------
SELECT * FROM EMP WHERE DEPTNO = '10'
Plan hash value: 3324114979
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 18 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_N1 | 1 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=10)
이와 같이 실제 수행된 실행계획과
SQL_ID ,
child number,
number plan hash value 와 함게 실제 수행된 실행 계획을 얻을수있습니다.
사용방법은 DBMS_XPLAN.display_cursor(sql_id,child number,'typical')
아래 예문에 따라 아래와 같이 재조회가 가능합니다.
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor('9s6dwucfkaq75','0','typical'));
DBMS_XPLAN.display_cursor 함수는
/*+ gather_plan_statistics */ 힌트와 함계 사용 할경우
실제와 예측된 실행계획 등 아주 상세한 정보를 제공 하여 줍니다.
SELECT /*+ gather_plan_statistics */ *
FROM EMP WHERE DEPTNO = '10'
SELECt * FROM TABLE(DBMS_XPLAN.display_cursor(null,null,'advanced allstats last'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID aa1xcwc6jg8w1, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ * FROM EMP WHERE DEPTNO = '10'
Plan hash value: 1182541070
---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 | 18 | 4 (0)| 00:00:01 | 9 |00:00:00.04 | 6 | 4 |
|* 2 | INDEX RANGE SCAN | EMP_DEPTNO | 1 | 1 | | 3 (0)| 00:00:01 | 9 |00:00:00.03 | 4 | 3 |
---------------------------------------------------------------------------------------------------------------------------------------------(보기어렵네요 ㄱ-)
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
2 - SEL$1 / EMP@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
OPT_PARAM('_optimizer_cost_model' 'cpu')
OPT_PARAM('_optim_peek_user_binds' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "EMP"@"SEL$1" ("EMP"."DEPTNO"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=10)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMP"."EMPNO"[NUMBER,22], "EMP"."JOB"[VARCHAR2,2], "EMP"."HIREDATE"[DATE,7], "DEPTNO"[NUMBER,22]
2 - "EMP".ROWID[ROWID,10], "DEPTNO"[NUMBER,22]
위와 같이 contorl 옵션 별로 자세한 정보들을 얻을수있습니다.
(위는 advanced allstats last 3가지의 옵션을 통해 얻은 정보입니다만 어떠한 옵션을 선택하느냐에 따라 출력 항목 들이 달라지게 됩니다.)
오라클이 SQL text 를 여러개의 query block 로 나누어 관리하는대
oracle 에서 부여하는 internel Query Block Name 를
QB_name 흰트 등을 사용해 이용자가 명시적으로 부여 하여
복잡한 쿼리의 를 쉽게 파악하여 컨트롤 할수있습니다..
Query Block Name 사용한 실행계획 변경
EXPLAIN PLAN FOR
SELECT /*+ QB_NAME(yep) */ *
FROM EMP WHERE DEPTNO = '10'
위와 같이 QB_NAME 힌트를 사용하여 조회 하게 될경우 아래와 같은 실행 겨획을 볼수 있습니다.
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 18 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_DEPTNO | 1 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - YEP / EMP@YEP
2 - YEP / EMP@YEP
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=10)
QB_NAME HINT 를 사용하지 않는다면
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 18 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_DEPTNO | 1 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
2 - SEL$1 / EMP@SEL$1
이와같은 interner 한 block name 를 나타내게됩니다. 위와 아래의 YEP와 SEL$1 차이 보이시죠?
query block name 을 명시적으로 지정함으로써
좀더 복잡한 문장의 쿼리들(여러개의 query block 을 이용하는 SQL) 들이
실행계획의 어떤부분에 속하는지 파악하기도 쉬워지게됩니다.
이제 QB_NAME 를 사용해 실행계획을 변경 하여 보겠습니다.
EXPLAIN PLAN FOR
SELECT t1.* from (
SELECT T.*
FROM EMP T WHERE DEPTNO = '10') T1 ,dept t2
where t1.deptno = t2.deptno
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 21 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | DEPT_U1 | 1 | 3 | 0 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 18 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_DEPTNO | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1
2 - SEL$F5BB74E1 / T2@SEL$1
3 - SEL$F5BB74E1 / T@SEL$2
4 - SEL$F5BB74E1 / T@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T2"."DEPTNO"=10)
4 - access("DEPTNO"=10)
이와같 실행 계획을 보이는 SQL문을
EXPLAIN PLAN FOR
SELECT /*+ FULL(@YEP T ) */ t1.* from (
SELECT /*+ QB_NAME(yep) */ T.*
FROM EMP T WHERE DEPTNO = '10') T1 ,dept t2
where t1.deptno = t2.deptno
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 7725 (7)| 00:01:33 |
| 1 | NESTED LOOPS | | 1 | 21 | 7725 (7)| 00:01:33 |
|* 2 | INDEX UNIQUE SCAN| DEPT_U1 | 1 | 3 | 0 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 1 | 18 | 7725 (7)| 00:01:33 |
------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$453F6D3F
2 - SEL$453F6D3F / T2@SEL$1
3 - SEL$453F6D3F / T@YEP
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T2"."DEPTNO"=10)
3 - filter("DEPTNO"=10)
(QB_NAME를 이용하여 실행 계획을 변경시키는 예제가 목적이므로 성능은 PASS)
3.DISPLAY_AWR 함수를 이용해 AWR 에서 보관중인 TOP SQL 에 대한 정보를 조회 할수있습니다.
Elapsed time(ms)
cpu time(ms)
executions
buffer gets
disk reads
parse calls
rows
user i/o wait time (ms)
cluster wait time (ms)
appllcation wait time (ms)
concurrency wait time (ms)
invalidations
version count
charable mem(KB)
위와 같은 항목으로 각각 30개 의 SQL 을 보관합니다.
11G 에서는 colored sql 로 SQL의 정보만 수집할수 있는
기능이 추가 되었습니다
4.DISPLAY_SQLSET 함수를 통해 SQL TUNING ADVISOR 를 통해 분석한 튜닝 실행 계획을 조회 할수있습니다.
5.DISPLAY_SQL_PLAN_BASELINE 함수는(11G) 에서 부터 사용되며 후보 실행계획들 조회 할수 있습니다.
