OPTIMIZER - PLAN TABLE 생성 과정 (Oracle 10g)

출 처 : http://blog.naver.com/cellpda01?Redirect=Log&logNo=29556263

- Oracle 10g에서 태스트 한 것입니다.

 

EXPLAIN PLAN 이란?

 

사용자들이 SQL 문의 액세스 경로를 확인하고
튜닝을 할 수 있도록 SQL 문을 분석하고 해석하여 실행 계획을 수립한 후
실행 계획을 테이블(plan_table)에 저장하도록 해주는 명령 입니다.

1. PLAN TABLE의 생성

Explain plan을 sql 에 포함해서 수행하면 옵티마이저가 실행 계획까지만
수립하여 plan_table에 저장해 둡니다.

PLAN을 사용하고자 하는 USER로 SQLPLUS LOGIN한 후
ORACLE_HOME/RDBMS/ADMIN/utlxplan.sql을 수행하여 plan_table을 생성 합니다.

C:\>SQLPLUS scott/tiger

SQL*Plus: Release 10.2.0.1.0 - Production on 화 10월 10 16:41:26 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

다음에 접속됨:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @C:\oracle\product\10.2.0\db_2\RDBMS\ADMIN\utlxplan.sql

테이블이 생성되었습니다.

SQL>

 

2. PLUSTRACE ROLE의 생성
sqlplus "/ as sysdba"로 접속하여 PLUSTRACE ROLE을 생성 합니다.
ORACLE_HOME/sqlplus/admin/plustrce.sql을 수행하여 plustrace role을 생성 합니다.

 

C:\>sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on 화 10월 10 17:01:26 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

다음에 접속됨:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @C:\oracle\product\10.2.0\db_2\sqlplus\admin\plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
          *
1행에 오류:
ORA-01919: 롤 'PLUSTRACE'(이)가 존재하지 않습니다

SQL> create role plustrace;

롤이 생성되었습니다.

SQL>
SQL> grant select on v_$sesstat to plustrace;

권한이 부여되었습니다.

SQL> grant select on v_$statname to plustrace;

권한이 부여되었습니다.

SQL> grant select on v_$mystat to plustrace;

권한이 부여되었습니다.

SQL> grant plustrace to dba with admin option;

권한이 부여되었습니다.

SQL>
SQL> set echo off
SQL>

 

3. PLUSTRACE Role의 부여

PLUSTRACE ROLE을 plan을 사용하고자 하는 유저에게 부여 합니다.

 

SQL> GRANT plustrace TO scott;

권한이 부여되었습니다.

권한을 부여 한다음.
다시 plan을 사용하는 유저로 접속을 합니다.

SQL> conn scott/tiger
연결되었습니다.

autotrace 상태를 on으로 바꿉니다.

SQL> SET AUTOTRACE ON  ;
SQL>

SQL문을 실행 합니다.

SQL> SELECT a.ename, a.sal, b.dname
  2   FROM emp a, dept b
  3  WHERE a.deptno = b.deptno;

 

ENAME             SAL DNAME
---------- ---------- --------------
SMITH             800 RESEARCH
ALLEN            1600 SALES
WARD             1250 SALES
JONES            2975 RESEARCH
MARTIN           1250 SALES
BLAKE            2850 SALES
CLARK            2450 ACCOUNTING
SCOTT            3000 RESEARCH
KING             5000 ACCOUNTING
TURNER           1500 SALES
ADAMS            1100 RESEARCH

ENAME             SAL DNAME
---------- ---------- --------------
JAMES             950 SALES
FORD             3000 RESEARCH
MILLER           1300 ACCOUNTING

14 개의 행이 선택되었습니다.

Execution Plan
----------------------------------------------------------
Plan hash value: 351108634

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

| Id  | Operation                                        | Name    | Rows  | Bytes | Cost (%CPU)| Time      |

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

|   0 | SELECT STATEMENT                       |               |     14 |    364 |        4       (0)| 00:00:01 |

|   1 |  NESTED LOOPS                             |               |     14 |    364 |        4       (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL                    | EMP        |     14 |    182 |        3       (0)| 00:00:01 |

|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT       |       1 |     13 |        1       (0)| 00:00:01 |

|*  4 |    INDEX UNIQUE SCAN                 | PK_DEPT  |       1 |          |        0       (0)| 00:00:01 |

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

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("A"."DEPTNO"="B"."DEPTNO")

Statistics
----------------------------------------------------------
        626  recursive calls
          0  db block gets
        134  consistent gets
         10  physical reads
          0  redo size
        856  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         10  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL>

⊙ 참고

☞ 8.17
@C:\oracle\ora81\RDBMS\ADMIN\utlxplan.sql; 
@C:\oracle\ora81\sqlplus\admin\plustrce.sql;

 

☞ 10g
@C:\oracle\product\10.2.0\db_2\RDBMS\ADMIN\utlxplan.sql
@C:\oracle\product\10.2.0\db_2\sqlplus\admin\plustrce.sql

 

⊙ Golden(골든)의 결과

 - Ctrl + P 키를 누루면 결과 나온다.

 

⊙ TOAD(토드)에서 설정방법

- Ctrl + E 키를 누루면 결과 나온다.

 

PLAN 테이블(TOAD_PLAN_TABLE)이 존재한다면 

TOAD 상단 메뉴에서
View 메뉴 > Options 메뉴 내용중 "Oracle > General" 에 Explain Plan Table Name 이 있습니다. 이곳에 "TOAD_PLAN_TABLE" 이라 명시하시고 사용하면 됩니다.