[관리] Oracle EXPLAIN PLAN(실행계획) 세우기

Oracle EXPLAIN PLAN(실행계획) 세우기

SQL문의 실행계획을 보면 비효율이 발생한 원인을 알 수 있고 좋은 실행계획이

수립될 수 있도록 하는 방법을 찾을 수 있다. 이러한 실행계획을 자주 참조하는

것이 바로 옵티마이저를 이해할 수 있는 최선책이며, SQL문장을 튜닝할 수 있는

능력을 키울 수 있는 가장 좋은 방법이다.

사용자들이 SQL문의 액세스 경로를 확인하고 튜닝을 할 수 있도록 SQL문을 분석하고

해석하여 실행계획을 수립한 후 실행계획을 테이블(Plan_table)에 저장하도록 해준다.

EXPLAIN PLAN 명령은 오라클 옵티마이저에 의해서 SELECT, UPDATE, INSERT 그리고

DELETE문의 실행계획을 보여준다.

실행계획은 데이타를 축출하기 위해 오라클이 SQL 문장을 차례로 실행하는

작업 방법을 말한다.

– EXPLAIN PLAN 실습 Spool File

우선 $ORACLE_HOME/rdbms/admin/utlxplan.sql 이라는 스크립트를 실행하여

PLAN_TABLE 테이블을 생성한다.

SQL> truncate table plan_table;

Table truncated.

SQL> explain plan for

  2  select ename, sal

  3  from scott.emp, scott.dept

  4  where emp.deptno = dept.deptno;

Explained.

SQL> select operation, options, object_name, id parent_id

  2  from plan_table;

OPERATION         OPTIONS      OBJECT_NAME    PARENT_ID

—————– ———— ————- ———-

SELECT STATEMENT                                      0

NESTED LOOPS                                          1

TABLE ACCESS      FULL         EMP                    2

INDEX             UNIQUE SCAN  PK_DEPT                3

SQL> truncate table plan_table;

Table truncated.

SQL> select * from dept;

    DEPTNO DNAME          LOC

———- ————– ————-

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

SQL> explain plan for

  2  select emp.ename, emp.sal, dept.loc

  3  from scott.emp, scott.dept

  4  where emp.deptno = dept.deptno;

Explained.

SQL> select operation, options, object_name, id parent_id

  2  from plan_table;

OPERATION         OPTIONS OBJECT_NAME   PARENT_ID

—————– ——- ———— ———-

SELECT STATEMENT                                0

HASH JOIN                                       1

TABLE ACCESS      FULL    DEPT                  2

TABLE ACCESS      FULL    EMP                   3

                          

SQL> /                    

                          

OPERATION         OPTIONS OBJECT_NAME   PARENT_ID

—————– ——- ———— ———-

SELECT STATEMENT                                0

HASH JOIN                                       1

TABLE ACCESS      FULL    DEPT                  2

TABLE ACCESS      FULL    EMP                   3

SQL> set autotrace on

SQL> select ename, sal

  2  from scott.emp, scott.dept

  3  where emp.deptno = dept.deptno;

ENAME             SAL

———- ———-

SMITH             800

ALLEN            1600

WARD             1250

JONES            2975

MARTIN           1250

BLAKE            2850

CLARK            2450

SCOTT            3000

KING             5000

TURNER           1500

ADAMS            1100

JAMES             950

FORD             3000

MILLER           1300

14 rows selected.

Execution Plan

———————————————————-

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=14 Bytes=168)

   1    0   NESTED LOOPS (Cost=1 Card=14 Bytes=168)

   2    1     TABLE ACCESS (FULL) OF ‘EMP’ (Cost=1 Card=14 Bytes=140)

   3    1     INDEX (UNIQUE SCAN) OF ‘PK_DEPT’ (UNIQUE)

Statistics

———————————————————-

          0  recursive calls

         12  db block gets

          8  consistent gets

          3  physical reads

          0  redo size

       1064  bytes sent via SQL*Net to client

        425  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          2  sorts (memory)

          0  sorts (disk)

         14  rows processed

SQL> select operation, options, object_name, id parent_id

  2  from plan_table;

OPERATION         OPTIONS  OBJECT_NAME   PARENT_ID

—————– ——– ———— ———-

SELECT STATEMENT                                 0

HASH JOIN                                        1

TABLE ACCESS      FULL     DEPT                  2

TABLE ACCESS      FULL     EMP                   3

Execution Plan

———————————————————-

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=6 Bytes=144)

   1    0   TABLE ACCESS (FULL) OF ‘PLAN_TABLE’ (Cost=1 Card=6 Bytes=144)

Statistics

———————————————————-

          0  recursive calls

          4  db block gets

          2  consistent gets

          0  physical reads

          0  redo size

        731  bytes sent via SQL*Net to client

        425  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          4  rows processed

