Re: Slow SQL, too many logical reads ?

On May 7, 5:21 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> Your description of the activity sounds correct -
> I think someone else actually made the same point
> in an earlier post.
>
> If you switched to a filter operation, it wouldn't improve.
> In fact in earlier versions of Oracle 9.2, possibly even 10.1,
> the check of the third index column with its in-list would
> not have been an access predicate, it would have been
> delayed to a filter predicate - with the result that the 5,906
> index range scans would have used the entire range dictated
> by the first two column, and then filtered on the last two,
> probably increasing the work done in the index.
>
> You might try reversing the third and fourth columns of
> the index - provided this doesn't cause more problems in
> other queries. Depending on the data sizes and distributions,
> this might reduce your logical I/O by pulling the data for the
> 11 different values into a smaller number of index leaf blocks,
> possibly allowing Oracle to do more work with pinned blocks
> than it is currently doing.
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> "Peter" <peterny..._at_gmail.com> wrote in message
>
> news:1178455067.253571.127520_at_o5g2000hsb.googlegroups.com...
>
> > On May 4, 8:07 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk>
> > wrote:
> >> "Peter" <peterny..._at_gmail.com> wrote in message
>
> >>news:1178030834.571357.275300_at_e65g2000hsc.googlegroups.com...
> >> 8 - access("IA"."IA_IACT2"="AA"."AA__ICODE" AND
> >> "IA"."IA_REASON"='#EPM#Int2Int'
> >> AND
> >> ("IA"."IA_SEQUE"=0 OR "IA"."IA_SEQUE"=1 OR
> >> "IA"."IA_SEQUE"=2 OR "IA"."IA_SEQUE"=3
> >> OR
> >> "IA"."IA_SEQUE"=4 OR "IA"."IA_SEQUE"=5 OR
> >> "IA"."IA_SEQUE"=6 OR "IA"."IA_SEQUE"=9
> >> OR
> >> "IA"."IA_SEQUE"=10 OR "IA"."IA_SEQUE"=11 OR
> >> "IA"."IA_SEQUE"=17)
> >> AND
>
> >> "IA"."IA__DELETED"='N')
>
> >> Peter,
>
> >> Can you tell us the definition of the four-column
> >> index - the column order may have some bearing
> >> on the resource usage.
>
> >> --
> >> Regards
>
> >> Jonathan Lewishttp://jonathanlewis.wordpress.com
>
> >> Author: Cost Based Oracle:
> >> Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> >> The Co-operative Oracle Users'
> >> FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> > Jonathan, the ordering of the index was given above:
>
> > select * from user_ind_columns
> > where index_name = 'ACURE_A_INDACT_1';
>
> > INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION
> > COLUMN_LENGTH
> > CHAR_LENGTH DESCEND
> > ACURE_A_INDACT_1 A_INDACT IA_IACT2 1
> > 12 12 ASC
> > ACURE_A_INDACT_1 A_INDACT IA_REASON 2
> > 12 12 ASC
> > ACURE_A_INDACT_1 A_INDACT IA_SEQUE 3
> > 22 0 ASC
> > ACURE_A_INDACT_1 A_INDACT IA__DELETED 4
> > 1 1 ASC
>
> > I am answering this from home, without access to the database etc.
> > right now.
> > Anyway, I still lack a real understanding of how my original query is
> > processed, and if/how it could possibly be improved.
>
> > Is this what goes on:
>
> > - for each of the 5906 rows in sub-select:
> > -- for each of the 11 elements in the inlist:
> > --- retrieve the corresponding ROWIDs from the index ?
>
> > Assuming 3 reads to get to an index leaf block, if the the third step
> > would always visit only one index leaf block, we get:
> > 5906 x 11 x 3 = 194898 logical reads on the index,
> > close to the actual 195945 reads reported from tkprof, the difference
> > being that it few times need to visit more than one index leaf block ?
>
> > Could the second step (inlist) be replaced with something more
> > efficient (filter?) ?
> > What would be the most efficient plan ?
>
> > -- Peter

Thanks, I will probably try your index tip (reversing the third and fourth columns) one of these days (... but I'm not the only one using the database, and the table is rather big, 48M rows).

Is it possible to force (hint or something) the inlist to be delayed to a filter predicate ? As you say, it might make things worse, but I would like to try anyway, as the first column, IA_IACT2, is very selective (20M distinct values), while the third (inlist) column, IA_SEQUE, is very un-selective (only 13 distinct values).

Also, see my May 1, 7:15 pm posting, where I tried removing the predicate
--AND IA.IA_SEQUE IN ( '17', '2', '4', '9', '11', '6', '1', '3', '10', '5', '0' )
with big performance gains.

Regards,

• Peter