Plan Hash Value

오라클은 데이터베이스는 실행계획을 식별할 수 있는 plan hash value를 제공하고 있다. 이번 글에서는 plan hash value가 어떤 값으로 계산되는지 살펴보자.

테스트를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 NUMBER, c2 NUMBER);
CREATE INDEX t1_x1 ON t1 (c1);
CREATE INDEX t1_x2 ON t1 (c2);

아래 예제는 동일한 쿼리를 11.2, 12.2 버전에서 수행했다. 1번 오퍼레이션이 달라 다른 plan hash value가 계산된 것을 확인할 수 있다. 2-3번 쿼리는 힌트를 통해 11.2 버전과 동일한 실행계획을 생성했다. plan hash value가 2-1번 쿼리와 동일한 확인할 수 있다. plan hash value는 버전과 무관하게 operation, options 값과 그 순서에 따라 계산된다고 추정할 수 있다.

-- 2-1 : 11.2
SELECT * FROM t1 WHERE c1 = 1;

SQL_ID  0n68umt5jgcbr, child number 0
-------------------------------------

Plan hash value: 1955407900

---------------------------------------------
| Id  | Operation                   | Name  |
---------------------------------------------
|   0 | SELECT STATEMENT            |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    | -- !
|*  2 |   INDEX RANGE SCAN          | T1_X1 |
---------------------------------------------

-- 2-2 : 12.2
SELECT * FROM t1 WHERE c1 = 1;

SQL_ID  0n68umt5jgcbr, child number 0
-------------------------------------

Plan hash value: 711837727

-----------------------------------------------------
| Id  | Operation                           | Name  |
-----------------------------------------------------
|   0 | SELECT STATEMENT                    |       |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    | -- !
|*  2 |   INDEX RANGE SCAN                  | T1_X1 |
-----------------------------------------------------

-- 2-3 : 12.2
SELECT /*+ NO_BATCH_TABLE_ACCESS_BY_ROWID(T1) */ * FROM t1 WHERE c1 = 1;

SQL_ID  1p5ct9bw1rj4x, child number 0
-------------------------------------

Plan hash value: 1955407900

---------------------------------------------
| Id  | Operation                   | Name  |
---------------------------------------------
|   0 | SELECT STATEMENT            |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    | -- !
|*  2 |   INDEX RANGE SCAN          | T1_X1 |
---------------------------------------------

-- 2-4 : 12.2
SELECT   sql_id, plan_hash_value, operation, options
    FROM v$sql_plan
   WHERE sql_id IN ('0n68umt5jgcbr', '1p5ct9bw1rj4x')
     AND operation = 'TABLE ACCESS'
ORDER BY sql_id;

SQL_ID        PLAN_HASH_VALUE OPERATION    OPTIONS
------------- --------------- ------------ ----------------------
0n68umt5jgcbr       711837727 TABLE ACCESS BY INDEX ROWID BATCHED
1p5ct9bw1rj4x      1955407900 TABLE ACCESS BY INDEX ROWID

2 행이 선택되었습니다.

아래 3-1번 쿼리는 c2 = 1 조건절을 기술했다. plan hash value가 1468581797로 변경되었다. 인덱스 명을 변경한 후 쿼리를 다시 수행해보면 값이 2327017684로 변경된다. plan hash value가 오브젝트 ID가 아닌 오브젝트 명에 따라 계산된다고 추정할 수 있다.

-- 3-1
SELECT * FROM t1 WHERE c2 = 1;

SQL_ID  a7dwm1zyqkjc6, child number 0
-------------------------------------
Plan hash value: 1468581797

---------------------------------------------
| Id  | Operation                   | Name  |
---------------------------------------------
|   0 | SELECT STATEMENT            |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |
|*  2 |   INDEX RANGE SCAN          | T1_X2 | -- !
---------------------------------------------

-- 3-2
ALTER INDEX t1_x2 RENAME TO t1_x3;

인덱스가 변경되었습니다.

-- 3-3
SELECT * FROM t1 WHERE c2 = 1;

