SQL Tuning problem

[sumit_can]

Hi All,

I am finding problem with one of the sql query.
Database is oracle 8.1.7.
Can anyone suggests about how I tune the query?
It is taking around 10 million hits (I/O).


Please take a look at the query :-

select
u.name OWNER, o.name TABLE_NAME, substr(c.name,1,30) COLUMN_NAME,
lo.name LOB_DATA_NAME, io.name LOB_INDEX_NAME, ts.name TABLESPACE_NAME,
l.chunk * ts.blocksize CHUNK, l.pctversion$ PCT_VERSION,
decode(bitand(l.flags, 2), 2, 'N', 'Y') LOGGING,
decode(l.flags, 0, 'Y', 'N') CACHE,
decode(l.property, 0, 'N', 'Y') IN_ROW,
decode(bitand(l.property, 32), 32, 'YES', 'NO') PARTITIONED,
decode(s.iniexts, '2147483645', null, '2147483647',null, s.iniexts * ts.blocksize) INITIAL_EXTENT,
decode(s.extsize, '2147483645', null, '2147483647', null, s.extsize * ts.blocksize) NEXT_EXTENT,
decode(s.minexts, '2147483645', null, '2147483647', null, s.minexts) MIN_EXTENTS,
decode(s.maxexts, '2147483645', null, '2147483647', null, s.maxexts) MAX_EXTENTS,
decode(s.extpct, '2147483645', null, '2147483647', null, s.extpct) PCT_INCREASE,
decode(s.lists, '2147483645', null, '2147483647', null, 0, 1, s.lists) FREELISTS,
decode(s.groups, '2147483645', null, '2147483647', null, 0, 1, s.groups) FREELIST_GROUPS
from
sys.obj$ o, sys.col$ c, sys.seg$ s,sys.lob$ l,
sys.obj$ lo, sys.obj$ io, sys.user$ u, sys.ts$ ts
where
o.owner# = u.user# and
u.name like 'WARREN' ESCAPE '\' and
o.name like '%' ESCAPE '\' and
c.type# in(112,113) and
o.obj# = c.obj# and
o.obj# = l.obj# and
c.intcol# = l.intcol# and
l.lobj# = lo.obj# and
l.ind# = io.obj# and
l.ts# = ts.ts# and
l.file# = s.file# and
l.block# = s.block#
order by OWNER, TABLE_NAME, COLUMN_NAME.


Regards,

Sumit

[slimdave]

This is the dba_tables view, I believe, so you can't change the query itself. Check that you do not have statistics on any tables in the SYS schema -- use of the Cost-based Optimizer could produce performance problems on this type of view.