[ORACLE] set autot 옵션, dbms_xplan.display

1. set autot option

set autotrace off/on
-. off : trace기능을 끄는 것
-. on : SQL 실행결과를 함께 화면에 출력 하라는 의미

-. traceonly : 실행결과는 출력하지 말라는 의미

 

1. set autotrace on
-> 실제 수행O + 수행 결과O + 실행 계획 + 실행 통계

2. set autotrace on explain

-> 실제 수행O + 수행 결과O + 실행 계획

 

3. set autotrace on statistics

-> 실제 수행O + 수행 결과O + 실행 통계

 

▶ 1 ~ 3은 실행결과를 출력해야 하므로 쿼리를 실제 수행한다.

 

4. set autotrace traceonly

-> 실제 수행 O + 수행결과 X +  실행계획 +  실행통계

 

5. set autotrace traceonly explain (set autot trace exp)

-> 실제 수행 X + 실행결과 X + 실행 계획

 

6. set autotrace traceonly statistics

-> 실제 수행 O + 실행결과 X  + 실행 통계

 

 

2. dbms_xplan.display

-. 단일 SQL문에 대해 예측 실행계획을 보여주는 function이다. 

-. 실행계획을 확인하려면 우선 Plan_table 생성 필요

$ORACLE_HOME/rdbms/admin/utlxplan.sql

-. 10g 버전 부터는 기본적으로 오라클이 sys.plan_table$ 테이블을 만들고 시노님도 만들어 놈

 

-. 파라미터설명

TABLE_NAME		Execution Plan이 저장되는 테이블을 지정하며, 기본값은 'PLAN_TABLE' 이다.
STATEMENT_ID		Execution Plan시 SET STATEMENT_ID를 지정한 경우 이를 불러올 수 있다. 값이 NULL일 경우 마지막에 실행된 문장을 불러온다.
FORMAT	BASIC	가장 기본적인 정보만 보여 준다.
	TYPICAL	Format의 Default값인 Typical은 SQL 튜닝에 필요한 Normal한 정보를 보여 준다. SQL 튜닝에 가장 유용하게 사용되는 Predicate Information이 제공 된다.
	ALL	Typical Format에 Query Block Name과 Column Projection Information이 추가로 제공된다
	OUTLINE	Typical Format에 추가적으로 Hidden Hint인 Outline Global Hint를 제공한다
	ADVANCED	ALL Format에 OUTLINE Format를 합친 정보를 제공한다
FILTER_PREDS		저장된 PLAN에서 일부 Row 또는 Row Set을 제한하여 출력할 수 있다.

-. 사용방법

explain plan for
select * from v$parameter;

1. select * from table(dbms_xplan.display(null,null,'advanced'));

2. select * from table(dbms_xplan.display(null,null,'typical'));

3. select * from table(dbms_xplan.display);

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 664232721

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |  4414 |     1 (100)| 00:00:01 |
|*  1 |  HASH JOIN        |          |     1 |  4414 |     1 (100)| 00:00:01 |
|*  2 |   FIXED TABLE FULL| X$KSPPI  |     1 |   249 |     0   (0)| 00:00:01 |
|   3 |   FIXED TABLE FULL| X$KSPPCV |   100 |   406K|     0   (0)| 00:00:01 |
------------------------------------------------------------------------------


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

   1 - access("X"."INDX"="Y"."INDX")
       filter(TRANSLATE("KSPPINM",'_','#') NOT LIKE '#%' OR
              "KSPPSTDF"='FALSE' OR BITAND("KSPPSTVF",5)>0)
   2 - filter("X"."INST_ID"=USERENV('INSTANCE') AND
              BITAND("KSPPIFLG",268435456)=0 AND TRANSLATE("KSPPINM",'_','#') NO
T

              LIKE '##%')