ORA-01476 ORA-06512: Statspack.Snap Fails

Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.2This problem can occur on any platform.

Symptoms
-- Problem Statement:While trying to use statspack procedure for taking snapshot , execution fails with ORA - 01476
exec statspack.snap(i_snap_level => 7);
ERROR at line 1:ORA-01476: divisor is equal to zero
ORA-06512: at "PERFSTAT.STATSPACK", line 4956
ORA-06512: at "PERFSTAT.STATSPACK", line 104
ORA-06512: at line 1

Error: ORA 1476Text: divisor is equal to zero------------------------------------------------------------Cause:
An expression attempted to divide by zero.Action: Correct the expression, then retry the operation

You can also observe similar problem if you observe trace files with the following contents are being created in the bdump directory by the m000 background process as discussed inNote 359950.1
*** SQLSTR: total-len=273, dump-len=240,STR={insert into wrh$_sga_target_advice (snap_id, dbid, instance_number,SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS) select:snap_id, :dbid, :instance_number,SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_R}

Cause
After enabling the 1476 errorstack, we found the following statement has failed
ksedmp: internal or fatal errorORA-01476: divisor is equal to zeroCurrent SQL statement for this session:INSERT INTO STATS$SGA_TARGET_ADVICE ( SNAP_ID , DBID , INSTANCE_NUMBER , SGA_SIZE , SGA_SIZE_FACTOR , ESTD_DB_TIME , ESTD_DB_TIME_FACTOR , ESTD_PHYSICAL_READS ) SELECT :B3 , :B2 , :B1 , SGA_SIZE , SGA_SIZE_FACTOR , ESTD_DB_TIME , ESTD_DB_TIME_FACTOR , ESTD_PHYSICAL_READS FROM V$SGA_TARGET_ADVICE

This behavior. is attributed toBug 5008142.To verify the same try to query V$SGA_TARGET_ADVICE

SQL> select * from v$sga_target_advice;select * from v$sga_target_advice*
ERROR at line 1:ORA-01476: divisor is equal to zero
Basically here the db_cache_advice (=ready/off ) advisory is turned off so value for base_estd_phy_reads is zero which causes the failure.

The fix for the bug is to put a decode statement while selecting from the X$ table and use that in view definition for v$sga_target_advice
Solution
-- To implement the solution, please execute the following steps::1)Download thePatch 5008142from metalink and apply it.

OR

Set db_cache_advice on
SQL> alter system set db_cache_advice=on scope=both;
You would need to wait for some time for statistics to be captured.