Re: delete performance from a standalone sql versus the same call from a procedure
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Fri, 13 Nov 2009 22:14:35 +0100
Message-ID: <4afdcc3f$0$22945$e4fe514c_at_news.xs4all.nl>
z1hou1 schreef:
> Hi,
>
> Database Oracle 10.2.0.4 running on Sun Solaris.
>
> 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
Date: Fri, 13 Nov 2009 22:14:35 +0100
Message-ID: <4afdcc3f$0$22945$e4fe514c_at_news.xs4all.nl>
z1hou1 schreef:
> Hi,
>
> Database Oracle 10.2.0.4 running on Sun Solaris.
>
> 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
Do you use dynamic SQL (execute immediate)? Can we see the delete part of your code?
Shakespeare Received on Fri Nov 13 2009 - 15:14:35 CST