태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

PLAN_HASH_VALUE

오라클/SQL 튜닝 2010.10.06 10:55
SQL A과 SQL B의 실행 계획이 같은지 다른지 비교할 수 있는 가장 쉬운 방법은 무엇일까요?

오라클이 제공하는 PLAN_HASH_VALUE가 정답이 아닐까합니다. 이 값은 말그대로 실행 계획에 대한 해시 값입니다. 해시 값이기 때문에 100% 유일성이 보장되지는 않지만, 거의 대부분의 경우 식별자로 사용할 수 있습니다.

오라클에서는 동일한 SQL 텍스트를 가지는 SQL 커서가 복수 개의 실행 계획을 가질 수 있도록 허용합니다. 이런 유연함 덕분에 여러 가지 성능 문제가 나타나지만요.

간단한 예제를 통해 논의해보겠습니다. 이 예제는 Oracle 11g의 Adaptive Cursor Sharing 기능을 이용하고 있습니다.

오라클 버전은 11gR2입니다.

TPACK@ukja1120> select * from v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
편향된 데이터를 가지는 테이블 T1을 만듭니다. 컬럼 C1에 대해서 인덱스를 만들고, 히스토그램을 만듭니다.
TPACK@ukja1120> 
TPACK@ukja1120> create table t1
  2  as
  3  select level as c1 from dual connect by level <= 10000
  4  union all
  5  select 1 as c1 from dual connect by level <= 100000
  6  ;

Table created.

TPACK@ukja1120> 
TPACK@ukja1120> create index t1_n1 on t1(c1);

Index created.

TPACK@ukja1120> 
TPACK@ukja1120> exec dbms_stats.gather_table_stats(user, 't1', method_opt=>'for all columns size skewonly');

PL/SQL procedure successfully completed.
"100"의 값이 조건으로 사용될 때는 INDEX RANGE SCAN을 사용합니다.
TPACK@ukja1120> explain plan for
  2  select count(*) from t1 where c1 = 100;

Explained.

TPACK@ukja1120> @plan
TPACK@ukja1120> set line 200
TPACK@ukja1120> select * from table(dbms_xplan.display);
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |     3 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |       |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| T1_N1 |     1 |     3 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------
"1"의 값을 조건으로 사용할 때는 TABLE FULL SCAN을 사용합니다.
TPACK@ukja1120> explain plan for
  2  select count(*) from t1 where c1 = 1;

Explained.

TPACK@ukja1120> select * from table(dbms_xplan.display);
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |    53   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 99173 |   290K|    53   (4)| 00:00:01 |
---------------------------------------------------------------------------
이제 "100"의 값과 "1"의 값을 반복적으로 사용하면서 쿼리를 수행합니다. 단, 바인드 변수를 사용해서 Adaptive Cursor Sharing이 동작하도록 유도합니다.
TPACK@ukja1120> var b1 number;
TPACK@ukja1120> exec :b1 := 100;

PL/SQL procedure successfully completed.

TPACK@ukja1120> select count(*) from t1 where c1 = :b1;

  COUNT(*)
----------
         1

TPACK@ukja1120> 
TPACK@ukja1120> exec :b1 := 1;

PL/SQL procedure successfully completed.

TPACK@ukja1120> select count(*) from t1 where c1 = :b1;

  COUNT(*)
----------
    100001

TPACK@ukja1120> 
TPACK@ukja1120> exec :b1 := 100;

PL/SQL procedure successfully completed.

TPACK@ukja1120> select count(*) from t1 where c1 = :b1;

  COUNT(*)
----------
         1

TPACK@ukja1120> 
TPACK@ukja1120> exec :b1 := 1;

PL/SQL procedure successfully completed.

TPACK@ukja1120> select count(*) from t1 where c1 = :b1;

  COUNT(*)
----------
    100001

TPACK@ukja1120> 
TPACK@ukja1120> exec :b1 := 100;

PL/SQL procedure successfully completed.

TPACK@ukja1120> select count(*) from t1 where c1 = :b1;

  COUNT(*)
----------
         1

TPACK@ukja1120> 
TPACK@ukja1120> exec :b1 := 1;

PL/SQL procedure successfully completed.

TPACK@ukja1120> select count(*) from t1 where c1 = :b1;

  COUNT(*)
----------
    100001

TPACK@ukja1120> 
TPACK@ukja1120> exec :b1 := 100;

