꿈꾸는 개발자, DBA 커뮤니티 구루비

튜닝 전

추출하고자 하는 대상은 DEPT 테이블의 데이터임에도 불구하고 EMP 테이블과 조인을 시도하였다.

결국 두 테이블의 관계가 1 : M 이므로 불필요하게 EMP 테이블을 모두 액세스하고 DISTINCT로 중복 제거를 하였다.

 
-- 성능 문제의 SQL 문장
SQL> SELECT DISTINCT D.*
     FROM   EMP  E, DEPT D
     WHERE  E.DEPTNO = D.DEPTNO;

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.003          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        2   18.740       21.832      40358      44244          0          8
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4   18.740       21.835      40358      44244          0          8

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      8   HASH UNIQUE (cr=44244 pr=40358 pw=0 time=21831482 us)
10000000    HASH JOIN  (cr=44244 pr=40358 pw=0 time=20004628 us)
      9     TABLE ACCESS FULL DEPT (cr=3 pr=0 pw=0 time=120 us)
10000000     TABLE ACCESS FULL EMP (cr=44241 pr=40358 pw=0 time=109 us)

튜닝 후

추출하고자 하는 대상만을 FROM절에 놓고 나머지는 체크만 하기 위해 EXISTS절에 위치시켰으며 이로 인해 수행속도가 대폭 감소되었다.

 
-- 추출하고자 하는 대상 테이블만을 FROM절에 위치시켰다.
SQL> SELECT D.*
     FROM   DEPT D
     WHERE  EXISTS (SELECT 1
                    FROM   EMP E
                    WHERE  E.DEPTNO = D.DEPTNO);

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.003          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        2    0.000        0.042          7         24          0          8
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4    0.000        0.046          7         24          0          8

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      8   NESTED LOOPS SEMI (cr=24 pr=7 pw=0 time=7432 us)
      9    TABLE ACCESS FULL DEPT (cr=4 pr=0 pw=0 time=263 us)
      8    INDEX RANGE SCAN EMP_N1 (cr=20 pr=7 pw=0 time=42135 us)(Object ID 6652496)