深入解析Oracle：数据库的初始化
上QQ阅读APP看书，第一时间看更新
上一章目录下一章

1.4 auto_space_advisor_job_proc案例一则

在数据库创建中所提到的“预防性指导报告”就包括空间指导报告，这一定时任务在很多 客户系统中带来了极大的麻烦。需要提醒大家注意的是：当Oracle 数据库中引入一个新特性 时，可能会因为不完善的缺陷而带来麻烦，所以关注新特性，当发现问题时及时处理屏蔽是 DBA 的一个重要职责。

在以下客户的SAP系统中，某个高负载的时段，数据库就遇到了DBMS_SCHEDULER任务的一个Bug，其数据库版本为10.2.0.2。在SQL Ordered By Elapsed Time的采样中，Top 6都是DBMS_SCHEDULER调度的任务，而且耗时显著，如图1-29所示。

图1-29 SQL Ordered By Elapsed Time采样

处在第一位的，是 auto_space_advisor_job_proc过程调用，CPU Time消耗高达 4226秒：

call dbms_space.auto_space_advisor_job_proc ( )

执行花费了大量的时间，3000多秒，进而执行的SQL：

insert into wri$_adv_objspace_trend_data select timepoint, space_usage, space_alloc, quality from table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL, NULL, 'FALSE', :5, 'FALSE'))

也花费了2514秒的时间，这显然是不正常的。在正常情况下，单独跟踪一下SQL*Plus中手工执行这个过程，可以获得这个SQL的执行统计信息，跟踪过程可能如下：

SQL> alter session set events '10046 trace name context forever,level 12';

SQL> call dbms_space.auto_space_advisor_job_proc ( )

SQL> alter session set events '10046 trace name context off';

格式化跟踪文件，获得如图1-30所示的输出。

图1-30 输出信息

可以发现，这个 Insert仍然消耗了389秒的时间，逻辑读 429 297，性能是存在问题的。在Metalink上存在如下一个Bug：

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

这个Bug在DBMS_SPACE.OBJECT_GROWTH_TREND进行空间分析时被触发，根本原因在于内部算法在执行空间检查时，耗费了大量的评估IO成本，导致了大量的IO资源使用。

临时的处理办法是，暂时关闭这个自动任务：

execute dbms_scheduler.disable('AUTO_SPACE_ADVISOR_JOB');

这个Bug在10.2.0.2之后的版本中被修正。

既然Oracle的缺省定时任务可能会带来如此多的问题，我们就很有必要去关注一下系统有哪些缺省的任务，执行情况如何。以下是一个10.2.0.5版本的数据库中一些自动任务的调度设置情况：

SQL> select job_name,state,enabled,last_start_date from dba_scheduler_jobs;

JOB_NAME　STATE　ENABL　LAST_START_DATE

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

AUTO_SPACE_ADVISOR_JOB　SCHEDULED　TRUE　07-AUG-10 06.00.03.792886 AM +08:00

GATHER_STATS_JOB　SCHEDULED　TRUE　07-AUG-10 06.00.03.783957 AM +08:00

FGR$AUTOPURGE_JOB　DISABLED　FALSE

PURGE_LOG　SCHEDULED　TRUE　07-AUG-10 03.00.00.353023 AM PRC

MGMT_STATS_CONFIG_JOB　SCHEDULED　TRUE　01-AUG-10 01.01.01.822354 AM +08:00

MGMT_CONFIG_JOB　SCHEDULED　TRUE　07-AUG-10 06.00.03.767320 AM +08:00

在以上的调度任务中，GATHER_STATS_JOB是Oracle 10g开始引入的自动统计信息收集的任务，该任务缺省的调度是，工作日每晚22:00至凌晨6:00进行分析，周末全天进行分析。在以下输出中，我们可以看到任务无法完成STOP的情况。

SQL> SELECT log_id, job_name, status,

2　TO_CHAR(ACTUAL_START_DATE,'DD-MON-YYYY HH24:MI') start_date,TO_CHAR (log_date, 'DD-MON-YYYY HH24:MI') log_date

3　FROM dba_scheduler_job_run_details

4　WHERE job_name = 'GATHER_STATS_JOB' order by 4;

LOG_ID　JOB_NAME　STATUS　START_DATE　LOG_DATE

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

1480　GATHER_STATS_JOB　SUCCEEDED　02-AUG-2010 22:00　03-AUG-2010 00:58

1561 GATHER_STATS_JOB　STOPPED　03-AUG-2010 22:00　04-AUG-2010 06:00

1640 GATHER_STATS_JO　BSUCCEEDED　04-AUG-2010 22:00　05-AUG-2010 05:36

1680 GATHER_STATS_JOB　SUCCEEDED　05-AUG-2010 22:00　05-AUG-2010 22:25

