SQL Tuning problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: SQL Tuning problem

  1. #1
    Join Date
    Jan 2003
    Location
    Pune,India
    Posts
    4

    SQL Tuning problem

    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

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width