門外不出のOracle現場ワザ

第4章　Oracleデータベースの頭脳 「オプティマイザ」徹底研究

日本オラクル株式会社 コンサルティング統括本部テクノロジーコンサルティング本部 小田 圭二(おだ けいじ)

目次

• Part1　 SQLを最適化するコストベースオプティマイザの基本機能

  • はじめに

  • CBOを使用する理由

  • SQL文の処理におけるオプティマイザの役割

• Part2　 CBOは何を見てどう判断するのか

  • CBOのアクセスパス選択方法

  • ヒストグラム

  • CBOとバインド変数

  • バインドピーク（Bind peek）

  • I/O + CPUコストモデル

  • CBOとフルスキャン

  • CBOとキャッシュ効率

  • CBOとパラメータ

  • CBOと結合順序

• Part3　 オプティマイザ統計の管理

  • 自動統計収集

  • 統計履歴の自動保存

  • 動的サンプリング

• Part4　 実行計画の固定と自動チューニングオプティマイザの活用方法

  • 実行計画を固定する機能

  • 自動チューニングオプティマイザ

  • CBOを使いこなすためには

Part2　CBOは何を見てどう判断するのか

CBOのアクセスパス選択方法

パート1では、CBOとRBOの違いと、SQL文の処理におけるCBOの役割を見てきました。パート2では、例を示しながらCBOの動作を詳しく見ていきます。

CBOは、ユーザーが指定した条件に対するヒット件数を予測してアクセスパスを選択します。その際に、テーブルの行数や列値の最小値、最大値、NULL値の数、値の種類といった統計を利用しています（場合によっては値の分布のヒストグラムを利用することも可能です）。

一般に、ヒット件数が15％以下に絞り込める場合は、フルテーブルスキャンよりもインデックスアクセスのほうが効果的とされています。しかしCBOは、例えばヒット件数の見積もりが1％であったとしても、フルテーブルスキャンを選択することがあります。そしてこれは正しい選択なのです。どういうことなのか、ちょっと考えてみてください。 トリックを見破れたでしょうか。これを解くには、まずコスト見積もりの際にCBOが利用する統計情報について押さえておく必要があります。 CBOはヒット件数を予測する際に、先ほど挙げた統計だけでなく、テーブルのHWM（High Water Mark）までのブロック数や索引の深さ、リーフブロック数およびキー値あたりのリーフブロック数といった物理情報も利用しています。このような統計の1つに「クラスタリングファクタ（クラスタ化係数）」があります。

クラスタリングファクタは、索引の統計情報の1つです。この統計値が大きいほど、索引が付けられている列の値がテーブル全体に満遍なく分布していることを意味します。クラスタリングファクタは、索引内で隣り合ったレコードが異なる表ブロックをポイントしている場合にカウントアップされます。したがって、クラスタリングファクタの最大値はレコード数（索引内の隣り合ったレコードがすべて異なる表ブロックを指しているケース）と等しくなります。つまり極端な例では、ユーザーの指定した値がテーブルのすべてのデータブロックに格納されている場合もあり得るわけです。

全体の行数から見れば1％であっても、テーブルのすべてのブロックから値を読み込まなければならないケースでは、テーブル全体をマルチブロックリード 注5でスキャンするほうが効率的です。インデックスアクセスの場合、テーブルのブロックを1つずつ読み込みますが、フルテーブルスキャンでは複数ブロックをまとめて1つのI/Oで読み込めるためです。 このようなケースをRBOで実行すると、必ずインデックスアクセスを行ないます。もちろん最適なアクセスパスではありません。CBOの賢さを実感していただけたでしょうか。

注5：異なる複数のブロックを一度に読み込むこと。

クラスタリングファクタが極端に高い場合

クラスタリングファクタが極端に高い場合はどのような動作になるのかを見てみましょう。 表TEST1のそれぞれのブロックに、列Cul2の値として1～100までの値が格納されているとします。Cul2に索引IND_Cul2を作成し、この索引を利用してアクセスした場合を考えます。ここで「WHERE Cul2=100」という条件句の問い合わせが発行されたとすると、値の種類は100種類ですから、1％（100種類中の1種類）の行がヒットするはずです。ところが100という値はすべての表ブロックに格納されているため、索引にアクセスした後、すべての表ブロックをシングルブロックリードで読み込む必要があることが分かります（図1）。

図1 すべての表をシングルブロックリードで読み込む例

索引を使用した場合の大量のシングルブロックリードと、表フルスキャン時のマルチブロックリードの性能差を見てみましょう。LIST1とLIST2はSQL Traceの出力結果です。 EVENT10046（Tips参照）という方法で取得したため、SQL実行時の待機イベントの情報も出力されています。

LIST1 フルテーブルスキャン実行時

LIST2 インデックスレンジスキャン実行時

両方のケースとも条件句は「Cul2=100」で同じ行数がヒットしますが、LIST1のケースではFULLヒントによってフルテーブルスキャンを強制しており、LIST2のケースではINDEXヒントによってインデックスアクセスを強制しています。なお、表領域はALLOCATION_TYPE=SYSTEMのローカル管理表領域を使用しています。 これらの統計を見ると、同じ結果を返すのにフルテーブルスキャンの場合は3.05秒、インデックスレンジスキャンの場合は5.7秒かかっており、明らかにフルテーブルスキャンのほうが効率が良いことが分かります。 出力結果からは、ほかにも以下の情報が読み取れます。

• マルチブロックリード 1 I/Oの読み込みブロック数平均：14.9ブロック （db_file_multiblock_read_count=16） 1 I/Oの平均レスポンス：13ミリ秒 I/O発行回数：151回 I/O待機時間合計：2.02秒

• シングルブロックリード 1 I/Oの読み込みブロック数平均：1ブロック 1 I/Oの平均レスポンス：2ミリ秒 I/O発生回数：2224回 I/O待機時間合計：5.01秒

表のほとんどのブロックにアクセスする必要がある場合には、小さなI/Oを大量に発行するインデックスアクセスよりも、大きなI/Oでまとめて読み込むフルテーブルスキャンのほうが効率的です。 オプティマイザは単にヒット行数の見積もりだけでなく、データがどの程度、表内に散らばっているか（その場合、索引アクセスの効率は下がる）も考慮してアクセスパスを選択しています。

通常のSQL Traceに加え、SQL実行時に発生した待機イベントの情報を収集するために使用するテクニックです。従来は公開されていない方法でしたが、10gではマニュアルに記述があります。パフォーマンスチューニング時には非常に重宝します。 次のコマンドを実行した後に当該セッションで発行したSQLは、待機イベントとともにトレースが出力されます。このコマンドによって出力されたトレースファイルも、通常のSQL Traceと同様にtkprofユーティリティを使ってフォーマットできます。

  
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';  
 
 

なお、Oracle 10gではほかにもDBMS_MONITORパッケージで待機イベントを含んだSQL Traceを取得可能です。カレントセッションだけでなく、ほかのセッションに対して外部からトレースを仕掛けることも可能であり、こちらのほうが使いやすいと思います。

ヒストグラム

次に、CBOが誤った選択をする例を見てみましょう。テーブルTEST1には100万行が格納されています。同テーブルにはNUMBER型の3つの列Cul1、Cul2、Cul3があり、以下のような値が格納されています。

• Cul1には行番号が格納されている。つまり、1行目から1,2,3,……1000000が格納

• Cul2は1～100までの値が循環的に格納されている。つまり、1,2,3,……100,1,2,3,……100,1,2,……と格納

• Cul3は1～100までの値がソートされて格納されている。つまり、1,1,1,……1,2,2,2,……100,100,……100となっている。各値は1万レコードずつ均等に格納

ここで、Cul3の1行（50万行目）だけを1000000に更新して、オプティマイザ統計を収集します（LIST3）。

LIST3 Cul3の1行だけを1000000に更新

  
exec dbms_stats.gather_table_stats 
 (ownname=>'TEST',tabname=>'TEST1',
 -  estimate_percent=>100,cascade=>true) 
 
 

TEST1表のCul3列には索引IND_Cul3があります。この状態で次のSELECT文を発行した場合、どんなアクセスパスが適切でしょうか。

