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

« 必须转载:无条件地执行是最好的消极怠工 | 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 IdSQL ModuleSQL 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资源使用:

在后台跟踪里可以看到这个步骤的资源消耗:
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-




历史上的今天...
    >> 2016-12-11文章:
    >> 2012-12-11文章:
    >> 2011-12-11文章:
    >> 2007-12-11文章:
           CRS-0184错误与CRS初始化
    >> 2005-12-11文章:
           The Waiting Life
           How Oracle Store Number internal?

无觅

By eygle on 2009-12-11 08:05 | Comments (3) | Case | Oracle12c/11g | 2472 |

3 Comments

请问关掉这个job对生产有什么影响吗?

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 ...


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