PL/SQL procedure successfully completed.

TPACK@ukja1120> select count(*) from t1 where c1 = :b1;

  COUNT(*)
----------
         1

TPACK@ukja1120> 
TPACK@ukja1120> exec :b1 := 1;

PL/SQL procedure successfully completed.

TPACK@ukja1120> select count(*) from t1 where c1 = :b1;

  COUNT(*)
----------
    100001

네 개의 차일드 커서가 생겼네요. (네 개가 생기는 이유는 무엇일까요? 추가적인 설명 필요!)
TPACK@ukja1120> col sql_id new_value sql_id
TPACK@ukja1120> select sql_id, version_count, plan_hash_value
  2  from v$sqlarea
  3  where sql_text = 'select count(*) from t1 where c1 = :b1';

SQL_ID        VERSION_COUNT PLAN_HASH_VALUE
------------- ------------- ---------------
7dwqb1wjmp5hm             4        73337487
차일드 커서를 나타내는 V$SQL 뷰를 보면 자식의 개수는 네 개이지만 PLAN_HASH_VALUE는 2개가 존재합니다. 즉, 실제 실행 계획은 두 개(INDEX RANGE SCAN + TABLE FULL SCAN) 뿐입니다.
TPACK@ukja1120> select sql_id, child_number, plan_hash_value
  2  from v$sql
  3  where sql_id = '&sql_id';
old   3: where sql_id = '&sql_id'
new   3: where sql_id = '7dwqb1wjmp5hm'

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE
------------- ------------ ---------------
7dwqb1wjmp5hm            0        73337487
7dwqb1wjmp5hm            1        73337487
7dwqb1wjmp5hm            2        73337487
7dwqb1wjmp5hm            3      3724264953
DBMS_XPLAN.DISPLAY_CURSOR 함수를 이용해서 실행 계획을 직접 조회해보면 더 상세한 정보를 얻을 수 있습니다.
TPACK@ukja1120> select * from table(dbms_xplan.display_cursor('&sql_id', null));
old   1: select * from table(dbms_xplan.display_cursor('&sql_id', null))
new   1: select * from table(dbms_xplan.display_cursor('7dwqb1wjmp5hm', null))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7dwqb1wjmp5hm, child number 0
-------------------------------------
select count(*) from t1 where c1 = :b1

Plan hash value: 73337487

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |       |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| T1_N1 |     1 |     3 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - access("C1"=:B1)

SQL_ID  7dwqb1wjmp5hm, child number 1
-------------------------------------
select count(*) from t1 where c1 = :b1

Plan hash value: 73337487

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |       |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| T1_N1 |     1 |     3 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - access("C1"=:B1)

SQL_ID  7dwqb1wjmp5hm, child number 2
-------------------------------------
select count(*) from t1 where c1 = :b1

Plan hash value: 73337487

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |       |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| T1_N1 |     1 |     3 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - access("C1"=:B1)

SQL_ID  7dwqb1wjmp5hm, child number 3
-------------------------------------
select count(*) from t1 where c1 = :b1

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    53 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 99173 |   290K|    53   (4)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("C1"=:B1)


76 rows selected.
PLAN_HASH_VALUE가 실행 계획을 구분하는데 사용될 수 있다는 특징을 잘 이용하면 실행 계획 변경을 추적하는데 사용할 수도 있습니다. 가령 AWR에 저장된 Top SQL과 SQL 텍스트는 동일하지만(SQL_ID) PLAN_HASH_VALUE은 다른 새로운 SQL 문이 등장했다면 실행 계획의 변경이 발생한 것으로 의심해볼 수 있습니다.

단, 이런 현상이 발생했다고 하더라도 하나의 SQL 텍스트가 여러 개의 실행 계획을 가질 수 있기 때문에 보다 정밀한 분석이 필요할 것입니다.

저작자 표시
신고

'오라클 > SQL 튜닝' 카테고리의 다른 글

조인 순서 제어하기 2  (2) 2010.11.04
조인 순서 제어하기  (3) 2010.10.25
비주얼 SQL 튜닝?  (6) 2010.10.21
Batching NLJ에 대한 오라클 매뉴얼의 설명  (0) 2010.10.11
PLAN_HASH_VALUE  (0) 2010.10.06
Trackback 0 : Comment 0

Write a comment

티스토리 툴바