Sunday, 6 January 2013

ORA-01476 divisor is equal to zero , DBMS_STATS.GATHER_TABLE_STATS


exec dbms_stats.gather_schema_stats('<username>',CASCADE => TRUE);

to gather index stats :-

SQL>  BEGIN
         dbms_stats.gather_schema_stats(
         ownname=>'<username>',
          METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE SKEWONLY',
          CASCADE=>TRUE,
           ESTIMATE_PERCENT=>100);
           END;
     /

 exec dbms_stats.gather_schema_stats('<username>',CASCADE=>TRUE,ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE);

===================================================================
Login as schema user and follow below steps one by one :-



step 1:-
  exec DBMS_STATS.SET_TABLE_PREFS('<username>','<tablename>','INCREMENTAL','TRUE');

Step 2:-
Check if the above command has set the preferences correcctly.

  select dbms_stats.get_prefs('INCREMENTAL', tabname=>'<tablename>') from dual;

step3:-

exec dbms_stats.gather_table_stats(ownname=>'<username>',tabname=>'<tablename>',estimate_percent=>dbms_stats.auto_sample_size,cascade=>TRUE,DEGREE=>dbms_stats.auto_degree);

=======================================================================
Or Use this :-
BEGIN
  DBMS_STATS.gather_table_stats(
    '<username>',
    '<tablename>',
    method_opt => 'for all columns size auto');
END;
/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

step to  gather stats for partition table:-

Step 1 :-
DECLARE
LV_SQL VARCHAR2(1000);
CURSOR C1 IS
SELECT TABLE_NAME T , PARTITION_NAME P , LAST_ANALYZED L  FROM USER_TAB_PARTITIONS WHERE LAST_ANALYZED < SYSDATE - 2 AND
TABLE_NAME = '<partition_table_name>';

BEGIN
  FOR I IN C1 LOOP
LV_SQL:= 'BEGIN  ';
   LV_SQL:= LV_SQL ||'dbms_stats.gather_table_stats (''<username>'',''<partition_table_name>'',partname=>'||':1'||',granularity=>''partition'') ;' ;
   LV_SQL:= LV_SQL ||' END ;';
EXECUTE IMMEDIATE LV_SQL USING  I.P ;
END LOOP;
END;
/

Or use step 2:-
  exec DBMS_STATS.SET_TABLE_PREFS('<username>','<partition_table_name>','INCREMENTAL','true');

next step of 2:-
 exec dbms_stats.gather_table_stats('<username>','<parttion_table_name>',CASCADE => TRUE ,ESTIMATE_PERCENT => 1);


OR You can use :-

BEGIN
dbms_stats.gather_table_stats ('<username>','<partition_table_name>',granularity=>'SUBPARTITION') ;
END ;

=============================================================================================
To remove the following error :-

ORA-01476: divisor is equal to zero , DBMS_STATS.GATHER_TABLE_STATS

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('<username>','<patition_table_name>', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree=>8, cascade=>true , granularity=>'ALL')
END;

*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "SYS.DBMS_STATS", line 10502
ORA-06512: at "SYS.DBMS_STATS", line 10516
ORA-06512: at line 1

It is a bug. following steps solved the problem as suggested by metalink.

SQL> ALTER SESSION SET EVENTS '38041 TRACE NAME CONTEXT FOREVER, LEVEL 24';
Session altered.
Elapsed: 00:00:00.00
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('OPS$MTNMUPB','CHARGE', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree=>8,casca
de=>true , granularity=>'ALL')
PL/SQL procedure successfully completed.
Elapsed: 05:46:29.55
SQL> SPOOL OFF



No comments:

Post a Comment