Getting ORA-01476 during execution of DBMS_STATS.GATHER_SCHEMA_STATS

联系:手机(+86 13429648788) QQ(107644445)QQ咨询惜分飞

标题:Getting ORA-01476 during execution of DBMS_STATS.GATHER_SCHEMA_STATS

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

alert日志

Fri Jan 27 22:00:09 2012
GATHER_STATS_JOB encountered errors.  Check the trace file.
Fri Jan 27 22:00:09 2012
Errors in file /oracle10/admin/ocs/bdump/ocs1_j001_29138.trc:
ORA-01476: divisor is equal to zero

trace内容

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /oracle10/app/product/db/10.2.0
System name:    HP-UX
Node name:      ocsdb1
Release:        B.11.23
Version:        U
Machine:        ia64
Instance name: ocs1
Redo thread mounted by this instance: 1
Oracle process number: 60
Unix process pid: 29138, image: oracle@ocsdb1 (J001)

*** ACTION NAME:(GATHER_STATS_JOB) 2012-01-27 22:00:09.308
*** MODULE NAME:(DBMS_SCHEDULER) 2012-01-27 22:00:09.308
*** SERVICE NAME:(SYS$USERS) 2012-01-27 22:00:09.308
*** SESSION ID:(988.31342) 2012-01-27 22:00:09.307
ORA-01476: divisor is equal to zero
*** 2012-01-27 22:00:09.417
GATHER_STATS_JOB: GATHER_TABLE_STATS('"OCS_SM"','"HLP_SMS_SEND"','""', ...)
ORA-01476: divisor is equal to zero

错误原因
oracle unpublished Bug 5645718

解决方法
1.Setting event 38041 at level 16

sql> connect / as sysdba
sql> alter system set events '38041 trace name context forever, level 16';

2.Patch 6319761

此条目发表在 ORA-xxxxx 分类目录,贴了 标签。将固定链接加入收藏夹。

Getting ORA-01476 during execution of DBMS_STATS.GATHER_SCHEMA_STATS》有 2 条评论

  1. 惜分飞 说:

    Getting ORA-01476 during execution of DBMS_STATS.GATHER_SCHEMA_STATS [ID 464440.1]

    Applies to:
    
    Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 10.2.0.4 - Release: 9.2 to 10.2
    Information in this document applies to any platform.
    Symptoms
    
    -- Problem Statement:
    Intermittent ORA-01476: divisor is equal to zero when running gather schema stats.
    
    *
    ERROR at line 1:
    ORA-01476: divisor is equal to zero
    ORA-06512: at "SYS.DBMS_STATS", line 13313
    ORA-06512: at "SYS.DBMS_STATS", line 13659
    ORA-06512: at "SYS.DBMS_STATS", line 13737
    ORA-06512: at "SYS.DBMS_STATS", line 13696
    ORA-06512: at line 1
    
    Analyse using dbms_stats.gather_table_stats gives errors for a table. But, it generally runs fine. The error is intermittent but still occurs occassionally.
    
    
    
    Cause
    
    Bug 5645718 (internal bug) 10.2.0.2 RDBMS 10.2.0.2 QRY OPTIMIZER PRODID-5 PORTID-197 ORA-1476 
    Abstract: ORA-1476 DBMS_STATS.GATHER_TABLE_STATS
    --> Fixed-Releases: 10.2.0.4,  11.1.0.6
    
    Bug 6319761 10.2.0.3 RDBMS 10.2.0.3 QRY OPTIMIZER PRODID-5 PORTID-23 ORA-1476
    Abstract: DBMS_STATS.GATHER_SCHEMA_STATS IS FAILING WITH ORA-1476
    --> Fixed-Release: 11.1.0.7
    
    Both bugs are related to scaling of "number of distinct values" with popular values while gathering stats. 
    Solution
    
    Solution is to  disable popular values adjustment for NDV scaling.
    
    -- To implement the solution, please execute the following steps::
    sql> connect / as sysdba
    sql> alter system set events '38041 trace name context forever, level 16';
    

      [引用]  [回复]

  2. 惜分飞 说:

    DBMS_STATS fails with ORA-01476 with patch 5645718 applied [ID 456857.1]

    Applies to:
    
    Oracle Server - Enterprise Edition - Version: 9.2.0.8 to 10.2.0.4 - Release: 9.2 to 10.2
    Information in this document applies to any platform.
    Symptoms
    
    1)While gathering stats using
    
    DBMS_STAT.GATHER_DATABASE_STATS we encouter the following error 
    
    begin 
    * 
    ERROR at line 1: 
    ORA-01476: divisor is equal to zero 
    ORA-06512: at "SYS.DBMS_STATS", line 13313 
    ORA-06512: at "SYS.DBMS_STATS", line 13659 
    ORA-06512: at "SYS.DBMS_STATS", line 13803 
    ORA-06512: at "SYS.DBMS_STATS", line 13767 
    ORA-06512: at line 3 
    
    
    
    2)Patch 5645718  is already applied.
    
    3)Setting event 38041 at level 24 resolves the problem
    
     
    
    Cause
    
    Bug 6319761
    
    The bug is fixed in version 11g and backport is feasible.
    
     
    
    
    .
    
    Solution
    
    Download and apply  Patch 6319761 if available for  your platform
    
    and release from metalink.
    

      [引用]  [回复]

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>