Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> 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. Received on Fri Jan 06 2006 - 04:35:29 CST
![]() |
![]() |