dbms_xplan
1.DISPLAY --> 예측 실행계획을 보여준다.
2.DISPLAY_CURSOR --> 실제 실행된 실행계획을 보여준다.
3.DISPLAY_AWR --> 실제 실행된 실행계획을 보여준다.
--sql레벨
select /*+ GATHER_PLAN_STATISTICS */ *
from dept a
where a.dept_no = '11111'
;
--session 레벨
select *
from dept a
where a.dept_no = '11111'
;
display_cursor, display_awr 의 경우 statistics_level 파라미터를 all 로 설정하거나 SQL 에 gather_plan_statistics 를 사용한경우 실행통계를 볼수 있다.
-- sql id만 알면 실행계획을 알수 있다
select * from table(dbms_xplan.display_awr(:v_sql_id,null,null,'advanced allstats last'));
select * from table(dbms_xplan.display_cursor(NULL, NULL, 'ALLSTATS ADVANCED'))
-------------------------------
1)Basics 항목 (Always Available)
Id :각 Operation 의 ID 임.
* 가 달려있는 경우는 predicate 정보에 access 및 filter 에 관한정보가 나옴을 표시한것임.
Operation : 각각 실행되는 JOB 을 나타냄 row source operation.의 줄임말임.
Name : Operation 이 엑세스하는 테이블 및 인덱스를 나타냄.
2)Query Optimizer Estimations(옵티마이져의 예상 row 수 및 bytes, cost , temp 사용량)
Rows (E-Rows) : 각 operation 이 끝났을 때 return되는 건수를 나타냄.
이것은 예측 건수 이므로 실제 건수와는 다름.
Bytes (E-Bytes) : 각 operation 이 return 한 byte 수.
예상치 이므로 실제 받은 byte와는 다름
TempSpc : 각 operation. 이 temporary space 를 사용한 양(예상치임)
Cost (%CPU) : 각 operation 의 Cost. (예상치 임)
괄호안의 내용은 CPU Cost 의 백분율임.
이값은 Child Operation 의 Cost 를 합친 누적치임.
Time : 예측 수행시간
3)Partitioning (파티션을 엑세스 할경우만 나타남)
Pstart : 파티션을 엑세스 하는경우 시작파티션을 나타냄
상수로 들어올때는 파티션 번호로 나타나며 변수로 들어올때는 KEY 로 나타남
Pstop : 마지막 파티션을 나타냄.
따라서 patart, pstop 를 이용하면 access 한 파티션을 알수 있음.
4)Parallel and Distributed Processing (Parallel Processsing 을 사용하거나 DB-LINK 를 사용하는경우)
Inst : DB-LINK 명(사용하는 경우만 나타남).
TQ : PARALLEL SQL 사용시 table queue 명을 나타냄
TQ 는 PARALLEL SLAVE 간의 통신을 담당함.
IN-OUT : Parallel processing 시에 각각의 Operation 이 Serial 로 실행되는지 parallel 로 진행되는지를
나타냄.
PQ Distrib : Parallel processing 시에 producers 와 consumers 간의 데이터의 분배방식을 나타냄.
* 이부분의 자세한 내용은 아래를 참조하기 바란다.
1.http://scidb.tistory.com/entry/Parallel-Query-의-조인시-Row-Distribution
2.http://scidb.tistory.com/entry/Parallel-Query-의-조인시-또다른-튜닝방법pxjoinfilter
5)Runtime Statistics (실제 수행시간밑 실제수행건수)
Starts : 각 operation 을 try 한 건수(예를 들어 nested loop join 이라면 인덱스를 여러 번 scan 함)
A-Rows : 각 operation 이 return 한 건수
A-Time : 실제 실행시간
0.1초까지 나타남 (HH:MM:SS.FF).
이값은 Child Operation 의 Cost 를 합친 누적치임.
6)I/O Statistics (I/O 관련하여 READ / WRITE 한 블록수)
Buffers : 각 Operation 이 메모리에서 읽은 block 수.
Reads :각 Operation 이 disk 에서 읽은 block 수.
Writes : 각 Operation 이 disk 에 write한 block 수.
7)Memory Utilization Statistics(hash 작업이나 sort 작업시 사용한 메모리 통계)
OMem : optimal execution 에 필요한 메모리(예측치임).
1Mem : one-pass execution. 에 필요한 메모리(예측치임)
O/1/M : 각 operation 이 실행한 optimal/one-pass/multipass 횟수가 순서대로 표시됨.
Used-Mem : 마지막 실행시의 사용한 메모리
Used-Tmp : 마지막 실행시 메모리가 부족하여 temporary space 를 대신 사용할 때 나타남.
보이는값에 1024 를 곱해야함.
예를들어 32K 로 나타나면 32MB 를 의미함.
Max-Tmp : 메모리가 부족하여 temporary space 를 사용할 때 최대 temp 사용량임.
USED-TMP 와 다른점은 마지막 수행시가 아니라 SQL을 여러 번 수행했을경우에
항상 최대값만 보인다는 것이다.
보이는값에 1024 를 곱해야함.
예를들어 32K 로 나타나면 32MB 를 의미함.
결론:
이상으로 PLAN 상에 나오는 각 항목에 대하여 빠짐없이 알아보았다.
특히 Runtime 통계, I/O 통계및 Memory 통계중의 일부항목은 Tkprof 보고서에도 나오지않는 정보
---------------------------------------------------------------------------------------------------------------
이정보들은 DBMS_XPLAN.DISPLAY 함수에는 적용되지 않는다. 왜냐하면 explain plan 은 쿼리가 실제 수행되는것이 아니므로 실행통계정보가 없기 때문이다.
또한 DBMS_XPLAN.DISPLAY_CURSOR 나 DBMS_XPLAN.DISPLAY_AWR 등의 함수 수행시에도 GATHER_PLAN_STATISTICS 힌트를 주거나 아니면 파라미터 STATISTICS_LEVEL = ALL 로 되어 있어야 출력이 가능하다.
1.allstats : I/O 통계정보(Buffers, Reads, Writes)와 PGA 통계정보(OMem, 1Mem , Used-Mem, Used-Tmp,
Max-Tmp 등)를 동시에 control 한다.
2.iostats : I/O 통계정보(Buffers, Reads, Writes)를 control 한다.
3.last : 실행통계 출력시 이 control을 명시하면 가장마지막에 수행된 실행통계를 출력한다.
이 control을 명시하지 않으면 실행통계의 누적치를 출력하므로 주의가 필요하다.
4.memstats :PGA 통계정보(OMem, 1Mem , Used-Mem, Used-Tmp, Max-Tmp 등)를 control 한다.
5.runstats_last : iostats control 과 last control 을 합친것과 같다.
이 control은 Oracle 10g Release 1 에서만 사용할수 있다.
6.runstats_tot : iostats control과 동일하다. 이 control은 Oracle 10g Release 1 에서만 사용할수 있다.
--------------------------------------------------------------------------------------------------------------
권장되는 포맷 유형
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL, 'allstats last -rows +predicate'));
포맷을 'allstats last -rows +predicate' 로 주었으므로 예측 row 수(E-row) 가 생략되고 실행통계와
Predicate Information 만을 출력한다.
SELECT * FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL, 'allstats last -rows +alias +outline +predicate'));
'allstats last -rows +alias +outline +predicate' 포맷을 사용하면 Query Block Name / Object Alias 정보와 Outline Data 정보가 추가로 출력된다.
쿼리변형이 발생한경우나 뷰(혹은 인라인뷰) 등을 튜닝할경우 아주 적합한 옵션이다. 하지만 특이한경우
Column Projection Information 이 필요한경우도 있다. 이경우는 +projection 포맷을 추가해주면 된다.
------------------------------------------------------------------------------------------------------------------
사용방법
① Explain plan [set statement_id = 'identifier' ] [ Into tablename ] for + SQL;
분석하고자 하는 SQL을 'Explain plan for' 다음에 넣고 Enter
② Select * from Table(DBMS_XPLAN.DISPLAY);
SQL이 Explain 되었으면 그 다음에 원하는 포맷으로 출력하기 위해서 위와 같이 기본적으로 'PLAN_TABLE'과 'TYPICAL' Format으로 출력
------------------------------------------------------------------------------------------------------------------
?*Access Predicate*는 Access Type을 결정하는데 사용되는 Predicate(조건)을 의미한다. 더 정확하게 말하면 실제 Block을 읽기 전에 어떤 방법으로 Block을 읽을 것인가를 결정한다는 의미이다.
따라서 Index Lookup이나 Join등은 Access Predicate로 표현된다.
?
*Filter Predicate*는 실제 Block을 읽은 후 Data를 걸러 내기 위해 사용되는 Predicate(조건)를 의미한다
--------------------------------------------------------------------------------------------------------------------
Nested Loops Join
선행 Table읽으면서 후행 Table을 한번씩 Access
실제 Join은 후행 Table에 대한 Access에서 발생하기 때문에 실제 Join은 후행 Table에 대한 Acccess에서 발생한다.
그러므로 후행 Table을 읽는 단계가 Access Predicate가 된다.
Hash Join
선행 Table을 먼저 Build한 후, 후행 Table과 한번에 Join
실제 Join이 발생하는 Hash Join 단계가 Access Predicat로 표현된다.
---------------------------------------------------------------------------------------------------------------------
실측내용
ALLSTATS LAST
실제 액세스한 로우수와 수행시간 그리고 CR, PR, PW 정보를 보여준다.
*가장 최근*에 수행된 값만 보여준다.
실측내용
ADVANCED ALLSTATS LAST
DBMS_XPLAN.DISPLAY_CURSOR에서 지원하는 모든 Format의
정보를 보여준다.
------------------------------------------------------------------
SQL> alter session set events '10046 trace name context forever, level 12';
SQL> explain plan for
select * from delete_test
where id < 1000;
SQL> alter session set events '10046 trace name context off';
----------------------------------------------------------------
colored sql 이란?
----------------------------------------------------------------
다섯 가지의 실행계획 존재
1. explain plan : 예상실행 계획
2. shared pool에 올라가 있는 실제 실행계획
3. AWR : 과거의 실행계획이 저장
4. SQL Tuning Advisor : 튜닝된 실행계획을 보관
5. SQL Plan Baseline(11g) : 실행가능한 후보 실행계획
이 다섯가지의 실행계획을 좀 더 편하게 조회하게 하는 것이 dbms_xplan package의 목적
다섯개의 함수 제공.
1. display (9i)
2. display_cursor (10g)
3. display_awr (10g)
4. display_sqlset (10g)
5. display_sql_plan_baseline (11g)
----------------------------------------------
2) V$SQL_PLAN
10gR2 까지 dbms_xplan 패키지로 v$sql_plan 을 직접 조회할 수 있는 기능은 구현되지 않았으나 차후에 구현될 예정이라고 합니다. Tom Kyte는 이러한 제약사항을 view로 만들어 해결하고 있습니다.
SQL> create or replace view dynamic_plan_table
2 as
3 select
4 rawtohex(address) || '_' || child_number statement_id,
5 sysdate timestamp, operation, options, object_node,
6 object_owner, object_name, 0 object_instance,
7 optimizer, search_columns, id, parent_id, position,
8 cost, cardinality, bytes, other_tag, partition_start,
9 partition_stop, partition_id, other, distribution,
10 cpu_cost, io_cost, temp_space, access_predicates,
11 filter_predicates
12 from v$sql_plan;
위와 같은 view를 만들고 난 후 아래 쿼리로 조회합니다.
SQL> select plan_table_output
2 from TABLE( dbms_xplan.display
3 ( 'dynamic_plan_table',
4 (select rawtohex(address)||'_'||child_number x
5 from v$sql
6 where sql_text='select * from t t1 where object_id > 32000' ),
7 'serial' ) )
8 /
DBA 입장에서는 다른 툴이나 모니터링 스크립트를 사용하여 SQL hash value 등을 구하여
아래와 같이 쿼리하는 것이 훨씬 편할 것입니다.
위의 쿼리도 2가지 정도 취약점이 있는데 첫번째는 조회 속도가 약간 느리며 두번째는 child cursor가 생성되면 single row가 아닌 multi row를 리턴하므로 실행 에러가 발생합니다.
또한 버전에 따라 internal error 가 발생할 가능성이 있을 수도 있습니다.
child cursor에 따른 version 문제는 child_number 를 조회조건에 추가하여 single row 만을 리턴하도록 회피하면 됩니다. 조회속도는 문제는 v$sql_plan 의 특정 실행계획을 plan_table 에 저장한 후 dbms_xplan 을 조회하는 방법을 사용해도 되겠습니다.
SQL> insert into plan_table
2 select
3 rawtohex(address) || '_' || child_number statement_id,
4 sysdate timestamp,'' remarks, operation, options, object_node,
5 object_owner, object_name, 0 object_instance, '' object_type,
6 optimizer, search_columns, id, parent_id, position,
7 cost, cardinality, bytes, other_tag, partition_start,
8 partition_stop, partition_id, other, distribution,
9 cpu_cost, io_cost, temp_space, access_predicates,
10 filter_predicates
11 from v$sql_plan where hash_value = 2799829231;
-SQL> select * from table(dbms_xplan.display);