delete performance from a standalone sql versus the same call from a procedure

From: z1hou1 <z1hou1_at_gmail.com>
Date: Fri, 13 Nov 2009 12:47:27 -0800 (PST)
Message-ID: <cb30783d-4c2d-48d2-b0d1-1e1f50233fc0_at_b2g2000yqi.googlegroups.com>

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 Received on Fri Nov 13 2009 - 14:47:27 CST

This message: [ Message body ]
Next message: joel garry: "Re: delete performance from a package and standalone"
Previous message: z1hou1: "delete performance from a package and standalone"
Next in thread: Shakespeare: "Re: delete performance from a standalone sql versus the same call from a procedure"
Reply: Shakespeare: "Re: delete performance from a standalone sql versus the same call from a procedure"
Reply: Tim X: "Re: delete performance from a standalone sql versus the same call from a procedure"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message