오라클 plan table 생성 및 권한부여

 

 

원 저작자 : http://www.bysql.net/index.php?document_srl=10922&mid=ORACLE

[oracle@localhost ~]$ sqlplus '/as sysdba'

plan table 생성
SQL> conn scott/tiger
Connected.
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql

Table created.
플랜 테이블명 : PLAN_TABLE

오렌지 플랜테이블 설정

PLAN TOOL(ALT+F7) -> OPTION -> OPTIONS -> PLAN TABLE -> PLAN_TABLE(플랜테이블명)

PLUSTRACE 룰 생성

SQL> conn sys/manager as sysdba
Connected.

SQL>
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL>

plustrce.sql 내용 시작

//기존 롤 제거
SQL> drop role plustrace;
drop role plustrace
          *
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist

//롤 생성
SQL> create role plustrace;

Role created.
//권한부여
SQL>
SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

plustrce.sql 내용 끝

 

SQL>
SQL> set echo off
SQL> grant plustrace to scott(사용자);

Grant succeeded.

 

PLAN 보기 확인

 EXPLAIN PLAN FOR
  2  쿼리~~~

====================

SQL> conn scott/tiger
Connected.
SQL> set linesize 120
SQL> set autot on   or  set autot trace exp
SQL> select * from emp where rownum=1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20


Execution Plan
----------------------------------------------------------
Plan hash value: 1973284518

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    87 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |  1218 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

수동 plan 설정

DESC PLAN_TABLE; <플랜테이블 확인

EXPLAIN PLAN SET STATEMENT_ID = 'TEMP1'
INTO PLAN_TABLE FOR
SELECT *
FROM MEMBER
WHERE 1=1;

플랜확인
SELECT ID, PARENT_ID P_ID
,LPAD(' ',2*(LEVEL))||OPERATION,OPTIONS, OBJECT_NAME
FROM PLAN_TABLE
START WITH ID =0 AND STATEMENT_ID = 'TEMP1'
CONNECT BY PRIOR ID = PARENT_ID AND STATEMENT_ID = 'TEMP1';