CBOはどの情報を元に実行計画を決めるのか
「開発環境と本番環境で実行計画が違うんだけどなんで！？」
今日もこんな問い合わせがありました。
もうね、最近は問い合わせの8割が実行計画と統計情報。
何べん同じことを言ったら良いんだろうね。
そろそろ、社内(といっても常駐先内)での情報共有の仕組みとかにも、
口を出していったほうが良いんだろうか。
っつーか、こんだけブログに書いてて、わりとそれっぽいキーワードで
検索したら、上位に来るようにもなっているのに、それでも聞いてくるってことは、
どんだけ丸投げ体質なんだよと。
まぁ、隣で開発やってるお会社さんは、アクセス禁止サイトへのアクセスを
させないために、Webへのアクセスを禁止しています。
なんという前時代的な・・・。
おっと、他の会社をdisってても、仕方ないですね。
とりあえず、タイトルの件に関しては、
自分なりにまとめて回答してみたので、こちらにも共有しますよっと。
さて、冒頭の質問に戻ります。
開発環境と、本番環境で実行計画が違う。
これはどういうことかといいますと、
それぞれの環境において適した実行計画(=コストの低い実行計画)が違うと
オプティマイザが判断した。ということになります。
ここで、「それぞれの環境」という言葉を出しましたが、
オプティマイザはデータベース上の情報で、「それぞれの環境」について
判断しています。つまり、実行計画が異なるということは、
オプティマイザへのインプットである、「情報」が異なる。
ということになりますね。
では、オプティマイザはどのような情報を参考に、
実行計画を立てているのでしょうか。
コストベースオプティマイザが参考にする情報
コストベースオプティマイザは、
オプティマイザ統計、システム統計、初期化パラメータを使用してコスト計算を行ないます。
また、初期化パラメータの設定によって、
使用不可となっているオプティマイザの機能は使用しません。
つまり実行計画の決定における要素は下記の３つになりますね。
では、この３点について説明していきましょう。
オプティマイザ関連のパラメータ設定
オラクルはバージョンアップのたんびに、オプティマイザの新機能を入れてきます。
で、その新機能やら、昔からある機能を使ったり使わなかったりというのを、
パラメータで制御できるわけです。
当然、パラメータによって停止されているオプティマイザの機能については、
実行計画の検討のフェーズで除外されるため、
この設定が異なると、実行計画に違いが出てくる可能性があります。
まぁ、基本中の基本ではあるんですが、開発環境と本番環境のパラメータ設定は
あわせておきましょうねってことです。
では確認方法を。
確認方法
V$SYS_OPTIMIZER_ENV にオプティマイザが使用するパラメータ設定が
格納されているので、これで確認可能です。
SQL> col name for a40 SQL> col value for a20 SQL> set pagesize 1000 SQL> select name ,value from V$SYS_OPTIMIZER_ENV; NAME VALUE ---------------------------------------- -------------------- parallel_execution_enabled true optimizer_features_enable 10.2.0.3 cpu_count 1 active_instance_count 1 parallel_threads_per_cpu 2 hash_area_size 204800 bitmap_merge_area_size 1048576 sort_area_size 102400 sort_area_retained_size 0 pga_aggregate_target 0 KB parallel_query_mode enabled parallel_dml_mode disabled parallel_ddl_mode enabled optimizer_mode all_rows cursor_sharing exact star_transformation_enabled false optimizer_index_cost_adj 100 optimizer_index_caching 0 query_rewrite_enabled true query_rewrite_integrity enforced workarea_size_policy manual optimizer_dynamic_sampling 2 statistics_level typical skip_unusable_indexes true optimizer_secure_view_merging true 25行が選択されました。
ちなみに、上では隠しパラメータが一つも出てきてませんが、
隠しパラメータに関しては、default以外の設定をすると、
表示されるようになるみたいです。
SQL> alter system set "_optim_peek_user_binds"=FALSE scope=both; システムが変更されました。 SQL> alter system set "_complex_view_merging"=FALSE scope=both; システムが変更されました。 SQL> select name ,value from V$SYS_OPTIMIZER_ENV; NAME VALUE ---------------------------------------- -------------------- parallel_execution_enabled true optimizer_features_enable 10.2.0.3 cpu_count 1 active_instance_count 1 parallel_threads_per_cpu 2 hash_area_size 204800 bitmap_merge_area_size 1048576 sort_area_size 102400 sort_area_retained_size 0 pga_aggregate_target 0 KB parallel_query_mode enabled parallel_dml_mode disabled parallel_ddl_mode enabled optimizer_mode all_rows cursor_sharing exact star_transformation_enabled false _complex_view_merging false optimizer_index_cost_adj 100 optimizer_index_caching 0 query_rewrite_enabled true query_rewrite_integrity enforced _optim_peek_user_binds false workarea_size_policy manual optimizer_dynamic_sampling 2 statistics_level typical skip_unusable_indexes true optimizer_secure_view_merging true 27行が選択されました。
あと、この中で特殊なパラメータとして、
optimizer_features_enable が上げられます。
このパラメータは複数のパラメータを一挙に変更するような動きを持つ、
アンブレラパラメータになってます。
興味のある人は、下記で隠しパラメータも調べられますので、
optimizer_features_enable を変更しながら、どのパラメータが変更されるのか、
確認してみるといいと思います。
隠しパラメータを確認するSQL。
set linesize 120 col name for a50 col value for a30 select i.ksppinm name ,v.ksppstvl value from x$ksppi i , x$ksppcv v where i.indx = v.indx and i.ksppinm like '\_%' escape '\' order by name;
システム統計
確か、Oracle 9iからだと思いますが、
コストベースオプティマイザがI/Oのコストだけではなく、
CPUの処理コストも考慮できるようになりました。
10gでは、起動時に、自動的にCPU、I/Oの統計情報が取得され、
コストベースオプティマイザが判断するときの材料として使われます。
ですので、開発環境と本番環境でハードウェアも合わせておいたほうが良いわけですね。
われわれみたいな箱売り、ライセンス売り屋さんにはありがたい話ですけど、
ユーザ側としては、やってられませんよね。
ちなみに、これが違う場合にどうやって合わせたらよいかというと・・・、
どうしようもありません。たぶん。
あきらめて、違う方法で実行計画を固定化することを考えてください。
とりあえず、じゃあ確認方法を。
確認方法
下記のSQLで確認できます。
SQL> col sname for a16 SQL> col pname for a20 SQL> col pval1 for 999,999,999 SQL> col pval2 for a20 SQL> SELECT * FROM SYS.AUX_STATS$ 2 WHERE SNAME IN ( 'SYSSTATS_INFO', 'SYSSTATS_MAIN' ) 3 ORDER BY SNAME,PNAME; SNAME PNAME PVAL1 PVAL2 ---------------- -------------------- ------------ -------------------- SYSSTATS_INFO DSTART 12-25-2007 15:54 SYSSTATS_INFO DSTOP 12-25-2007 15:54 SYSSTATS_INFO FLAGS 1 SYSSTATS_INFO STATUS COMPLETED SYSSTATS_MAIN CPUSPEED SYSSTATS_MAIN CPUSPEEDNW 1,285 SYSSTATS_MAIN IOSEEKTIM 10 SYSSTATS_MAIN IOTFRSPEED 4,096 SYSSTATS_MAIN MAXTHR SYSSTATS_MAIN MBRC SYSSTATS_MAIN MREADTIM SYSSTATS_MAIN SLAVETHR SYSSTATS_MAIN SREADTIM 13行が選択されました。
SYSSTATS_MAIN のほうの、「CPUSPEEDNW」「IOSEEKTIM」「IOTFRSPEED」が
システム統計になります。
CPUSPEEDNW -> CPUの速度。(Oracleの独自の判定により数値化)
IOSEEKTIM -> Disk I/O にかかる時間
IOTFRSPEED -> Disk I/O のスループット
となっているようです。
オプティマイザ統計
コストベースオプティマイザは下記の統計情報を参考にして、
実行計画を決定します。
ですので、これらの情報が違う場合にも、実行計画が異なるケースが出てきます。
おそらく一番多いパターンじゃないかと思います。
そもそも、データが違うと、統計情報も当然異なってくるわけで、
最近では、SOX法やら、セキュリティやらの絡みで、
本番データを開発環境にロードすることを禁止されていたりするところが
多くなってきてるので、非常に悩ましい問題なわけです。
とりあえずここを合わせたい場合は、
統計情報の export/import を行なって同期を取る。
ぐらいの対応しか思いつきませんね・・・。
詳しくは、下記のエントリを参照・・・。
ちなみに、同じデータをimportして統計情報を取得した場合でも、
厳密には、統計情報が異なる可能性があります。
オプティマイザが参考にしている情報のうち、
「ブロック数」や「リーフブロック数」「索引の高さ」に関しては、
物理的な要因に左右されます。
データが論理的に同じだからといって、
統計情報が同じになるとは限りません。ご注意ください。
では、これらの確認方法。
確認方法
ディクショナリビューに格納されている情報で確認ができます。
まずは、表統計。
SQL> col table_name for a20 SQL> select TABLE_NAME,NUM_ROWS,BLOCKS,AVG_ROW_LEN 2 from DBA_TABLES 3 where OWNER = 'SCOTT' 4 and TABLE_NAME = 'TEST'; TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN -------------------- ---------- ---------- ----------- TEST 10000 10097 4008
パーティションを使用している場合は、
DBA_TAB_PARTITIONS や DBA_TAB_SUBPARTITIONS を見る必要があるので
注意してくださいね。
続いて、列統計。
SQL> col table_name for a20 SQL> col column_name for a20 SQL> select TABLE_NAME,COLUMN_NAME,NUM_DISTINCT,NUM_NULLS,HISTOGRAM 2 from DBA_TAB_COL_STATISTICS 3 where OWNER = 'SCOTT' 4 and TABLE_NAME = 'TEST'; TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM -------------------- -------------------- ------------ ---------- --------------------------------------------- TEST COL1 10000 0 NONE TEST COL2 3334 0 NONE TEST COL3 10000 0 NONE
TABLEの場合と違って、ディクショナリは DBA_TAB_COL_STATISTICS を見に行ってますので
注意ください。
非パーティション表の場合は、DBA_TAB_COLUMNS でも見れるんですけど、
パーティションを使用している場合は、
DBA_PART_COL_STATISTICS や DBA_SUBPART_COL_STATISTICS を
見に行く必要があるので、整合性を取るために敢えて DBA_TAB_COL_STATISTICS を
例として出してみました。
最後、索引統計。
SQL> col index_name for a20 SQL> select INDEX_NAME,LEAF_BLOCKS,BLEVEL,CLUSTERING_FACTOR 2 from DBA_INDEXES 3 where OWNER = 'SCOTT' 4 and TABLE_NAME = 'TEST'; INDEX_NAME LEAF_BLOCKS BLEVEL CLUSTERING_FACTOR -------------------- ----------- ---------- ----------------- TEST_IND 20 1 10000
まとめ
まとめです。
上記を見てもわかるように、開発環境と本番環境で必ず同じ実行計画が選択されるように、
環境面を合わせる形で運用を行なっていくのは、非常に困難なことだと思います。
よって、クリティカルなSQLに関しては、毎回同じ実行計画を選ぶように「環境をあわせる」のではなく、
毎回同じ実行計画を選ぶように人為的に何らかの手を加える。
人為的な何かとは、たとえば、プランスタビリティであったり、HINT句であったり。
こういった方法で対応するほうが、正解なんじゃないかと思います。
あ、でも、パラメータぐらいはあわせておいてくださいね・・・。