SQL> 1

  1* select operation, options, object_name, id parent_id

SQL> c/id/id,

  1* select operation, options, object_name, id, parent_id

SQL> l

  1  select operation, options, object_name, id, parent_id

  2* from plan_table

SQL> /

OPERATION         OPTIONS  OBJECT_NAME          ID  PARENT_ID

—————– ——– ———— ———- ———-

SELECT STATEMENT                                 0

HASH JOIN                                        1          0

TABLE ACCESS      FULL     DEPT                  2          1

TABLE ACCESS      FULL     EMP                   3          1

Execution Plan

———————————————————-

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=6 Bytes=162)

   1    0   TABLE ACCESS (FULL) OF ‘PLAN_TABLE’ (Cost=1 Card=6 Bytes=162)

Statistics

———————————————————-

          0  recursive calls

          4  db block gets

          2  consistent gets

          0  physical reads

          0  redo size

        795  bytes sent via SQL*Net to client

        425  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          4  rows processed

SQL> set autotrace off

SQL> select operation, options, object_name, id, parent_id

  2  from plan_table;

OPERATION         OPTIONS  OBJECT_NAME          ID  PARENT_ID

—————– ——– ———— ———- ———-

SELECT STATEMENT                                 0

HASH JOIN                                        1          0

TABLE ACCESS      FULL     DEPT                  2          1

TABLE ACCESS      FULL     EMP                   3          1

SQL> set autotrace on

SQL> select ename, sal

  2  from scott.emp, scott.dept

  3  where emp.deptno = dept.deptno;

ENAME             SAL

———- ———-

SMITH             800

ALLEN            1600

WARD             1250

JONES            2975

MARTIN           1250

BLAKE            2850

CLARK            2450

SCOTT            3000

KING             5000

TURNER           1500

ADAMS            1100

JAMES             950

FORD             3000

MILLER           1300

14 rows selected.

Execution Plan

———————————————————-

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=14 Bytes=168)

   1    0   NESTED LOOPS (Cost=1 Card=14 Bytes=168)

   2    1     TABLE ACCESS (FULL) OF ‘EMP’ (Cost=1 Card=14 Bytes=140)

   3    1     INDEX (UNIQUE SCAN) OF ‘PK_DEPT’ (UNIQUE)

Statistics

———————————————————-

          0  recursive calls

         12  db block gets

          8  consistent gets

          0  physical reads

          0  redo size

       1064  bytes sent via SQL*Net to client

        425  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         14  rows processed

SQL> set autotrace off

SQL> select operation, options, object_name, id, parent_id

  2  from plan_table;

OPERATION         OPTIONS  OBJECT_NAME          ID  PARENT_ID

—————– ——– ———— ———- ———-

SELECT STATEMENT                                 0

HASH JOIN                                        1          0

TABLE ACCESS      FULL     DEPT                  2          1

TABLE ACCESS      FULL     EMP                   3          1

SQL> select emp.ename, emp.sal, dept.loc

  2  from scott.emp, scott.dept

  3  where emp.deptno = dept.deptno;

ENAME             SAL LOC

———- ———- ————-

SMITH             800 DALLAS

ALLEN            1600 CHICAGO

WARD             1250 CHICAGO

JONES            2975 DALLAS

MARTIN           1250 CHICAGO

BLAKE            2850 CHICAGO

CLARK            2450 NEW YORK

SCOTT            3000 DALLAS

KING             5000 NEW YORK

TURNER           1500 CHICAGO

ADAMS            1100 DALLAS

JAMES             950 CHICAGO

FORD             3000 DALLAS

MILLER           1300 NEW YORK

14 rows selected.

SQL> select operation, options, object_name, id, parent_id

  2  from plan_table;

OPERATION         OPTIONS  OBJECT_NAME          ID  PARENT_ID

—————– ——– ———— ———- ———-

SELECT STATEMENT                                 0

HASH JOIN                                        1          0

TABLE ACCESS      FULL     DEPT                  2          1

TABLE ACCESS      FULL     EMP                   3          1

SQL> set autotrace on

SQL> select emp.ename, emp.sal, dept.loc

  2  from scott.emp, scott.dept

  3  where emp.deptno = dept.deptno;

ENAME             SAL LOC

———- ———- ————-

SMITH             800 DALLAS

ALLEN            1600 CHICAGO

WARD             1250 CHICAGO

JONES            2975 DALLAS

MARTIN           1250 CHICAGO

BLAKE            2850 CHICAGO

CLARK            2450 NEW YORK

SCOTT            3000 DALLAS

KING             5000 NEW YORK

TURNER           1500 CHICAGO

ADAMS            1100 DALLAS

JAMES             950 CHICAGO

FORD             3000 DALLAS

MILLER           1300 NEW YORK

14 rows selected.

Execution Plan

