Re: Help with sql query speed. Explain plan included

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

The OP is accessing the same table twice in the query with two aliases: "test1 t0,test1 tx". The t0 alias is being accessed by the RESIDENTID column: "t0.residentid=1486674". To access those rows, and index on just the RESIDENTID should be helpful. I think that we both agree that such an index will not help this portion of the WHERE clause "t1.residentid!=1486674" The one index on the table (IDXE) is a composite index covering all three columns. What I was suggesting is to test performance with this one three column index split into two indexes. The two column composite index will hopefully be able to satisfy the "t0.questionnumber=tx.questionnumber and t0.answer=tx.answer" portion of the query without requiring a full tablescan of table TEST1.

Thinking about it a bit more, I think that I understand what you are saying - the number of blocks in the two column index may be the same or greater than that of the three column table. His query would have to check ((3,000,000 - 80) / (3,000,000) * 100)% of the rows in the table, and that looks to be quite a bit greater than 15% to 25%. It could be interesting to see if Oracle could optimize this situation... if each question has an even distribution between the 100 possible answers.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sun Mar 18 2007 - 20:36:53 CDT