Re: Query performance problem

From: dave mausner <dmausner_at_ameritech.>
Date: Wed, 08 Dec 1999 05:04:36 GMT
Message-ID: <gTl34.287$IN6.26480@nntp0.chicago.il.ameritech.net>

In article <82jf28$hh8$1_at_nnrp1.deja.com>, gcm281p_at_my-deja.com wrote:
>I'm having a problem with a query in a stored
>procedure. When I run the query in SQL*Plus or
>any other tool it runs in about 1 second. When I
>put the query in a stored procedure as a cursor
>the execution time jumps to about an hour.
>Anybody have any suggestions on where to look?

check for dumb stuff like bind variables having the wrong type, forcing coersions which ignore indexes. stuff like that.

do a trace with plan for the good optimization outside of pl/sql.

then do it again while running the procedure. the different plans suggest what you might wish to hint.

try hinting where you want the query to run (target_site hint) and anything else that the good optimization benefits from (index hints). also select an optimizer strategy (first_rows hint, e.g.).

--
Dave Mausner, Senior Manager, Braun Consulting Inc., Chicago. Received on Tue Dec 07 1999 - 23:04:36 CST