Re: Slow SQL, too many logical reads ?

From: Peter <peternybor_at_gmail.com>
Date: 7 May 2007 10:47:35 -0700
Message-ID: <1178560055.313111.166210@u30g2000hsc.googlegroups.com>

On May 7, 6:10 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "Peter" <peterny..._at_gmail.com> wrote in message
>
> news:1178554012.265020.88360_at_y80g2000hsf.googlegroups.com...
>
>
>
> > On May 7, 5:21 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk>
>
> > 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
>
> Try changing the predicate to
> AND IA.IA_SEQUE||'' IN ( '17', '2', '4', '9', '11', '6', '1', '3','10',
> '5', '0' )
>
> In your version of Oracle this should still be resolved
> in the index (earlier versions would postpone it to the
> table visit) but as a FILTER rather than an ACCESS
> predicate
>
> --
> 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

Oh yes, that certainly made a difference:

---

SELECT

  IA.IA__ICODE,IA.IA__TIMESTAMP,IA.IA__STVDATE,IA.IA__ENVDATE,
  IA.IA_IACT1,IA.IA_IACT2,IA.IA_NOTES,IA.IA_EXECU,IA.IA_MEANI,
  IA.IA_SEQUE,IA.IA_REASON

FROM
  A_INDACT IA
WHERE
  IA.IA_REASON=:"SYS_B_00"
  AND IA.IA_SEQUE || :"SYS_B_01" IN
( :"SYS_B_02", :"SYS_B_03", :"SYS_B_04", :"SYS_B_05", :"SYS_B_06", :"SYS_B_07", :"SYS_B_08", :"SYS_B_09", :"SYS_B_10", :   AND IA.IA_IACT2 IN (
    SELECT AA.AA__ICODE
    FROM A_ACTA AA
    WHERE

      AA.AA_IPERM=:"SYS_B_13"
      AND AA.AA_IRSER IN
( :"SYS_B_14", :"SYS_B_15", :"SYS_B_16", :"SYS_B_17" )
      AND AA.AA__DELETED=:"SYS_B_18"

    )
  AND IA.IA__DELETED=:"SYS_B_19"

call     count       cpu    elapsed       disk      query
current        rows

------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        1      0.00       0.00          0          0
0           0
Execute      1      0.01       0.00          0          0
0           0
Fetch      376      0.34       0.38          1      18911
0        5623

------- ------ -------- ---------- ---------- ---------- ----------

---

total      378      0.35       0.38          1      18911
0        5623

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 62 (EPM)

Rows Row Source Operation

-------  ---------------------------------------------------

   5623 TABLE ACCESS BY INDEX ROWID OBJ#(52752) (cr=18911 r=1 w=0 time=336463 us)
  11560 NESTED LOOPS (cr=13773 r=0 w=0 time=200276 us)    5871 SORT UNIQUE (cr=1312 r=0 w=0 time=53191 us)

   5871     INLIST ITERATOR  (cr=1312 r=0 w=0 time=30377 us)
   5871      TABLE ACCESS BY INDEX ROWID OBJ#(52679) (cr=1312 r=0 w=0
time=28464 us)
   5871       INDEX RANGE SCAN OBJ#(77622) (cr=45 r=0 w=0 time=6794 us)

(object id 77622)

   5688 INDEX RANGE SCAN OBJ#(77480) (cr=12461 r=0 w=0 time=121143 us)(object id 77480)

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE

   5623 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'A_INDACT'   11560 NESTED LOOPS

   5871     SORT (UNIQUE)
   5871      INLIST ITERATOR
   5871       TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                  'A_ACTA'
   5871        INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'MORTEN'
                   (NON-UNIQUE)
   5688     INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'ACURE_A_INDACT_1'
                (NON-UNIQUE)

===============================

... now only 18911 reads total (12461 on the index) !!

Thanks for explaining the version differences regarding ACCESS/FILTER operations.
When I posted the original question, I simply did not understand why the inlist was not processed as a FILTER ... and I'm still not sure *why* Oracle makes the "wrong" choice in this case (we do have up-to- date statistics in place) ? And you say it will be the same in later 9.2 versions, and 10.2 as well ?

As It is not an in-house developed application, we cannot just change the SQL (but we might be able to make the vendor change it). Also, we would have to make sure any proposed solution does not hurt other queries.

Are there any other possibilities than rewriting the IA_SEQUE predicate like above ? hints ? settings ?

I suppose another possible workaround could be to remove the IA_SEQUE column from the index (or make a new one, and make the query use that). But again we have to take any negative side effects (on other queries) into consideration ... not an easy task :-(

Thanks,

• Peter

Received on Mon May 07 2007 - 12:47:35 CDT