« 必须转载:无条件地执行是最好的消极怠工 | Blog首页 | CBO中 SMON 进程与 col_usage$ 的维护 »
SAP系统中的Bug 5376783 dbms_space高物理读
在客户的SAP系统中，一个非常高负载的情况下，数据库遇到了DBMS_SCHEDULER任务的一个Bug，数据库版本为10.2.0.2。在以下的Top SQL中，系统调用：
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'))
也花费了1600多秒的时间，这显然是不正常的。
SQL ordered by Elapsed Time
- Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
- % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
| Elap per Exec (s) | % Total DB Time | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|
| 83.03 | 6mcpb06rctk0x | DBMS_SCHEDULER | call dbms_space.auto_space_adv... | |
| 0.08 | 59.19 | 8szmwam7fysa3 | DBMS_SCHEDULER | insert into wri$_adv_objspace_... |
| 0.03 | 22.55 | gnux0zb3sxduk | DBMS_SCHEDULER | SELECT TIMEPOINT, DELTA_SPACE... |
| 0.03 | 18.29 | bxrry8kxks9hz | DBMS_SCHEDULER | select sn.startup_time timepoi... |
| 0.02 | 17.94 | cfxfxjk00tq0m | DBMS_SCHEDULER | SELECT TASKS.TASK_ID, REC.ID ... |
| 0.06 | 1.76 | a2quvhw5mqd8m | DBMS_SCHEDULER | select sn.startup_time timepoi... |
在Metalink上存在如下一个Bug：
Bug 5376783: DBMS_SPACE.OBJECT_GROWTH_TREND CALL TAKES A LOT OF DISK READS
这个Bug在DBMS_SPACE.OBJECT_GROWTH_TREND进行空间分析时被触发，根本原因在于内部算法在执行空间检查时，耗费了大量的评估IO成本，导致了大量的IO资源使用：
这个Bug在DBMS_SPACE.OBJECT_GROWTH_TREND进行空间分析时被触发，根本原因在于内部算法在执行空间检查时，耗费了大量的评估IO成本，导致了大量的IO资源使用：在后台跟踪里可以看到这个步骤的资源消耗：
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'))
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 159 47.75 389.32 1881632 429297 383 5724
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 161 47.75 389.32 1881632 429297 383 5724
临时的处理办法是，暂时关闭这个自动任务：
execute dbms_scheduler.disable('sys.auto_space_advisor_job');
这个Bug在10.2.0.2之后的版本中被修正。记录一下供参考！
-The End-
历史上的今天...
>> 2007-12-11文章:
>> 2005-12-11文章:
------
这篇 【SAP系统中的Bug 5376783 dbms_space高物理读】来自 eygle.com | CSDN网摘| del.icio.us|Google订阅 | 鲜果订阅 | 抓虾订阅
By eygle on 2009-12-11 08:05 | Comments (3) | Posted to Case | Oracle10g/11g | Edit |Pageviews:
留言 (3)
请问关掉这个job对生产有什么影响吗？
Posted by: mulanboy at December 29, 2009 7:03 PM
没有影响，这是空间建议的功能。
Anyway,如果你不知道这个功能，显然这个特性对你没有起到作用。
Posted by: eygle 
at December 29, 2009 7:16 PM
SAP 其实有个note 讲这个事情的 SAP note 1001925
Symptom
You notice an increase in the number of disk accesses in the system.
Transaction ST04/ST04N SQL shows statements that have a very high number
of disk accesses for a relatively small number of executed operations.
The following SQL statements have been observed to date:
...call dbms_space.auto_space_advisor_job_proc()
...insert into wri$_adv_objspace_trend_data ...
Posted by: Larry at February 24, 2010 11:46 PM