eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« DBA手记:Cache-Low RBA与On-Disk RBA的恢复 | Blog首页 | 我的写作和博文视点及51CTO的评选 »

DBA手记:从10g到12c,JOB的问题auto_space_advisor_job_proc
modb.pro

上一则案例提到的情况,在另外一个客户的系统中,再次遇到了类似的问题。在客户的SAP系统中,某个高负载的时段,数据库遇到了DBMS_SCHEDULER任务的一个Bug,其数据库版本为10.2.0.2

SQL Ordered By Elapsed Time的采样中,Top 6都是DBMS_SCHEDULER调度的任务,而且耗时显著:

dbanb211.png

处在第一位的,是和上一则案例相同的auto_space_advisor_job_procCPU 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的执行统计信息:

JobAutoDiag.png

注意到,这个Insert仍然消耗了389秒的时间,逻辑读429297,性能是存在问题的。在Metalink上存在如下一个Bug
Bug 5376783: DBMS_SPACE.OBJECT_GROWTH_TREND CALL TAKES A LOT OF DISK READS

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

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

execute dbms_scheduler.disable('AUTO_SPACE_ADVISOR_JOB');

这个Bug10.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_JOBOracle Database 10g开始引入的自动统计信息收集的任务,该任务缺省的调度是,工作日每晚2200至凌晨600进行分析,周末全天进行分析。在以下输出中,我们可以看到任务无法完成,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_JOB SUCCEEDED 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_NAME STATUS 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

在 Oracle Database 11g 和 12c 中,系统的自动任务通过 AUTOTASK 任务实现:

SQL> select * from v$version;

BANNER CON_ID

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

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0

PL/SQL Release 12.2.0.1.0 - Production 0

CORE 12.2.0.1.0 Production 0

TNS for Linux: Version 12.2.0.1.0 - Production 0

NLSRTL Version 12.2.0.1.0 - Production 0

SQL> SELECT client_name,status FROM dba_autotask_client;

CLIENT_NAME STATUS

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

sql tuning advisor ENABLED

auto optimizer stats collection ENABLED

auto space advisor ENABLED

禁用和启用需要通过 dbms_auto_task_admin 进行禁用和启用:

SQL> BEGIN

2 dbms_auto_task_admin.disable(

3 client_name => 'auto space advisor',

4 operation => NULL,

5 window_name => NULL);

6 END;

7 /

PL/SQL procedure successfully completed.

SQL> SELECT client_name,status FROM dba_autotask_client;

CLIENT_NAME STATUS

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

sql tuning advisor ENABLED

auto optimizer stats collection ENABLED

auto space advisor DISABLED

以上供参考。


历史上的今天...
    >> 2008-02-25文章:
           Oracle10g 10.2.0.4 PatchSet发布
    >> 2006-02-25文章:
           逻辑严谨与数据安全
    >> 2005-02-25文章:

By eygle on 2011-02-25 11:28 | Comments (0) | Case | 2735 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com