Oracle AUTO_SPACE_ADVISOR

Oracle AUTO_SPACE_ADVISOR_JOB 说明

有时我们会在AWR中看到如下信息：

里面对应的3条SQL 如下：

（1）call dbms_space.auto_space_advisor_job_proc( )

（2）calldbms_stats.gather_database_stats_job_proc ( )

（3）insert into wri$_adv_objspace_trend_dataselect timepoint, space_usage, space_alloc, quality fromtable(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL, NULL, 'FALSE',:5, 'FALSE'))

1.自动优化器统计收集：为所有方案对象收集陈旧的或缺少的统计数据，所收集的统计信息将被用来提高SQL的执行的性能，任务名是“autooptimizer stats collection”

2 自动分段顾问：标识数据库中的段是否有可以回收的空间，并以此信息统计为基础做出怎样整理段的碎片以节约空间。也可以手动的执行此job来获取最新的建议信息，或者获取自动段advisor 不检测的但又可以回收的段的信息，任务名是“auto space advisor”

3 自动SQL调整顾问：自动标识并尝试调整高负载的SQL，任务名是“sqltuning advisor”

如何用 SQLTuning Advisor (STA) 优化SQL语句

http://blog.csdn.net/tianlesoftware/article/details/5630888

在Oracle 11g中有如下3个自动维护任务：

SQL> select client_name,status fromdba_autotask_client;

CLIENT_NAME STATUS

------------------------------------------------------------------------

auto optimizer stats collection ENABLED

auto spaceadvisor ENABLED

sql tuningadvisor ENABLED

10.2.0.5版本的数据库中一些自动任务的调度设置情况：

SQL> selectjob_name,state,enabled,last_start_date fromdba_scheduler_jobs;

JOB_NAME STATE ENABLLAST_START_DATE

--------------------------------------------- ----- -----------------------------------

AUTO_SPACE_ADVISOR_JOB SCHEDULED TRUE 07-AUG-1006.00.03.792886 AM +08:00

GATHER_STATS_JOB SCHEDULED TRUE 07-AUG-1006.00.03.783957 AM +08:00

FGR$AUTOPURGE_JOB DISABLED FALSE

PURGE_LOG SCHEDULED TRUE 07-AUG-1003.00.00.353023 AM PRC

MGMT_STATS_CONFIG_JOB SCHEDULED TRUE 01-AUG-1001.01.01.822354 AM +08:00

MGMT_CONFIG_JOB SCHEDULED TRUE 07-AUG-1006.00.03.767320 AM +08:00

Oracle 11g:

SQL> selectjob_name,state,enabled,last_start_date from dba_scheduler_jobs;

JOB_NAME STATE ENABL LAST_START_DATE

--------------------------------------------- ----- ---------------------------------------------

XMLDB_NFS_CLEANUP_JOB DISABLED FALSE

SM$CLEAN_AUTO_SPLIT_MERGE SCHEDULED TRUE 10-7月 -1212.00.00.329000 上午 US/CENTRAL

RSE$CLEAN_RECOVERABLE_SCRIPT SCHEDULED TRUE 10-7月 -1212.00.00.544000 上午 US/CENTRAL

FGR$AUTOPURGE_JOB DISABLED FALSE

BSLN_MAINTAIN_STATS_JOB SCHEDULED TRUE 09-7月 -1209.56.03.422000 下午 -05:00

DRA_REEVALUATE_OPEN_FAILURES SCHEDULED TRUE 05-7月 -1209.44.10.601000 上午 US/CENTRAL

HM_CREATE_OFFLINE_DICTIONARY DISABLED FALSE

ORA$AUTOTASK_CLEAN SCHEDULED TRUE 10-7月 -1203.00.00.411000 上午 US/CENTRAL

FILE_WATCHER DISABLED FALSE

PURGE_LOG SCHEDULED TRUE 10-7月 -1203.00.00.414000 上午 US/CENTRAL

MGMT_STATS_CONFIG_JOB SCHEDULED TRUE 01-7月 -1203.21.42.545000 上午 -05:00

JOB_NAME STATE ENABL LAST_START_DATE

--------------------------------------------- ----- ---------------------------------------------

MGMT_CONFIG_JOB SCHEDULED TRUE 05-7月 -1209.44.10.392000 上午 US/CENTRAL

RLM$SCHDNEGACTION SCHEDULED TRUE 10-7月 -1209.15.17.021000 下午 +08:00

RLM$EVTCLEANUP SCHEDULED TRUE 10-7月 -1208.30.16.823000 上午 -05:00

14 rows selected.

Gather_database_stats 是在晚上10点早上6点及周末全天执行。

可以使用如下SQL 查看相关的执行情况：

SQL> col status FOR A10

SQL> COL RUN_DURATION FOR A20

SQL> COL start_date FOR A20

SQL> COL log_date FOR A20

SQL> SELECTstatus,TO_CHAR(ACTUAL_START_DATE,'YYYY-MM-DD HH24:MI:SS') start_date,TO_CHAR(log_date, 'YYYY-MM-DD HH24:MI:SS') log_date,RUN_DURATION FROMdba_scheduler_job_run_details WHERE job_name = 'AUTO_SPACE_ADVISOR_JOB' orderby 3;

STATUS START_DATE LOG_DATE RUN_DURATION

---------- ---------------------------------------- --------------------

SUCCEEDED 2011-12-31 00:00:02 2011-12-31 00:03:05 +000 00:03:03

SUCCEEDED 2012-01-02 00:00:03 2012-01-02 00:03:05 +000 00:03:03

SUCCEEDED 2012-01-03 00:00:02 2012-01-03 00:02:17 +000 00:02:15

SUCCEEDED 2012-01-04 00:00:02 2012-01-04 00:01:41 +000 00:01:39

SUCCEEDED 2012-01-05 00:01:14 2012-01-05 04:02:05 +000 04:00:51

比如这里的AUTO_SPACE_ADVISOR_JOB平时运行时间只有3分钟左右的时间就可以结束，在2012-01-05，异常的运行了4个小时。

在Oracle 10.2.0.2之前的版本，存在bug：5376783，会导致AUTO_SPACE_ADVISOR_JOB在运行时，占用大量的DISK READS.

Bug 5376783:DBMS_SPACE.OBJECT_GROWTH_TREND CALL TAKES A LOT OF DISK READS

MOS 上给的方法是关闭这个自动任务：

execute dbms_scheduler.disable('AUTO_SPACE_ADVISOR_JOB');