Re: delete performance from a package and standalone

From: joel garry <joel-garry_at_home.com>
Date: Fri, 13 Nov 2009 13:08:09 -0800 (PST)
Message-ID: <05d587b2-a73e-474e-818d-3a4fa23cc409_at_h14g2000pri.googlegroups.com>

On Nov 13, 12:35 pm, z1hou1 <z1h..._at_gmail.com> wrote:
> Hi,
> I am facing a peculiar issue. I have a package with a procedure that
> handles deletes off a particular table. The delete runs in sub seconds
> for about 10000 rows when I execute it standalone from sqlplus. But
> the very same statement from inside a stored procedure (of the
> package) indicates a full table scan and takes forever to run.
>
> What is it that I may be doing that causes the very same delete to run
> with different plans from a stored procedure as opposed to an
> anonymous sql block?
>
> Thanking you all for your response.
>
> Regards,
> z1hou1

The knee-jerk response is "bind variable peeking," but really we need to know a lot more about your environment and your exact statement. For example, the exact version/patch level, any non-standard init.ora parameters, a runtime plan for each situation, and we may need to trace the run. People have written entire books on the optimizer and how to tune.

It not only depends on the things the optimizer uses to decide which plan to use, but what else is going on in the system. It could even be that you only think it is the very same delete. Was the procedure compiled by the same user? Is there recursive sql?

jg

--
_at_home.com is bogus.
If you are going to delete your programs that commit massive fraud,
don't forget to burn the backups! http://www.reuters.com/article/domesticNews/idUSN1346294620091113

Received on Fri Nov 13 2009 - 15:08:09 CST