A blog about Oracle Technology Network Japan
- January 19, 2015
しばちょう先生の試して納得！DBAへの道 第36回 SQLのパース処理とバインド変数の理解
しばちょう先生の試して納得！DBAへの道 indexページ▶▶
みなさん、こんにちは。年末年始のお休みに入ったと思ったら原因不明の高熱で寝込むというサラリーマンの鏡的（？）な状況だった、"しばちょう"こと柴田長（しばた つかさ）です。2015年も張り切って連載を続けていきたいと思っておりますし、記事への質問やリクエストのコーナーも開設させて頂きましたので、引き続きご愛読のほど、よろしくお願い致します。
さて、2015年一発目の今回は、第11回の記事の最後に置き去りにしていて最近記事化のリクエストを頂いた、バインド変数とAdaptive Cursor Sharing機能を取り上げさせて頂きたいと思います。とは言え、一回の連載ではボリュームが大きすぎますので、まずはAdaptive Cursor Sharing機能のメリットを感じて頂ける体験をして頂こうと考えています。
それは、SQLの実行計画を作成するハード・パースとそれを流用するソフト・パースのCPUコスト差の体感し、バインド変数の必要性を感じて頂くと同時に、 バインド変数を使用する上での問題点にも遭遇して頂きたいと思います。さあ、体験スタートです！！
【今回ご紹介するネタ一覧（逆引き）】
- SQLを連続実行するテクニック（演習2）
- V$SQLで共有プール上にキャッシュされたSQL文の確認方法（演習2）
- SQL*Plusでのバインド変数の使い方（演習3）
- ヒストグラム統計の取得方法と確認方法（演習4）
- 直前に実行したSQLの実行計画の確認方法（演習5）
- バインド・ピーク問題（演習6）
- Adaptive Cursor Sharingの効果（演習7）
1. 次のSQLで今回の演習で使用するTAB36表をTRYスキーマ内に作成して下さい。
$ sqlplus / as sysdba
SQL>
-- TBS36表領域と、この表領域をデフォルト表領域とするTRYユーザーの作成
create tablespace TBS36 datafile '＋DATA(DATAFILE)' size 512M ;
create user TRY identified by TRY default tablespace TBS36 ;
grant connect, resource, dba to TRY ;
-- TAB36表の作成
connect TRY/TRY
create table TAB36 (COL1 number NOT NULL, COL2 number, COL3 char(100)) ;
-- TAB36表へレコード100万件を挿入
declare
intNum_COL1 number := 0 ;
intInterval number := 10000 ;
begin
for i in 1..100 loop
insert into TAB36 select LEVEL + intNum_COL1, i , rpad('A',100,i)
from DUAL connect by LEVEL <= intInterval ;
commit ;
intNum_COL1 := intNum_COL1 + intInterval ;
end loop ;
end ;
/
-- TAB36表のCOL1列にユニーク索引を作成
create unique index IDX_TAB36_COL1 on TAB36(COL1) ;
-- TAB36表に主キー（COL1列）を定義。その際、COL1列のユニーク索引を利用
alter table TAB36 add primary key (COL1) using index ;
-- TAB36表のCOL2列にBツリー索引を作成
create index IDX_TAB36_COL2 on TAB36(COL2);
-- TAB36表のオプティマイザ統計情報を取得
exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TRY', tabname => 'TAB36');
はい、この連載の特徴とも言える環境構築の演習からスタートです。今回のスクリプトを見覚えのあった方は本当にありがとうございます。実は、冒頭に述べさせて頂いた「第11回 オプティマイザ統計情報の管理 ～ヒストグラムの効果を体験してみる～」のほぼコピペなのですね～。なので、PL/SQL部分の処理が何をしているのかが難しい場合には、第11回の演習0の解説を読んでみてくださいね。とは言え、SQLの実行計画を扱う上でTAB36表にどのような特徴のレコードが格納されているのかだけは重要ですから、その部分だけ述べておくと、「COL1列が1~100万の一意の値、COL2列が1~100へ1万レコード毎に1つずつ増加していく。ちなみに、COL3列はオマケです。」となります。これもほぼコピペです。楽してしまってスイマセン！！
2. TAB36表から一つのレコードを抽出するSELECT文を10000回連続実行した際の処理時間を測定して下さい。ただし、主キーを検索条件すること、かつ、一回の実行毎に条件値を変更して下さい。
$ sqlplus / as sysdba
SQL> -- バッファ・キャッシュと共有プールにキャッシュされている情報をクリア
alter system flush shared_pool ;
# リテラルのSELECT文を10000回連続実行
$
date ; for i in `seq 1 10000`; do
echo "select * from TAB36 where COL1=${i} ;"
done | sqlplus TRY/TRY > /dev/null ; date
Sat Jan 17 22:22:03 JST 2015
Sat Jan 17 22:22:23 JST 2015
--> 20秒
$ sqlplus / as sysdba
SQL> -- 実行計画が作成されたSQLの合計数
select count(*) from V$SQL where SQL_TEXT like 'select * from TAB36 where COL1=%' ;
COUNT(*)
----------
10000
ちょっと演習問題が分かりづらかったかもしれませんが、この演習2ではリテラルで検索条件を指定する10,000個のSELECT文を実行した際の処理時間、つまりは、10,000個のSQL文の実行計画を生成する為に要する時間を測定して欲しかったのです。ちなみに、ご存知の方も多いと思いますが、このSQLの実行計画を生成する処理をハード・パースと呼びます。
上記の回答例を少し解説させて頂きますが、「date;」コマンドで開始する3行が10,000個のSELECT文を連続実行している部分です。forループの処理でちょっとカッコよく書いてあるだけで、単純に10,000個のSELECT文を記述したSQLファイルをSQL*Plusでデータベースへ接続して実行することと同じ事ですね。
でもって、その10,000個のSELECT文の実行計画を生成する処理時間は20秒でした。あれ？この20秒の中には、データブロックをディスクからバッファ・キャッシュへ読み込む時間も含まれてしまうのでは？と思われた方、正解です。正しいです。なので、バッファ・キャッシュ上に必要なデータブロックが全てキャッシュされた状態（キャッシュ・ヒット率がほぼ100%）が望ましいですから、上記の回答例を複数回繰り返し実行して測定することをお薦めします。（この20秒はそうして複数回繰り返した結果です。）今回のTAB36表とその索引のサイズの合計は200MBも有りませんから、バッファ・キャッシュのサイズが200MBあれば全てのブロックがキャッシュに乗るでしょう。
最後に、V$SQLへ問合せして本当に10,000個のSELECT文の実行計画が生成されたのかを確認しています。forループ処理で生成したSELECT文をSQL_TEXT列でLike検索することで10,000個にヒットしていますから、事実ですね。
3. 演習3で実行したSELECT文をバインド変数化して、同様に10000回連続実行した際の処理時間を測定して下さい。
$ sqlplus / as sysdba
SQL> -- バッファ・キャッシュと共有プールにキャッシュされている情報をクリア
alter system flush shared_pool ;
# バインド変数のSELECT文を10000回連続実行
$
date ; for i in `seq 1 10000`; do
echo "variable B1 number"
echo "execute :B1 := ${i}"
echo "select * from TAB36 where COL1=:B1 ;"
done | sqlplus TRY/TRY > /dev/null ; date
Sat Jan 17 22:36:05 JST 2015
Sat Jan 17 22:36:33 JST 2015
--> 28秒
$ sqlplus / as sysdba
SQL> -- 実行計画が作成されたSQLの合計数
select count(*) from V$SQL where SQL_TEXT like 'select * from TAB36 where COL1=%' ;
COUNT(*)
----------
1
当たり前の演習の流れですが、リテラルの次はバインド変数ですね。しかし、意外にもSQL*Plusでバインド変数を使用する方法を知らない方がいらっしゃるように思えるので、この演習で是非とも抑えておいてくださいね。
まずは、「variable」でバインド変数名とデータ型を宣言します。上記の回答例では、バインド変数「B1」でNumber型ですね。その宣言したバインド変数に実際の値をセットしているのがexecuteコマンドです。最後に、SQL文中でバインド変数を使用する場合には変数名の前にコロン（:）を付ければ良いだけです。簡単ですよね。
と言う事で、バインド変数の値を変化させながら10,000回のSELECT文を実行すると28秒でした。はい？あれ？おかしい。私が皆さんに体験して頂きたかったことになっていないのですよ！！
念のため、この演習3で生成されたSQL文の実行計画の数をV$SQLビューで確認してみると1個であり、正しいですね。Where句の条件をリテラルでは無くバインド変数化して頂いたので、10,000個のSELECT文が全て同じSQL文を共有した形となります。つまりは、実行計画を生成するハード・パースは1回のみであり、残りの9,999回は実行計画を生成はしておらず、共有プール上のその実行計画を使い回したと言う事になります。
実行計画を生成するハード・パースに対して、実行計画を使い回すのをソフト・パースと呼びます。直感的にご理解頂けると思いますが、ハード・パースとソフト・パースはどちらの処理が重い（CPU消費量が多い、実行時間を要する）でしょうか？そうハード・パースですよね。しかし、演習2と3の結果を比較してみると如何でしょう？10,000回のハード・パースした演習2が20秒で、1回のハード・パースと9,999回のソフト・パースした演習3が28秒。これは何かオカシイです。
と言う事で、じーっとそれぞれのテスト・スクリプトを眺めてみましょう。分かりましたかね？バインド変数化した演習3では、バインド変数の宣言と値のセットの2行が追加されてしまっているのですよ。この部分の処理時間が上乗せされていないのか？と疑いが生まれたので、演習2のリテラルでの実行のテスト・スクリプトにも、この2行を追加して実行時間を測定してみました。
$ sqlplus / as sysdba
SQL> -- バッファ・キャッシュと共有プールにキャッシュされている情報をクリア
alter system flush shared_pool ;
# リテラルのSELECT文を10000回連続実行（バインド変数は使用しないがセット処理を含む）
$ date ; for i in `seq 1 10000`; do
echo "variable B1 number"
echo "execute :B1 := ${i}"
echo "select * from TAB36 where COL1=${i} ;"
done | sqlplus TRY/TRY > /dev/null ; date
Sat Jan 17 22:40:32 JST 2015
Sat Jan 17 22:41:12 JST 2015
--> 40秒
いかがでしょう？20秒から40秒へ処理時間が増加しましたね。なるほど、整理しますね。
演習2: リテラルのSQL実行、ハード・パース10,000回で20秒
演習3: バインド変数のセット、バインド変数のSQL実行、ハード・パース 1回 + ソフト・パース 9,999回で28秒
演習3+ : バインド変数のセット、リテラルのSQL実行、ハード・パース10,000回で40秒
懐かしい方程式の出番ですね。
- 「リテラルのSQL実行」= 20秒
- 「バインド変数のセット」+ 「リテラルのSQL実行」= 40秒
→ 「バインド変数のセット」= 40 – 20 = 20秒 - 「バインド変数のセット」+ 「バインド変数のSQL実行」= 28秒
→ 「バインド変数のSQL実行」= 28 – 20 = 8秒
よって、ハード・パースを10,000回実行した場合（リテラルのSQL実行）は20秒に対し、ハード・パースを1回+ソフト・パースを9,999回実行した場合（バインド変数のSQL実行）は8秒ですね。
ふー。ちょいと遠回りしましたが、私が皆さんに体験して頂きたかったことは正にこれです。ハード・パースはソフト・パースと比較して処理コストが大きいのです。なので、Where句の条件値だけが異なるような複数のSQL文は基本的にバインド変数化して共通化することがお薦めなのですね！
しかーし、何でもかんでもではないです。「基本的に」という部分が大切ですよ。ということで、次に、このバインド変数化によって、想定外のパフォーマンス・ダウンを引き起こすケースが存在することを体験しておきましょう。
4. TAB36表に COL1=0, COL2=1000000 のレコードを一件INSERTし、オプティマイザ統計情報（COL2列のヒストグラム統計を含む）を取得し直して下さい。
$ sqlplus TRY/TRY
SQL> -- 1レコードをTAB36表へ挿入
insert into TAB36 values(0, 1000000, 'Adding Record') ;
commit ;
-- TAB36表の統計情報をCOL2のヒストグラムを含めて取得
exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TRY', -
tabname => 'TAB36', -
cascade => FALSE, -
method_opt => 'FOR COLUMNS COL2 SIZE AUTO');
-- COL2列のヒストグラムの存在確認
set linesize 150 pages 5000
col TABLE_NAME for a12
col COLUMN_NAME for a12
select TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
from USER_TAB_COL_STATISTICS
where TABLE_NAME = 'TAB36' and COLUMN_NAME = 'COL2' ;
TABLE_NAME COLUMN_NAME NUM_DISTINCT HISTOGRAM
------------ ------------ ------------ ---------------
TAB36 COL2 101 FREQUENCY
-- COL2列のヒストグラム統計の確認
col COLUMN_NAME for a5
select TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE
from USER_TAB_HISTOGRAMS
where TABLE_NAME = 'TAB36' and COLUMN_NAME = 'COL2' ;
TABLE_NAME COLUM ENDPOINT_NUMBER ENDPOINT_VALUE
------------ ----- --------------- --------------
TAB36 COL2 55 1
TAB36 COL2 109 2
TAB36 COL2 170 3
..... .... ... ..
TAB36 COL2 5379 98
TAB36 COL2 5435 99
TAB36 COL2 5494 100
TAB36 COL2 5495 1000000
101 rows selected.
これまた、見覚えがある方がいらっしゃるのではないでしょうか？
追加して頂いたレコードは、これまでTAB36表に格納されていたレコードとは傾向が大きく異なる点が重要ですね。このような特異なレコードが格納された場合の注意点として、「第11回 オプティマイザ統計情報の管理 ～ヒストグラムの効果を体験してみる～」でヒストグラム統計情報を解説させて頂きました。そちらも非常に大切ですから、お時間がある方は、是非とも第11回の演習を今一度復習してみてくださいね。
ちなみに、上記の回答例では、DBMS_STATS.GATHER_TABLE_STATSの実行でCOL2列のヒストグラム統計が取得できていますが、これを実行しても取得できない場合には、一度、COL2列をWHERE句に含めたSQL文を実行してから再度DBMS_STATS.GATHER_TABLE_STATSを実行してみてくださいね。
5. TAB36表に対して次の検索条件を持つ2つのリテラルなSQL文を実行し、それぞれの実行計画を比較して下さい。 検索条件(1)：COL2 >= 1000000、検索条件(2)：COL2 >= 0
$ sqlplus /nolog SQL> -- 検証目的の為、バッファ・キャッシュと共有プールをフラッシュ connect / as sysdba alter system flush buffer_cache ; alter system flush shared_pool ; connect TRY/TRY set timing on -- 検索条件(1) : COL2 >= 1000000 select sum(ora_hash(COL3,10)) from TAB36 where COL2 >= 1000000 ; SUM(ORA_HASH(COL3,10)) ---------------------- 4 Elapsed: 00:00:00.01 select * from table(dbms_xplan.display_cursor()) ; PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------- SQL_ID 828rzjg5mwa7s, child number 0 ------------------------------------- select sum(ora_hash(COL3,10)) from TAB36 where COL2 >= 1000000 Plan hash value: 2904886222 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 6 (100)| | | 1 | SORT AGGREGATE | | 1 | 104 | | | | 2 | TABLE ACCESS BY INDEX ROWID| TAB36 | 182 | 18928 | 6 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IDX_TAB36_COL2 | 182 | | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("COL2">=1000000) -- 検索条件(2) : COL2 >= 0 select sum(ora_hash(COL3,10)) from TAB36 where COL2 >= 0 ; SUM(ORA_HASH(COL3,10)) ---------------------- 5050004 Elapsed: 00:00:00.27 select * from table(dbms_xplan.display_cursor()) ; PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------- SQL_ID 8v5vss1tp77p0, child number 0 ------------------------------------- select sum(ora_hash(COL3,10)) from TAB36 where COL2 >= 0 Plan hash value: 3280447420 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 4492 (100)| | | 1 | SORT AGGREGATE | | 1 | 104 | | | |* 2 | TABLE ACCESS FULL| TAB36 | 1000K| 99M| 4492 (3)| 00:00:54 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("COL2">=0)
まずは、リテラルなSQL文を使用して、それぞれの検索条件の値に応じた最適な実行計画を確認しておくのがこの演習5の目的です。
検索条件(1)：COL2 >= 1000000の場合は、索引IDX_TAB36_COL2を使用した「INDEX RANGE SCAN」のオペレーションが選択されていますね。一方、検索条件(2)：COL2 >= 0の場合は、表TAB36を全表検索「TABLE ACCESS FULL」するオペレーションが選択されています。
TAB36表は全部で1,000,001レコードが格納されていますが、COL2列が100以下のレコードが大部分（1,000,000レコード）を占めており、残りの1レコードが演習4で追加INSERTしたレコード（COL2列が1,000,000）です。よって、検索条件(1)に該当するレコードは1件ですから、明らかに索引アクセスが効率的ですよね。一方の検索条件(2)に該当するレコードは全件となりますから、こちらは全表検索が効率的です。
と、ここでバインド変数化を思い出してみてください。今回の二つのSELECT文は条件値が異なるだけですよね？と言う事は、バインド変数化して、SQL文を共通化することが出来るではないですか！！それでは早速試してみましょう！！（フッフッフ・・・）
6. 演習5で実行した2つのSQL文をバインド変数化して連続実行し、それぞれの実行計画を比較して下さい。
$ sqlplus /nolog SQL> -- 検証目的の為、バッファ・キャッシュと共有プールをフラッシュ connect / as sysdba alter system flush buffer_cache ; alter system flush shared_pool ; connect TRY/TRY set timing on -- バインド変数を宣言 variable B1 number -- 検索条件(1) : COL2 >= 1000000 ---- バインド変数に値をセットし、SQLを実行 execute :B1 := 1000000 select sum(ora_hash(COL3,10)) from TAB36 where COL2 >= :B1 ; Elapsed: 00:00:00.01 select * from table(dbms_xplan.display_cursor()) ; PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------- SQL_ID bgtxhhb97x9qa, child number 0 ------------------------------------- select sum(ora_hash(COL3,10)) from TAB36 where COL2 >= :B1 Plan hash value: 2904886222 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 6 (100)| | | 1 | SORT AGGREGATE | | 1 | 104 | | | | 2 | TABLE ACCESS BY INDEX ROWID| TAB36 | 182 | 18928 | 6 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IDX_TAB36_COL2 | 182 | | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("COL2">=:B1) -- 検索条件(2) : COL2 >= 0 execute :B1 := 0 select sum(ora_hash(COL3,10)) from TAB36 where COL2 >= :B1 ; Elapsed: 00:00:01.50 select * from table(dbms_xplan.display_cursor()) ; PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------- SQL_ID bgtxhhb97x9qa, child number 0 ------------------------------------- select sum(ora_hash(COL3,10)) from TAB36 where COL2 >= :B1 Plan hash value: 2904886222 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 6 (100)| | | 1 | SORT AGGREGATE | | 1 | 104 | | | | 2 | TABLE ACCESS BY INDEX ROWID| TAB36 | 182 | 18928 | 6 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IDX_TAB36_COL2 | 182 | | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("COL2">=:B1)
キター！やっちゃいましたね。これがバインド変数化することによって引き起こされる可能性のある典型的な事象です。
まず、検索条件(1)：COL2 >= 1000000のSELECT文をバインド変数化して実行していますが、ハード・パースによって、このSELECT文の実行計画が生成されます。その実行計画では、演習5でも確認して頂いた通り、検索条件(1)に対して最適なオペレーションの索引アクセス「INDEX RANGE SCAN」が選択されています。これは問題ありません。
次に、検索条件(2)：COL2 >= 0のSELECT文をバインド変数化した共通なSQL文で実行しています。共通なSQL文ですから実行計画も共有されるわけで、改めて実行計画を生成するハード・パースは行われず、ソフト・パースとなります。ソフト・パースの方が低いCPUコストだからイイよね！と思っていると・・・アウトです。検索条件(2)として最適な実行計画はTAB36を全表検索「TABLE ACCESS FULL」するオペレーションでしたが、ソフト・パースによって実行計画を流用することになるので、検索条件(1)に最適化された実行計画が流用されてしまうことになり、性能劣化が引き起こされる可能性があります。
検索条件(2)のSELECT文の実行時間としては、演習5のリテラル実行で最適な実行計画が選択された場合は0.27秒に対し、演習6のバインド変数化で最適ではない実行計画が選択された場合は1.5秒を要しています。今回の検証環境では表データが100MBちょっとのサイズですから影響はあまり目立たないかもしれませんが、本番環境で数十GB、数百GB、数TBの表サイズになった場合には、確実にパフォーマンス劣化問題として取り上げられることになってしまうでしょう。
これを回避するには、回避策(1) バインド変数を使わない。もしくは、回避策(2) バインド変数は使用するがバインド・ピーク機能を無効化する。の2パターンあります。
回避策(1)を全てのSELECT文に採用すれば、ハード・パースが多発してデータベース・サーバーのCPU消費量を上げることになってしまいますからお薦めしませんが、特定のSELECT文に限定してバインド変数を使用しないと言うのは、個人的にはありだと思っています。
もう一つの回避策(2)に関しては、「バインド・ピーク機能」を解説しておく必要がありますね。と言っても、皆さんは既にこの機能の恩恵を体験して頂いています。それは、演習6の検索条件(1)のSELECT文をバインド変数化して実行した際に、バインド変数にセットした値（execute :B1 := 1000000）に最適化された実行計画が選択された部分です。バインド変数を含むSQL文の実行計画を生成するハード・パースの処理において、バインド変数にセットされた値を考慮するのがバインド・ピーク機能なのです。
しかし、これによって、次に実行した検索条件(2)のSELECT文の実行計画としては最適ではなくなってしまいます。そして、もし検索条件(2)のSELECT文が先に実行されていたとしたら、検索条件(2)に対して最適な実行計画がハード・パースで生成されて、逆に検索条件(1)のSELECT文にとっては最適では無い状況になるでしょう。つまりは、バインド・ピーク機能は初めにハード・パースする（実行計画を生成する）際のバインド変数にセットされている値によって、どんな実行計画が生成されるのかが不安定になる傾向があるのですね。
なので、バインド・ピーク機能を無効化（これは隠しパラメータ_optim_peek_user_bindsなので使用する場合はサポートの指示に従って下さい）することでこれを回避するケースがありますが、この機能が無ければ「バインド変数にセットされた値を考慮 しない」 = 「ヒストグラム統計を無視」した実行計画が生成されるので、演習6の検索条件(1)のSELECT文の実行計画が最適化されなくなる可能性もあります。本当に悩ましいですよね。と言う事で、登場したのが、Adaptive Cursor Sharing機能なのです。凄く前置きが長くなりましたが、ようやくたどり着きましたね。では最後に少しだけ、その片鱗を体験して頂いて今回の演習を終わりにしましょう。
7. 演習6で実行した「検索条件(2)：COL2 >= 0」のバインド変数化したSQL文を再実行し、実行計画を確認して下さい。
SQL>
execute :B1 := 0
select sum(ora_hash(COL3,10)) from TAB36 where COL2 >= :B1 ;
SUM(ORA_HASH(COL3,10))
----------------------
5050004
Elapsed: 00:00:00.29
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID bgtxhhb97x9qa, child number 1
-------------------------------------
select sum(ora_hash(COL3,10)) from TAB36 where COL2 >= :B1
Plan hash value: 3280447420
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4492 (100)| |
| 1 | SORT AGGREGATE | | 1 | 104 | | |
|* 2 | TABLE ACCESS FULL| TAB36 | 1000K| 99M| 4492 (3)| 00:00:54 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL2">=:B1)
演習問題間違っていませんか？って思われた方、ありがとうございます。間違っていないのです。本当に、再実行するだけなのですよ。騙されたー！！と思って実行してみてくださいね。
ほら、今度は検索条件(2)に最適化された実行計画（TABLE ACCESS FULL）が選択されていましたよね？注目は、SQL_IDの横に出力している「child number 1」です。ここまでの回答例をざーっと見直して頂きたいのですが、全て「child number 0」だったと思います。また、SQL_IDは演習6で生成されたものと同一ですよね。つまり、同一のSQL_IDだけど、child numberが異なる実行計画であり、これがAdaptive Cursor Sharing機能によって生成されたものなのです。
演習6の検索条件(2)は最適では無い実行計画で実行されてしまったのですが、再実行した演習7では最適な実行計画で実行されているということです。過去の失敗の反省を次の機会に生かす。そんなインテリジェントな機能だと感じて頂けたかもしれませんね。
さて、今更ながらSQL文の実行計画を生成するハード・パースと流用するソフト・パース、さらにはバインド変数のメリット、デメリットを体験して頂きましたがいかがでしたでしょうか？最後に、少しだけとなってしまいましたが、Adaptive Cursor Sharing機能の効果もお見せすることが出来て良かったです。
私自身駆け出しのDBAだったころ、データベース・サーバーのサイジングの為の負荷テストを実施した際に、性能要件を満たす為に必要なCPUコア数が想像以上に多くなる結果が出てしまっていました。負荷テストではSQL文をテスト・ツールから大量に投げるわけですが、それらが全てリテラルなSQL文を投げてしまっていたのです。本番のアプリケーションはバインド変数のSQL文だったので、これは明らかにサイジングとして間違ったテストをしていたわけですね。テスト・ツールは変更できないので、CURSOR_SHARING初期化パラメータを使用して、リテラルなSQLをデータベース側で強制的にバインド変数化して再テストを実行したところ、CPU使用率が大幅に改善したことを鮮明に覚えています。
と言う事、皆様にも体感して頂きたかったのです。是非、感想や質問をお待ちしておりますね。今回も最 後まで体験して頂きましてありがとうございました。次回以降もどうぞよろしくお願い致します。