• SQL1

  Copy

  
    
  select count(cul1) from TEST1  
  	where Cul3 <= 10000; 
   
   

  この場合、100万行中の99万9999行（50万行目以外のすべて）がヒットするわけですから、当然フルスキャンが適切です。ところが、オプティマイザは次のような推測を行ないます。

  • Cul3列の値は最小値1、最大値1000000の間に均等に分布している

  • Cul3列の値の種類（NDV：Number of Distinct Values）は101個である

  • 「Cul3 <= 10000」にヒットする行数は以下のようになるはずである

    • 選択率（Selectivity）

      Copy

      
        
      Sel(Cul3<=10000) = Sel（Cul3<10000）+  Sel（Cul3=10000）
      			=（10000－最小値）/（最大値－最小値）+ 1/NDV 
      			=（10000－1）/（1000000－1）+1/101 
      			= 0.0199 
       
       

    • ヒット行数

      Copy

      
        
      = 全行数 × 選択率
      			= 1000000 × 0.0199  =   
      			19900
       
       

  予測された選択率は約2％となります。このヒット行数であれば、クラスタリングファクタを考慮しても索引を使用したほうがコストが低いと見なされ、インデックススキャンが選択されました（LIST4）。

  LIST4 インデックススキャンが選択される

  さらに、次のSELECT文を発行してみます。この場合はどんなアクセスパスが適切でしょうか。

• SQL2

  Copy

  
    
  select count(cul1) from TEST1  
  	where Cul3 > 500000;  
   
   

  このSELECT文では100万行中の1行（50万行目）しかヒットしません。したがって当然インデックスアクセスが必要です。 ところがオプティマイザは全体の50％がヒットすると予測し、これだけヒットするのであればフルスキャンのほうが効率的だろうという結論に至ってしまいます（LIST5）。
  Copy

  
    
  Sel(Cul3>500000) = (1000000－500000) /
  	(1000000－1) =0.5  
   
   

  この間違いの原因は、オプティマイザの仮定である「最小値と最大値の間の値の一様分布」および「値の発生頻度は同じ」が、実際のデータ分布と一致していない点にあります。 こんな間違いをするようではCBOは当てにならないのでしょうか。現実に使用されるデータには、値の種類によって発生頻度が大きく異なったり、最小値と最大値の間にまばらにデータが存在するというケースがよくあります。 こういった状況に対処するため、CBOは統計情報の1つである列値ヒストグラムを利用して、アクセスパスの選択精度を向上できます。Oracleでは2種類のヒストグラムをサポートしており、最大254のバケット数を指定して列値ヒストグラムを作成できます。

  
Sel(Cul=x) = 1/NDV  
  Sel(Cul>x) =（最大値－x）/（最大値－最小値） 
  Sel(Cul<x) = 1－Sel(Cul>x) 
  Sel(Cul>=x) = Sel(Cul>x) +Sel(Cul=x) 
  Sel(Cul between x and y) = Sel(Cul>=x AND Cul<=y) 
  = Sel(Cul>=x) + Sel(Cul<=y)－1 
 
 

頻度分布（Frequency）ヒストグラム

ヒストグラムには2種類あります。列値の種類が指定されたバケット数以下であれば、「頻度分布（Frequency）」のヒストグラムが作られます。これは、それぞれの値が何行あるかを正確に記録するものです。「頻度分布」ヒストグラムを作成した状態で次の2つのクエリを実行してみましょう。 列の個別値数（NDV）よりも大きな値をmethod_optに指定することで、頻度分布ヒストグラムは作成可能です。ここでは最大値の254を指定して作成します。

  
execdbms_stats.gather_table_stats(ownname=>  
'TEST',tabname=>'TEST1', -  estimate_percent=>100,method_opt=>  

'FOR CulUMNS Cul3 SIZE 254'
,cascade=>true)
 
 

Cul3列に対してLIST6のような頻度分布ヒストグラムが作成されました。

LIST6 頻度分布ヒストグラム

USER_HISTOGRAMSディクショナリビューを参照すると、ENDPOINT_VALUEが列値を表わし、ENDPOINT_NUMBERが行数の累計を表わしています。したがって1つ前のエントリとの差分がその列値の行数となります。 値の分布が正しく記録されていますから、オプティマイザの見積もり行数も正確であり、正しいアクセスパスが選択されます。

• SQL1 正しく999K行（Kはキロ、1000倍の意）がヒットすると見積もり、フルスキャンを選択しています。

• SQL2 正しく1行がヒットすると見積もり、インデックススキャンを選択しています。

高さ調整（Height Balanced）ヒストグラム

もう1つのヒストグラムは「高さ調整」ヒストグラムです。頻度分布ほど正確ではありませんが、値の偏りを検出するために使用されます。

高さ調整ヒストグラムは、指定されたバケット数にソートしたデータを順番に入れていき、それぞれのバケットの最後の値（ENDPOINT_VALUE）を記録していくものです。この時、それぞれのバケットには同じ行数しか入らないので、「高さ調整（Height Balanced）」と呼ばれます。

連続したバケットのENDPOINT_VALUEが同じであれば、その値がほかの値より多く存在する証拠になります。このような複数のバケットのENDPOINT_VALUEとなる値を「ポピュラー値」と呼びます。Oracleは領域使用率向上のため、ポピュラー値が存在した場合は、最後のバケットの情報のみ格納し、先行するバケットの情報は省略します。バケット0のENDPOINT_VALUEは最小値です。最後のバケットのENDPOINT_VALUEは最大値となります。 先にも書きましたが、Oracleでは最大254のバケット数までしか指定できないため、256種類以上（バケット0を含めて255種類の値を記録可能）の値を持つ列に対しては「頻度分布」のヒストグラムを作れません。

バケット数を際限なく増やすと、ヒストグラム生成に要する時間が長くなるだけでなく、ヒストグラム情報を格納するための領域（ディクショナリ表）も大量に消費されてしまいます。ヒストグラムの使用に関しては、統計の正確さとのトレードオフになります。 高さ調整ヒストグラムは統計の正確さという点では限界がありますが、厳密な統計がなくても、正しい実行計画の選択という目的は達成できます。 例えば1～1000000までの101種類の値が存在するCul3列にバケット数10で高さ調整ヒストグラムを作ってみましょう。

  
exec dbms_stats.gather_table_stats(ownname=>'TEST'
  ,tabname=>'TEST1', -  estimate_percent=>100,method_opt=> 

 'FOR CulUMNS Cul3 SIZE 10'
 ,cascade=>true)
 
 

Cul3列に対してLIST7のような高さ調整ヒストグラムが作成されています。

LIST7 高さ調整ヒストグラム

ポイントは以下のとおりです。

• USER_HISTOGRAMSディクショナリビューの参照結果

• ENDPOINT_NUMBERがバケット番号を表わす。列値をソートし、それぞれのバケットに同数の行を入れていったと考える。ポピュラー値が存在する場合はバケット番号に抜け番が発生する（逆に、抜け番の発生からポピュラー値の存在が分かる）

• ENDPOINT_VALUEがそのバケットに入れられた最後の列値を表わす。このケースでは100万行に対して10バケットを使用しているため、1つのバケットに10万行が入れられる。したがって、バケット1のENDPOINT_VALUEはソートした10万行目の値、バケット2は20万行目の値、バケット3は……となる

この高さ調整ヒストグラムには ポピュラー値（Tips参照）は存在しません。ポピュラー値は、ある値がほかの値に比べて偏って多く存在する場合に発生します。

このヒストグラムからは範囲における非一様性（範囲内の値の偏り）が読み取れます。LIST7のバケット9とバケット10を比較してみましょう。2つのバケットに格納されている行数は同じです。バケット9には81～91までの11種類の値が入っていますが、バケット10には91～1000000までの99万9910もの値の種類があります。これだけの種類があるにもかかわらずバケット9と同じだけの行数ということは、91～1000000までの値はまばらに存在していることが分かります。

このヒストグラムを使用して、先の2つのSQLのヒット行数はどのように計算されるかを見てみましょう。 以下でSel（Cul3>10000）やSel（Cul3>500000）を計算している部分がありますが、どちらもヒストグラムのバケット10の範囲（91～1000000）に包含されることに注意してください。バケット数が10であることから、1つのバケット内に含まれる範囲の選択率は最大でも0.1（10％）となります。以下の計算では0.1に絞り込まれた後、バケット内で値が一様分布していると見なしてさらに計算しています。

