Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Slow SQL, too many logical reads ?

Re: Slow SQL, too many logical reads ?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 7 May 2007 16:21:26 +0100
Message-ID: <EKWdnWqb_cXE2aLbnZ2dnUVZ8smonZ2d@bt.com>

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 Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Peter" <peternybor_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
>
Received on Mon May 07 2007 - 10:21:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US