DATOR


요즘도 PLAN_TABLE 사용하세요? 물론 사용해야죠. Tunning


요즘에는 DBMS_XPLAN 패키지나 AUTOTRACE등을 많이 이용하기 때문에 PLAN_TABLE을 이용하여 실행계획을 보는 경우가 많으실지 모르겠습니다.

저같은 경우에도 쿼리를 작성하고 버릇적으로 AUTOT 시킨이후 작성하면서 머리속에 그려진 실행계획대로 움직이나, 그리고 결과가 맞는지를 확인하고 넘어갑니다.

그런데 가끔 자신에 입맛에 맞춰 실행계획이 필요할 경우도 있지 않을까요?

가령, 인덱스가 4개의 컬럼으로 구성이 되어 있는데, 실제 인덱스를 구성하는 컬럼중 첫번째만을 이용하여 타는 것을 화면에 표기해줄 필요가 있습니다.

전에 사내에서 이화식 사장님께서 교육을 하셨을때 하신말씀이 기억이 나네요.

정확하지는 않지만 '인간이 번거로운 것을 편리하고자 하는 것이 새로운 것을 만들어낸다.' 라는 것입니다 (정확하지 않다고 다시 한번 표기합니다.)

한번 귀찮더라도 대량의 쿼리를 점검해야 할경우가 있다면 PLAN_TABLE과 DBMS에서 재공하는 DICTIONARY를 효율적으로 사용하여 많은 양을 쿼리를 Eye Check하는 시간이 줄어들수 있는 장점인것이지요.

위의 예제를 기준으로 들면, PLAN_TABLE과 XXX_IND_COLUMNS를 사용하면 인덱스 매칭도를 실행계획에서도 볼수가 있게 할수 있다는 겁니다.

또한 OBJECT_INSTANCE와 DEPTH컬럼의 값으로 초보자도 손쉽고 빠르게 어떤 OBJECT부터 읽히는지 알수 있게 되는거죠.

그렇다면 분석함수등을 이용하여 해당 OPERATION에 따로 START라고 표기해줄수 있는 방법을 모색할수도 있게 되겠죠.

저는... 실행계획상에서 시간이 누적되어 나오기 때문에 누적시간과 해당 STEP에서만의 시간을 따로 표기하도록 해놨습니다.

 

자, 이제 PLAN_TABLE을 구성하는 컬럼이 어떻게 되어있는지 보겠습니다.

 

  • STATEMENT_ID : EXPLAIN PLAN 구문에서 지정한 SQL문의 식별자(필수아님)
  • TIMESTAMP : EXPLAIN PLAN 구문을 실행한 시간
  • REMARKS : 실행계획의 단계별로 추가할 수 있는 주석
  • OPERATION : 이것은 각 단계별로 오라클이 수행할 연산자 이름(연산자는 앞장에서 해시조인이나 테이블 엑세스 등 옵티마이저 연산자 등을 말함)
  • OPTIONS : 각 단계에서 수행된 연산을 위해 사용한 옵션(전체 검색, 범위 검색등)
  • OBJECT_NODE : 사용된 객체가 참고하는데 사용한 데이터베이스 링크
  • OBJECT_INSTANCE : 원래 SQL문에 있는 객체의 위치를 정수화한 값(FROM절에 위치한 테이블의 순서)
  • OBJECT_TYPE : 객체의 유형
  • OPTIMIZER : 현재의 옵티마이저모드(CHOOSE, FIRST_ROW등)
  • SEARCH_COLUMNS : 시작과 끝 키를 가진 인덱스 컬럼의 수
  • ID : 실행계획의 각 단계에 대한 ID
  • PARENT_ID : 각 단계의 결과를 가지고 연산을 하는 상위 단계의 ID
  • POSITION : 두 개의 단계가 같은 상위 ID를 가질때 가장 낮은 위치가 가장 먼저 실행
  • COST: CBO에 의해서 추정된 현재의 연산비용
  • CARDINALITY : 현재 연산을 통해서 추출될 테이블의 수(CBO의 추정치)
  • BYTES : 현재연산을 통해 추출된 바이트
  • OTHER_TAG : 이것은 OTHER 컬럼에서 SQL 텍스트의 기능을 표현한다.
  • PARTITION_START : 파티션 범위 검색을 할때 시작파티션
  • PARTITION_STOP : 엑세스된 마지막파티션
  • PARTTITON_ID : PARTITION_START와 PARTITION_STOP컬럼의 값 계산
  • OTHER : 병령 실행 슬레이브와 병렬쿼리에 대한 정보
  • DISTRIBUTION : 병렬 실행 슬레이브가 어떻게 레코드를 추출하는 방법
  • CPU_COST : 사용자 정의 CPU비용
  • IO_COST : 사용자 정의 I/O비용
  • TEMP_SPACE : 실행계획상에 TEMP공간을 사용할때 Bytes값으로 나옵니다.
  • ACCESS_PREDICATES : 해당 인덱스를 범위 검색하는 조건
  • FILTER_PREDICATES : 해당 오브젝트를 확인하는 조건
  • PROJECTION : 해당 STEP에서 무엇을 표현하였는지 를 보여준다고 보시면 됩니다.
  • TIME : 해당 STEP에서 작업에 걸린 시간을 초로 표시하여 줍니다.
  • QBLOCK_NAME : 쿼리 블록의 명칭을 보여줍니다. 사용자가 QB_NAME의 힌트와 함께 사용한다면 편리하겠죠.
  • (제가 영어에 워낙 약하니 읽으실때 긴장하시고 보셔야 합니다.)

     

    그리고 오라클에서 제공하는 해당 내용입니다. 아래를 따라 가보세요. 아래에 링크를 더 깊이있게 탐구하시는 것이 좋을듯 합니다. 저를 믿지는 마세요 ^^

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/ex_plan.htm

     

    PLAN_TABLE을 보는 기본 쿼리입니다. 맘껏 활용하세요.

    EXPLAIN PLAN 확인하는 기본 쿼리
     SELECT cardinality "Rows",
       lpad(' ',level-1)||operation||' '||options||' '||object_name "Plan"
      FROM PLAN_TABLE
    CONNECT BY prior id = parent_id
            AND prior statement_id = statement_id
      START WITH id = 0
            AND statement_id = 'st1'
      ORDER BY id;

     

    간단하게는 이렇게 말이죠.

     SELECT cardinality "Rows",
       lpad(' ',level*2-1)||operation||' '||options||' '||object_name "Plan", 
       B.IND_MAX "인덱스 총길이", 
       A.SEARCH_COLUMNS "SCAN에 참여된 컬럼", 
       A.ACCESS_PREDICATES, 
       A.FILTER_PREDICATES
      FROM PLAN_TABLE A, (SELECT INDEX_OWNER, INDEX_NAME, MAX(COLUMN_POSITION) IND_MAX FROM ALL_IND_COLUMNS
                          GROUP BY INDEX_OWNER, INDEX_NAME)  B
      WHERE A.OBJECT_OWNER = B.INDEX_OWNER(+)
      AND A.OBJECT_NAME = B.INDEX_NAME(+)
    CONNECT BY prior id = parent_id
            AND prior statement_id = statement_id
      START WITH id = 0
            AND STATEMENT_ID = '9'
      ORDER BY id


     

TAG

Leave Comments


profile우리는 하나님을 믿는다. 다른 모든 것들은 데이터로 검증해야 한다. 

Recent Trackback