【RDS for Oracle】自動統計情報収集（自動オプティマイザ統計収集）変更方法

• Oracleでは3つの自動メンテナンス・タスクがあります

  • 自動統計情報収集（自動オプティマイザ統計収集）
  • 自動セグメント・アドバイザ
  • 自動SQLチューニング・アドバイザ

• これらのタスクはメンテナンス・ウィンドウ（メンテナンス可能として設定した時間帯）で実行されます。

• 自動化メンテナンス・タスクの有効／無効の確認

SQL> col CLIENT_NAME for a40
SQL> select client_name, status from dba_autotask_client;

CLIENT_NAME				 STATUS
---------------------------------------- --------
auto optimizer stats collection 	 ENABLED
auto space advisor			 ENABLED
sql tuning advisor			 ENABLED

※注意 自動SQLチューニング・アドバイザを使用するにはEnterprise EditionのオプションのOracle Tuning Packオプションが必要です。このオプションがない場合は自動SQLチューニング・アドバイザを無効化する必要があります。

自動メンテナンス・タスク	CLIENT_NAME
自動統計情報収集	auto optimizer stats collection
自動セグメント・アドバイザ	auto space advisor
自動SQLチューニング・アドバイザ	sql tuning advisor

• 自動メンテナンス・タスクの無効化の方法

　- 例：自動SQLチューニング・アドバイザの無効

BEGIN
 dbms_auto_task_admin.disable(
 client_name => 'sql tuning advisor',
 operation => NULL,
 window_name => NULL);
END;
/

• 自動SQLチューニング・アドバイザの確認

SQL> select client_name, status from dba_autotask_client;

CLIENT_NAME				 STATUS
---------------------------------------- --------
auto optimizer stats collection 	 ENABLED
auto space advisor			 ENABLED
sql tuning advisor			 DISABLED  ★無効化されてる

• 自動メンテナンス・タスクの有効化の方法

　- 例：自動SQLチューニング・アドバイザの有効

BEGIN
 dbms_auto_task_admin.enable(
 client_name => 'sql tuning advisor',
 operation => NULL,
 window_name => NULL);
END;
/

• 自動SQLチューニング・アドバイザの確認

SQL> select client_name, status from dba_autotask_client;

CLIENT_NAME				 STATUS
---------------------------------------- --------
auto optimizer stats collection 	 ENABLED
auto space advisor			 ENABLED
sql tuning advisor			 ENABLEDD  ★有効化に戻った

• メンテナンス・ウィンドウのスケジュールを確認

• デフォルトの実行時間

曜日	実行時間
月〜金	22:00〜翌日2:00（4時間）
土〜日	06:00〜翌日2:00（20時間）

SQL> col REPEAT_INTERVAL for a70
SQL> col DURATION for a30
SQL> select WINDOW_NAME ,REPEAT_INTERVAL ,DURATION, ENABLED from DBA_SCHEDULER_WINDOWS;

WINDOW_NAME	     REPEAT_INTERVAL							    DURATION			   ENABL
-------------------- ---------------------------------------------------------------------- ------------------------------ -----
WEEKEND_WINDOW	     freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0		    +002 00:00:00		   FALSE
WEEKNIGHT_WINDOW     freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0  +000 08:00:00		   FALSE
SUNDAY_WINDOW	     freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0		    +000 20:00:00		   TRUE
SATURDAY_WINDOW      freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0		    +000 20:00:00		   TRUE
FRIDAY_WINDOW	     freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0		    +000 04:00:00		   TRUE
THURSDAY_WINDOW      freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0		    +000 04:00:00		   TRUE
WEDNESDAY_WINDOW     freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0		    +000 04:00:00		   TRUE
TUESDAY_WINDOW	     freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0		    +000 04:00:00		   TRUE
MONDAY_WINDOW	     freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0		    +000 04:00:00		   TRUE

9 rows selected.

• メンテナンス・ウィンドウの実行予定時間を確認