• 選択率（Selectivity）

  Copy

  
    
  Sel(Cul3<=10000) = 1－Sel(Cul3>10000)
  	= 1 －0.1 * (1000000－10000)/(1000000－91) 
  	= 0.900991  Sel(Cul3>500000)
  	= 0.1 * (1000000－500000)/(1000000－91) 
  	= 0.050005 
   
   

• ヒット行数

  Copy

  
    
  (Cul3<=10000)  
  	= 1000000 * 0.900991 =  
  	900,991  
  	(Cul3>500000)    
  	= 1000000 * 0.050005 =    
  	 50,005 
   
   

ヒット行数の見積もりは、ヒストグラムを作らなかった時よりはかなり向上した（現実に近づいた）と言えます。アクセスパスもそれぞれ正しいものを選択するようになりました。

• SQL1 90万（900K）行がヒットすると見積もり、フルスキャンを選択しています。

• SQL2

  5万5行がヒットすると見積もり、インデックススキャンを選択しています。以上から、高さ調整ヒストグラムによって、適切な実行計画が選択される可能性が向上することが理解できるかと思います。 さらに、ポピュラー値が現われるケースを見てみましょう。次のSQL文で、Cul3の列値に偏りを作ってみます。

  Copy

  
    
  update TEST1 
  	set
  	Cul3=30 
  	where 
  	Cul3
  	between
  	31
  	and
  	39; 
   
   

  これによりCul3で列値が30台のものがすべて「30」になりましたので、Cul3の値の種類としては9個減って92、Cul3=30の行が以前の10倍になり、全体の10％になりました。それでは、全体の10％を占める値を高さ調整ヒストグラムで検出するのに適切なバケット数はいくつでしょうか。

  すでに述べたように、高さ調整ヒストグラムでは2つ以上のバケットのエンドポイントとなっている値がポピュラー値と呼ばれ、ポピュラー値の存在によって値の発生頻度の偏りが検出できます。2つのバケットのエンドポイントに同じ値が現われるためには、少なくとも1つのバケットにすべて同じ値が入る必要があります。そのため、2バケット分の行数が存在すれば、確実にポピュラー値となります。したがって全体の10％を占める値を検出するには、10％が2バケットに相当するバケット数を使用すれば良いことになります。ヒストグラム作成のバケット数として20バケットを使用すれば、1バケットが5％に相当するため、2バケットで10％を表わせます。

  次のコマンドでバケット数20の高さ調整ヒストグラムを作成します。

  Copy

  
    
  execdbms_stats.gather_table_stats 
  	(ownname=>'TEST',tabname=>'TEST1', 
  	-  estimate_percent=>100,method_opt=>'FOR
  	CulUMNS  Cul3   
  	 SIZE 20',cascade=>true) 
   
   

  LIST8のようなヒストグラムが作成されました。

  LIST8 高さ調整ヒストグラム（ポピュラー値あり）

  ENDPOINT_NUMBERが6のエントリが欠落しています（9～19の間は単なる省略です）。したがってその次のエントリのENDPOINT_VALUEである「30」はポピュラー値であることが分かります 注6。 値30は全体の10％あるため、20バケットの高さ調整ヒストグラムでこの値の偏りをポピュラー値として検出できました。全体の10％に相当する行数を取り出すのに適切なアクセスパスは何でしょうか。

• ポピュラー値を指定 select count(cul1) from TEST1 where Cul3=30; ヒストグラムから10万行がヒットすると見積もり、フルスキャンが妥当と判断されました。

• 非ポピュラー値を指定 select count(cul1) from TEST1 where Cul3=40; 非ポピュラー値の場合、等価条件の選択率は1/NDVとなるため、1万行がヒットすると見積もり、索引アクセスが妥当と判断されました。

注6：この例ではポピュラー値は1つですが、現実のデータでは複数の値がポピュラー値となることもあります。

• ポピュラー値 ENDPOINT_NUMBERに抜け番があった場合、後続のバケットのENDPOINT_VALUEは「ポピュラー値」です。ポピュラー値の存在は、その値がほかの値と比べて際立って多く存在していることを示します。 例：ENDPOINT_NUMBERが1,2,3,4,5,7,8,9,10であったとすると、バケット7のENDPOINT_VALUEはポピュラー値である。ポピュラー値の選択率は次のようになる。

  Sel(Cul= ポピュラー値 ) = ( ポピュラー値が   ENDPOINT_VALUEのバケット数 ) / ( 全バケット数 )  

  この例では2/10 = 0.2となる

• 間隔が空いている場合 ENDPOINT_VALUEの間隔が大きく飛んでいる区間は、値の存在がまばらと判断できます。

ヒストグラムを使用する場合の注意点

ヒストグラムを使用する場合の注意点を以下にまとめてみましょう。

• 高さ調整ヒストグラムの場合、等価条件ではポピュラー値以外は偏りを見つけられない。ポピュラー値を検出するには適切なサイズのバケット数が必要 ポピュラー値以外は1/NDVだけ存在すると見なされるため、ほかの値よりも極端に少ない値や、わずかな偏りの場合は偏りを検出できないことがあります。 使用するバケット数が10の場合、全体の20％（2バケットのエンドポイントにまたがる）を特定の値が占める場合は確実にポピュラー値として検出できます。200バケットでは全体の1％を超えると検出可能です。このとき、もし値の種類が1000でヒストグラムがなく、等価条件で指定すると1/1000=0.1％がヒットすると見なされますが、ヒストグラムがある場合は、1％以上0.5％刻みで偏りを検出可能です。 ただし、1％程度の選択率ではフルスキャンが選択される可能性は低いため、偏りを検出した効果があったとは言えません。偏りを検出して意味があるのは、フルスキャンのほうが高性能になる可能性のある10％以上の偏りです 注7。全体の10％の分布の検出はバケット数20で可能であるため、バケット数255の上限は「適切なアクセスパスの選択」という目的に対しては十分大きな値と言えます。

• 値の種類（NDV）が少ないが、ほかの値よりも極端に少ない値の検索時にインデックスアクセスが選択されるようにしたい場合は、頻度分布ヒストグラムが必要 例えば、値の種類が4種類でそのうち1つの値は極端に数が少なく、1％しか存在しなくても、オプティマイザは25％あるものとしてアクセスパスを選択するため、フルテーブルスキャンの可能性が高くなります。頻度分布ヒストグラムがあれば、値が1％しか存在しないことも検出できるため、索引が適切に使用される可能性が高くなります。 頻度分布ヒストグラムは最大255種類の値までしか作成できません。しかし、値の種類が255種類あれば1/NDVは0.39％以下であるため、ヒストグラムがなくても索引が使用される確率は高いと言えます。

• 文字型データの場合、ヒストグラムに列値として格納されるのは32バイトまで。つまり、先頭から32バイト（1文字が2バイトの日本語を使用していた場合、16文字）が同一のデータはヒストグラム上では同値として扱われる 例えば、TEST1表に文字型の列ADDRESSがあったとして、この列にヒストグラムを作成し、「ADDRESS='トウキョウトチヨダクキオイチョウ4－1%'」という条件を指定してSQL文を発行したとします。この場合、ヒストグラムに入っている値は「トウキョウトチヨダクキオイチョウ」（16文字）までですから、「4－1」で4丁目1番地まで絞った条件指定をしていても、オプティマイザは「トウキョウトチヨダクキオイチョウ」から始まるすべての行がヒットすると想定してしまいます。これによって実際以上の選択率が見込まれ、大量データアクセスに有利なほうのアクセスパスが選択される可能性があります。 文字型のコード値列などにヒストグラムを作成する場合は、値に長いプリフィクス（先頭文字列が同じになる）を付けることは避けたほうが良いと言えます。

注7：先に述べたように、この割合はクラスタリングファクタによって変わってきます。

オプティマイザは基本的に、列値は最小値と最大値の間に一様に分布しており、それぞれの値は同じ数だけ存在していると仮定しています。そのため、実際の値の分布に偏りのある場合、誤ったアクセスパスを選択することがあります。オプティマイザ統計の収集時に列値ヒストグラムを作成することにより、正しいアクセスパスが選択される可能性が高まります。

列の個別値数が少ない場合は「頻度分布」ヒストグラムを作成でき、その場合オプティマイザは正確な選択率を算出できます。列の個別値数が多い場合でも「高さ調整」ヒストグラムを作成でき、正確さでは「頻度分布」におよびませんが、適切なアクセスパスを選択するのに十分な情報が得られます。

CBOとバインド変数

読者の皆さんのシステムでは、SQLにバインド変数を使用しているでしょうか。バインド変数を使用するかどうかは、アプリケーションの設計時の重要な判断事項です。バインド変数を使用したSQLの例を示します。

  
select count(cul1) from TEST1 where Cul3 = 
 :B1;
 
 

これに対し「リテラルを使用したSQL」とは、以下のようになります。

  
select count(cul1) from TEST1 where Cul3=  
   10;
  select count(cul1) from TEST1 where Cul3=  
  20;
  select count(cul1) from TEST1 where Cul3=   
   30; 
 
 

バインド変数の利点は、構文が同じで条件値のみが異なるSQLを1つにまとめられることです。これにより次のような効果が期待できます。

• 共有プール内のメモリ使用量の低減

• ハードパース発生率の低減

一般にulTP系システムではバインド変数を使用したSQLの記述が必須と言えます。したがって、オプティマイザのバインド変数の扱いを知ることは重要です。 以前からOracleを使用されている方は、バインド変数を使ったSQLでは、選択率として内部デフォルト値を使っていると理解しているかもしれません。しかし最近のリリースでは動作が変更されており、「バインドピーク」という機能が使われています。この機会にぜひ理解しておきましょう。

バインドピーク（Bind peek）

Oracle9i以降のリリースでは、デフォルトでバインドピークと呼ばれる機能が有効になっています。 9iより前のリリースでは、WHERE句にバインド変数を使用した場合、SQL文のパース時には当該変数にセットされる値が分からないため、行の選択率を内部デフォルト値で決定していました。例えば、次の例のように範囲条件にバインド変数を使用した場合、オプティマイザは指定された範囲が分からないため、内部デフォルト値から「5％がヒットする」と判断してコストを計算し、アクセスパスを選択します。

  
SELECT ....WHERE cul1 > :B1; 
 
 

この場合、内部デフォルト値と現実のデータの分布が乖離していると、選択される実行計画が不適切になる可能性が高くなります。 バインドピークは、ハードパースでオプティマイザが実行計画を生成する際、バインド変数にセットされた実際の値を「覗く（peek）」機能です。これにより、リテラルSQLの場合と同じように、値の分布を考慮して（ヒストグラムも利用して）、行の選択率を計算できるようになります。内部デフォルト値ではなく、ユーザーが指定した実際の値と統計を利用できるため、より適切な実行計画の選択が期待できます。 ただし、バインド変数を使用したSQLの実行計画（アクセスパスなど）は、共有メモリ内の1つの共有カーソルとしてほかのユーザーにも共有されます。つまり、あるSQLを最初に実行したユーザーがセットした値に実行計画が最適化され、その後、同じSQLを実行しようとするユーザーにはすべて同じ実行計画が適用されます。例えば、最初のユーザーの範囲指定に対してフルスキャンが最適だったとすると、次のユーザーが1件だけヒットするような条件指定をしたとしてもフルスキャンとなります。 以下にそうしたケースの例を示しましょう。このテスト環境は「ヒストグラム」の項で使用したものと同じです。TEST1には100万行が格納されており、Cul3列の値が30である行は10万行（全体の10％）存在します。Cul3列には高さ調整ヒストグラムが作成されています。 まず、:B1に値30をバインドしてSELECT文を実行し、SQL Traceを取得します。

  
variable B1 number;  exec :B1 :=  
  30; --  バインド変数:B1に値30をセット 
  alter session set sql_trace=true;  
  --SQL Trace 取得 
  select count(cul1) from TEST1 where Cul3 = :B1;  

  COUNT(Cul1) 

  ----------- 

  100000
  ← 10万行がヒット
 
 

このときSQL Traceに記録された実行計画はLIST9のとおりです。

LIST9 実行計画（：B1に値30をバインド）

10％のヒットに対してフルテーブルスキャンが選択されました。これはバインドピーク機能によって:B1の値が30であることを認識し、ヒストグラムを使用して10％の行がヒットすることをオプティマイザが理解したためです。 次に、:B1に値40をバインドして同じSELECT文を実行します。値40は1万行（全体の1％）存在します。

  
variable B1 number;  
exec :B1 :=  
  40;   
  --バインド変数:B1に値40をセット 
  alter session set sql_trace=true;  
  --SQL Trace 取得 
  select count(cul1) 
  from TEST1 where Cul3 = :B1;  

  COUNT(Cul1) 

  ----------- 

  10000
  ← 1万行がヒット 
 
 

このときSQL Traceに記録された実行計画はLIST10のとおりです。

LIST10 実行計画（：B1に値40をバインド）

値40でヒットするのは1％であるにもかかわらず、フルテーブルスキャンが選択されています。これは、先に実行した値30に対して最適化された共有カーソルにヒットし、同じ実行計画が使用されたためです。 値40に対しては、フルスキャンは明らかに不適切です。共有プールをフラッシュし、再度、値40を指定して同じSELECT文を実行してみます。

  
alter system flush shared_poul; -- 共有プールのフラッシュ  
variable B1 number;  exec :B1 
:=    40;  
          --バインド変数:B1に値40をセット 
		  alter session set sql_trace=true;  
		  --SQL Trace 取得  
		  select count(cul1)
		  from TEST1 where Cul3 = :B1;

		  COUNT(Cul1)

		  ----------- 

		  10000 
		  ← 1万行がヒット
 
 

LIST11を見ると、ハードパースが発生し、オプティマイザはバインド変数にセットされた値40に対して実行計画を最適化しました。1％のヒットに対して、インデックスアクセスパスを選択しています。

LIST11 実行計画が最適化された

このように、バインドピークを使用していると、ハードパースが実行されたタイミングで例外的なバインド値が偶然セットされることがあり得ます。 従来の「内部デフォルト値」が使用されるケースでは、特定のバインド値に対しては最適でない実行計画となる可能性がありますが、運用中の偶然の作用によって突然実行計画が変化するということはなく、安定したパフォーマンスを維持しやすいと言えます。 したがって、システムによっては当該機能をOFFにしたほうが良いケースがあります。バインド変数を使用すべきか、またバインドピークをOFFにすべきかを判断するための指針として以下を考慮してください。

• バインド変数を使用する目的は、同じ構文のSQLの実行計画を1つに統一し、リソース使用効率を向上することである

• 1つの実行計画がすべてのバインド値に対して最適となるはずはなく、現実的にはユーザーが使用する「大部分」（例えば90％）の値で良好な性能が得られれば可とすべきである

• 1つの実行計画が「大部分」の要求を満たせない場合、複数の実行計画を使い分ける必要がある。この場合はリテラルで記述したほうが適切

  • - DWH系のシステムはこのケースに当てはまる

  • - ulTP系では、このようなSQLの割合をできるだけ少なくする必要がある

• バインドピークがONの場合、例外的な値に実行計画が最適化される可能性があり、「大部分」の性能を劣化させるリスクがある。ただし「大部分」の側の値に最適化された場合は、バインドピークがOFFの場合には得られないような効率的な実行計画が生成される可能性がある

  • - 特に範囲検索の場合

  • - ヒストグラムが存在する場合の等価検索

• バインドピークがOFFの場合、最適性は薄れるが、実行計画変動のリスクは少ない

  • - 等価検索は1/NDVが選択率とされるため、ヒストグラムがない状態のリテラルSQLと同じ精度

  • - 範囲検索のデフォルトの選択率が5％であるため、比較的索引が使われやすいと言える

  • - 索引の選択や結合順序など、最適なものが選択されない可能性がある。その場合、ヒントによる強制が必要となる

バインドピーク機能をOFFにするには、「_optim_peek_user_binds」パラメータをFALSEにします。このパラメータは、いわゆる「隠しパラメータ」で、通常は「サポートセンターの指示なしでは設定すべきでない」パラメータです。しかし、サポート契約のあるユーザーに対しては公開されており、「機能を理解した上で使用する場合はサポートされる」とされています 注8。 なお、バインドピークについては10gのマニュアルでは「ユーザー定義のバインド変数の照合」と記述されています。

EXPLAIN PLANやAUTOTRACEではバインドピークが行なわれないため、実際とは異なる実行計画を出力することがあります。バインド変数を使用したSQLの実行計画を確認するには、SQL TraceやV$SQL_PLANを使用してください。

バインドピークがOFFの場合、10g R1では選択率として次の値が使用されます。

  
Sel(Cul=:x) = 1/NDV ※等価条件の場合はデフォルト値ではなくNDV統計を使用  
  Sel(Cul>:x) = 0.05  
  Sel(Cul>=:x) = 0.05  
  Sel(Cul LIKE :x) = 0.05 
 
 

範囲条件の場合はすべて5％であるため索引が使用されやすいと言えます。

CURSOR_SHARING

バインド変数を使用したSQLの重要性は前述したとおりですが、アプリケーションによってはリテラルSQLで作ってしまったとか、パッケージアプリケーションがバインド変数を使用していないというケースもあると思います。 このようなケースでパフォーマンスの劣化が見られる場合、アプリケーションを大幅に改変しなくても可能なDBチューニングの方法として、CURSOR_SHARINGがあります。

CURSOR_SHARINGパラメータのデフォルト値は「EXACT」であり、「FORCE」や「SIMILAR」といった値を取ります。CURSOR_SHARINGをFORCEもしくはSIMILARに設定した場合、ユーザーがリテラルSQLを発行すると、次のようにOracleがリテラル値をバインド変数に置換します。

• ユーザーアプリケーションが発行したSQL

  Copy

  
    
  select /* TEST */ count(cul1)
  	from TEST1 where Cul3=30; 
   
   

• OracleがパースしたSQL

  Copy

  
    
  select /* TEST */ count(cul1) from TEST1 where Cul3=:  
  	 "SYS_B_0";  
   
   

これにより、リテラルSQLを発行しているものの、実際にはバインド変数を使用したのと同等の効果が得られます。FORCEとSIMILARの違いは以下のとおりです。

• FORCE

  • - リテラル値によらず、1つの共有カーソルを共有する。バインドピークと同様に特異値に対して最適化されるリスクがある

  • - 共有プール使用量の削減効果、競合低減効果は高い

• SIMILAR

  • - 実行計画が確実に同じになる場合のみ、1つの共有カーソルを共有する。最適な実行計画が同じにならない可能性（unsafe）がある場合には、同一文の別バージョンの共有カーソル（子カーソル）を作成する

  • - unsafeな場合とは、オプティマイザ統計が収集されている場合の範囲条件指定や、ヒストグラムが収集されているときの等価条件などである。リテラル値に依存して最適な実行計画が異なる可能性があるので、異なるリテラル値に対してそれぞれ子カーソルを作成する

  • - オプティマイザ統計がなく、動的サンプリングもOFFの場合、内部デフォルト値によって実行計画が決まるため、同じ共有カーソルを共有する。列統計が最小値と最大値のみ（ヒストグラムなし）の場合の等価条件は、選択率が常に1/NDVであるため、同じ共有カーソルを共有する

以上より、CURSOR_SHARING=SIMILARの場合、場合によっては共有プール使用量の削減効果はほとんどなく、競合も低減されないことがあります。SIMILARを使用する場合は、テスト時にV$SQLAREA.VERSION_COUNTを調べ、値が十分少ない（共有されている）ことを確認してください（LIST12）。

LIST12 値が少ないことを確認

高負荷のulTP系システムではバインド変数を使用したSQLの記述は必須です。バインド変数により、同じ構文のSQLの実行計画が統一されます。 バインド変数を使用したSQLのハードパースには、従来は内部デフォルト値（等価条件以外）を使用していましたが、9i以降ではバインドピーク機能によりすべての統計を利用します。この場合、当該SQLの最初の実行や共有プールからのキャッシュアウト後にセットされた値に依存して実行計画が固定される可能性があります。 リテラルSQLを使用したアプリケーションで性能問題が発生した場合、CURSOR_SHARING機能が使用できます。ただし、SIMILARを設定した場合は、メモリ使用量などの改善効果が現われない可能性もあるため事前に確認が必要です。

I/O + CPUコストモデル

オプティマイザによるヒット行数の予測やアクセスブロック量の予測を理解したところで、オプティマイザが評価の道具として利用する「コスト」について説明しておきましょう。 Oracle 10gでは、「I/O+CPUコストモデル」を採用しています。以前のリリースではコスト≒I/O回数でしたが、Oracle9iで「システム統計」の取得が可能になってからはCPUコストも考慮するようになりました。9iではシステム統計を明示的に収集することでオプティマイザがCPUコストを考慮するようになりますが、10gではインスタンスの起動時に自動的に一部のシステム統計を収集するため、デフォルト状態でI/O+CPUコストモデルが利用されます。 I/O+CPUコストモデルでは、必要なI/O数の見積もり（IO_COST）とCPUサイクル数の見積もり（CPU_COST）が行なわれます。この2つのコストをマージし、ほかの実行計画と比較するためのコストとするわけですが、そもそも単位の違う2つの数字をそのまま足し合わせても意味はありません。そこで、Oracleはそれぞれのコストを「所要時間」に変換してマージします。 必要なI/O数を「所要時間」に変換するためには、「I/O当たりのレスポンス時間」の統計が必要です。例えば、100ブロックをすべてシングルブロックリードで読み込むのに要する時間は、「100×（平均シングルブロックリード時間）」で得られます。同様に、必要なCPUサイクル数を所要時間に変換するためには、「CPU速度」の統計が必要です。例えば500万サイクルが必要な処理を100MHzの速度で実行すれば、50ミリ秒（5000000×1/100000000）で完了します。このような統計を「（オプティマイザ）システム統計」と呼びます。 システム統計はsys.AUX_STATS$表に格納されています。10gではインスタンスの起動と同時に以下のような統計が自動的に収集されています。

• CPUSPEEDNW（CPU Speed No Workload） 1秒当たりの平均サイクル数（単位：MHz）。Oracleが定義するところのサイクル数であり、Oracleから見た相対的なCPU速度。通常、ハードウェア的なCPUクロック数とは大きな差がある

• IOSEEKTIM（I/O Seek Time） ディスクリード当たりのオーバーヘッド時間。ディスクシーク時間+ディスク回転遅延時間+OSオーバーヘッド時間（単位：ミリ秒）

• IOTFRSPEED（I/O Transfer Speed） I/O転送速度（単位：バイト／ミリ秒）で、read時間=IOSEEKTIM+（readサイズ／IOTFRSPEED）の関係がある

CPU+I/Oコストモデルは、統計によってシステム固有の能力や特性を考慮し、より的確な実行計画を生成できる点で優れています。 一例を挙げると、マルチブロックリードによるフルテーブルスキャンの効果は、従来のI/Oコストモデルでは過大に評価される場合がありました。つまり、マルチブロックリードのほうがI/O回数が減るからということで、索引アクセスよりもフルテーブルスキャンが選ばれやすくなるケースがあったのです。しかし、フルテーブルスキャンはI/Oだけでなく、CPUにも負荷をかけるアクセスパスです。これは、大量のブロックにアクセスし、ブロック内のレコードを1行ずつチェックしては条件に一致したレコードを拾っていくという処理をするためです。 CPU+I/Oコストモデルは、CPU負荷もきちんと評価しますし、場合によってはシステム統計からマルチブロックリードのI/O効率がそれほど高くないことに気づくかもしれず、より適切なアクセスパスの判断が期待できます。

ワークロード下のシステム統計

CPU+I/Oコストモデルをより有効に利用するためには、デフォルトで収集される統計だけでなく、ワークロードをかけた状態でのシステム統計収集が重要です。これにより以下のような統計が収集されます。

• SREADTIM（Single Block Read Time） シングルブロックリード時間（単位：ミリ秒）。単一ブロックのランダムリードの平均レスポンス

• MREADTIM（Multiblock Read Time） 平均マルチブロックリード時間（単位：ミリ秒）。 複数ブロックのシーケンシャルな読み込みの平均レスポンス

• CPUSPEED 1秒当たりの平均サイクル数（単位：MHz）

• MBRC（Multiblock Read Count） マルチブロックリード時の1回の平均読み込みブロック数

• MAXTHR I/Oサブシステムが提供可能な最大I/Oスループット（単位：バイト／秒）

• SLAVETHR パラレルスレーブごとの平均I/Oスループット（単位：バイト／秒）

これらの統計を適切に取得することにより、オプティマイザの判断の精度は上がります。ただし、統計収集時にはシステムの負荷特性を考慮し「典型的」なワークロードが流れているときに収集するようにしてください。オンライン時間帯とバッチ時間帯を別々に取得することが有効なケースもあります。

負荷が低すぎるときに統計収集すると、統計としての異常値が取得される可能性があります。例えば、大規模なフルスキャンがほとんど発生していないときに統計収集すると、異様に高速なレスポンスが記録されてしまいます。また、マルチブロックリード係数であるdb_file_multiblock_read_countパラメータによってフルスキャンのコストが変化しますが、システム統計のMBRCが記録されると、オプティマイザはマルチブロックリード係数としてMBRCのほうを使用するため、db_file_multiblock_read_countの変更によって実行計画の選択が変わらなくなる点に注意してください 注9。システム統計を収集した後はsys.AUX_STATS$を参照し、各統計値が異常な値でないかを確認することをお勧めします。

CPUコストの有無によるフルテーブルスキャンの評価例

ここでは、CPUコストの有無によるフルテーブルスキャンの評価の違いを見てみましょう。まずはワークロードのかかっている状態でシステム統計を収集します。

  
exec dbms_stats.gather_system_stats('start'); 
 exec dbms_lock.sleep(300); 
 exec dbms_stats.gather_system_stats('stop');
 
 

5分間のサンプリングのために300秒のスリープを入れています。sys.AUX_STATS$によると以下の統計が収集されています。

  
PNAME            PVAL1
----------- ----------
CPUSPEEDNW     118.615
IOSEEKTIM        6.593
IOTFRSPEED   12638.436
SREADTIM         6.354
MREADTIM         6.157
CPUSPEED           121
MBRC                 9
MAXTHR           16384
SLAVETHR
 
 

さて、ここで次のSQLを使用したとします。このSQLは「ヒストグラム」の項で使用した、全体の10％がヒットするSELECT文です。

  
select count(cul1) from TEST1 where Cul3=30;  
 
 

従来のI/Oコストモデルでオプティマイズした場合の実行計画を調べます。CPUコストを計算させないためにNO_CPU_COSTINGヒントを利用し、$ORACLE_HOME/rdbms/admin/utlxpls.sqlを使用して実行計画を確認します。

  
SQL> explain plan for
  select
  /*+  NO_CPU_COSTING 
  */ count(cul1)   
  from TEST1 where Cul3=30;
  SQL> @?/rdbms/admin/utlxpls 
 
 

LIST13より、I/Oコストモデルの場合にはフルテーブルスキャンが選択されたことが分かります。

LIST13 NO_CPU_COSTINGの実行計画

次に、CPU+I/Oコストモデルの場合を調べます。今度はNO_CPU_COSTINGヒントを付けません（LIST14）。CPUコストを意識させると、10％がヒットする問い合わせに対して索引アクセスを選択するようになりました。

LIST14 CPU＋I/Oコストモデル

ちなみに、FULLヒントで無理矢理フルスキャンを選択させると、コストをどのように見積もるかを確認してみましょう（LIST15）。フルテーブルスキャンのコストの44％がCPUコストである点に注目してください。CPU+I/OコストモデルではフルスキャンのCPUコストが適切に見積もられます。また、そのシステムのI/Oレスポンスの統計を考慮してマルチブロックリードの優位性が評価されるため、安易にフルスキャンを選択しないようになっていることが分かります。

LIST15 CPU＋I/Oコストモデル（FULL SCAN強制）

Oracle 10gでは、コスト見積もり時にI/OコストだけでなくCPUコストも加味し、「処理完了までの時間」の観点で最適化しています。CPU＋I/Oコストモデルにはシステム統計が必要です。 フルスキャンのCPUコストや、現実のマルチブロックリードの性能統計が考慮されるため、以前のリリースに比べてインデックスアクセスの優位性が正しく評価されます。

CBOとフルスキャン

「CBOにするとやたらとフルテーブルスキャンが増える」という印象を持たれたことはありますか。その場合はまず、db_file_multiblock_read_countパラメータの値を確認してみてください。db_file_multiblock_read_countパラメータは、Oracleがマルチブロックリードを実行するときに、1つのI/Oでいくつのブロックを読み込むかを指定します。 大量のブロックを読み込む場合、1ブロックずつI/Oを発行していてはオーバーヘッドが大きくなります。連続するブロックをまとめて読み込むことによって、スループットを向上します 注10。 Oracleの場合、フルテーブルスキャンやインデックスファストフルスキャンの実行時にマルチブロックリードが有効になります。Oracleの1回のI/Oサイズは最大で1MBです。したがって、db_block_sizeが8KBのときは、db_file_multiblock_read_countの最大サイズは128になります 注11。

注10：マルチブロックリードの効果については、「CBOのアクセスパス選択方法」の項で例を示しました。 注11：ただしバッファキャッシュサイズの4分の1を超えることができないという制限もあり、db_file_multiblock_read_countに128を設定しようとする場合、バッファ数が512以上なければなりません。

OracleのI/Oサイズを最大化しても、ボリュームマネージャやOSの最大I/Oサイズが1MB未満であればI/Oは分割され、最大のスループットは望めません。I/Oスループットを最大化するためには、これらのOracleから見た下位レイヤにおいてもI/Oが分割されないようにカーネルパラメータを設定する必要があります。 Oracleのチューニングパラメータとしてよく知られるdb_file_multiblock_read_countですが、「I/Oスループットが高くなりそうだから」という理由で安易に値を大きく設定していると、本当はインデックスアクセスのほうが高速な場合でもCBOがフルスキャンのアクセスパスを選択してしまうことがあります。 これは、同パラメータを大きくするとオプティマイザが見積もるフルスキャンのコストが低くなるからです。図2のグラフはあるテスト環境でフルテーブルスキャンを実行し、db_file_multiblock_read_countとオプティマイザが計算するIO_COSTと実際にかかった時間の関係を表わしたものです。

図2 db_file_multiblock_yead_countとIO_COSTおよび経過時間

グラフを見て分かるとおり、オプティマイザはフルスキャンのIOコストをdb_file_multiblock_read_countに反比例するものとして計算しています。ところが、実際の経過時間はIOコストと同じようには推移しません。「I/O+CPUコストモデル」の部分で説明したように、10gではデフォルトでCPUコストが見積もられます。フルスキャンのIOコストはdb_file_multiblock_read_countによって変動しますが、CPUコストは変化しません。したがって10gのオプティマイザは以前のバージョンほどdb_file_multiblock_read_countパラメータに敏感ではありません 注12。

注12：ワークロード下でのシステム統計を収集した場合、マルチブロックリード係数やマルチブロックリードのレスポンス統計が取得されるため、db_file_multiblock_read_countパラメータの値はオプティマイザの計算には影響しなくなります。

10g以前のバージョンを使用している場合は、このパラメータを不必要に大きくしないことをお勧めします。一般的なulTP環境では、大規模なフルスキャンが必要なケースはほとんどないはずで、意図的に実施する場合には、そのセッションでのみ動的にdb_file_multiblock_read_countを増加させること（ALTER SESSION文）も可能です。 ただし、「I/O+CPUコストモデル」で記述したように、ワークロード下でシステム統計が収集されている場合、セッションパラメータを変更しただけではフルスキャンが選択されない可能性があります。この場合、SQLにヒントを付加することでフルスキャンを強制できます。 バッチ時間帯は積極的にフルスキャンを実行したほうが良い場合は、db_file_multiblock_read_countの値が大きく設定されたワークロード下のシステム統計を収集し、これを「バッチ時間帯システム統計」としてオンライン時間帯と入れ替えて運用することも効果的です。 db_file_multiblock_read_countパラメータの適切な設定値を検討する場合には、本番環境上でベンチマークを実行することをおすすめします。先のグラフの例では、値が32の場合とそれ以上に増やした場合とであまり差がありません。したがって、マルチブロックリードの効果を最大にし、かつオプティマイザに無駄にフルスキャンのアクセスパスを選ばせないための最適値は、32であると判断できます（もちろん、この値はすべての環境に有効なものではありません）。

オプティマイザはフルスキャンのI/Oコストをdb_file_multiblock_read_countに反比例するとみなしています。このためI/Oコストのみを考慮するモデルで同パラメータの値を大きく設定していると、インデックスアクセスよりもフルスキャンを選択しやすくなります。10gではデフォルトでフルスキャンのCPUコストも考慮されるため、以前ほど同パラメータに敏感ではありません。 マルチブロックリードの効果は環境固有であるため、むやみに大きく設定するのではなく、テストを実施してリーズナブルな値を決定してください。

CBOとキャッシュ効率

ここからは、CBOがバッファキャッシュをどう評価するかを見てみましょう。 例えば、ある実行計画を生成するとき、2つの索引が利用可能であったとします。一方は90％の索引ブロックがバッファキャッシュ上にあり、もう一方は5％しかキャッシュされていないとします。オプティマイザはどちらを選ぶと思いますか。 答えは、「これだけの情報では分からない」です。10g R1では、オプティマイザはコスト計算にキャッシュ効率を加味していません（コラム「オプティマイザとキャッシュ効率」参照）。

将来のリリースにおいては、オプティマイザがキャッシュ効率を考慮するようになるかもしれません。10gの新しいディクショナリビューであるDBA_TAB_STATISTICSは表のオプティマイザ統計を格納していますが、NUM_ROWSやBLOCKSといった一般的なオプティマイザ統計に加えて、AVG_CACHED_BLOCKS（バッファキャッシュ内 のブロック数 ）とAVG_CACHE_HIT_RATIO（キャッシュヒット率）という列があります。 通常の使用方法ではこれらのキャッシュ統計列に値が生成されることはありませんが、将来のリリースにおいてこれらの統計を生成する方法が明らかにされるかもしれません。

したがって、オプティマイザは、必要なブロックにアクセスするためには必ずI/Oが発生するものとしてコストを計算し、ほかのアクセスパスと比較しています。これが、CPUコストを考慮しない以前のリリースにおいて、オプティマイザがインデックスアクセスよりもフルテーブルスキャンを好んでしまう理由の1つになっていました。

しかし、索引ブロックはバッファキャッシュ上に残っている可能性が高いため、実際にはオプティマイザが見積もったほどのシングルブロックリードが発生せず、フルスキャンより高速なケースがあります。このようなオプティマイザの動作を補正するため、次の2つのパラメータが存在します。

• OPTIMIZER_INDEX_CACHING

• OPTIMIZER_INDEX_COST_ADJ

これらのパラメータは、物理的に索引ブロックの扱い方を変えるものではなく、単にオプティマイザのコスト計算に経験則的なバイアスをかけるものです。「経験則的」というのは「実統計に基づくものではない」という意味になります。 OPTIMIZER_INDEX_CACHINGは「索引ブロックが何％くらいキャッシュヒットすると考えるか」を指定するパラメータです。デフォルトは0％です。この値が大きいほど、オプティマイザはインデックスアクセスのコストを低く見積もるようになります。 OPTIMIZER_INDEX_COST_ADJはもっと直接的に、「索引アクセスのコストを通常の何％で計算するか」を指定するパラメータです。デフォルトは100％であり、この値を小さくするほどオプティマイザは索引アクセスのコストを低く見積もるようになります。 これらのパラメータ値を検討する際、「本当のキャッシュヒット率」を調査して、それに合わせようとすることはあまり意味がありません。これらのパラメータの目的は「バイアスをかけてインデックスアクセスを選択しやすくする」ことですから、実際にアクセスパスの変化が現われるまでチューニングする必要があります。 これらのパラメータは非常に強力で、遅くて使い物にならなかったシステムが、この2つのパラメータで非常に高速になったというケースもあります。 2つのパラメータのデフォルト値は、データウェアハウス系システムには最適と言えます。一般的なulTP環境における初期設定値としては以下を目安にすると良いでしょう。

  
OPTIMIZER_INDEX_CACHING = 90 
 OPTIMIZER_INDEX_COST_ADJ = 25 
 
 

特に、9iまでのリリースで、索引を作ったもののフルスキャンの発生率が高いことが問題視されているシステムでは、ぜひ試してみていただきたいパラメータです。

オプティマイザは特定のオブジェクトに対するアクセスのコストを見積もるとき、それがメモリ（バッファキャッシュ）上でヒットするか、ディスクから読み込むのかを区別しません（すべてディスクI/Oが必要とみなします）。 索引ブロックはメモリ上にキャッシュされている可能性が高く、フルスキャンよりも高速なケースがあります。この状況を補正するために、オプティマイザの判断にバイアスをかけるパラメータが存在します（OPTIMIZER_INDEX_CACHINGとOPTIMIZER_INDEX_COST_ADJ）。これにより、オプティマイザにキャッシュされた索引ブロックへのアクセスの優位性を教えられます。

PLAN_TABLEやV$SQL_PLANビューには以下のカラムがあります。

• COST（オペレーションのコスト。CPU_COSTとIO_COSTから導出）

• CPU_COST（オペレーションに必要なCPUサイクル数に比例）

• IO_COST（オペレーションによって読み込まれるブロック数に比例。マルチブロックリードの効果も織り込んで補正済み）

これらは次のように計算されています。

  
COST = (IO_COST * SREADTIM + CPU_COST / (CPUSPEED*1,000) ) / SREADTIM
 
 

「1,000」は桁合わせのための係数です。つまり、CPU ＋I/OコストモデルにおけるCOSTは「処理完了までの時間」を意味しますが、SREADTIMで割ることによって、それを「シングルブロックリードの回数」という単位で表現していると考えることができます。

CBOとパラメータ

本番環境とほぼ同じデータのはずのテスト環境で実行計画を確認したのに、本番環境では異なる実行計画が選択されていたということはないでしょうか。

オプティマイザは実行計画の生成時に、表1のようなパラメータの値を確認しています。

表1 オプティマイザが確認するパラメータの例

実際にはもっと多くのパラメータを参照していますが、通常公開されていないパラメータであるため、ここでは除外しています。 テスト環境の実行計画を本番環境で再現できない場合、まずはこれらのパラメータ値が両環境で同じであるかを確認してください。これらの値が同じにもかかわらず実行計画が異なる場合は、オプティマイザ統計の取得対象や取得パラメータが異なっていないか確認します。

10gでは、オプティマイザが参照しているパラメータと現在の設定値を次のV$ビューで確認できます。

  
V$SYS_OPTIMIZER_ENV：システムレベルの設定値  
  V$SES_OPTIMIZER_ENV：セッションごとの設定値 
  V$SQL_OPTIMIZER_ENV：SQLごとの設定値 
 
 

OPTIMIZER_MODE

OPTIMIZER_MODEは、オプティマイザが実行計画を生成する際のゴールを指定するパラメータです。オプティマイザが行なう「最適化」の方向性を指定するものとも言えます。この方向性は大きく2つがあります。

• レスポンス重視（最初の数行を返すまでの時間を最速化）

• スループット重視（最後の行を返すまでの時間を最速化）

10gにおけるOPTIMIZER_MODEのデフォルトは「ALL_ROWS」です。これは、「スループット重視」型です。ALL_ROWSはデータウェアハウス系のシステムや、帳票出力などのバッチ処理に最適なモードです。 これに対し、ulTP系のシステムやWebアプリケーションではレスポンスが重要です。これらのシステムでは1万件がヒットしたとしても、ユーザーは最初の20件しか見ないかもしれません。例えば、20件単位で改ページするようなアプリケーションや、ヒット件数のチェックを行なってしきい値以上の行数で警告を返すアプリケーションがあります。このような環境では、最初の数行をいかに高速に返すかが重要であり、1万行を高速に返す場合とは異なる実行計画が必要となります。 最初の結果が返るまでの時間を最速化したい場合には、OPTIMIZER_MODEとして「FIRST_ROWS_n」を指定できます。これは「最初のn行を返すためのコストを最小化する」ということを意味します。nには1/10/100/1000を指定可能です。 従来の「FIRST_ROWS」を使用した場合、「索引が使用可能な場合は必ず索引を利用する」といった経験則が適用されるため、コストを度外視した実行計画が選択されることがありました。10gにおいては、FIRST_ROWSは下位互換性のために用意されており、レスポンス重視の最適化が必要な場合はFIRST_ROWS_nを使用することが推奨されています。 しかし、FIRST_ROWS_nを指定したからと言って、すべての問い合わせのレスポンスが早くなるわけではありません。結合方法にハッシュジョインよりもネステッドループジョインが選択されやすくなりますが、ソートや集計などすべての行にアクセスしなければ結果を返せないものでは、ALL_ROWSの場合と同じ実行計画になることがあります。 なお、OPTIMIZER_MODEはセッションレベル（ALTER SESSION文）やSQLレベル（ヒント）で変更可能です。実際は、ulTP環境においても基本はALL_ROWSにしておき、一部の「大量行が返されるかもしれない自由検索において件数を制限する」ようなケースで、部分的にFIRST_ROWS_nを使用するのが良いかと思います。このような処理が大半であれば、逆にFIRST_ROWS_nをシステムレベルで設定し、バッチ処理のみALL_ROWSとすることもできます。 また、10gではROWNUM擬似列を使用して出力行数を絞るSQLに対しては、内部的にFIRST_ROWS_nの最適化を適用しています。したがって、ROWNUMを使用して行数制限をする場合は、OPTIMIZER_MODEで指定する必要がありません。

オプティマイザは統計情報だけでなく、各種初期化パラメータの値も参照しています。テスト環境と本番環境では設定値を同じにするべきです。 OPTIMIZER_MODEのデフォルトはALL_ROWSです。最初の数行のレスポンスの最速化が重要である場合はFIRST_ROWS_nを指定します。これにより、n行を返すためのコストの最小化という観点で最適化が実行されます。

CBOと結合順序

多数の表を結合する問い合わせで、性能トラブルが起きたことはありませんか。オプティマイザは結合（Join）をハンドリングする際、以下のコストを比較しています。

• アクセスパス（Access Path） 各表から物理的にデータを取り出すためのアプローチ（インデックスアクセス、フルスキャンなど）

• 結合順序（Join Order） 表を結合していく順番

• 結合方法（Join Method） ネステッドループ結合（Nested Loop Join）：外部表の1行に対して内部表のマッチングを行なうループを、外部表のヒット行数分だけ繰り返す。少量の結合に効果的 ソートマージ結合（Sort Merge Join）：2つのソートされた行ソースを作り、マージすることで結合を効率化する。不等号が結合条件に使用された場合に効果的 ハッシュ結合（Hash Join）：一方の表を読み込んでメモリ上にハッシュテーブルを作り、もう一方の表をスキャンしてハッシュ値を利用したマッチングを行なう。大量行の結合に効果大（等価結合のみ）

Oracleの結合処理は「left-deep join strategy」です。これは図3のように表を1つずつ結合していくことを意味します。このとき、1つ前の結合処理がすべて完了していなくても、1行の結合が完了すれば、次の表が次々とパイプライン式に結合されていきます。ただしソートマージ結合やハッシュ結合のように、中間生成物（ソート結果、ハッシュテーブル）が必要な結合方法の場合は、前の結合処理が全行完了するのを待つ必要があります 注13 。

図3 “left-deep join Strategy”

注13：最初の1行を返すレスポンスを最速化するためにFIRST_ROWSモードを使用すると、ネステッドループ結合が使用されやすくなるのはこのためです。

図のように結合はシーケンシャルに実行されるため、どの表を先頭にし、どういう順番で結合していくか（結合順序）は問い合わせの性能に大きく影響します。オプティマイザは考えられる結合順序においてそれぞれの結合方法とアクセスパスのコストを計算し、最もコストの低い組み合わせを最適なアクセスパス／結合順序／結合方法として決定しています。 しかし少し考えると、結合対象の表数が増えるとオプティマイザが比較検討しなければならない結合順序の数が爆発的に増えることが分かります。理論的な結合順序の数は、結合対象の表数の階乗だけ存在します。表2は表数と結合順序数のマトリクスです。

表2 表数と結合順序数

例えば、10個の表を結合する場合、結合順序は360万通り以上になりますから、さらにそれぞれの結合について3種類の結合方法のコストを計算して比較していくというのは膨大な作業です。ベストの結合順序を探すためには、すべての組み合わせについて計算する必要がありますが、実際にはパフォーマンスに甚大な影響を与えます。真に最適なものが見つかったとしても、それを見つけるまでに膨大な時間とCPUパワーを要してしまっては、本末転倒です。そこで、Oracleは「最適」でなくても「準最適」な結合順序を高速に探し出すために以下のようなテクニックを使います。

• 経験則に基づいた初期結合順序の決定 結合処理を効率的に行なうためには、ヒット件数の少ない表をできるだけ結合順序の前方に持ってくることが有効です。このため、オプティマイザが最初に考慮する結合順序は、各表から取り出される「実効行数」の少ない順です 注14 。

• ベストコスト保持によるコストの高い結合順序の早期排除 それまでに評価した結合順序の中でのベストコストを保持し、1つの結合順序の評価中であっても、ベストコストをオーバーした時点でその結合順序を捨てることにより、無駄なコスト計算を回避します。例えば、10表のうち、3表までの結合のコストを計算した時点で、それまでのベストコスト（10表すべてを結合するコストのうちベストのもの）をオーバーしたら、残りの7表の結合のコスト計算はせずに、次の結合順序の評価を開始します。

• 評価する結合順序数の上限の設定 10gでは、評価する結合順序数の上限は2000通りです。以前のバージョンではOPTIMIZER_MAX_PERMUTATIONSパラメータでこの上限を制御できましたが、10gで廃止されました。

• 直積（Cartesian Product）を含めた結合順序を考慮する表数の上限の設定 結合する表が5表（単一行テーブル 注15 は含まない）までは、SQLに結合条件が記述されていない表同士の結合（直積 注16 ）も含めて、すべての結合順序（最大120）が評価されます。 6表以上になると直積を含む結合順序は最後に評価されます。これは通常、直積結合はコストが高いオペレーションであるという経験則によります。このため直積結合を含む結合順序は「結合順序数の上限」に達して評価されない可能性があります。以前のバージョンではOPTIMIZER_SEARCH_LIMITでこの上限表数を制御できましたが、このパラメータは8iで廃止されました。

オプティマイザは取り得るすべての組み合わせについて評価しているわけではないため、真に最適な結合順序を発見できないというリスクは避けられません。そういう意味で、オプティマイザが決定する結合順序は「準最適」であると言えます。しかし、正確性とパフォーマンスはトレードオフであり、どこかでバランスさせなければなりません。 上記のような動作のため、Oracleでは特にulTP系の高速なレスポンスが要求されるシステムにおいては、結合する表数の上限を6表程度としておいたほうが良いでしょう。6表であれば2000通りの結合順序数の上限に達することがないためです。

注14：「実効行数」とは、各表に対するすべての条件句を適用した結果、それぞれの表から取り出されると予測される行数です。各表の行数に選択率を掛けたものとも言えます。 注15：指定された条件によって1行のみ取り出されることが保証されている表。NULL値を許可しない一意索引列に対して条件句が指定されている表。

注16：デカルト結合とも言い、結合条件のない2つの表を結合する唯一の方法。すべての行の組み合わせを作るため、通常はコストの高い結合方法ですが、場合によっては一部の表の直積を作ったほうがパフォーマンスが良い場合があります。

結合処理のパフォーマンスを最大化するためには、最適な結合順序／結合方法／アクセスパスの選択が必要です。結合順序の評価は表数が増えるにしたがって爆発的に増加するため、効率的なサーチの方法が必要です。オプティマイザは経験則と上限値の設定によって「準最適」な実行計画を高速に決定しています。しかし、取り得るすべてのケースを評価しているわけではないため、真に最適な結合順序を発見できないリスクはあります。

Part3　オプティマイザ統計の管理 >>

▲ ページTOPに戻る

"門外不出のOracle現場ワザ" インデックスに戻る

Copyright © 2009, Oracle Corporation Japan. All rights reserved.

無断転載を禁ず

この文書はあくまでも参考資料であり、掲載されている情報は予告なしに変更されることがあります。日本オラクル社は本書の内容に関していかなる保証もいたしません。また、本書の内容に関連したいかなる損害についても責任を負いかねます。

Oracleは米国Oracle Corporationの登録商標です。文中に参照されている各製品名及びサービス名は米国Oracle Corporationの商標または登録商標です。その他の製品名及びサービス名はそれぞれの所有者の商標または登録商標の可能性があります。

小田 圭二(おだ けいじ)

1996年日本オラクル入社。人事教育本部にて、新卒や中途採用社員に対し、データベースやOS、ネットワークの講師を5年ほど経験した後、2000年にテクノロジーコンサルティング本部に異動。 テクノロジーのコンサルタントとして、主に大規模ミッションクリティカルシステムを担当。

ポリシーは、「OracleもOS上で動くアプリケーションにすぎない。だから、OS、ストレージ、ネットワークを学ぶべき」。 スキル面の興味は、アーキテクチャ、DBA、インフラ技術、教育、コンサル手法など。