ORACLE 쿼리 분석(실행계획)

1) 기본

a. 실행계획 이란?

   - Optimizer가 SQL에 대해 해석(parse)을 하고 데이터를 처리하기 위해
     작업 방법을 수립하게 되는데, 이를 실행계획이라고 한다.

 

b. 실행계획을 확인하기 위한 기본 셋팅

   - plan_table 생성
      start $ORACLE_HOME/rdbms/admin/utlxplan.sql 
      * utlxplan. sql 파일 내용 확인 (table 생성 쿼리 들어있음)
         !vi $ORACLE_HOME/rdbms/admin/utlxplan.sql

   - 권한 할당

      1. sys로 user 변경
        connect sys/as sysdba  
        show user

      2. Plustrace Role 생성
         start $ORACLE_HOME/sqlplus/admin/plustrce.sql
      3. 권한 할당

         grant plustrace to ecampus;

      4. user 변경
         connect ecampus/ecampus;

 

b. 실행계획 확인방법 (3가지)

     1. Explain Plan 명령 사용

          - 문법
          EXPLAIN PLAN SET STATEMENT_ID = 'TEST1'

          INTO PLAN_TABLE
          FOR SELECT[DELETE/UPDATE/INSERT] ~ ;

 

          - 확인

          1) SELECT LPAD(' ',4*(LEVEL-1),'-')||OPERATION||' '||
              OPTIONS||' '||OBJECT_NAME||' '||OBJECT_TYPE
              FROM PLAN_TABLE
              CONNECT BY PRIOR ID = PARENT_ID AND STATEMENT_ID = 'TEST1'
              START WITH ID = 0 AND STATEMENT_ID = 'TEST1';

 

           2) 프로시져 활용            

              SELECT * 
              FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','TEST1','ALL'));

 

          - 결과

            프로시져를 이용하면 결과가 보다 깔끔하게 나온다.

         

      2. Set Autotrace 기능 사용

         - 옵션  

           set timing on -- 시간정보 표시
           set autotrace on explain -- 데이터, 실행계획 까지만 표시
           set autotrace on statistics -- 데이터, 통계정보 표시 (실행계획 안보임)
           set autotrace traceonly -- 데이터 표시안함
           set autotrace traceonly explain -- 데이터 표시안하고, 실행계획만 표시
           set autotrace traceonly statistics --통계정보 표시 (데이터 읽음)

        * 장시간 데이터를 처리하는 쿼리의 경우, 데이터를 실제로 볼수 없다.

           그럴땐 set autotrace traceonly explain 옵션을 이용하여 실행.

        

        * Optimizer_mode 확인

           show parameter optimizer_mode;

           - value 값 설명(상세 내용은 이후에 다루겠음.)

                 1) Choose  : ANALYZE 통계정보 유무에 따라 CBO / RBO로 구분
                 2) Rule       : 오로지 Rule에 의해서만 실행
                 3) FIRST_ROWS(CBO) : 일부데이터를 먼저 보여줄수 있더로고 최적화 (Index 사용)
                 4) ALL_ROWS   (CBO) : Batch 시스템, 대량의 데이터를 다룸

         

         - 결과

          set timing on

          set autotrace traceonly

          select * from emp;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'EMP' 

 

2) 분석 방법

 

 

오른쪽 상단에 표시된 실행계획을 순서대로 보기 쉽게 정의한것이다.

 

왼쪽이 선행, 오른쪽이 후행.

4-3-6-7-2-7-1-0 의 순서가 된다.

이때 table access 와 index는 함께 묵어서 봐도 무방하다.

 

말로설명이 너무 어렵군요..  Pass

[출처] D1 - SQL 튜닝 (ORACLE) : 기본, 분석 방법 (CREATE TABLE DEVELOPMENT) |작성자 Msoon