SQL> set pages 100 line 200
SQL> col WINDOW_NAME for a20
SQL> col WINDOW_NEXT_TIME for a40
SQL> select WINDOW_NAME,to_char(WINDOW_NEXT_TIME,'yyyy/mm/dd hh24:mm;ss'),WINDOW_ACTIVE,AUTOTASK_STATUS,OPTIMIZER_STATS,SEGMENT_ADVISOR,SQL_TUNE_ADVISOR,HEALTH_MONITOR
 from DBA_AUTOTASK_WINDOW_CLIENTS order by 1;                                             

WINDOW_NAME	     TO_CHAR(WINDOW_NEXT WINDO AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE HEALTH_M
-------------------- ------------------- ----- -------- -------- -------- -------- --------
FRIDAY_WINDOW	     2019/07/12 22:07;00 FALSE ENABLED	ENABLED  ENABLED  ENABLED  DISABLED
MONDAY_WINDOW	     2019/07/08 22:07;00 FALSE ENABLED	ENABLED  ENABLED  ENABLED  DISABLED
SATURDAY_WINDOW      2019/07/13 06:07;00 FALSE ENABLED	ENABLED  ENABLED  ENABLED  DISABLED
SUNDAY_WINDOW	     2019/07/14 06:07;00 FALSE ENABLED	ENABLED  ENABLED  ENABLED  DISABLED
THURSDAY_WINDOW      2019/07/11 22:07;00 FALSE ENABLED	ENABLED  ENABLED  ENABLED  DISABLED
TUESDAY_WINDOW	     2019/07/09 22:07;00 FALSE ENABLED	ENABLED  ENABLED  ENABLED  DISABLED
WEDNESDAY_WINDOW     2019/07/10 22:07;00 FALSE ENABLED	ENABLED  ENABLED  ENABLED  DISABLED

7 rows selected.

• メンテナンス・ウィンドウの開始時刻を変更

　- (例)日曜のメンテナンス・ウィンドウの開始時間を2:00に変更

BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE(
         NAME      =>'SYS.SUNDAY_WINDOW'
        ,ATTRIBUTE =>'REPEAT_INTERVAL'
        ,VALUE     =>'freq=daily;byday=SUN;byhour=2;byminute=0; bysecond=0'
    );
END;
/

• 実行時間を変更
• (例)日曜のメンテナンス・ウィンドウの実行時間を6時間に変更

BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE(
         NAME      =>'SYS.SUNDAY_WINDOW'
        ,ATTRIBUTE =>'DURATION'
        ,VALUE     => NUMTODSINTERVAL(6, 'HOUR')
    );
END;
/

• 確認

SQL> select WINDOW_NAME ,REPEAT_INTERVAL ,DURATION, ENABLED from DBA_SCHEDULER_WINDOWS;

WINDOW_NAME	     REPEAT_INTERVAL							    DURATION			   ENABL
-------------------- ---------------------------------------------------------------------- ------------------------------ -----
WEEKEND_WINDOW	     freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0		    +002 00:00:00		   FALSE
WEEKNIGHT_WINDOW     freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0  +000 08:00:00		   FALSE
SUNDAY_WINDOW	     freq=daily;byday=SUN;byhour=2;byminute=0; bysecond=0		    +000 06:00:00		   TRUE  ★2:00〜8:00（6時間）に変更
SATURDAY_WINDOW      freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0		    +000 20:00:00		   TRUE
FRIDAY_WINDOW	     freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0		    +000 04:00:00		   TRUE
THURSDAY_WINDOW      freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0		    +000 04:00:00		   TRUE
WEDNESDAY_WINDOW     freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0		    +000 04:00:00		   TRUE
TUESDAY_WINDOW	     freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0		    +000 04:00:00		   TRUE
MONDAY_WINDOW	     freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0		    +000 04:00:00		   TRUE

9 rows selected.

参考

DBMS_SCHEDULER ジョブの変更