최근의 많은수의 사람들이 DBMS_XPLAN
패키지를 사용하여 튜닝을 하고 있다.
필자는 DBMS_XPLAN 패키지에 대한 너무많은 질문공세 때문에 아예 블로그에 올릴
결심을 하였다.
오늘은 DBMS_XPLAN 패키지에 대한 첫번째 이야기로 가장중요한 실행계획에 대하여 조목조목 따져보려고
한다.
DBMS_XPLAN 패키지는 9i 부터 점점 발전하여 지금은 Trace + tkprof 보고서와 자웅을 겨룰
정도로 발전하고
있다.
DBMS_XPLAN 패키지내의 함수는 10g R2 기준으로 6개 이지만 가장 자주 사용하는
함수는 아래의 3가지 이다.
1.DISPLAY --> 예측 실행계획을 보여준다.
2.DISPLAY_CURSOR
--> 실제 실행된 실행계획을 보여준다.
3.DISPLAY_AWR --> 실제 실행된
실행계획을 보여준다.
오늘의 주제는 실행계획상의 각항목에 대한 설명이므로 3개의 함수에 대한 자세한 설명은 다음에
계속하여
연재할 계획이다.
실행계획은 패키지 내의 3가지 함수(display, display_cursor,
display_awr)를 통해 모두 조회가 가능하다.
아래의 스크립트는 display_awr 의 예제이며 sql_id 만
구하면 언제든지 실행될수 있다.
위 PLAN 은 DBMS_XPLAN 패키지의 format 항목을 Advanced 로 했을 경우에 나타나는 Plan 의 모습이다.
아래는 위의 Plan 항목 하나하나에 대한 자세한 설명이다.
물론 위의 예제는 실행계획의 모든 항목이 나온것은 아니다.
예를 들면 파티션테이블을 사용하지 않았으므로 Partiton 관련 항목이 빠진것이다.
DBMS_XPLAN 패키지의 실행계획의 항목은 아래처럼 크게 7개로 나눌수 있다.
주의사항:
파랑색부분( 5), 6), 7)번 에해당됨)은 실행통계가 있을경우만 해당된다.
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).
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 를 의미함.
결론: [출처] About
DBMS_XPLAN - 1.실행계획|작성자 치킨플라이
이상으로 PLAN 상에 나오는 각
항목에 대하여 빠짐없이 알아보았다.
특히 Runtime 통계, I/O 통계및 Memory 통계중의 일부항목은 Tkprof
보고서에도 나오지않는 정보들로
튜닝시 요긴하게 사용할수 있다는 점을 기억 해야 한다.
다음시간에는 3가지 함수의
여러가지 옵션에 대하여 알아볼것이다.
Reference :
1.Ttroubleshooting Oracle
Performance (Christian Antognini)
2.Oracle 10g Manual : PLSQL
Packages and Types Reference
'ORACLE > TUNING' 카테고리의 다른 글
| [SQL튜닝] 오라클 CPU 많이 차지하는 쿼리 찾기 (0) | 2011.03.28 |
|---|---|
| DBMS_XPLAN - 2.포맷 설정하기 (0) | 2010.05.24 |
| HWM(High Water Mark)란? (0) | 2010.03.31 |
| AWR - Monitoring & Tuning (0) | 2010.03.23 |
| AWR (Automatic Workload Repository) (0) | 2010.03.23 |
Optimizing Oracle Optimizer‎.7z
