'2008/12'에 해당되는 글 2건

  1. 2008.12.18 About DBMS_XPLAN - 1.실행계획 9
  2. 2008.12.12 InList / Concatnation / Range Scan Control 하기 4

최근의 많은수의 사람들이 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,'advanced allstats 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

Posted by extremedb
,

프로젝트를 하다보면 결합인덱스의 2번째 혹은 3번째 컬럼에 IN 조건을 많이 사용하는것이 관찰된다.
오늘은 결합인덱스 에서 IN 을 사용한경우 나타날수있는3가지 경우(InList, Concatnation, Range Scan)에
대하여 알아 보겠다.

버젼 11.1.0.6
인덱스 : EMP_JOB_MGR_SAL_IDX (JOB_ID, MANAGER_ID, SALARY)  

아래는 인덱스의 첫번째 컬럼이 = 조건으로 들어오고 두번째 컬럼이 IN 변수조건으로 들어 왔을때의
전통적인 방식의 PLAN 이다.

EXPLAIN PLAN FOR
SELECT /*+ RULE */
             a.*
  FROM hr.employees a
 WHERE job_id = :v_job
   AND manager_id IN (:v_manager1, :v_manager2);
select * from table(dbms_xplan.display);


------------------------------------------------------------
| Id  | Operation                    | Name                |
------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |
|   1 |  CONCATENATION               |                     |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES           |
|*  3 |    INDEX RANGE SCAN          | EMP_JOB_MGR_SAL_IDX |
|   4 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES           |
|*  5 |    INDEX RANGE SCAN          | EMP_JOB_MGR_SAL_IDX |
------------------------------------------------------------
 
별로 특별할것이 없는 전통적인 방식의 CONCATENATION Plan 이 나왔다.
그렇다면 Rule 힌트를 빼면 어떻게 되는겠는가?
CONCATENATION Plan 대신에 InList Plan 을 기대하는가?
아래의 Plan 을 보자.

EXPLAIN PLAN FOR
SELECT a.*
  FROM hr.employees a
 WHERE job_id = :v_job
   AND manager_id IN (:v_manager1, :v_manager2) ;


---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |     1 |    62 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES           |     1 |    62 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_JOB_MGR_SAL_IDX |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("JOB_ID"=:V_JOB)
       filter("MANAGER_ID"=TO_NUMBER(:V_MANAGER1) OR "MANAGER_ID"=TO_NUMBER(:V_MANAGER2))

InList Plan 이 나오지 않는다.
어떻게 된것인가?
혹자는 NO_EXPAND 힌트를 사용하라고 이야기 하지만 그것은 CONCATENATION Plan 을 방지하는 힌트일뿐
InList Plan으로 유도하지 않는다.

EXPLAIN PLAN FOR
SELECT /*+ NO_EXPAND */
       a.*
  FROM hr.employees a
 WHERE job_id = :v_job
   AND manager_id IN (:v_manager1, :v_manager2);

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |     1 |    62 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES           |     1 |    62 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_JOB_MGR_SAL_IDX |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("JOB_ID"=:V_JOB)
       filter("MANAGER_ID"=TO_NUMBER(:V_MANAGER1) OR "MANAGER_ID"=TO_NUMBER(:V_MANAGER2))

예상과는 다르게 Range Scan 이 나오고 IN 절은 Filter 조건으로 되고말았다.
그렇다면 이런경우 도데체 어떻게 InList Plan 으로 유도 한단 말인가?
이런경우에 사용할수 있는 힌트가 바로 NUM_INDEX_KEYS 이다.
아래의 SQL 을보자.

EXPLAIN PLAN FOR
SELECT /*+ NUM_INDEX_KEYS(a EMP_JOB_MGR_SAL_IDX 2) */
       a.*
  FROM hr.employees a
 WHERE job_id = :v_job
   AND manager_id IN (:v_manager1, :v_manager2)
   ;


