실행계획 읽기

EXPLAIN PLAN FOR
SELECT o.order_id, c.customer_nm
FROM   orders o
JOIN   customer c ON c.customer_id = o.customer_id
WHERE  o.order_dt >= SYSDATE - 7;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

가볍게 확인할 때 씁니다. 운영에서 실행하기 부담스러운 쿼리의 계획을 미리 볼 때 유용합니다.

-- 통계를 수집하도록 힌트를 붙여 실제로 실행
SELECT /*+ GATHER_PLAN_STATISTICS */
       o.order_id, c.customer_nm
FROM   orders o
JOIN   customer c ON c.customer_id = o.customer_id
WHERE  o.order_dt >= SYSDATE - 7;

-- 직전에 실행한 커서의 실제 계획
SELECT * FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST')
);

ALLSTATS LAST 옵션이 핵심입니다. 예상 건수와 실제 건수를 나란히 보여 줍니다. V$ 뷰 조회 권한이 필요해서 Part 1에서 SELECT_CATALOG_ROLE 을 부여했습니다.

SET AUTOTRACE ON EXPLAIN STATISTICS
SET LINESIZE 200

SELECT COUNT(*) FROM orders WHERE status_cd = 'DONE';

SET AUTOTRACE OFF

AUTOTRACE 는 PLUSTRACE 롤이 필요합니다. 계획과 함께 읽은 블록 수(consistent gets)를 보여 주는데, 이 수치가 튜닝 전후를 비교하는 가장 확실한 지표입니다. 실행 시간은 서버 부하에 따라 흔들리지만 블록 수는 일정합니다.

-------------------------------------------------------------------
| Id | Operation                    | Name           | Rows | Cost |
-------------------------------------------------------------------
|  0 | SELECT STATEMENT             |                | 1200 |  340 |
|  1 |  NESTED LOOPS                |                | 1200 |  340 |
|  2 |   TABLE ACCESS BY INDEX ROWID| ORDERS         | 1200 |  120 |
|* 3 |    INDEX RANGE SCAN          | IDX_ORDERS_DT  | 1200 |    8 |
|  4 |   TABLE ACCESS BY INDEX ROWID| CUSTOMER       |    1 |    1 |
|* 5 |    INDEX UNIQUE SCAN         | PK_CUSTOMER    |    1 |    0 |
-------------------------------------------------------------------

읽는 순서: 3 -> 2 -> 5 -> 4 -> 1 -> 0

3. IDX_ORDERS_DT 인덱스를 범위 스캔해 1200건의 ROWID를 얻는다
2. 그 ROWID로 ORDERS 테이블에서 실제 행을 읽는다
5. 각 행의 customer_id 로 PK_CUSTOMER 를 찾는다 (1200번 반복)
4. 찾은 ROWID로 CUSTOMER 테이블 행을 읽는다
1. 둘을 NESTED LOOPS 로 결합한다

* 표시가 붙은 단계는 아래 Predicate Information 에 조건이 나옵니다. access 는 인덱스로 찾아간 조건, filter 는 읽은 뒤 걸러낸 조건입니다. filter 로 많이 버려지고 있다면 인덱스 설계를 다시 봅니다.

---------------------------------------------------------------
| Id | Operation           | Name    | E-Rows | A-Rows | A-Time |
---------------------------------------------------------------
|  1 |  NESTED LOOPS       |         |     12 |  84210 | 00:00:47|
|* 2 |   TABLE ACCESS FULL | ORDERS  |     12 |  84210 | 00:00:01|
|* 3 |   INDEX UNIQUE SCAN | PK_CUST |      1 |      1 | 00:00:46|
---------------------------------------------------------------

2번 단계: 12건을 예상했지만 실제 84,210건
-> 그 예상 때문에 NESTED LOOPS 를 선택
-> 3번 단계가 84,210번 반복되며 47초 소요

이 경우 힌트로 HASH JOIN 을 강제하기 전에, 왜 12건으로 예상했는지를 먼저 봅니다. 통계가 오래됐거나, 조건이 복잡해 옵티마이저가 추정하지 못한 것입니다. 원인을 고치는 편이 오래갑니다.

-- 특정 인덱스 사용
SELECT /*+ INDEX(o IDX_ORDERS_DT) */ * FROM orders o WHERE ...;

-- 전체 스캔 강제
SELECT /*+ FULL(o) */ * FROM orders o WHERE ...;

-- 조인 방식 지정
SELECT /*+ USE_HASH(o c) */ ... FROM orders o JOIN customer c ...;

-- 조인 순서 고정
SELECT /*+ LEADING(c o) */ ... FROM orders o JOIN customer c ...;

-- 결과 일부만 빨리 (온라인 조회 화면)
SELECT /*+ FIRST_ROWS(50) */ ... FROM orders WHERE ...;

힌트의 별칭은 쿼리에서 쓴 별칭과 정확히 같아야 합니다. 다르면 힌트가 조용히 무시되고 아무 오류도 안 납니다. 힌트를 넣은 뒤에는 반드시 실행계획으로 적용 여부를 확인합니다.