RE: Ora 9.2/HPUX 11i/Performance

From: Vergara, Michael (TEM) <mvergara_at_guidant.com>
Date: Thu, 19 Feb 2004 10:36:02 -0800
Message-ID: <791D0E1ECDECD04D89205F33806FC38701E91BD2@temmse06.tem.guidant.com>

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_at_oraperf.com] 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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


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

Received on Thu Feb 19 2004 - 12:36:02 CST