----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |     1 |    62 |     2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |                     |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES           |     1 |    62 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_JOB_MGR_SAL_IDX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("JOB_ID"=:V_JOB AND ("MANAGER_ID"=TO_NUMBER(:V_MANAGER1) OR
              "MANAGER_ID"=TO_NUMBER(:V_MANAGER2)))

깔끔하게 InList Plan 으로 유도 되었다.
그렇다면 NUM_INDEX_KEYS 힌트의 마지막 인자인 숫자 2는 무엇을 의미하는가?
결합 인덱스의 2번째 컬럼(MANAGER_ID) 까지는 InList Plan 으로 풀리는 것을 의미한다.
거꾸로 이야기하면 NUM_INDEX_KEYS(a EMP_JOB_MGR_SAL_IDX 2) 로 힌트를 주면 인덱스의 3번째
컬럼인 SALARY 조건에 IN 조건을 주어도 InList Plan 으로 풀리지 않는다.
아래의 SQL및 Plan 을 보고 InList Plan 이 나왔다고 생각하면 오산이다.

EXPLAIN PLAN FOR
SELECT /*+ NUM_INDEX_KEYS(a EMP_JOB_MGR_SAL_IDX 2) */
       a.*
  FROM hr.employees a
 WHERE job_id = :v_job
   AND manager_id IN (:v_manager1, :v_manager2, :v_manager3)
   AND salary IN (:v_sal1, :v_sal2, :v_sal3);


----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |     1 |    62 |     2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |                     |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES           |     1 |    62 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_JOB_MGR_SAL_IDX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("JOB_ID"=:V_JOB AND ("MANAGER_ID"=TO_NUMBER(:V_MANAGER1) OR
              "MANAGER_ID"=TO_NUMBER(:V_MANAGER2) OR "MANAGER_ID"=TO_NUMBER(:V_MANAGER3)))
       filter("SALARY"=TO_NUMBER(:V_SAL1) OR "SALARY"=TO_NUMBER(:V_SAL2) OR
              "SALARY"=TO_NUMBER(:V_SAL3))

위 Plan 에서의 InList 는 Salary 조건과는 상관없이 Job_id 와 Manager_id  두가지 조건만으로 InList 가
나온것이다.
Salary 조건은 역시 Filter 로 빠진것을 알수 있다.
아래처럼 3으로 바꾼다면 인덱스의 3번째컬럼 까지 InList 로 풀리는걸 볼수 있다.

EXPLAIN PLAN FOR
SELECT /*+ NUM_INDEX_KEYS(a EMP_JOB_MGR_SAL_IDX 3) */
        a.*
  FROM hr.employees a
 WHERE job_id = :v_job
   AND manager_id IN (:v_manager1, :v_manager2, :v_manager3)
   AND salary IN (:v_sal1, :v_sal2, :v_sal3);


----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |     1 |    62 |     2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |                     |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES           |     1 |    62 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_JOB_MGR_SAL_IDX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("JOB_ID"=:V_JOB AND ("MANAGER_ID"=TO_NUMBER(:V_MANAGER1) OR
              "MANAGER_ID"=TO_NUMBER(:V_MANAGER2) OR "MANAGER_ID"=TO_NUMBER(:V_MANAGER3)) AND
              ("SALARY"=TO_NUMBER(:V_SAL1) OR "SALARY"=TO_NUMBER(:V_SAL2) OR "SALARY"=TO_NUMBER(:V_SAL3)))

하지만 manager_id 종류가 많지않은 경우에는 Range Scan이 더 나은 성능을 보이는 경우가 많으므로
무조건 적용해서는 안된다.
오늘 사용한 Rule 힌트나
NUM_INDEX_KEYS 힌트는 Production 시스템에서는 사용하면 안된다.
사용하려면 deprecate 된 힌트나 undocument 힌트를 사용한 SQL 만 따로 목록을 만들어서 관리해야 할것이다.

PS: 테스트 환경은 11g 에서 테스트 됬지만 10g 에서 테스트 해도 동일한 결과를 얻을수 있다.

Posted by extremedb
,