Re: Help with sql query speed. Explain plan included

On Mar 18, 1:58 pm, JL19..._at_HOTMAIL.CO.UK wrote:
> If I recall correctly, Cary Millsap wrote something about Obsessive
> Tuning Disorder and I think this is what you are talking about too. 70
> seconds was a show stopper - 10-20 seconds is far better. At least no
> one suggested we restructure our data. We were prepared to do it but
> wanted to canvas opinion on tuning rather than software rewrites to
> start with.
>
> Thank you again.
> John

You might be thinking of Gaja Krishna Vaidyanatha (author of "Oracle Performance Tuning 101"), rather than Cary Millsap. Compulsive Tuning Disorder is a serious problem. However, you have not yet reached that stage with this problem.

I suspect that if you can eliminate the full tablescan that is requiring the processing of 278MB (system has become IO bound), you will likely find that the query executes in less than one second. I suspect that with a SORT_AREA_SIZE of 31MB and a HASH_AREA_SIZE of 293MB, it will not take but a couple sessions connecting to the database and executing SQL statements before the server experiences severe memory problems. My second post (and possibly the first as well) suggested two indexes: one index on the RESIDENTID column and a second, composite index on the QUESTIONNUMBER and ANSWER columns. Since this is a test environment, try creating indexes on those columns and then use DBMS_STATS.ANALYZE_TABLE_STATS with the CASCADE option set to TRUE to gather statistics.

TKPROF likely does not know what to do with a 10104 trace.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sun Mar 18 2007 - 13:56:28 CDT