Monday, October 25, 2010

結合の手順の制御

Oracleが提供するヒントが強すぎて、基本的なことを忘れてしまうことがあります。たまにはヒントがないとしたらどんな方法で実行計画を制御しようかを考えてみれば面白いことを見つけるようになるでしょう。


例えば、次のような五つのテーブルがあります。


create table t1(c1, c2)
as
select
level, level
from dual
connect by level <= 5000;

create table t2(c1, c2)
as
select
level, level
from dual
connect by level <= 4000;

create table t3(c1, c2)
as
select
level, level
from dual
connect by level <= 3000;

create table t4(c1, c2)
as
select
level, level
from dual
connect by level <= 2000;

create table t5(c1, c2)
as
select
level, level
from dual
connect by level <= 1000;

create index t1_n1 on t1(c1);
create index t2_n1 on t2(c1);
create index t3_n1 on t3(c1);
create index t4_n1 on t4(c1);
create index t5_n1 on t5(c1);

exec dbms_stats.gather_table_stats(user, 't1');
exec dbms_stats.gather_table_stats(user, 't2');
exec dbms_stats.gather_table_stats(user, 't3');
exec dbms_stats.gather_table_stats(user, 't4');
exec dbms_stats.gather_table_stats(user, 't5');

テーブルT1, T2, T3, T4, T5を結合するクエリで結合手順はどうなりましょうか。テーブルのデータ分布を見るとT1(c1 between 1 and 10 条件のため)がドライビングテーブルになり、その次はT5(1000件)、T4(2000件)、T3(3000件)、T2(4000件)になるのが分かります。

explain plan for
select
*
from
t1, t2, t3, t5, t4
where
t1.c1 = t2.c1
and t1.c1 = t3.c1
and t1.c1 = t4.c1
and t1.c1 = t5.c1
and t1.c2 between 1 and 10
;

--------------------------------------
| Id | Operation | Name |
--------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN | |
|* 2 | HASH JOIN | |
|* 3 | HASH JOIN | |
|* 4 | HASH JOIN | |
|* 5 | TABLE ACCESS FULL| T1 |
| 6 | TABLE ACCESS FULL| T5 |
| 7 | TABLE ACCESS FULL | T4 |
| 8 | TABLE ACCESS FULL | T3 |
| 9 | TABLE ACCESS FULL | T2 |
--------------------------------------

結合の手順をT1 -> T2 -> T3 -> T4 -> T5としたいと言ったらどう?次のようにORDEREDヒントを使えばいいでしょう。またはLEADING(t1 t2 t3 t4 t5)ヒントを使ってもいいです。

explain plan for
select /*+ ordered */
*
from
t1, t2, t3, t5, t4
where
t1.c1 = t2.c1
and t1.c1 = t3.c1
and t1.c1 = t4.c1
and t1.c1 = t5.c1
and t1.c2 between 1 and 10
;

--------------------------------------
| Id | Operation | Name |
--------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN | |
|* 2 | HASH JOIN | |
|* 3 | HASH JOIN | |
|* 4 | HASH JOIN | |
|* 5 | TABLE ACCESS FULL| T1 |
| 6 | TABLE ACCESS FULL| T2 |
| 7 | TABLE ACCESS FULL | T3 |
| 8 | TABLE ACCESS FULL | T4 |
| 9 | TABLE ACCESS FULL | T5 |
--------------------------------------

仮にヒントを使用せず(そしてRBOではなくてCBOと言えば)、結合の手順が常に上のように出るようにしたいといったらどうすればいいでしょうか。ヒントに慣れてしまえばこのような簡単な質問に答えることが難しくなりかねません。


最も典型的な方法は次のようにt1.c1 = t3.c1の条件をt1.c1 + 0*t2.c1 = t3.c1のような形で使用することです。t3.c1で結合が可能になるためにはt2.c1の値を知らなければならないので、T2 -> T3の手順しか結合方法がありません。同じ原理で条件を付けば全ての結合手順を制御できます。


explain plan for
select
*
from
t1, t2, t3, t4, t5
where
t1.c1 = t2.c1
and t1.c1 + 0*t2.c1 = t3.c1 -- t2.c1値なしは t3.c1の結合不可能
and t1.c1 + 0*t3.c1= t4.c1 -- t3.c1値なしはt4.c1の結合不可能
and t1.c1 + 0*t4.c1 = t5.c1 -- t4.c1値なしは t5.c1結合不可能
and t1.c2 between 1 and 10
;

--------------------------------------
| Id | Operation | Name |
--------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN | |
|* 2 | HASH JOIN | |
|* 3 | HASH JOIN | |
|* 4 | HASH JOIN | |
|* 5 | TABLE ACCESS FULL| T1 |
| 6 | TABLE ACCESS FULL| T2 |
| 7 | TABLE ACCESS FULL | T3 |
| 8 | TABLE ACCESS FULL | T4 |
| 9 | TABLE ACCESS FULL | T5 |
--------------------------------------

このような方法を応用すれば結合の手順を自由に制御できます。例えば結合の手順をT1 -> T2 -> T4 -> T3 -> T5(T4がT3より先に結合されるように)とするためにはどうすればいいでしょうか。

