Re: Tables scanning ??

In article <DE8zv8.LvG_at_freenet.carleton.ca> ah513_at_FreeNet.Carleton.CA (Doug Harris) writes:
>Sharon Q (squarrington_at_mdshealth.com) writes:
>> I have third party layered application running on Oracle 7.0.16 on DEC
>> OSF UNIX. The performance is not very good and we believe that the
>> system is doing table scanning. How can we verify that this is occuring
>> without access to the source code? How can we tell what tables are being
>> scanned and how often? We have seen very high disk I/O rates and believe
>> the software is the problem but are getting nowhere with the software developer.
>
> Enable the SQL trace option by setting init.ora parameter SQL_TRACE=TRUE.
>Then run the app through it's paces. This will produce trace files in
>the directory pointed to by init.ora paramater USER_DUMP_DEST. You can
>then run the tkprof utility on these file with the EXPLAIN option and
>get all of the information you are looking for.
>
> This is all documented in the Application Developers Guide.
>
>

FWIW, I saw a situation like this, the explain plan showed an extra WHERE clause. Turned out to be due to the 7.1 cost based optimizer bug. The lesson is, it ain't necessarily the code (although the code usually has the most room for improvement...).

You might try writing some of your own code to get some benchmarks as to what to expect when indices are or aren't used. You might also get a performance consultant or one of the third party DBA monitoring tools to be sure you aren't thrashing rollbacks or something.

-- 
Joel Garry               joelga_at_rossinc.com               Compuserve 70661,1534
These are my opinions, not necessarily those of Ross Systems, Inc.   <> <>
%DCL-W-SOFTONEDGEDONTPUSH, Software On Edge - Don't Push.            \ V /
panic: ifree: freeing free inodes...                                   O