stale statistics in KOTTD$ table

From: News <Contact_404_at_hotmail.com>
Date: 21 Dec 2005 08:11:01 -0800
Message-ID: <1135181461.534392.255350@z14g2000cwz.googlegroups.com>

10.2 Upgrade Information Utility show this warning :

WARNING: --> Database contains stale optimizer statistics.

.... Refer to the 10g Upgrade Guide for instructions to update
.... statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
....   SYS

The problem persists after running the script given in upgrade Guide

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14238/statistics.htm#CHDHFFAH

which contains dbms_stats.gather_schema_stats statements ..

I have run this query extracted from Upgrade Information script:

SELECT u.name, o.NAME

           FROM sys.tab$ t, sys.obj$ o, sys.user$ u
           WHERE
                 -- u.name = 'SYS' AND
                 t.obj# = o.obj# and o.owner# = u.user# AND
                 bitand(t.flags,16) != 16 AND -- not analyzed
                 -- we don't collect stats for the following types.
                 bitand(t.property,512) != 512 AND  -- not an iot
overflow
                 bitand(t.flags,536870912) != 536870912 AND


                                                    -- not an iot

mapping table
                 bitand(t.property,2147483648) != 2147483648 AND


                                                    -- not external

table
                 bitand(o.flags, 128) != 128 AND    -- not in recycle
bin
                 NOT (bitand(o.flags, 16) = 16 AND o.name like 'DR$%')
AND


                                                    -- no CTX

                 bitand(t.property,4194304) != 4194304 AND


                                                    -- no global temp

tables
                 bitand(t.property,8388608) != 8388608 AND


                                                    -- no session temp

tables
                 NOT EXISTS  -- not an mv log
                    (SELECT * FROM sys.mlog$
                     WHERE (mowner = u.name AND log = o.name) OR
                           (mowner = u.name AND temp_log = o.name)) AND

ROWNUM <= 1;

the problem concerns SYS.KOTTD$ whose columns are hidden. I think this is a corrupt dictionary data problem related to previous upgrade ? how to fix the problem ? Received on Wed Dec 21 2005 - 10:11:01 CST