Re: delete performance from a standalone sql versus the same call from a procedure
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Fri, 13 Nov 2009 13:27:00 -0800 (PST)
Message-ID: <f538933a-57d1-42b1-98e3-3b35f7e7d87a_at_u7g2000yqm.googlegroups.com>
On Nov 13, 4:14 pm, Shakespeare <what..._at_xs4all.nl> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
Date: Fri, 13 Nov 2009 13:27:00 -0800 (PST)
Message-ID: <f538933a-57d1-42b1-98e3-3b35f7e7d87a_at_u7g2000yqm.googlegroups.com>
On Nov 13, 4:14 pm, Shakespeare <what..._at_xs4all.nl> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
Where are the explain plans? Bind variable peeking can result in different executions using different plans. Even minor differences in the coding of the SQL will make the two statements different to Oracle and as such different plans are possible.
HTH -- Mark D Powell -- Received on Fri Nov 13 2009 - 15:27:00 CST