Re: Source of expensive SQL

Hi,

select      i.obj#, i.flags, u.name, o.name

from        sys.obj$ o, sys.user$ u, sys.ind$ i

where       (bitand(i.flags, 256) = 256 or bitand(i.flags, 512) = 512) 

and         (not((i.type# = 9) and bitand(i.flags,8) = 8)) 

and         o.obj#=i.obj# 

and         o.owner# = u.user#



That might be SMON trying to search for some work to do regularly, but hitting 
a bad execution plan when doing dictionary queries. Unfortunately the catalog 
views don't have ind$.flags bits representing 256 and 512 documented, so I 
don't know what is it searching for. Maybe these have something to do with LOB 
indexes and undo retention, but not sure.

Check if any of those three tables (or their corresponding indexes) are 
analyzed - 9.2.0.4 isn't too good version to have data dictionary analyzed.

Tanel.

Other related posts: