DBMS XPLAN - DB CAFE

About DBMS_XPLAN - 1.실행계획[편집]

Oracle/Performance Analysis2008.12.18 23:16최근의 많은수의 사람들이 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 만 구하면 언제든지 실행될수 있다.

select * from table(dbms_xplan.display_awr(:v_sql_id,null,null,'advancedallstats last'));

위 PLAN 은 DBMS_XPLAN 패키지의 format 항목을 Advanced 로 했을 경우에 나타나는 Plan 의 모습이다.

아래는 위의 Plan 항목 하나하나에 대한 자세한 설명이다.

물론 위의 예제는 실행계획의 모든 항목이 나온것은 아니다.

예를 들면 파티션테이블을 사용하지 않았으므로 Partiton 관련 항목이 빠진것이다.

DBMS_XPLAN 패키지의 실행계획의 항목은 아래처럼 크게 7개로 나눌수 있다.

주의사항:

파랑색부분( 5), 6), 7)번 에해당됨)은실행통계가 있을경우만 해당된다.

display_cursor, display_awr 의 경우statistics_level 파라미터를 all 로 설정하거나SQL 에gather_plan_statistics 를 사용한경우 실행통계를 볼수 있다.

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 보고서에도 나오지않는 정보들로

튜닝시 요긴하게 사용할수 있다는 점을 기억 해야 한다.

다음시간에는 3가지 함수의 여러가지 옵션에 대하여 알아볼것이다.

Reference :

1.Ttroubleshooting Oracle Performance (Christian Antognini)

2.Oracle 10g Manual : PLSQL Packages and Types Reference