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テキストに複数の実行計画が持てるから、精密な分析は必要だということです。
Posts
No comments:
Post a Comment