[Oracle] 플랜 확인방법

1. 플랜 테이블에 있는 xxx 아이디를 갖는 플랜 결과 삭제

delete from plan_table where statement_id='xxx'

 

2. 쿼리를 플랜 테이블에 기록

EXPLAIN plan set statement_id='xxx' into plan_table for 쿼리

 

3. 플랜 확인

SELECT

        TRIM(LEVEL)||'. '||LPAD (' ', LEVEL - 1)||operation||' '||options||' on '||object_name "Query",
        cost "Cost", cardinality "Rows", bytes "Bytes", decode(level,1,0,position) "Pos"
FROM plan_table
CONNECT BY prior id = parent_id

AND prior statement_id = statement_id
START WITH id = 0

AND statement_id = 'xxx' ORDER BY id

 

4. 테이블내 인덱스 확인 쿼리

SELECT * FROM ALL_IND_COLUMNS WHERE TABLE_NAME = '테이블명(대문자)'


출처 : http://blog.naver.com/goolungsoi?Redirect=Log&logNo=10091454349 



====플랜 해석방법====

이거 어캐보는지 좀 갈켜주세요. 맨 안쪽 테이블부터 검색을 하면서 밖으로 나오는 건가요? USSR01PK -> USSR010T -> USTP050T -> USSJ01PK -> USSJ010T -> COM_SYS_C0025372 -> COM_COMM010T 이런 순서로 검색하나요?   그리고 이걸 어떻게 이용하여 튜닝을 하게 되나요?

정해범 Cost 베이스로 본 것 같은데? 우선 Cost가 큰 것은 해당 연산을 수행하기 위해 자원을 낭비한 거지... 그런 걸 줄여야지. Cost 베이스 말고 다른 방법이 더 있을텐데 오래 되어서 생각이 잘... ㅋ~ USTP0501T는 Full table scan 한 것 같은데? 그런것두 없애 줘야 하구... got it?

 

#############################################################################

 

Plan에 대한 수행순서인데 정확한 판단은 힘들고 대략 아래 순서에 따라서 수행된

다고 이해하면 될 거야...(Optimizer가 어떻게 수행하는 지 정확한 판단을 내리기는

어렵거든... 암튼 참고가 되었으면 한다.)

 

①USTP050T 테이블을 Full Scan하고

②Full Scan한 각 각의 Row를 USSR010T 테이블과 조인하여
  USSR01PK 인덱스를 통한 1:1로 NESTED LOOP를 통해 매치된 결과가 나올 것이고

③2번항에서 나온 결과에 대한 각 각의 Row를 USSJ010T 테이블과 조인하여
  USSJ01PK 인덱스를 통한 1:1로 NESTED LOOP를 통해 매치된 결과가 나올 것이고

④3번항에서 나온 결과에 대한 각 각의 Row를 COMM010T 테이블과 조인하여
  SYS_C0025372 인덱스를 통한 1:1로 NESTED LOOP를 통해 매치된 결과가 
  최종 조회된 Row로 나타나는 구조로 되어 있네^^*

 

2,3,4번항에 대한 조인시 1:1 Unique한 NESTED LOOP가 반복되는 구조이므로
수행시간에 대한 영향은 그리 크지 않으므로 튜닝은 필요가 없을 테고

※문제는 1번항에 대한 Full Scan에 대한 영향도를 따져야 할 듯 함.

USTP050T테이블의 총 Row수가 1~2만건 정도라면 INDEX보다 Full Scan이 나을테고

 

그렇지 않다면
USTP050T테이블에 별도의 인덱스 구성이 필요할 것이라 생각됨.

조건에서 보면 A.PICK_YY = :pickYy와 A.DEPT_CD = :deptCD가 있는 것으로 보아
PICk_YY + DEPT_CD로 되어 있는 복합 인덱스 필요한 사항이라 생각 됨.

CREATE INDEX 인덱스명 ON USTP050T
(PICk_YY, DEPT_CD)
TABLESPACE 테이블스페이스명 
             .
             .
(테이블스페이명 이하는 기존 테이블에 대한 사항을 고려해서 적용하면 될 것 같음)

단, 인덱스 추가시에는 반드시 다를 애플리케이션에서 USTP050T테이블을 사용하는지
여부와 검색조건을 고려하여 생성하는 것이 바람직함.
(타 애플리케이션 로직에 영향을 줄 수가 있음)

########################################################################

 

SQL 문장의 최적화와 최적화 방법.

  1) 규칙기반의 최적화 - RBO (Rule Based Optimization).

     ORACLE이 정의하고 있는 우선 순위를 기반으로 해서 문장 수행의 과정을 정의한다.

     기본적인 방법이라는 장점이 있으나 ORACLE이 정의하고 있는 우선 순위가 항상 최

     적화 되어있지 않을 수 있다는 변수를 갖고 있다는 단점이 존재한다.

     - ORACLE이 정의하는 RBO 기반 처리 우선순위표.