explain plan for
select
*
from
t1, t2, t3, t4, t5
where
t1.c1 = t2.c1
{ ここにどんな条件があれば下のような結合手順が? }
and t1.c2 between 1 and 10
;

--------------------------------------
| Id | Operation | Name |
--------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN | |
|* 2 | HASH JOIN | |
|* 3 | HASH JOIN | |
|* 4 | HASH JOIN | |
|* 5 | TABLE ACCESS FULL| T1 |
| 6 | TABLE ACCESS FULL| T2 |
| 7 | TABLE ACCESS FULL | T4 |
| 8 | TABLE ACCESS FULL | T3 |
| 9 | TABLE ACCESS FULL | T5 |
--------------------------------------

解答はもう公開されていたも同然ですからここに書くことはしません。


でもやっぱりヒントが便利ですね。

Tuesday, October 12, 2010

RAW値の切り替え

ディクショナリーを検索してみるとRAWの形で提供されるコラムが時々あります。例えばUSER_TAB_COLSビューでコラムの最小値と最大値を見ると次のように意味の分からない変な値と見えます。

SQL> col column_name format a10
SQL> col data_type format a10
SQL> select
2 column_name,
3 data_type,
4 low_value,
5 high_value
6 from
7 user_tab_cols
8 where
9 table_name = 'T1'
10 ;

COLUMN_NAM DATA_TYPE LOW_VALUE HIGH_VALUE
---------- ---------- -------------------- --------------------
C1 NUMBER C102 C302
C2 VARCHAR2 6D616E79 6F6E65

このような値たちはUTL_RAW パッケージで切り替えられます。

SQL> select utl_raw.cast_to_number('C102') from dual;

UTL_RAW.CAST_TO_NUMBER('C102')
------------------------------
1

SQL> select utl_raw.cast_to_varchar2('6D616E79') from dual;

UTL_RAW.CAST_TO_VARCHAR2('6D616E79')
--------------------------------------------------------------------------------
many

次のように使用されるでしょう。

SQL> select
2 column_name,
3 data_type,
4 decode(data_type,
5 'NUMBER', utl_raw.cast_to_number(low_value)||'',
6 'VARCHAR2', utl_raw.cast_to_varchar2(low_value), low_value||'') as low_value,
7 decode(data_type,
8 'NUMBER', utl_raw.cast_to_number(high_value)||'',
9 'VARCHAR2', utl_raw.cast_to_varchar2(high_value), high_value||'') as high_value
10 from
11 user_tab_cols
12 where
13 table_name = 'T1'
14 ;

COLUMN_NAM DATA_TYPE LOW_VALUE HIGH_VALUE
---------- ---------- -------------------- --------------------
C1 NUMBER 1 10000
C2 VARCHAR2 many one

DBMS_STATS パッケージも同一な役割の関数を提供します。でも、パラメータの指定しかたが少し違います。使用の便利性のために次のように使用者定義関数を作ります。

SQL> -- dbms_stats (conversion)
SQL> create or replace function convert_me(p_value in raw, p_type in varchar2)
2 return varchar2
3 is
4 v_number number;
5 v_varchar2 varchar2(4000);
6 begin
7 if (p_type = 'NUMBER') then
8 dbms_stats.convert_raw_value(p_value, v_number);
9 return v_number|| '';
10 elsif (p_type = 'VARCHAR2') then
11 dbms_stats.convert_raw_value(p_value,v_varchar2);
12 return v_varchar2;
13 /* other data types */
14 else
15 return p_value || '';
16 end if;
17 end;
18 /

Function created.

SQL>
SQL> select convert_me('C102', 'NUMBER') from dual;

CONVERT_ME('C102','NUMBER')
--------------------------------------------------------------------------------
1

SQL> select convert_me('6D616E79', 'VARCHAR2') from dual;

CONVERT_ME('6D616E79','VARCHAR2')
--------------------------------------------------------------------------------
many

そうすると次のようにSQL文内で使用できます。

SQL> select
2 column_name,
3 data_type,
4 convert_me(low_value, data_type) as low_value,
5 convert_me(high_value, data_type) as high_value
6 from
7 user_tab_cols
8 where
9 table_name = 'T1'
10 ;

COLUMN_NAM DATA_TYPE LOW_VALUE HIGH_VALUE
---------- ---------- -------------------- --------------------
C1 NUMBER 1 10000
C2 VARCHAR2 many one

上のような面倒なことをしなくても良いようにOracleが自ら切り替えてくれたら良かったんですけどね。ともかく時折こんな切り替え作業が必要なところがあります。その際、活用すれば良いでしょう。

Monday, October 11, 2010

Batching NLJに対するOracleマニュアルの説明

Oracle 11gのBatching NLJにより物理I/Oが発生する時、整列が壊れてしまうように見える現象をこのポストで紹介したことがあります。


今日偶然にOracleマニュアル(Performance Tuning Guide)でOracle 11gのBatching NLJに対して説明している部分を見つかったんです。



単純で明確に説明してあります。この説明と共に私が説明したヒントと隠しパラメーターを理解していただければ本番環境で問題が発生した際、効果的に対処できるはずです。

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