RE: Ora 9.2/HPUX 11i/Performance
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 19 Feb 2004 13:23:58 -0700
Your bad query has a lot more (20) items in the in list than the good query
(12). With that many items it does not use the inlist iterator anymore but
drives the query with a range scan of an index on LGORT or SPTAG.
If you can not use a hint, tuning suggestions would depend on the exact
column and index statistics.
At 11:36 AM 2/19/2004, you wrote:
>The problem is probably not with SQL Trace, but more likely
>my ability to read it. All it showed us is that in one occurence
>of a query against a table, it used a bad index and took forever.
>A second query used a better index and zoomed through. I cannot
>tell why there is a difference.
>
>Here's the bad one...
>
>SELECT "SPTAG" , "WERKS" , "LGORT" , "SPART" , "PRDHA" , "MTART" , =
>"MATNR" ,=20
> "CHARG" , "BWART" , "BZEIT" , "BASME" , "MENGE"=20
>FROM
> "S676" WHERE "MANDT" =3D :A0 AND "MATNR" IN ( :A1 , :A2 , :A3 , :A4 , =
>:A5 ,=20
> :A6 , :A7 , :A8 , :A9 , :A10 , :A11 , :A12 , :A13 , :A14 , :A15 , :A16 =
>,=20
> :A17 , :A18 , :A19 , :A20 ) AND "VRSIO" =3D :A21 AND "WERKS" =3D :A22 =
>AND=20
> "LGORT" > :A23 AND "SPTAG" BETWEEN :A24 AND :A25
>
>
>call count cpu elapsed disk query current =
> rows
>------- ------ -------- ---------- ---------- ---------- ---------- =
>----------
>Parse 0 0.00 0.00 0 0 0 =
> 0
>Execute 34 0.00 0.00 0 0 0 =
> 0
>Fetch 45 1766.90 3489.94 3684094 3805627 0 =
> 10478
>------- ------ -------- ---------- ---------- ---------- ---------- =
>----------
>total 79 1766.90 3489.95 3684094 3805627 0 =
> 10478
>
>Misses in library cache during parse: 0
>Optimizer goal: CHOOSE
>Parsing user id: 8 (SAPR3)
>
>Rows Execution Plan
>------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: CHOOSE
> 0 FILTER
> 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'S676'
> 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'S676~0' (UNIQUE)
>*************************************************************************=
>*******
>And here's the good one...
>*************************************************************************=
>*******
>SELECT "SPTAG" , "WERKS" , "LGORT" , "SPART" , "PRDHA" , "MTART" , =
>"MATNR" ,=20
> "CHARG" , "BWART" , "BZEIT" , "BASME" , "MENGE"=20
>FROM
> "S676" WHERE "MANDT" =3D :A0 AND "MATNR" IN ( :A1 , :A2 , :A3 , :A4 , =
>:A5 ,=20
> :A6 , :A7 , :A8 , :A9 , :A10 , :A11 , :A12 ) AND "VRSIO" =3D :A13 AND =
>"WERKS"=20
> =3D :A14 AND "LGORT" > :A15 AND "SPTAG" BETWEEN :A16 AND :A17
>
>
>call count cpu elapsed disk query current =
> rows
>------- ------ -------- ---------- ---------- ---------- ---------- =
>----------
>Parse 1 0.00 0.00 0 0 0 =
> 0
>Execute 1 0.00 0.00 0 0 0 =
> 0
>Fetch 1 0.00 0.01 2 36 0 =
> 0
>------- ------ -------- ---------- ---------- ---------- ---------- =
>----------
>total 3 0.00 0.01 2 36 0 =
> 0
>
>Misses in library cache during parse: 1
>Optimizer goal: CHOOSE
>Parsing user id: 8 (SAPR3)
>
>Rows Row Source Operation
>------- ---------------------------------------------------
> 0 FILTER (cr=3D36 r=3D2 w=3D0 time=3D14499 us)
> 0 INLIST ITERATOR (cr=3D36 r=3D2 w=3D0 time=3D14493 us)
> 0 TABLE ACCESS BY INDEX ROWID S676 (cr=3D36 r=3D2 w=3D0 =
>time=3D14370 us)
> 0 INDEX RANGE SCAN S676~VAB (cr=3D36 r=3D2 w=3D0 time=3D14338 =
>us)(object id 794344)
>
>
>Rows Execution Plan
>------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: CHOOSE
> 0 FILTER
> 0 INLIST ITERATOR
> 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'S676'
> 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'S676~VAB'=20
> (NON-UNIQUE)
>
>*************************************************************************=
>*******
>
>Cheers,
>Mike
>
>
>-----Original Message-----
>From: Anjo Kolk [mailto:anjo@xxxxxxxxxxx]
>Sent: Thursday, February 19, 2004 10:22 AM
>To: Vergara, Michael (TEM)
>Subject: Re: Ora 9.2/HPUX 11i/Performance
>
>
>What is the problem with SQLTrace so that you can't use it? There are
>others ways of looking at this!
>
>Anjo.
>
>----------------------------------------------------------------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>----------------------------------------------------------------
>To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
>put 'unsubscribe' in the subject line.
>--
>Archives are at //www.freelists.org/archives/oracle-l/
>FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
>-----------------------------------------------------------------
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