분류	액세스 경로
1	Row ID(행 주소)에 의한 단일행 검색
2	클러스터 조인에 의한 단일행
3	유일키 또는 기본키가 있는 해시 클러스터키에 의한 단일행
4	유일키 또는 기본키에 의한 단일 행
5	클러스터 조인
6	해시 클러스터 키
7	인텍스된 클러스터 키
8	복합 인덱스
9	단일 열 인덱스
10	인덱스된 열에서 제한된 범위 검색
11	인덱스된 열에서 무제한 범위 검색
12	정렬-병합 조인
13	인덱스된 열에서 MAX 또는 MIN
14	인덱스된 열에서 ORDER BY
15	전체 테이블 스캔

   

 

     - Session 레벨의 최적화 관련 환경변수 설정을 이용하여 규칙기반의 최적화를 설정

       하기 위한 명령은 다음과 같다.

       . Alter Session Set Optimizer_Mode = 'Rule'

       SQL> alter session set optimizer_mode = 'rule' ;

       Session altered.

       SQL> select strcuid, strouid, dateproc, strtprocno from ecommloginfo ;

       STRCUID   STROUID   DATEPROC STRTPR

       ---------- ---------- --------    ------

       INTERPK    NATE1124   20020101

       INTERPK    WINKER71   20020101

       INTERPK    NATE1124   20000110

       INTERPK    GORA99     20000104  000002

       4 rows selected.

       Execution Plan

       ----------------------------------------------------------

       0      SELECT STATEMENT Optimizer=RULE

       1    0   TABLE ACCESS (FULL) OF 'ECOMMLOGINFO'

       Statistics

       -------------------------------------------------------

              0  recursive calls

              4  db block gets

              2  consistent gets

              0  physical reads

              0  redo size

           1721  bytes sent via SQL*Net to client

            700  bytes received via SQL*Net from client

              4  SQL*Net roundtrips to/from client

              1  sorts (memory)

              0  sorts (disk)

              9  rows processed

 

 

 

  2) 비용기반의 최적화 - CBO (Cost Based Optimization).

     통계자료를 기반으로 하여 문장 수행의 우선순위를 결정하므로 가장 정확한 최적화 기

     법으로 선택되어지고 있다. 그러나, 통계자료를 만들어 준다는 일이 항상 최신의 통계

     정보를 구축해 놓아야 한다는 부담으로 존재한다는 단점을 들 수 있다.

     - Session 레벨의 최적화 관련 환경변수 설정을 이용하여 비용기반의 최적화를 설정

       하기 위한 명령은 다음과 같다.

       . Alter Session Set Optimizer_Mode = Choose

         위의 명령은 SQL 문장을 수행함에 있어서 비용기반의 최적화를 지정한다.

       . Alter Session Set Optimizer_Mode = First_Row

         위의 명령은 SQL 문장을 수행함에 있어서 비용기반의 최적화를 수행하며 특히,

         명령수행의 첫 Response에 중점을 둔다.

       . Alter Session Set Optimizer_Mode = All_Row

         위의 명령은 SQL 문장을 수행함에 있어서 비용기반의 최적화를 수행하며 특히,

         명령수행의 전체 Response에 중점을 둔다.

     - 비용기반의 최적화를 위한 통계 정보의 구축.

       . Analyze Table table_name Compute Statistics

         지정한 테이블에 대한 전체의 통계를 구축한다.

       . Analyze Table table_name Estimate Statistics

         지정한 테이블에 대해 비율적 통계를 구축한다. Compute Statistic으로 생성한 통

         계에 비해서는 다소 정확도가 떨어질 수 있으나 생성속도가 빠르고 그 생성속도에

         비해서 상당히 정확한 통계를 구축한다.

       . Analyze Table table_name Delete Statistics

         구축되어 있는 통계자료를 삭제한다. 새로운 통계자료를 구축하기 전에 필요한 과

         정이다.

     - 통계 정보를 구축한 후 비용기반의 최적화 실습.

     SQL> analyze table ecommloginfo compute statistics for table for columns strcuid

           size 20 ;

     Table analyzed.

     SQL> alter session set optimizer_mode = 'choose' ;

     Session altered.

     SQL> select strcuid, strouid, dateproc from ecommloginfo

           2  where dateproc like '200001%' ;

     STRCUID    STROUID    DATEPROC

     ---------- ---------- --------

     INTERPK    WINKER71   20000110

     INTERPK    NATE1124   20000110

     INTERPK    GORA99     20000104

     Execution Plan

     ----------------------------------------------------------

     0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=26)

     1    0   TABLE ACCESS (FULL) OF 'ECOMMLOGINFO' (Cost=1 Card=1 Bytes

              =26)

     Statistics

     ----------------------------------------------------------

          0  recursive calls

          4  db block gets

          2  consistent gets

          0  physical reads

          0  redo size

       1327  bytes sent via SQL*Net to client

        718  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          3  rows processed

 

[출처] Oracle 튜닝 플랜보기|작성자 마이다스

출처 : http://blog.naver.com/hunnycho?Redirect=Log&logNo=140038413061