しばちょう先生の試して納得！DBAへの道 indexページ▶▶
みなさん、こんにちは。“しばちょう”こと柴田長（しばた つかさ）です。前回に引き続き今回もオプティマイザ統計収集の管理についてご紹介していきます。
前回の演習ではオプティマイザ統計情報の収集時間を高速化するOracle Database 11gの新機能を体験して頂きましたが、今回は効率化という観点で、統計情報の収集時間を短縮削減する機能をご紹介したいと思います。統計情報が実際に格納されているデータの特徴にどれだけ近いかという精度がオプティマイザに最良な実行計画を選択させる為には不可欠ですが、統計情報をどのような頻度で取得する必要があるのでしょうか？極端な話をすれば、ある表のレコード数が1件増えただけで統計情報を再取得する必要性は感じませんよね。逆に、レコード数が2倍、3倍と大幅に増加した場合には、再取得した方が良さそうだという感覚は理解し易いかと思います。
では何%が閾値なのか。この一般的な解として、Oracle Database 11gでは表の既存レコード数の10%（= デフォルト値）が更新された場合、その表の統計情報が失効したと自動的に判断する仕組みを持っています。そして、この情報を基に失効したオブジェクトに限定した統計情報の再取得が可能である為、日々の統計情報収集に要する時間の短縮を実現します。
と、いくら言葉で紹介していても実際のオペレーションが分からなければ利用することができませんので、以下の演習をこれまでの演習で使用したOracle Database 11g Release 2 Enterprise Editionのデータベースで試してみてください。
■ 1. TRYスキーマに、第一カラムがNUMBER型、第二カラムがCHAR(100)型で、100レコードが格納されている表「TBL2」と表「TBL3」を作成して下さい。また、第一カラムに主キーを作成してください。
sqlplus TRY/TRY SQL> -- TBL2表の作成 create table TBL2 (COL1 number NOT NULL, COL2 char(100)) ; insert into TBL2 select LEVEL, 'hoge'||to_char(LEVEL) from DUAL connect by LEVEL <= 100 ; commit ; create unique index IDX_TBL2_COL1 on TBL2(COL1) ; alter table TBL2 add primary key (COL1) using index ; SQL> -- TBL3表の作成 create table TBL3 as select * from TBL2 ; create unique index IDX_TBL3_COL1 on TBL3(COL1) ; alter table TBL3 add primary key (COL1) using index ;
はい、これまで何度も同じような演習がありましたので、サクッと作成出来たかと思います。ちなみに、主キーを作成する手法はいくつか存在しますが、私は上記の手順で作成することをお勧めしています。今回は100レコードしかINSERTしませんので、どの手順でもほとんど実行時間の差を感じることはできませんが、数十GB以上にもなるレコード量を扱う場合には大きな差が出てくる傾向があります。詳細は今後の連載で制約を扱った際に説明させて頂きたいと思っていますので、今回は3つのポイントだけ挙げておきます。1つ目はINSERTと索引作成の順序。2つ目は索引作成と主キーの定義を分けること。3つ目は主キーとなるカラムの定義にNot Null制約を付けておくこと。これらの全ての理由を思いつかれた方は、本当に良く実機を触られている方だと思います。
■ 2. TRYスキーマ内の全てのオブジェクトの統計情報を収集し、各表のオプティマイザ統計情報が収集されたことを確認して下さい。
sqlplus TRY/TRY SQL> select TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, to_char(LAST_ANALYZED, 'YYYY/MM/DD HH24:MI:SS') from USER_TABLES ; TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN TO_CHAR(LAST_ANALYZ ------------------------------ ---------- ---------- ----------- ------------------- TBL1 10000000 263322 183 2012/07/18 13:48:40 TBL2 TBL3 SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'TRY') ; PL/SQLプロシージャが正常に完了しました。 SQL> select TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, to_char(LAST_ANALYZED, 'YYYY/MM/DD HH24:MI:SS') from USER_TABLES ; TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN TO_CHAR(LAST_ANALYZ ------------------------------ ---------- ---------- ----------- ------------------- TBL1 10000000 263322 183 2012/07/30 18:33:25 TBL2 100 46 104 2012/07/30 18:33:43 TBL3 100 46 104 2012/07/30 18:33:43
まずは、TRYスキーマ内の各表にオプティマイザ統計情報が存在するか否か、存在する場合にはその統計情報はいつ収集されたものなのかを確認しています。その結果、前回の演習で作成したTBL1表では7/18に収集した統計情報が存在していて、今回の演習1で作成したTBL2表とTBL3表では、統計情報が空の状態であることが確認できます。
次に、TRYスキーマ内の全てのオブジェクトの統計情報を収集する為、DBMS_STATSパッケージのGATHER_SCHEMA_STATSプロシージャを実行します。引数のパラメータとしては、スキーマ名を指定する「OWNNAME」のみを設定しており、特にデータのサンプリング率を指定する「ESTIMATE_PERCENT」は設定していません。これにより、前回の記事でご紹介したOracle Database 11gの「DBMS_STATS.AUTO_SAMPLE_SIZE」が使用されることになり、統計情報の収集時間の高速化が行われますね。
最後に、念のため各表のオプティマイザ統計情報が収集されたのかを確認してみると、全ての表で統計情報がセットされていますし、それらが直近で収集されたものであることが「LAST＿ANALYZED」列から判断することができます。
■ 3. TBL2表の11レコードを更新した後、統計情報が失効していることを確認して下さい。
sqlplus TRY/TRY
SQL> -- TBL2表を11行更新
update TBL2 set COL2='UPDATED' where COL1 between 1 and 11 ;
commit ;
SQL> connect / as sysdba
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
SQL> connect TRY/TRY
-- 表の更新状況の確認
select TABLE_NAME, INSERTS, UPDATES, DELETES, TRUNCATED from USER_TAB_MODIFICATIONS ;
TABLE_NAME INSERTS UPDATES DELETES TRU
------------------------------ ---------- ---------- ---------- ---
TBL2 0 11 0 NO
SQL> -- TRYスキーマ内で統計情報が失効していると判断されたオブジェクトの確認
SET SERVEROUTPUT ON
DECLARE
obj_list DBMS_STATS.ObjectTab := DBMS_STATS.ObjectTab() ;
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'TRY', objlist=> obj_list, options=>'LIST STALE') ;
FOR i in obj_list.FIRST.. obj_list.LAST LOOP
dbms_output.put_line(obj_list(i).objtype || ' ' ||
obj_list(i).ownname || '.' ||
obj_list(i).objname || ' ' ||
nvl(obj_list(i).partname, 'NonPartition') || ' – ' ||
nvl(obj_list(i).subpartname, '*'));
END LOOP;
END;
/
TABLE TRY.TBL2 NonPartition - *
TBL2表の11レコードを更新するのは問題ないと思いますが、上記の回答例の中で、SYSユーザーで接続してDBMS_STATS.FLUSH_DATABASE_MONITORING_INFOプロシージャを実行しています。これはマニュアルに説明があるように「メモリ内のすべての表の監視情報をディクショナリにフラッシュ」するプロシージャであり、Oracle Databaseは定期的に表の更新履歴をメモリからディクショナリにフラッシュしますが、それを待機せずに手動でフラッシュすることが可能です。今回は検証の都合上、表の更新直後にUSER_TAB_MODIFICATIONSディクショナリ・ビューを確認する為に実行していますので、通常は実行する必要が無いプロシージャです。
USER_TAB_MODIFICATIONSディクショナリ・ビューを参照することで、表毎の更新（INSERT/UPDATE/DELETE）されたレコード数等確認することが可能です。今回の演習ではTBL2表の11レコードをUPDATEしたので、その結果がそのまま出力されていることが確認できたかと思います。非常に便利なビューなので、私はパフォーマンス・チューニングの際に各表がアプリケーションからどのように更新されるのかを把握する一つの手段として、このビューを参照することがあります。
ただし、ここで驚いていてはいけません。演習問題としては「統計情報が失効していることを確認する」でしたので、それを確認してみましょう。演習2においてDBMS_STATS.GATHER_SCHEMA_STATSプロシージャで統計情報を収集しましたが、このプロシージャにはもう一つの用途が存在し、「統計情報が失効」もしくは「統計情報が空」と判別されたオブジェクトの一覧を抽出することができてしまうのです。では、解説しますね。
改めて、マニュアル「PL/SQLパッケージ・プロシージャ及びタイプ・リファレンス」でDBMS_STATS.GATHER_SCHEMA_STATSプロシージャを参照してみると、引数が異なる構文が2つ記述されていていることに気付くと思います。ぱっと見てしまうとほとんど同じ引数が指定されているように思いがちですが、データ型の列をじっくり見ていくと「OUT」と記述されている「objlist」パラメータなるものがあるではないですか。ということで、これを使用します。とは言え、データ型が「ObjectTab」と意味不明な記述になっていることに不安を感じてしまいますよね。今回はこういったものをどのように読み解くのかも解説しておきます。プロシージャをご自身で作成されたことが有る方はご存知だと思いますが、各PL/SQLパッケージには独自のタイプが定義されていることがあります。そして、まさに「ObjectTab」はDBMS_STATSパッケージ独自のタイプであり、きちんとマニュアルに次のような記載があります。
TYPE ObjectElem IS RECORD ( ownname VARCHAR2(30), -- owner objtype VARCHAR2(6), -- 'TABLE' or 'INDEX' objname VARCHAR2(30), -- table/index partname VARCHAR2(30), -- partition subpartname VARCHAR2(30)); -- subpartition type ObjectTab is TABLE of ObjectElem;
ここでのタイプ「ObjectTab」は、スキーマ名、オブジェクト・タイプ、オブジェクト名、パーティション名、サブ・パーティション名を構成要素とするObjectElemで定義されたレコードを複数行格納するメモリ上の表のような入物だと認識してください。
と言うことで、GATHER_SCHEMA_STATSプロシージャを実行する際、「options」パラメータに「LIST STALE」を指定することで統計情報が失効したと判断されたオブジェクトの一覧が抽出されますが、その結果を格納する為に「objlist」パラメータにObjectTabタイプの変数を指定する必要があるのです。上記の回答例では、TRYスキーマのTBL2表の統計情報が失効していると判断されていることが確認できたかと思います。
ちなみに、「options」パラメータに指定可能な値は次の7つであり、デフォルト（optionsを明示的に記述しない）では「GATHER」が選択される為、スキーマ内の全てのオブジェクトの統計情報を収集してくれているのです。意外と知らないですよね。
GATHER : スキーマ内の全てのオブジェクトの統計情報を収集 GATHER AUTO : 新しい統計情報の収集が必要と判断したオブジェクトの統計情報を収集 + 対象リストも戻す GATHER STALE : 統計情報が失効しているオブジェクトの統計情報を収集 + 対象リストも戻す GATHER EMPTY : 統計情報がないオブジェクトの統計情報を収集 + 対象リストも戻す LIST AUTO : GATHER AUTOを使用して処理されるオブジェクトのリストを戻す LIST STALE : 統計情報が失効しているオブジェクトのリストを戻す LIST EMPTY : 統計情報がないオブジェクトのリストを戻す
さらに、ちなみにですが、表毎の更新履歴はメモリ上に保持されていて定期的にディクショナリへフラッシュすると説明しましたが、最新の監視情報（更新履歴）がディクショナリにフラッシュされていないタイミングで「options => ‘GATHER STALE’」で統計情報を収集しようとした場合、どうなるの？DBMS_STATS.FLUSH_DATABASE_MONITORING_INFOプロシージャを実行した後に、統計情報の収集をした方がよいの？という疑問を持たれた方は非常に鋭いです。答えは「気にする必要なし」になります。理由は、GATHER_*_STATSプロシージャを実行すると、監視情報が内部処理で自動的にフラッシュされるからです。これも実はマニュアルに掲載されているのですね。マニュアルを読む必要性を少しは納得して頂けましたかね。
■ 4. TRYスキーマ内で統計情報が失効しているオブジェクト（TBL2）のみの統計を収集するように、OPTIONSパラメータを適切に設定してDBMS_STATS.GATHER_SCHEMA_STATSプロシージャを実行してください。
sqlplus TRY/TRY SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'TRY', options => 'GATHER STALE'); SQL> select TABLE_NAME, to_char(LAST_ANALYZED, 'YYYY/MM/DD HH24:MI:SS') from USER_TABLES ; TABLE_NAME TO_CHAR(LAST_ANALYZ ------------------------------ ------------------- TBL1 2012/07/30 18:33:25 TBL2 2012/07/30 18:43:40 TBL3 2012/07/30 18:33:43
既に演習2で解説していますので、あとは試すだけですね。「options => ‘GATHER STALE’」を設定してGATHER_SCHEMA_STATSプロシージャで統計情報を収集してみた結果、統計情報が更新されているのはLAST_ANALYZED列から判断して、TBL2表だけであることが確認できたと思います。
この統計情報の失効を判断する機能をフル活用すれば、実データにほとんど変更が無い表は統計情報を再収集する時間やリソースを省くことが可能になるので、冒頭にも記載させて頂いた通り、日々の統計情報収集に要する時間の短縮が実現できそうですよね。
■ 5. TRYスキーマ内の全てのオブジェクトに対して、統計情報が失効したと判断する閾値を5%に変更して下さい。
sqlplus TRY/TRY SQL> -- TRYスキーマ内の全オブジェクトに対し、統計情報が失効したと判断する閾値を5%に変更 exec DBMS_STATS.SET_SCHEMA_PREFS(ownname => 'TRY', pname => 'STALE_PERCENT', pvalue => 5); SQL> -- 閾値が変更されたかの確認 select TABLE_NAME, DBMS_STATS.GET_PREFS(pname => 'STALE_PERCENT', ownname => 'TRY', tabname => TABLE_NAME) "STALE_PERCENT" from USER_TABLES; TABLE_NAME STALE_PERCENT ------------------------------ ---------------- TBL1 5 TBL2 5 TBL3 5
折角の機会なので、統計情報が失効したと見なす閾値をデフォルト値から変更する方法も体験しておきましょう。
特定スキーマ内の全てのオブジェクトに対してデフォルト値を変更するには、DBMS_STATS.SET_SCHEMA_PREFSプロシージャとなります。また、失効の閾値は「STALE_PERCENT」パラメータになるので、引数「pname」にこれを指定し、変更後の値を引数「pvalue」に指定します。
この変更が適用されているのかを確認する為には、DBMS_STATS.GET_PREFSファンクションを使用します。それほど難しくは無いと思うので、説明は割愛させて頂きます。結果的には、TRYスキーマ内の全ての表（TBL1、TBL2、TBL3）のSTALE_PERCENTが5%に変更されていることが確認できたかと思います。
■ 6. TBL2表のみ統計情報が失効したと判断する閾値をデフォルトに戻して下さい。
sqlplus TRY/TRY
SQL> -- TBL2表の統計情報が失効したと判断する閾値をデフォルトに戻す
exec DBMS_STATS.SET_TABLE_PREFS(ownname => 'TRY', tabname => 'TBL2', -
pname => 'STALE_PERCENT', pvalue => NULL);
SQL> -- 閾値が変更されたかの確認
select TABLE_NAME, DBMS_STATS.GET_PREFS(pname => 'STALE_PERCENT', ownname => 'TRY', tabname => TABLE_NAME) "STALE_PERCENT" from USER_TABLES;
TABLE_NAME STALE_PERCENT
------------------------------ ----------------
TBL1 5
TBL2 10
TBL3 5
デフォルト値を変更する方法を学んだだけでは不十分ですよね。それを戻す方法もきちんと身につけておくべきと言うことで、TBL2表のみ閾値をデフォルトの10%に戻してみます。スキーマ全体ではなく、1つの表のみを対象としたいので、DBMS_STATS.SET_TABLE_PREFSプロシージャを使用します。「RESET」的なプロシージャは無く、演習4でデフォルト値から別の値に変更した方法とほぼ同じように実行します。ただし、「デフォルト値に戻す」= 「pvalue => NULL」と言うことだけ覚えておいてください。
さて、Oracle Database 11gでのオプティマイザ統計情報の取得の効率化を体験して頂きましたが、いかがでしたでしょうか？
以前と比較して自動統計収集でオプティマイザ統計収集を取得しているデータベースも増えてきている印象がありますが、まだまだ手動取得されているケースも多いかと思います。また、データ量も爆発的に増加しているという傾向もあるので、統計情報の収集に要する時間を少しでも短縮したいという要望は多いです。前回と今回の演習で体験して頂いた高速化と効率化。データベース管理者にとっては非常に強い武器となる機能なので、引き続き細かい部分の動作確認を行って頂きたいと思っています。
次回は、「統計情報の保留」という機能について体験して頂きたいと考えています。こちらは高速化や効率化の観点ではなく、SQLの実行計画の安定化につながる機能となりますので、楽しみにしていて下さい。今回もありがとうございました。次回も頑張りますので、よろしくお願いします。