Wednesday, October 6, 2010

PLAN_HASH_VALUE

SQL AとSQL Bの実行計画がお互いに同じか違うか比べられる一番易い方法は何でしょうか。


Oracleがて提供するPLAN_HASH_VALUEが正解ではないかと思います。この値は文字通り実行計画に対するハッシュねであります。ハッシュねなので100%の唯一性が保障されてはいないが、殆ど大部分の場合識別するに使えます。


Oracleでは同一なSQLテキストのSQLカーソルが複数の実行計画が持てます。こんな柔らかのおかげでいろいろな性能問題が現れますが...


簡単な例えを通して説明してみます。この例えはOracle 11gのAdaptive Cursor Sharing機能を利用しています。


Oracleのバージョンは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> 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 FullScanを使用します。

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」と値を繰り返しながらクエリを実行します。バインド変数を使用してAdpative 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つが存在します。すなわち、実際の実行計画は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 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テキストは同様ですがPLAN_HASH_VALUEは違う新しいSQL文が現れたら、実行計画の変更が発生したのではないか疑われます。


1つ注意していただきたいこてゃ、1つのSQLテキストに複数の実行計画が持てるから、精密な分析は必要だということです。

No comments:

Post a Comment