SQL_ID  a7dwm1zyqkjc6, child number 0
-------------------------------------
Plan hash value: 2327017684

---------------------------------------------
| Id  | Operation                   | Name  |
---------------------------------------------
|   0 | SELECT STATEMENT            |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |
|*  2 |   INDEX RANGE SCAN          | T1_X3 | -- !
---------------------------------------------

다른 계정에서 동일한 쿼리를 수행한 후 V$SQL 뷰를 조회해보면 커서가 하나 더 생성된 것을 확인할 수 있다. plan hash value가 동일하므로 오브젝트 소유자는 plan hash value 계산과 무관하다고 추정할 수 있다.

-- 4-1
SELECT * FROM t1 WHERE c1 = 1;

SQL_ID  0n68umt5jgcbr, child number 0
-------------------------------------

Plan hash value: 1955407900

---------------------------------------------
| Id  | Operation                   | Name  |
---------------------------------------------
|   0 | SELECT STATEMENT            |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |
|*  2 |   INDEX RANGE SCAN          | T1_X1 |
---------------------------------------------

-- 4-2
SELECT sql_id, child_number, plan_hash_value, parsing_schema_name
  FROM v$sql
 WHERE sql_id = '0n68umt5jgcbr';

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE PARSING_SCHEMA_NAME
------------- ------------ --------------- -------------------
0n68umt5jgcbr            0      1955407900 TUNA
0n68umt5jgcbr            1      1955407900 SCOTT

2 행이 선택되었습니다.

-- 4-3
SELECT   child_number, operation, options, object_owner, object_name 
    FROM v$sql_plan
   WHERE sql_id = '0n68umt5jgcbr'
     AND object_owner IS NOT NULL
ORDER BY child_number, id;

CHILD_NUMBER OPERATION        OPTIONS        OBJECT_OWNER OBJECT_NAME
------------ ---------------- -------------- ------------ -----------
           0 TABLE ACCESS     BY INDEX ROWID TUNA         T1
           0 INDEX            RANGE SCAN     TUNA         T1_X1
           1 TABLE ACCESS     BY INDEX ROWID SCOTT        T1
           1 INDEX            RANGE SCAN     SCOTT        T1_X1

4 행이 선택되었습니다.

아래 5-1, 5-2번 쿼리는 조건절을 달리 기술했지만 plan hash value가 1955407900로 동일한 것을 확인할 수 있다. predicates 정보는 역시 plan hash value 계산과 무관하다고 추정할 수 있다.

-- 5-1
SELECT * FROM t1 WHERE c1 = 1;

SQL_ID  0n68umt5jgcbr, child number 0
-------------------------------------

Plan hash value: 1955407900

---------------------------------------------
| Id  | Operation                   | Name  |
---------------------------------------------
|   0 | SELECT STATEMENT            |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |
|*  2 |   INDEX RANGE SCAN          | T1_X1 |
---------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=1)

-- 5-2
SELECT * FROM t1 WHERE c1 = 2;

SQL_ID  ag73xyk6dgdmh, child number 0
-------------------------------------

Plan hash value: 1955407900

---------------------------------------------
| Id  | Operation                   | Name  |
---------------------------------------------
|   0 | SELECT STATEMENT            |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |
|*  2 |   INDEX RANGE SCAN          | T1_X1 |
---------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=2)

-- 5-3
SELECT sql_id, plan_hash_value, access_predicates
  FROM v$sql_plan
 WHERE sql_id IN ('0n68umt5jgcbr', 'ag73xyk6dgdmh')
   AND access_predicates IS NOT NULL;

SQL_ID        PLAN_HASH_VALUE ACCESS_PREDICATES
------------- --------------- -----------------
0n68umt5jgcbr      1955407900 "C1"=1
ag73xyk6dgdmh      1955407900 "C1"=2

결론적으로 plan hash value는 V$SQL_PLAN 뷰를 기준으로 operation, options, object_name 값과 그 순서에 의해 계산된다고 추정할 수 있다.