今回はOracleの統計情報について検証しました。
統計情報(オプティマイザ統計とも呼ばれます)は、SQLの処理(ワークロード)の見積もりに必要なデータ(サーバやOS、データベースの特性を数値化したもの)です。
オプティマイザが実行計画を作成/選択する際、アクセスパスのコストを見積もる元データ(判断材料)として使用されます。
統計情報は2種類あります。
オブジェクト統計
表統計(XXX_TAB_STATISTICS、XXX_TABLES)
・行数(NUM_ROWS)
・ブロック数(BLOCKS)
・行の平均の長さ(AVG_ROW_LEN)
※XXX_TABLESは統計情報の失効状態(STALE_STATS)は確認不可
※ブロック数はHWMまでのブロック数
列統計(XXX_TAB_COL_STATISTICS)
・列内の個別値(NDV)の数(NUM_DISTINCT)
・列内のNULL数(NUM_NULLS)
・ヒストグラム(HISTGRAM)
索引統計(XXX_IND_STATISTICS、XXX_INDEXES)
・索引の高さ(深さ)(BLEVEL)
・リーフブロック数(LEAF_BLOCKS)
・クラスタ化係数(CLUSTERING_FACTOR)
※XXX_INDEXESは統計情報の失効状態(STALE_STATS)は確認不可
【補足】
NDV(Number of Distinct Value)
列に存在する値の種類の数
たとえば性別列に「男性」、「女性」のデータが存在する場合、2(性別列に存在するデータは2種類)となる
ヒストグラム
表の列データの分布状況
セレクティビティ(行の選択率)のコストを見積もるために使用される統計情報
通常の統計情報に比べ、より最適な実行計画を作成/選択するのが目的
頻度分布ヒストグラム、高さ調整ヒストグラム、上位頻度ヒストグラム、ハイブリッドヒストグラムがある
詳細は別記事で書きます
※セレクティビティ：行が選択される割合。全表スキャンか索引スキャンかの判断指標になる
※上位頻度ヒストグラム、ハイブリッドヒストグラムは12c以降の機能
クラスタ化係数
索引の列データと表の列データの分布度(データの格納順がどれくらい異なるかの指標)
索引の列データに格納されているデータと実際の表の列データに格納されているデータの順番がどれくらい揃っているかを表す
データの格納順がだいたい揃っている(一致している)場合、クラスタ化係数は小さくなる
データの格納順がほとんど揃っていない場合、クラスタ化係数は大きくなる
クラスタ化係数の値によってデータアクセスのオペレーションが変わってくる(クラスタ化係数が小さい場合、TABLE ACCESS BY INDEX ROWIDが採用されやすくなる)
詳細は別記事で書きます
システム統計
ハードウェアやOSのリソースI/O、使用率など(AUX_STATS$)
・CPUSPEEDNW(CPU速度(MHz))
・IOSEEKTIM(ディスクのシーク時間・回転待ち時間・OSオーバーヘッドの合計(ミリ秒))
・IOTFRSPEED(1ブロックの平均転送速度)
統計情報の収集方法は2種類あります。
・自動(自動メンテナンスタスク、動的サンプリング)
・手動(DBMS_STATSパッケージ、Enterprise Manager)
【補足】
自動メンテナンスタスク
データベースのメンテナンスのため、定期的に自動で実行されるタスク(ジョブ)
データベース作成時に自動的に作成される
自動メンテナンスタスクのうち、自動オプティマイザ統計収集ジョブが統計情報を収集する
※システム統計は収集されないため、手動で収集する必要がある
※自動メンテナンスタスクを使用する場合、STATISTICS_LEVEL初期化パラメータをTYPICALまたはALLに設定する
※自動メンテナンスタスクには自動セグメントアドバイザや自動SQLチューニングアドバイザ、SQL計画管理(SPM)展開アドバイザ(12c～)もある
収集時間帯(デフォルト)
・月～金 22:00～02:00
・土、日 06:00～20時間後に終了
収集対象
・オブジェクト統計が存在しないオブジェクト
・更新(表全体に対し、10%以上の変更)があったオブジェクト
・オブジェクト統計が古いオブジェクト
動的サンプリング
12c以降は動的統計と呼ばれている
オプティマイザが実行計画を作成する際、統計情報の欠落など統計情報が最新化されていない場合、自動的に統計情報を収集する
SQLを実行した際、何かしらの理由で統計情報が取得されていない場合、統計情報を取得してSQLの処理を継続するために動的サンプリングは実装されている
動的サンプリングを使用する場合、OPTIMIZER_DYNAMIC_SAMPLING初期化パラメータを設定する(0だと無効)
今回は手動による統計情報の収集とリストアを検証してみます。
Enterprise Manager環境が無いため、DBMS_STATSパッケージを使用します。
■検証環境
OS：Oracle Linux 6.5
DB/GI：Oracle Database 12c Release 1 (12.1.0.2.0) Enterprise Edition
※2ノードRAC(管理者管理型DB)
■前提
・非CDB
・索引の統計情報も自動収集する
select DBMS_STATS.GET_PREFS(pname => 'CASCADE') as cascade from dual;
CASCADE
------------------------------
DBMS_STATS.AUTO_CASCADE
■検証パターン
①統計情報取得＆リストア(データベース単位)
②統計情報取得＆リストア(ディクショナリ単位)
③統計情報取得＆リストア(固定表(動的パフォーマンス表)単位)
④統計情報取得＆リストア(スキーマ単位)
⑤統計情報取得＆リストア(テーブル単位)
⑥統計情報取得＆リストア(インデックス単位)
⑦統計情報取得＆リストア(システム統計単位)
■検証
①統計情報取得＆リストア(データベース単位)
オブジェクト統計のうち、データベース単位で統計情報を取得＆リストアできるか検証します
【検証手順】
1. 統計情報取得状態確認
2. 統計情報取得
3. 統計情報取得状態確認
4. 統計情報リストア
5. 統計情報取得状態確認
【作業ログ】
1. 統計情報取得状態確認SQL> SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME IN('EMP', 'DEPT', 'BONUS', 'SALGRADE');
OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
-------------------- --------------- ---------- ---------- ----------- ------------------- ---------
SCOTT BONUS
SCOTT DEPT
SCOTT EMP
SCOTT SALGRADE
SQL> SELECT OWNER, INDEX_NAME, TABLE_NAME, BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR, NUM_ROWS, LAST_ANALYZED, STALE_STATS FROM DBA_IND_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME IN('EMP', 'DEPT', 'BONUS', 'SALGRADE');
OWNER INDEX_NAME TABLE_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS LAST_ANALYZED STALE_STA
-------------------- --------------- --------------- ---------- ----------- ----------------- ---------- ------------------- ---------
SCOTT PK_DEPT DEPT
SCOTT PK_EMP EMP
2. 統計情報取得
SQL> BEGIN
2 DBMS_STATS.GATHER_DATABASE_STATS();
3 END;
4 /
PL/SQLプロシージャが正常に完了しました。
3. 統計情報取得状態確認
SQL> SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME IN('EMP', 'DEPT', 'BONUS', 'SALGRADE');
OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
-------------------- --------------- ---------- ---------- ----------- ------------------- ---------
SCOTT BONUS 0 0 0 2023-01-23 13:18:39 NO
SCOTT DEPT 4 5 20 2023-01-23 13:18:48 NO
SCOTT EMP 14 5 38 2023-01-23 13:18:50 NO
SCOTT SALGRADE 5 5 10 2023-01-23 13:19:27 NO
SQL> SELECT OWNER, INDEX_NAME, TABLE_NAME, BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR, NUM_ROWS, LAST_ANALYZED, STALE_STATS FROM DBA_IND_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME IN('EMP', 'DEPT', 'BONUS', 'SALGRADE');
OWNER INDEX_NAME TABLE_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS LAST_ANALYZED STALE_STA
-------------------- --------------- --------------- ---------- ----------- ----------------- ---------- ------------------- ---------
SCOTT PK_DEPT DEPT 0 1 1 4 2023-01-23 13:18:48 NO
SCOTT PK_EMP EMP 0 1 1 14 2023-01-23 13:18:50 NO
★統計情報が取得された
4. 統計情報リストア
SQL> --リストア前の統計情報(リストア確認用に事前に統計情報を取得済み)
SQL> SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME IN('EMP', 'DEPT', 'BONUS', 'SALGRADE');
OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
-------------------- --------------- ---------- ---------- ----------- ------------------- ---------
SCOTT BONUS 0 0 0 2023-01-23 13:27:26 NO
SCOTT DEPT 4 5 20 2023-01-23 13:27:34 NO
SCOTT EMP 14 5 38 2023-01-23 13:27:36 NO
SCOTT SALGRADE 5 5 10 2023-01-23 13:28:11 NO
SQL> SELECT OWNER, INDEX_NAME, TABLE_NAME, BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR, NUM_ROWS, LAST_ANALYZED, STALE_STATS FROM DBA_IND_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME IN('EMP', 'DEPT', 'BONUS', 'SALGRADE');
OWNER INDEX_NAME TABLE_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS LAST_ANALYZED STALE_STA
-------------------- --------------- --------------- ---------- ----------- ----------------- ---------- ------------------- ---------
SCOTT PK_DEPT DEPT 0 1 1 4 2023-01-23 13:27:34 NO
SCOTT PK_EMP EMP 0 1 1 14 2023-01-23 13:27:36 NO
SQL> BEGIN
2 DBMS_STATS.RESTORE_DATABASE_STATS(as_of_timestamp => TO_DATE('2023-01-23 13:19:28', 'YYYY-MM-DD HH24:MI:SS'));
3 END;
4 /
PL/SQLプロシージャが正常に完了しました。
★リストア日時は統計情報取得日時+1した値を指定します（取得時刻が2023-01-23 13:19:27の場合、2023-01-23 13:19:28にする）
5. 統計情報取得状態確認
SQL> SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME IN('EMP', 'DEPT', 'BONUS', 'SALGRADE');
OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
-------------------- --------------- ---------- ---------- ----------- ------------------- ---------
SCOTT BONUS 0 0 0 2023-01-23 13:18:39 NO
SCOTT DEPT 4 5 20 2023-01-23 13:18:48 NO
SCOTT EMP 14 5 38 2023-01-23 13:18:50 NO
SCOTT SALGRADE 5 5 10 2023-01-23 13:19:27 NO
SQL> SELECT OWNER, INDEX_NAME, TABLE_NAME, BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR, NUM_ROWS, LAST_ANALYZED, STALE_STATS FROM DBA_IND_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME IN('EMP', 'DEPT', 'BONUS', 'SALGRADE');
OWNER INDEX_NAME TABLE_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS LAST_ANALYZED STALE_STA
-------------------- --------------- --------------- ---------- ----------- ----------------- ---------- ------------------- ---------
SCOTT PK_DEPT DEPT 0 1 1 4 2023-01-23 13:18:48 NO
SCOTT PK_EMP EMP 0 1 1 14 2023-01-23 13:18:50 NO
★統計情報がリストアされた
②統計情報取得＆リストア(ディクショナリ単位)
オブジェクト統計のうち、ディクショナリ単位で統計情報を取得＆リストアできるか検証します
【検証手順】
1. 統計情報取得状態確認
2. 統計情報取得
3. 統計情報取得状態確認
4. 統計情報リストア
5. 統計情報取得状態確認
【作業ログ】
1. 統計情報取得状態確認SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER IN('SYS', 'SYSTEM', 'SCOTT') AND TABLE_NAME NOT LIKE 'X$%';
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
------------------------------ ---------- ---------- ----------- ------------------- ---------
（略）
HISTGRM$ 47905 1374 34 2023-01-23 13:18:51 YES
2. 統計情報取得
SQL> BEGIN
2 DBMS_STATS.GATHER_DICTIONARY_STATS();
3 END;
4 /
PL/SQLプロシージャが正常に完了しました。
3. 統計情報取得状態確認
SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER IN('SYS', 'SYSTEM', 'SCOTT') AND TABLE_NAME NOT LIKE 'X$%';
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
------------------------------ ---------- ---------- ----------- ------------------- ---------
（略）
HISTGRM$ 47862 1481 34 2023-01-23 13:45:18 NO
★統計情報が取得された
4. 統計情報リストア
SQL> --リストア前の統計情報(リストア確認用に事前に統計情報を取得済み)
SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER IN('SYS', 'SYSTEM', 'SCOTT');
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
------------------------------ ---------- ---------- ----------- ------------------- ---------
（略）
HISTGRM$ 52592 1481 34 2023-01-23 13:52:25
SQL> BEGIN
2 DBMS_STATS.RESTORE_DICTIONARY_STATS(as_of_timestamp => TO_DATE('2023-01-23 13:45:19', 'YYYY-MM-DD HH24:MI:SS'));
3 END;
4 /
PL/SQLプロシージャが正常に完了しました。
5. 統計情報取得状態確認
SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER IN('SYS', 'SYSTEM', 'SCOTT') AND TABLE_NAME NOT LIKE 'X$%';
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
------------------------------ ---------- ---------- ----------- ------------------- ---------
（略）
HISTGRM$ 47862 1481 34 2023-01-23 13:45:18 NO
★統計情報がリストアされた
③統計情報取得＆リストア(固定表(動的パフォーマンス表)単位)
オブジェクト統計のうち、固定表(動的パフォーマンス表)単位で統計情報を取得＆リストアできるか検証します
【検証手順】
1. 統計情報取得状態確認
2. 統計情報取得
3. 統計情報取得状態確認
4. 統計情報リストア
5. 統計情報取得状態確認
【作業ログ】
1. 統計情報取得状態確認SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE TABLE_NAME LIKE 'X$%';
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
------------------------------ ---------- ---------- ----------- ------------------- ---------
（略）
X$DIAG_INFO 11 63 2023-01-03 22:02:12
2. 統計情報取得
SQL> BEGIN
2 DBMS_STATS.GATHER_FIXED_OBJECTS_STATS();
3 END;
4 /
PL/SQLプロシージャが正常に完了しました。
3. 統計情報取得状態確認
SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE TABLE_NAME LIKE 'X$%';
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
------------------------------ ---------- ---------- ----------- ------------------- ---------
（略）
X$DIAG_INFO 11 63 2023-01-23 14:30:32
★統計情報が取得された
4. 統計情報リストア
SQL> --リストア前の統計情報(リストア確認用に事前に統計情報を取得済み)
SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE TABLE_NAME LIKE 'X$%';
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
------------------------------ ---------- ---------- ----------- ------------------- ---------
（略）
X$DIAG_INFO 11 63 2023-01-23 14:36:45
SQL> BEGIN
2 DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(as_of_timestamp => TO_DATE('2023-01-23 14:30:33', 'YYYY-MM-DD HH24:MI:SS'));
3 END;
4 /
PL/SQLプロシージャが正常に完了しました。
5. 統計情報取得状態確認
SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE TABLE_NAME LIKE 'X$%';
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
------------------------------ ---------- ---------- ----------- ------------------- ---------
（略）
X$DIAG_INFO 11 63 2023-01-23 14:30:32
★統計情報がリストアされた
④統計情報取得＆リストア(スキーマ単位)
オブジェクト統計のうち、スキーマ単位で統計情報を取得＆リストアできるか検証します
【検証手順】
1. 統計情報取得状態確認
2. 統計情報取得
3. 統計情報取得状態確認
4. 統計情報リストア
5. 統計情報取得状態確認
【作業ログ】
1. 統計情報取得状態確認SQL> SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME IN('EMP', 'DEPT', 'BONUS', 'SALGRADE');
OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
-------------------- --------------- ---------- ---------- ----------- ------------------- ---------
SCOTT BONUS 0 0 0 2023-01-23 13:18:39 NO
SCOTT DEPT 4 5 20 2023-01-23 13:18:48 NO
SCOTT EMP 14 5 38 2023-01-23 13:18:50 NO
SCOTT SALGRADE 5 5 10 2023-01-23 13:19:27 NO
SQL> SELECT OWNER, INDEX_NAME, TABLE_NAME, BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR, NUM_ROWS, LAST_ANALYZED, STALE_STATS FROM DBA_IND_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME IN('EMP', 'DEPT', 'BONUS', 'SALGRADE');
OWNER INDEX_NAME TABLE_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS LAST_ANALYZED STALE_STA
-------------------- --------------- --------------- ---------- ----------- ----------------- ---------- ------------------- ---------
SCOTT PK_DEPT DEPT 0 1 1 4 2023-01-23 13:18:48 NO
SCOTT PK_EMP EMP 0 1 1 14 2023-01-23 13:18:50 NO
2. 統計情報取得
SQL> BEGIN
2 DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SCOTT');
3 END;
4 /
PL/SQLプロシージャが正常に完了しました。
3. 統計情報取得状態確認
SQL> SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME IN('EMP', 'DEPT', 'BONUS', 'SALGRADE');
OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
-------------------- --------------- ---------- ---------- ----------- ------------------- ---------
SCOTT BONUS 0 0 0 2023-01-23 14:50:17 NO
SCOTT DEPT 4 5 20 2023-01-23 14:50:17 NO
SCOTT EMP 14 5 38 2023-01-23 14:50:17 NO
SCOTT SALGRADE 5 5 10 2023-01-23 14:50:17 NO
SQL> SELECT OWNER, INDEX_NAME, TABLE_NAME, BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR, NUM_ROWS, LAST_ANALYZED, STALE_STATS FROM DBA_IND_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME IN('EMP', 'DEPT', 'BONUS', 'SALGRADE');
OWNER INDEX_NAME TABLE_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS LAST_ANALYZED STALE_STA
-------------------- --------------- --------------- ---------- ----------- ----------------- ---------- ------------------- ---------
SCOTT PK_DEPT DEPT 0 1 1 4 2023-01-23 14:50:17 NO
SCOTT PK_EMP EMP 0 1 1 14 2023-01-23 14:50:17 NO
★統計情報が取得された
4. 統計情報リストア
SQL> --リストア前の統計情報(リストア確認用に事前に統計情報を取得済み)
SQL> SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME IN('EMP', 'DEPT', 'BONUS', 'SALGRADE');
OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
-------------------- --------------- ---------- ---------- ----------- ------------------- ---------
SCOTT BONUS 0 0 0 2023-01-23 14:58:28 NO
SCOTT DEPT 4 5 20 2023-01-23 14:58:28 NO
SCOTT EMP 14 5 38 2023-01-23 14:58:28 NO
SCOTT SALGRADE 5 5 10 2023-01-23 14:58:28 NO
SQL> SELECT OWNER, INDEX_NAME, TABLE_NAME, BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR, NUM_ROWS, LAST_ANALYZED, STALE_STATS FROM DBA_IND_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME IN('EMP', 'DEPT', 'BONUS', 'SALGRADE');
OWNER INDEX_NAME TABLE_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS LAST_ANALYZED STALE_STA
-------------------- --------------- --------------- ---------- ----------- ----------------- ---------- ------------------- ---------
SCOTT PK_DEPT DEPT 0 1 1 4 2023-01-23 14:58:28 NO
SCOTT PK_EMP EMP 0 1 1 14 2023-01-23 14:58:28 NO
SQL> BEGIN
2 DBMS_STATS.RESTORE_SCHEMA_STATS(ownname => 'SCOTT',
3 as_of_timestamp => TO_DATE('2023-01-23 14:50:18', 'YYYY-MM-DD HH24:MI:SS'));
4 END;
5 /
PL/SQLプロシージャが正常に完了しました。
5. 統計情報取得状態確認
SQL> SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME IN('EMP', 'DEPT', 'BONUS', 'SALGRADE');
OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
-------------------- --------------- ---------- ---------- ----------- ------------------- ---------
SCOTT BONUS 0 0 0 2023-01-23 14:50:17 NO
SCOTT DEPT 4 5 20 2023-01-23 14:50:17 NO
SCOTT EMP 14 5 38 2023-01-23 14:50:17 NO
SCOTT SALGRADE 5 5 10 2023-01-23 14:50:17 NO
SQL> SELECT OWNER, INDEX_NAME, TABLE_NAME, BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR, NUM_ROWS, LAST_ANALYZED, STALE_STATS FROM DBA_IND_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME IN('EMP', 'DEPT', 'BONUS', 'SALGRADE');
OWNER INDEX_NAME TABLE_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS LAST_ANALYZED STALE_STA
-------------------- --------------- --------------- ---------- ----------- ----------------- ---------- ------------------- ---------
SCOTT PK_DEPT DEPT 0 1 1 4 2023-01-23 14:50:17 NO
SCOTT PK_EMP EMP 0 1 1 14 2023-01-23 14:50:17 NO
★統計情報がリストアされた
⑤統計情報取得＆リストア(テーブル単位)
オブジェクト統計のうち、テーブル単位で統計情報を取得＆リストアできるか検証します
【検証手順】
1. 統計情報取得状態確認
2. 統計情報取得
3. 統計情報取得状態確認
4. 統計情報リストア
5. 統計情報取得状態確認
【作業ログ】
1. 統計情報取得状態確認SQL> SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME = 'EMP';
OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
-------------------- --------------- ---------- ---------- ----------- ------------------- ---------
SCOTT EMP 14 5 38 2023-01-23 14:50:17 NO
2. 統計情報取得
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'EMP');
4 END;
5 /
PL/SQLプロシージャが正常に完了しました。
3. 統計情報取得状態確認
SQL> SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME = 'EMP';
OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
-------------------- --------------- ---------- ---------- ----------- ------------------- ---------
SCOTT EMP 14 5 38 2023-01-23 15:12:51 NO
★統計情報が取得された
4. 統計情報リストア
SQL> --リストア前の統計情報(リストア確認用に事前に統計情報を取得済み)
SQL> SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME = 'EMP';
OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
-------------------- --------------- ---------- ---------- ----------- ------------------- ---------
SCOTT EMP 14 5 38 2023-01-23 15:17:27 NO
SQL> BEGIN
2 DBMS_STATS.RESTORE_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'EMP',
4 as_of_timestamp => TO_DATE('2023-01-23 15:12:52', 'YYYY-MM-DD HH24:MI:SS'));
5 END;
6 /
PL/SQLプロシージャが正常に完了しました。
5. 統計情報取得状態確認
SQL> SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME = 'EMP';
OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
-------------------- --------------- ---------- ---------- ----------- ------------------- ---------
SCOTT EMP 14 5 38 2023-01-23 15:12:51 NO
★統計情報がリストアされた
⑥統計情報取得＆リストア(インデックス単位)
オブジェクト統計のうち、インデックス単位で統計情報を取得＆リストアできるか検証します
【検証手順】
1. 統計情報取得状態確認
2. 統計情報取得
3. 統計情報取得状態確認
4. 統計情報リストア
5. 統計情報取得状態確認
【作業ログ】
1. 統計情報取得状態確認SQL> SELECT OWNER, INDEX_NAME, TABLE_NAME, BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR, NUM_ROWS, LAST_ANALYZED, STALE_STATS FROM DBA_IND_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME = 'EMP';
OWNER INDEX_NAME TABLE_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS LAST_ANALYZED STALE_STA
-------------------- --------------- --------------- ---------- ----------- ----------------- ---------- ------------------- ---------
SCOTT PK_EMP EMP 0 1 1 14 2023-01-23 15:12:51 NO
2. 統計情報取得
SQL> BEGIN
2 DBMS_STATS.GATHER_INDEX_STATS(ownname => 'SCOTT',
3 indname => 'PK_EMP');
4 END;
5 /
PL/SQLプロシージャが正常に完了しました。
3. 統計情報取得状態確認
SQL> SELECT OWNER, INDEX_NAME, TABLE_NAME, BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR, NUM_ROWS, LAST_ANALYZED, STALE_STATS FROM DBA_IND_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME = 'EMP';
OWNER INDEX_NAME TABLE_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS LAST_ANALYZED STALE_STA
-------------------- --------------- --------------- ---------- ----------- ----------------- ---------- ------------------- ---------
SCOTT PK_EMP EMP 0 1 1 14 2023-01-23 15:25:30 NO
★統計情報が取得された
4. 統計情報リストア
★インデックスの統計情報のリストア関数が無いため確認不可
⑦統計情報取得＆リストア(システム統計単位)
システム統計の統計情報を取得＆リストアできるか検証します
【検証手順】
1. 統計情報取得状態確認
2. 統計情報取得
3. 統計情報取得状態確認
4. 統計情報リストア
5. 統計情報取得状態確認
【作業ログ】
1. 統計情報取得状態確認SQL> SELECT * FROM AUX_STATS$;
SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 07-07-2014 06:53
SYSSTATS_INFO DSTOP 07-07-2014 06:53
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 3308.9701
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
2. 統計情報取得
SQL> BEGIN
2 DBMS_STATS.GATHER_SYSTEM_STATS();
3 END;
4 /
PL/SQLプロシージャが正常に完了しました。
3. 統計情報取得状態確認
SQL> SELECT * FROM AUX_STATS$;
SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 01-23-2023 15:42
SYSSTATS_INFO DSTOP 01-23-2023 15:42
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 1477
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
★統計情報が取得された
4. 統計情報リストア
SQL> --リストア前の統計情報(リストア確認用に事前に統計情報を取得済み)
SQL> SELECT * FROM AUX_STATS$;
SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 01-23-2023 15:46
SYSSTATS_INFO DSTOP 01-23-2023 15:46
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 1477
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
SQL> BEGIN
2 DBMS_STATS.RESTORE_SYSTEM_STATS(as_of_timestamp => TO_DATE('23-01-23 15:42:55', 'YY-MM-DD HH24:MI:SS'));
3 END;
4 /
PL/SQLプロシージャが正常に完了しました。
5. 統計情報取得状態確認
SQL> SELECT * FROM AUX_STATS$;
SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 01-23-2023 15:51
SYSSTATS_INFO DSTOP 01-23-2023 15:51
SYSSTATS_INFO FLAGS 128
SYSSTATS_MAIN CPUSPEEDNW 1477
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
★リストアは正常終了したが、統計情報取得日時は指定した時刻に戻らなかった(リストアを実施した日時になった)
■参考資料
DBMS_STATS
自動データベース・メンテナンス・タスクの管理
https://www.oracle.com/technetwork/jp/ondemand/branch/120328-cbo-1593994-ja.pdf
パフォーマンス改善と事前対策に役立つ Oracle SQLチューニング 本気で学ぶ実践的な考え方とテクニック
■おわりに
RESTORE_XXX_STATS()関数に指定するリストア日時に「統計情報を取得した日時」を指定した場合、NUM_ROWS列などの統計情報がNULLになってしまいました。
リストア日時には統計情報を取得した日時より、1大きい値を指定する必要がありそうでした(秒なら1秒進める、分なら1分進める、時間なら1時間進める)
SQL> --統計情報取得
SQL> SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME = 'EMP';
OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
-------------------- --------------- ---------- ---------- ----------- ------------------- ---------
SCOTT EMP 14 5 38 2023-01-23 13:18:50 NO
SQL> --リストア前の統計情報(リストア確認用に事前に統計情報を取得済み)
SQL> SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME = 'EMP';
OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
-------------------- --------------- ---------- ---------- ----------- ------------------- ---------
SCOTT EMP 14 5 38 2023-01-23 13:27:36 NO
SQL> --リストア日時に「統計情報を取得した日時」を指定してリストア
SQL> BEGIN
2 DBMS_STATS.RESTORE_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'EMP',
4 as_of_timestamp => TO_DATE('2023-01-23 13:18:50', 'YYYY-MM-DD HH24:MI:SS'));
5 END;
6 /
PL/SQLプロシージャが正常に完了しました。
SQL> SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME = 'EMP';
OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
-------------------- --------------- ---------- ---------- ----------- ------------------- ---------
SCOTT EMP
★統計情報がNULLになってしまった
SQL> --リストア日時に「統計情報を取得した日時+1」を指定してリストア
SQL> BEGIN
2 DBMS_STATS.RESTORE_TABLE_STATS(ownname => 'SCOTT',
3 tabname => 'EMP',
4 as_of_timestamp => TO_DATE('2023-01-23 13:18:51', 'YYYY-MM-DD HH24:MI:SS'));
5 END;
6 /
PL/SQLプロシージャが正常に完了しました。
SQL> SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME = 'EMP';
OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED STALE_STA
-------------------- --------------- ---------- ---------- ----------- ------------------- ---------
SCOTT EMP 14 5 38 2023-01-23 13:18:50 NO
★「統計情報を取得した日時」の統計情報が入った(リストアされた)
なお、リストアに使用する統計情報はデフォルトで31日間保存されます。
統計情報は他にも保留(取得した統計情報をオプティマイザに公開しない)、エクスポート/インポート(EXPORT_XXX_STATS/IMPORT_XXX_STATS)、ロック(統計情報を変更させない)などができます。
