/********************************************************************************************
-- Title : [10g] 오라클 실행 계획 보기
-- Reference : oracle.com
-- Key word : execution explain plan 오렌지 설정 orange
********************************************************************************************/
-- PLAN_TABLE 확인
SQL> select * from dba_tables where table_name = 'PLAN_TABLE';
-- PLAN_TABLE 생성
SQL> conn scott/tiger
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
-- ORANGE PLAN_TABLE 설정
PLAN TOOL(@F7) → OPTION → OPTIONS → PLAN TABLE → PLAN_TABLE(테이블명)
-- 권한 설정
SQL> grant select on v_$sesstat to scott;
SQL> grant select on v_$statname to scott;
SQL> grant select on v_$mystat to scott;
-- 실행 계획 수행
SQL> EXPLAIN PLAN FOR
SQL> select ~~
-- 실행 계획 보기
SQL> select * from table(dbms_xplan.display);
-- Plan_Table 수동 생성
-- ORANGE의 경우 orange.orange_plan_table로 생성하면 될까?
CREATE TABLE dbrang.PLAN_TABLE --<< 요기 소유자 변경
(
STATEMENT_ID VARCHAR2 (30),
TIMESTAMP DATE,
REMARKS VARCHAR2 (80),
OPERATION VARCHAR2 (30),
OPTIONS VARCHAR2 (255),
OBJECT_NODE VARCHAR2 (128),
OBJECT_OWNER VARCHAR2 (30),
OBJECT_NAME VARCHAR2 (30),
OBJECT_INSTANCE NUMBER,
OBJECT_TYPE VARCHAR2 (30),
OPTIMIZER VARCHAR2 (255),
SEARCH_COLUMNS NUMBER,
ID NUMBER,
PARENT_ID NUMBER,
POSITION NUMBER,
COST NUMBER,
CARDINALITY NUMBER,
BYTES NUMBER,
OTHER_TAG VARCHAR2 (255),
PARTITION_START VARCHAR2 (255),
PARTITION_STOP VARCHAR2 (255),
PARTITION_ID NUMBER,
OTHER LONG,
DISTRIBUTION VARCHAR2 (30),
CPU_COST NUMBER,
IO_COST NUMBER,
TEMP_SPACE NUMBER,
ACCESS_PREDICATES VARCHAR2 (4000),
FILTER_PREDICATES VARCHAR2 (4000)
)
TABLESPACE TS_DATA; --<< 요기 TS 변경