1. EXPLAN PLAN STATEMENT
옵티마이저가 주는 실행계획은 차선으로 생각.
실행계획은 만들겠다
EXPLAIN PLAN
SET STATEMENT_ID ='TEST1'
INTO PLAN_TABLE FOR
SELECT A.ENAME, A.DEPTNO, B.DNAME
FROM EMP A, DEPT B WHERE A.DEPTNO = B.DEPTNO;
해당 실행 계획이 만들어지고 플랜에 테이블에 플랜이 만들어진다.
그리고 결과를 보려면
SELECT LPAD(' ',2 * (LEVEL-1))||OPERATION as OPERATION,
OPTIONS, OBJECT_NAME, POSITION as POS,
OBJECT_INSTANCE as INST, ID, PARENT_ID as P_ID
FROM PLAN_TABLE
START WITH ID = 0 AND STATEMENT_ID ='TEST1'
CONNECT BY PRIOR ID = PARENT_ID AND STATEMENT_ID='TEST1';
2.오토트레이스를 이용하여 실행 계획+ 결과 + 트레이스 까지 볼수 있다.
SET AUTOTRACE ON
SQL> SET AUTOTRACE ON
SELECT A.ENAME, A.DEPTNO, B.DNAME
FROM EMP A, DEPT B
WHERE A.DEPTNO = B.DEPTNO;
SQL> SQL> 2 3
ENAME DEPTNO DNAME
---------- ---------- --------------
SMITH 20 RESEARCH
ALLEN 30 SALES
WARD 30 SALES
JONES 20 RESEARCH
MARTIN 30 SALES
BLAKE 30 SALES
CLARK 10 ACCOUNTING
SCOTT 20 RESEARCH
KING 10 ACCOUNTING
TURNER 30 SALES
ADAMS 20 RESEARCH
JAMES 30 SALES
FORD 20 RESEARCH
MILLER 10 ACCOUNTING
14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE // 룰베이스 동작했다.
1 0 NESTED LOOPS
2 1 TABLE ACCESS (FULL) OF 'EMP'
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
4 3 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
Statistics
----------------------------------------------------------
81 recursive calls
0 db block gets
42 consistent gets
5 physical reads
0 redo size
948 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
3. 오토트레이스를 이용하여 실행 계획만 볼 경우.
SET AUTOTRACE TRACEONLY EXPLAIN
SQL>SET AUTOTRACE TRACEONLY EXPLAIN
SQL> SELECT ename, job, sal, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND emp.empno > 7800
AND exists (SELECT *
FROM salgrade
WHERE emp.sal BETWEEN losal AND hisal);
2 3 4 5 6 7
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 FILTER
2 1 NESTED LOOPS
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
4 3 INDEX (RANGE SCAN) OF 'PK_EMP' (UNIQUE)
5 2 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
6 5 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
7 1 TABLE ACCESS (FULL) OF 'SALGRADE'
댓글