1741 GATHER_STATS_JOB　SUCCEEDED　06-AUG-2010 22:00　06-AUG-2010 22:27

1800 GATHER_STATS_JOB　SUCCEEDED　07-AUG-2010 06:00　07-AUG-2010 06:02

384 GATHER_STATS_JOB　STOPPED　07-JUL-2010 22:00　08-JUL-2010 06:00

463 GATHER_STATS_JOB　SUCCEEDED　08-JUL-2010 22:00　09-JUL-2010 05:06

503 GATHER_STATS_JOB　SUCCEEDED　09-JUL-2010 22:00　09-JUL-2010 22:05

544 GATHER_STATS_JOB　SUCCEEDED　10-JUL-2010 06:00　10-JUL-2010 06:02

589 GATHER_STATS_JOB　SUCCEEDED　12-JUL-2010 22:00　12-JUL-2010 22:04

597 GATHER_STATS_JOB　SUCCEEDED　13-JUL-2010 22:00　13-JUL-2010 22:03

在一些大型数据库中，这个任务不一定能够有效执行，以下是某用户的数据库环境，输出显示，多日数据库都因为ORA-04031错误未能完成统计信息收集采样：

SQL> SELECT LOG_DATE,RUN_DURATION,JOB_NAME,STATUS,ERROR#

2 FROM DBA_SCHEDULER_JOB_RUN_DETAILS

3 WHERE JOB_NAME='GATHER_STATS_JOB'

4 order by 1 desc;

LOG_DATE　RUN_DURATION　JOB_NAME　STATUS　ERROR#

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

26-MAY-10 10.00.09.290291　PM +08:00 +000 00:00:05　GATHER_STATS_JOB　FAILED　22303

25-MAY-10 10.00.08.973684　PM +08:00 +000 00:00:06　GATHER_STATS_JOB　FAILED　4031

24-MAY-10 10.00.22.977244　PM +08:00 +000 00:00:18　GATHER_STATS_JOB　FAILED　4031

22-MAY-10 06.00.16.950362　AM +08:00 +000 00:00:13　GATHER_STATS_JOB　FAILED　4031

21-MAY-10 10.00.49.653788　PM +08:00 +000 00:00:47　GATHER_STATS_JOB　FAILED　4031

20-MAY-10 10.00.14.028432　PM +08:00 +000 00:00:11　GATHER_STATS_JOB　FAILED　4031

19-MAY-10 10.00.20.828607　PM +08:00 +000 00:00:18　GATHER_STATS_JOB　FAILED　4031

19-MAY-10 05.54.27.871444　AM +08:00 +000 07:54:25　GATHER_STATS_JOB　SUCCEEDED　0

18-MAY-10 05.36.01.494920　AM +08:00 +000 07:35:59　GATHER_STATS_JOB　SUCCEEDED　0

15-MAY-10 07.06.05.793257　AM +08:00 +000 01:06:01　GATHER_STATS_JOB　SUCCEEDED　0

15-MAY-10 03.56.50.898303　AM +08:00 +000 05:56:48　GATHER_STATS_JOB　SUCCEEDED　0

在 GATHER_STATS_JOB 任务不能够有效地执行时，我们必须及时地介入进行手工处理（如自定义任务，跳过那些不重要的大表，或者选择在业务峰值以外的时间，手工执行统计信息收集），不及时的统计信息可能使数据库产生错误的执行计划。

正常的AUTO_SPACE_ADVISOR_JOB调度可能应该具有类似以下输出的执行结果：

SQL> SELECT log_id, job_name, status,TO_CHAR(ACTUAL_START_DATE,'DD-MON-YYYY HH24:MI') start_date,

2　TO_CHAR (log_date, 'DD-MON-YYYY HH24:MI') log_date

3　FROM dba_scheduler_job_run_details

4　WHERE job_name = 'AUTO_SPACE_ADVISOR_JOB' order by 4;

LOG_ID　JOB_NAMESTATUS　START_DATE　LOG_DATE

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

1460　AUTO_SPACE_ADVISOR_JOB SUCCEEDED　02-AUG-2010 22:00　02-AUG-2010 22:16

1520　AUTO_SPACE_ADVISOR_JOB SUCCEEDED　03-AUG-2010 22:00　03-AUG-2010 23:18

1600　AUTO_SPACE_ADVISOR_JOB　SUCCEEDED　04-AUG-2010 22:00　04-AUG-2010 22:19

1681　AUTO_SPACE_ADVISOR_JOB　SUCCEEDED　05-AUG-2010 22:00　05-AUG-2010 22:28

1740　AUTO_SPACE_ADVISOR_JOB　SUCCEEDED　06-AUG-2010 22:00　06-AUG-2010 22:17

对于生产环境，需要详细了解这些定时任务，并将检查工作加入DBA的工作任务列表。

上一章目录下一章