Re: Help with sql query speed. Explain plan included

On Mar 18, 1:56 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> 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.

I can understand creating a composite index on the QUESTIONNUMBER and ANSWER columns but I fail to see the benefit of having an index on the RESIDENTID column as the pertinent portion of the WHERE clause reduces to, essentially:

 where t1.residentid!=1486674

as proven by the posted query plan. This returns every row in T1 except 1 and Oracle should be 'smart' enough to not use the index for that type of condition. Perhaps rewriting the WHERE clause might also help:

   where (t0.questionnumber=tx.questionnumber

              and t0.answer=tx.answer)
   and t0.residentid=1486674
   and t0.residentid!=tx.residentid

although with 10g the CBO may already have done that.

The composite index may help the situation by only returning those RESIDENTID values where the answers match those of the selected record, thus reducing the size of the filtering operation. And an index won't speed filtering.

David Fitzjarrell Received on Sun Mar 18 2007 - 19:23:12 CDT