Re: Slow UPDATE

From: Terry Sutton <terrysutton_at_usa.net>
Date: Mon, 17 May 2004 09:18:06 -0700
Message-ID: <00cd01c43c2b$9cd8a300$0400a8c0@TerrySutton>

Is it (showing a different plan)? Is the trace showing index lookups or full scans?

Original Message -----

Thanks Terry, I'm aware of what these events are, it's more the question: why is tkprof showing=20
an execution plan that obviously isn't the one Oracle executed as per the trace? tkprof isn't
reliable?

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Terry Sutton Sent: Sunday, May 16, 2004 9:29 PM
To: oracle-l_at_freelists.org
Subject: Re: Slow UPDATE

Look at the trace itself (not the tkprof), see what the query is actually doing. If the query is using the index, you'll see "db file sequential reads" of the index and table. If it's doing table scans you'll see "db file scattered reads".

--Terry

Original Message -----=20 You're saying that a 10046 trace isn't always reliable with respect to identifying the 'real' execution plan?

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of jaromir nemec
Sent: Sunday, May 16, 2004 10:26 AM
To: oracle-l_at_freelists.org
Subject: Re: Slow UPDATE

Hi Thomas,

Original Message -----=20 From: "Thomas Jeff" <jeff.thomas_at_thomson.net> To: <oracle-l_at_freelists.org> Sent: Friday, May 14, 2004 3:18 PM Subject: Slow UPDATE

if the table 'OD_CUST_LOCS' has approximately 9682 blocks = (=3D1936484/200)
I'm pretty sure that the update is doing full scan on it.

If you are on 9i check the "real" execution plan in V$SQL_PLAN.

>call count cpu elapsed disk query current rows
> Execute 200 578.44 789.45 1852160 1936484 218 200

An "elegant" explanation will be that the Informatica ports have different types than the PK columns. Check the type of the ports 66-68

> WHERE SRC_EFF_FROM_DT =3D :66 AND KEY_ID =3D :67 AND SOURCE_ID =3D :68 regards

Jaromir D.B. Nemec
http://www.db-nemec.com

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


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

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

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

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


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

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

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

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 Mon May 17 2004 - 11:24:11 CDT