———————————————————-

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=14 Bytes=266)

   1    0   HASH JOIN (Cost=3 Card=14 Bytes=266)

   2    1     TABLE ACCESS (FULL) OF ‘DEPT’ (Cost=1 Card=4 Bytes=36)

   3    1     TABLE ACCESS (FULL) OF ‘EMP’ (Cost=1 Card=14 Bytes=140)

Statistics

———————————————————-

          0  recursive calls

         24  db block gets

         11  consistent gets

          0  physical reads

          0  redo size

       1253  bytes sent via SQL*Net to client

        425  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         14  rows processed

SQL> set autotrace on explain

SQL> select emp.ename, emp.sal, dept.loc

  2  from scott.emp, scott.dept

  3  where emp.deptno = dept.deptno;

ENAME             SAL LOC

———- ———- ————-

SMITH             800 DALLAS

ALLEN            1600 CHICAGO

WARD             1250 CHICAGO

JONES            2975 DALLAS

MARTIN           1250 CHICAGO

BLAKE            2850 CHICAGO

CLARK            2450 NEW YORK

SCOTT            3000 DALLAS

KING             5000 NEW YORK

TURNER           1500 CHICAGO

ADAMS            1100 DALLAS

JAMES             950 CHICAGO

FORD             3000 DALLAS

MILLER           1300 NEW YORK

14 rows selected.

Execution Plan

———————————————————-

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=14 Bytes=266)

   1    0   HASH JOIN (Cost=3 Card=14 Bytes=266)

   2    1     TABLE ACCESS (FULL) OF ‘DEPT’ (Cost=1 Card=4 Bytes=36)

   3    1     TABLE ACCESS (FULL) OF ‘EMP’ (Cost=1 Card=14 Bytes=140)

SQL> select emp.ename, emp.sal, dept.loc

  2  from scott.emp, scott.dept

  3  where emp.deptno = dept.deptno;

ENAME             SAL LOC

———- ———- ————-

SMITH             800 DALLAS

ALLEN            1600 CHICAGO

WARD             1250 CHICAGO

JONES            2975 DALLAS

MARTIN           1250 CHICAGO

BLAKE            2850 CHICAGO

CLARK            2450 NEW YORK

SCOTT            3000 DALLAS

KING             5000 NEW YORK

TURNER           1500 CHICAGO

ADAMS            1100 DALLAS

JAMES             950 CHICAGO

FORD             3000 DALLAS

MILLER           1300 NEW YORK

14 rows selected.

Execution Plan

———————————————————-

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=266)

   1    0   HASH JOIN (Cost=3 Card=14 Bytes=266)

   2    1     TABLE ACCESS (FULL) OF ‘DEPT’ (Cost=1 Card=4 Bytes=36)

   3    1     TABLE ACCESS (FULL) OF ‘EMP’ (Cost=1 Card=14 Bytes=140)

SQL> alter session set optimizer_mode = rule;

Session altered.

SQL> select emp.ename, emp.sal, dept.loc

  2  from scott.emp, scott.dept

  3  where emp.deptno = dept.deptno;

ENAME             SAL LOC

———- ———- ————-

SMITH             800 DALLAS

ALLEN            1600 CHICAGO

WARD             1250 CHICAGO

JONES            2975 DALLAS

MARTIN           1250 CHICAGO

BLAKE            2850 CHICAGO

CLARK            2450 NEW YORK

SCOTT            3000 DALLAS

KING             5000 NEW YORK

TURNER           1500 CHICAGO

ADAMS            1100 DALLAS

JAMES             950 CHICAGO

FORD             3000 DALLAS

MILLER           1300 NEW YORK

14 rows selected.

Execution Plan

———————————————————-

   0      SELECT STATEMENT Optimizer=RULE

   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)

SQL> select /*+ all_rows */ emp.ename, emp.sal, dept.loc

  2  from scott.emp, scott.dept

  3  where emp.deptno = dept.deptno;

ENAME             SAL LOC

———- ———- ————-

SMITH             800 DALLAS

ALLEN            1600 CHICAGO

WARD             1250 CHICAGO

JONES            2975 DALLAS

MARTIN           1250 CHICAGO

BLAKE            2850 CHICAGO

CLARK            2450 NEW YORK

SCOTT            3000 DALLAS

KING             5000 NEW YORK

TURNER           1500 CHICAGO

ADAMS            1100 DALLAS

JAMES             950 CHICAGO

FORD             3000 DALLAS

MILLER           1300 NEW YORK

14 rows selected.

Execution Plan

———————————————————-

   0      SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=3 Card=14 Bytes=266)

   1    0   HASH JOIN (Cost=3 Card=14 Bytes=266)

   2    1     TABLE ACCESS (FULL) OF ‘DEPT’ (Cost=1 Card=4 Bytes=36)

   3    1     TABLE ACCESS (FULL) OF ‘EMP’ (Cost=1 Card=14 Bytes=140)