Oracleデータベースのパフォーマンスを最大化するには、統計情報を取得する必要があります。データの種類や分布に応じてデータ検索を高速化することが可能となります。統計情報はデータベース・チューニングには欠かせないものとなっていますので、Oracleデータベースの統計情報を収集しましょう。
システム統計情報の収集
Oracleデータベースでは、SQLをより早く実行する実行計画を立てる際に、データの種類やデータ分布やSQLの実行効率以外にも、CPUやディスクI/O速度を参考にしています。システムの統計情報を取得することで、より正確なCPUやディスクI/O速度を算出することが出来るようになり、システムに合わせたより最適な実行計画が選択されるようになります。
EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS();実行するタイミングとしては、サーバーのハードウェアが変更になった場合に実行するとよいでしょう。
データ・ディクショナリの統計情報取得
データディクショナリの統計情報を取得します。
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;ユーザー権限を変更したりなど、データ・ディクショナリの変更が発生した場合に実行するとよいでしょう。
データベース全体の統計情報を取得
データベース全体の統計情報を取得します。
EXECUTE DBMS_STATS.GATHER_DATABASE_STATS();データベース全体を対象として統計情報を取得しますので、非常に時間がかかります。負荷が問題となる場合には、スキーマ単位や個別のオブジェクト単位での統計情報収集を実施すべきでしょう。
スキーマ全体の統計情報取得
指定したスキーマの統計情報だけを取得します。
-- スキーマ全体の統計情報を取得する(全てのオブジェクトが統計情報収集対象となる)
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'TEST');
-- スキーマ全体の統計情報を取得する(統計情報の更新が必要と判断されたオブジェクトのみ統計情報収集対象となる)
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'TEST', OPTIONS=>'GATHER AUTO');指定されたスキーマ全体を対象として統計情報を取得しますので、非常に時間がかかります。負荷が問題となる場合には、個別のオブジェクト単位での統計情報収集を実施すべきでしょう。
個別のデータベース・オブジェクトの統計情報取得
個別のテーブルやインデックスの統計情報を取得します。
-- 指定したテーブルの統計情報取得
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TEST', tabname=>'TABLE1', cascade=>false);
-- 指定したテーブルと関連するインデックスの統計情報取得
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TEST', tabname=>'TABLE1', cascade=>true);
-- 指定したインデックスの統計情報取得
EXECUTE DBMS_STATS.GATHER_INDEX_STATS(ownname=>'TEST', indname=>'IDX_TABLE1');
-- 指定したテーブルとインデックス、列統計を取得
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TEST', tabname=>'TABLE1', cascade=>true, method_opt=>'FOR ALL COLUMNS SIZE AUTO');
-- 指定したテーブルとインデックス、列統計
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TEST', tabname=>'TABLE1', cascade=>true, method_opt=>'FOR ALL COLUMNS SIZE 254');パーティション表の統計情報取得
パーティション表の統計情報を取得します。
-- パーティション表のグローバルレベルだけの統計情報取得
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TEST', tabname=>'TABLE1', cascade=>true, granularity=>'GLOBAL');
-- パーティション表のパーティションレベルだけの統計情報取得
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TEST', tabname=>'TABLE1', cascade=>true, granularity=>'PARTITION');
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TEST', tabname=>'TABLE1', cascade=>true, granularity=>'SUBPARTITION');ANALYZEコマンドで統計情報取得
ANALYZEコマンドで統計情報を収集することも可能ですが、あくまでも下位互換のために残されています。より正確な統計情報を取得できて履歴管理も出来るDBMS_STATSパッケージを利用しましょう。
-- 統計の取得
ANALYZE TABLE TABLE1 COMPUTE STATISTICS;
-- 統計の削除
ANALYZE TABLE TABLE1 DELETE STATISTICS;統計情報のロック・アンロック
収集した統計情報を固定したり、固定化を解除したりする場合には、統計情報のロック・アンロックを行います。
--統計のロック
EXECUTE DBMS_STATS.LOCK_TABLE_STATS('TEST', 'TABLE1');
--統計のアンロック
EXECUTE DBMS_STATS.UNLOCK_TABLE_STATS('TEST', 'TABLE1');統計情報のエクスポート・インポート
統計情報を他のデータベースにコピーしたい場合に使います。データベースサーバーを移行する場合など、移行先のデータベースに統計情報をコピーしたい場合に利用します。
-- 統計情報のエクスポート
EXECUTE DBMS_STATS.CREATE_STAT_TABLE(ownname =>'TEST', stattab =>'STAT_TAB', tblspace =>'USERS');
EXECUTE DBMS_STATS.EXPORT_TABLE_STATS(ownname => 'TEST', tabname => 'TABLE1', stattab => 'STAT_TAB', cascade => TRUE);
-- 統計情報エクスポートテーブルの削除
EXECUTE DBMS_STATS.DROP_STAT_TABLE(ownname => 'TEST', stattab => 'STAT_TAB');
-- 統計情報インポート
EXECUTE DBMS_STATS.IMPORT_TABLE_STATS(ownname => 'TEST', tabname => 'TABLE1', stattab => 'STAT_TAB', cascade => TRUE);統計情報の削除
不要な統計情報を削除します。
-- 特定のテーブルの統計情報削除
EXECUTE DBMS_STATS.DELETE_TABLE_STATS(ownname=>'TEST', tabname=>'TABLE1');
-- システム統計情報削除
EXECUTE DBMS_STATS.DELETE_SYSTEM_STATS();統計情報を確認できるディクショナリー一覧
収集した統計情報を確認できるディクショナリ一覧です。
SELECT * FROM DBA_TAB_STATISTICS;
SELECT * FROM DBA_IND_STATISTICS;
SELECT * FROM DBA_TAB_COL_STATISTICS;
SELECT * FROM DBA_PART_COL_STATISTICS;
SELECT * FROM DBA_SUBPART_COL_STATISTICS;
SELECT * FROM DBA_OPTSTAT_OPERATIONS;
SELECT * FROM DBA_TAB_STATS_HISTORY;まとめ
Oracleデータベースで様々な統計情報収集を行うコマンドを紹介しました。統計情報収集はいきなり本番環境で実施するのではなく、まず試験環境で統計情報を収集してみて、既存のSQLの実行効率が悪化していないか確認するとともに、統計情報収集に掛かる時間も計測しておきましょう。本番環境でいきなり統計情報を取得することによって、いままで早かったSQLが突然遅くなってしまったというケースや、統計情報収集に非常に時間がかかってしまい、サーバーに負荷がかかってしまった、というような問題が発生する可能性があります。これらの点に注意しつつ、統計情報を収集することにより、データの種類や分布に応じて、SQLの実行計画が最適なものになるはずです。
