SQL튜닝의 도구 – DBMS_XPLAN(DISPLAY,DISPLAY_CURSOR)
n 오라클9i 이후 사용가능한 DBMS_XPLAN 패키지는 실행계획을 DISPLAY하고, 포맷을 주기위해 사용된다.
n 사용하기 위해서는 PLAN_TABLE이 존재해야 하는데 아래처럼 실행하면 된다.
SQL>conn sys/password as sysdba
--PLAN_TABLE생성
SQL> @C:\app\사용자명\product\11.2.0\dbhome_1\RDBMS\ADMIN/utlxplan.sql
--전역 동의어 생성
SQL> CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;
--모든 사용자가 PLAN_TABLE을 사용가능하도록 권한 부여
SQL> GRANT ALL ON sys.plan_table TO public;
n DISPLAY 함수 : PLAN_TABLE에 저장된 실행계획을 보여주는 함수로 먼저 EXPLAIN PLAN으로 SQL문을 실행해야 한다.
SQL>set autotrace off
SQL> explain plan
for
select empno, ename, sal, emp.deptno, dname from emp, dept
where emp.deptno = dept.deptno
and emp.deptno = 10;
해석되었습니다.
SQL> set linesize 130
SQL> set pagesize 0
SQL> select * from table(dbms_xplan.display);
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 90 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 3 | 90 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_DEPT_DEPTNO | 1 | | 0 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | EMP | 3 | 51 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
SQL> select * from table(dbms_xplan.display(format=>'ALL'));
Plan hash value: 2613277841
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 390 | 5 (20)| 00:00:01 |
| 1 | MERGE JOIN | | 13 | 390 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 13 | 221 | 3 (34)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 13 | 221 | 2 (0)| 00:00:01 |
|* 6 | INDEX FULL SCAN | IDX_EMP_DEPTNO | 13 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / DEPT@SEL$1
3 - SEL$1 / DEPT@SEL$1
5 - SEL$1 / EMP@SEL$1
6 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
6 - filter("EMP"."DEPTNO" IS NOT NULL)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) "EMP"."DEPTNO"[NUMBER,22], "DNAME"[VARCHAR2,14], "EMPNO"[NUMBER,22],
"ENAME"[VARCHAR2,10], "SAL"[NUMBER,22]
2 - "DEPT"."DEPTNO"[NUMBER,22], "DNAME"[VARCHAR2,14]
3 - "DEPT".ROWID[ROWID,10], "DEPT"."DEPTNO"[NUMBER,22]
4 - (#keys=1) "EMP"."DEPTNO"[NUMBER,22], "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10],
"SAL"[NUMBER,22]
5 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10], "SAL"[NUMBER,22],
"EMP"."DEPTNO"[NUMBER,22]
6 - "EMP".ROWID[ROWID,10], "EMP"."DEPTNO"[NUMBER,22]
DBMS_XPLAN.DISPLAY 함수는 네가지 파라미터 옵션이 있는데 아래와 같다.
[기본 형식]
DBMS_XPLAN.DISPLAY(
table_name IN VARCHAR2 DEFAULT 'PLAN_TABLE',
statement_id IN VARCHAR2 DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL',
filter_preds IN VARCHAR2 DEFAULT NULL);
table_name : PLAN테이블의 이름, 기본은 PLAN_TABLE
statement_id : 실행계획에 DISPLAY될 SQL명령문의 ID, 기본은 NULL
format : 실행계획이 보여지는데 있어서의 보여지는 정도를 설정. 기본값은 ‘TYPICAL’이며 ‘BASIC’,’ALL’,’SERIAL’등이 있다.
- BASIC : 최소한의 정보만 표시(ID, NAME, OPTION)
- TYPICAL : 기본 DISPLAY 형식, ID, NAME, OPTION, #ROWS, #BYTES, OPTIMIZER COST
- SERIAL : 실행계획이Parallel로 돌더라도 parallel을 제외하고 표시, TYPICAL과 유사하다.
- ALL : 최대레벨, TYPICAL에 PROJECTION, ALIAS, 분산환경이라면 REMOTE SQL정보등 표시)
filter_preds : 실행계획이 저장된 테이블에 대해 ROW를 제한하는 경우 사용, 기본값은 NULL이며 마지막에 실행된 SQL의 실행계획을 DISPLAY 한다. (filter_preds = ‘plan_id = 99’)
n DISPLAY_CURSOR 함수 : 사용되어진 커서에 대한 내용을 Display, 기본적으로 마지막 실행된 SQL명령어에 대한 내용을 출력
SQL> set autotrace off
SQL> select empno, ename, sal, emp.deptno, dname
from emp, dept
where emp.deptno = dept.deptno
and emp.deptno in (10, 20);
EMPNO ENAME SAL DEPTNO DNAME
---------- ---------- ---------- ---------- --------------
7369 SMITH 800 20 RESEARCH
……
7934 MILLER 1300 10 ACCOUNTING
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
SQL_ID dmjyh438h1pzs, child number 0
-----------------------------------------------
select empno, ename, sal, emp.deptno, dname from emp, dept where
emp.deptno = dept.deptno and emp.deptno in (10, 20)
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
|* 1 | HASH JOIN | | 5 | 150 | 6 (17)| 00:00:01 |
| 2 | INLIST ITERATOR | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 2 | 26 | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT_DEPTNO | 2 | | 1 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 8 | 136 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
select /* jclee_onj */ empno, ename, sal, dname
from emp , dept
where emp.deptno = dept.deptno;
EMPNO ENAME SAL DNAME
---------- ---------- ---------- --------------
7782 CLARK 2450 ACCOUNTING
……
7654 MARTIN 1250 SALES
14 개의 행이 선택되었습니다.
SQL> SELECT sql_id, child_number
2 FROM v$sql
3 WHERE sql_text LIKE '%jclee_onj%';
SQL_ID CHILD_NUMBER
------------- ------------
9h3s7mm930b45 0
8m1a14pmu281a 0
경 과: 00:00:00.04
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('9h3s7mm930b45',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
SQL_ID 9h3s7mm930b45, child number 0
-------------------------------------
SELECT sql_id, child_number FROM v$sql WHERE sql_text LIKE '%jclee_onj%'
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('8m1a14pmu281a',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
SQL_ID 8m1a14pmu281a, child number 0
-------------------------------------
select /* jclee_onj */ empno, ename, sal, dname from emp , dept where
emp.deptno = dept.deptno
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)|
| 1 | MERGE JOIN | | 14 | 420 | 6 (17)| 00:00:01
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT_DEPTNO | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 238 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 14 | 238 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
댓글 없음:
댓글 쓰기