Re: stale statistics in KOTTD$ table

News wrote:
> 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 ?

Run DBMS_STATS.GATHER_DATABASE_STATS.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)