2013/08/29

Oracle統計情報のPENDING&PUBLISH機能

■Oracleの統計情報の取得は恐い・・・

筆者はSQLのパフォーマンスチューニングをする際、まずは対象のオブジェクトの統計方法の状態から確認します。
というのも、過去に自動取得機能のせいで毎日SQLのパフォーマンスが劣化し大変な思いをしたことがあり、それ以来自動取得機能をOFFにしているのです。

その為、手動で統計情報を収集しなおすことがよくあるのですが、
収集後すぐに統計情報が反映してしまうので、さらに劣化するということも多々ありました。

一度テストしたいな~と常々思っていたら、ありました。そんな機能が。

■PENDING&PUBLISH

Oracle Database 11gから、統計情報に"保留"="PENDING"と、"公開"="PUBLISH"という制御が新たに追加されました。

デフォルトではすぐに公開する設定(TRUE)ですが、
FALSEにしておくと、収集された統計情報は一旦保留されます。
以下ではUSERユーザーのTAB表の統計情報を保留状態で収集し、一度テストをしてから公開する流れを書いていきます。

①現在の状況を確認
select dbms_stats.get_prefs('PUBLISH','user','tab') from dual;

DBMS_STATS.GET_PREFS('PUBLISH','user','tab')
-------------------------------------------
TRUE


②TAB表のPUBLISHをFALSEに変更
exec dbms_stats.set_table_prefs('user', 'tab', 'PUBLISH', 'FALSE');

PL/SQLプロシージャが正常に完了しました。

テーブルに反映されたかどうか再度①のSQLで確認
select dbms_stats.get_prefs('PUBLISH','user','tab') from dual;

DBMS_STATS.GET_PREFS('PUBLISH','user','tab')
-------------------------------------------
FALSE


③統計情報収集
exec dbms_stats.gather_table_stats(ownname=>'user',tabname=>'tab'
                                           ,estimate_percent=>100,cascade=>TRUE);

PL/SQLプロシージャが正常に完了しました。



この状態でオプティマイザは収集した統計情報を使用しません。
この統計情報をテストするためには、optimizer_use_pending_statisticsパラメータをTRUEにしてSQLを実行します。
これにより、現行のセッションのみ保留中の統計情報を使うようになります。

④テスト準備:optimizer_use_pending_statisticsパラメータをTRUEに変更
alter session set optimizer_use_pending_statistics = TRUE;

セッションが変更されました。

⑤テスト
autotraceなどを使って実行計画を確認します。
もし問題なければ公開し、もし劣化したら削除します。

※なお、性能劣化の原因を調べたい場合には、
dbms_stats.diff_table_stats_in_pendingファンクションを使えば、保留中と公開中の統計情をを比較できます。

⑥-1保留していた統計情報を公開
exec dbms_stats.publish_pending_stats('user1', 'tab1);

PL/SQLプロシージャが正常に完了しました。


⑥-2保留していた統計情報を削除
exec dbms_stats.delete_pending_stats('user1', 'tab1);

PL/SQLプロシージャが正常に完了しました。


⑦テーブルに対して公開状態を元に戻す
exec dbms_stats.set_table_prefs('user1', 'tab1', 'PUBLISH', 'TRUE');

PL/SQLプロシージャが正常に完了しました。



合間合間でuser_tablesディクショナリのlast_analyzed列で状況を確認すると、
統計情報の動きがわかりやすいかもしれませんね。
なお、テーブル単位だけではなく、データベース単位、スキーマ単位でも同じことができます。


0 件のコメント:

コメントを投稿