■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 件のコメント:
コメントを投稿