Oracleデータベース作成時の自動メンテナンスタスクとは

Oracle11gR2でDBCAを使用してデータベースを作成していると、↓のような自動メンテナンスタスクのチェックボックスを選択する項目がある。

この自動メンテナンスタスクはもちろんONにしたほうが良いが(defalt ON)何をやっているのか調べてみた。

自動メンテナンスタスクの機能

以下、三つの機能を実行する。
1. 自動オプティマイザ統計収集
2. 自動セグメント・アドバイザ
3. 自動SQLチューニング・アドバイザ（EEのみ）

自動メンテナンスタスクの実行される時間

デフォルトでは、7種類のメンテナンス・ウィンドウが事前定義されており、それぞれが曜日を表し、各windowに時間が設定されている。
その時間内にOracleが自動で負荷の少ない時間帯を判断し、上記の自動メンテナンスタスクの機能を実行する。

select WINDOW_NAME, AUTOTASK_STATUS,OPTIMIZER_STATS, SEGMENT_ADVISOR, SQL_TUNE_ADVISOR from DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME                    AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE
------------------------------ -------- -------- -------- --------
MONDAY_WINDOW                  ENABLED  ENABLED  ENABLED  DISABLED
TUESDAY_WINDOW                 ENABLED  ENABLED  ENABLED  DISABLED
WEDNESDAY_WINDOW               ENABLED  ENABLED  ENABLED  DISABLED
THURSDAY_WINDOW                ENABLED  ENABLED  ENABLED  DISABLED
FRIDAY_WINDOW                  ENABLED  ENABLED  ENABLED  DISABLED
SATURDAY_WINDOW                ENABLED  ENABLED  ENABLED  DISABLED
SUNDAY_WINDOW                  ENABLED  ENABLED  ENABLED  DISABLED

1.自動オプティマイザ統計収集

毎日のwindowの時間内に以下条件の元、テーブルの統計情報が自動で収集されるという機能。
1. 統計情報が取得されていないために動的サンプリングされたテーブル
2. 統計情報を取得してから10％以上更新があったテーブル

• 統計情報が取得されていないために動的サンプリングされたテーブルとは
  これはuser_tablesのLAST_ANALYZEDがNULLのテーブル（一度も統計情報が取得されていないテーブル）に対してクエリを発行した際に動的サンプリングが行われる。
  動的サンプリングしたかは実行計画取得時にわかる。

SQL> select TABLE_NAME,LAST_ANALYZED from user_tables where TABLE_NAME='TESTING';

TABLE_NAME                     LAST_ANALYZED
------------------------------ -------------------
TESTING                                             ←LAST_ANALYZEDがNULLのテーブル

SQL> set autot on
SQL> select * from TESTING where t=1;

        ID          T
---------- ----------
         2          1

実行計画
----------------------------------------------------------
Plan hash value: 4028391465

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     6 |   156 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TESTING |     6 |   156 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T"=1)

Note
-----
   - dynamic sampling used for this statement (level=2)　←動的サンプリング

• 統計情報を取得してから10％以上更新があったテーブルとは
  MONITORING属性を利用し、テーブルに対する追加や変更(insert,update,delete)が発生すると、それらがだいたい何行ぐらい処理されたかをdba_tab_modificationsに記録する。この記録を元に10％以上更新があったテーブルか判断している模様。
  MONITORING属性はstatistics_level=TYPICAL(デフォルト)またはALLの場合には一時表以外の全てのテーブルのDMLを監視する。

SQL>select table_owner,table_name,inserts,updates,deletes,timestamp,truncated from sys.dba_tab_modifications where TABLE_OWNER='ORAUSER' order by 6;

TABLE_OWNER  TABLE_NAME      INSERTS    UPDATES    DELETES TIMESTAMP           TRU
------------ ------------ ---------- ---------- ---------- ------------------- ---
ORAUSER      ORATEST              13          0         13 2015/03/30 00:29:59 NO
ORAUSER      ORATABL               7          0          0 2015/03/30 00:29:59 NO
ORAUSER      TESTING             223          0        223 2015/03/30 00:29:59 NO

TIMESTAMP・・前回統計情報を取得した日時
INSERTS・・前回統計情報を取得してから実行されたINSERT
UPDATES・・前回統計情報を取得してから実行されたUPDATE
DELETES・・前回統計情報を取得してから実行されたDELETE
truncated・・前回の分析以降に表が切り捨てられているかどうか

統計収集されたか確認

1. テーブルの場合、user_tablesのLAST_ANALYZEDに統計情報を取得した日時が入る
2. インデックスの場合、user_indexesのLAST_ANALYZEDに統計情報を取得した日時が入る

2. 自動セグメント・アドバイザ

再生可能な領域が存在しているテーブルやインデックスを識別し、それらのセグメントの断片化を調査する仕組み。
自動で分析対象を取得する対象は以下
1. 領域のクリティカルまたは警告のしきい値を超えた表領域
2. アクティビティが最も多いセグメント
3. 増加率が最も高いセグメント

セグメント断片化の確認

1. Enterprise Manager を使用し、セグメント・アドバイザ推奨→推奨を確認。
2. DBA_ADVISOR_* ビューを問い合せる。
3. DBMS_SPACE.ASA_RECOMMENDATIONS プロシージャを使用する。

3. 自動SQLチューニング・アドバイザ（EEのみ）

入力として1つ以上のSQL文を取得し、自動チューニング・オプティマイザを起動して文に対するSQLチューニングを実行するらしい。
Enterprise Editionの機能なのでStandard Editionでは使用できない。