Home » RDBMS Server » Performance Tuning » Stored Procedure - Bind Variables - Performance
Stored Procedure - Bind Variables - Performance [message #64890] Thu, 26 February 2004 05:02 Go to next message
Patrick Tahiri
Messages: 119
Registered: January 2004
Senior Member
Hi,

I'm trying to test 2 stored procedure that are doing exactely the same SELECT statement but I use one within an EXECUTE IMMEDIATE and bind variables to it. And the other one is a static SQL statment: exactely the same statement but without EXECUTE IMMEDIATE '....' INTO ... USING ...

And then I try to check in the Shared Pool via V$SQLAREA if I have several occurences of my select statement (several query plans..). But I can't see the difference!!??

Is it not better to use Dynamic SQL (EXECUTE IMMEDIATE) and bind variables within a stored procedure (within a package)?

Many thanks for your tips.

Regards,

Patrick Tahiri.
Stored Procedure - Bind Variables - Performance [message #64891 is a reply to message #64890] Thu, 26 February 2004 05:45 Go to previous messageGo to next message
Patrick Tahiri
Messages: 119
Registered: January 2004
Senior Member
I would like to add this:

I red in books that Dynamic SQL with binded variables are much better that SQL without binded variable?! Because it's using only a soft parse and not hard parses each time we are running the same query with different parameters.
Does it mean that static SQL is worth than than Dynamic SQL?
In that case I should always use in my stored procedure:
EXECUTE IMMEDIATE 'SELECT ... FROM ... WHERE .. :x, ...' INTO ... USING ...

AND NOT

Static SQL like:
SELECT ... INTO ... FROM ...
WHERE ... ;

Is that right? Am I not mixing here Dynamic SQL with binding variables, Dynamic SQL without binding variables AND static SQL? Which is best to use within my stored procedure? Static SQL when I can, isn't it?

Thank you very much for your tips.

Regards,

Patrick Tahiri.
Re: Stored Procedure - Bind Variables - Performance [message #64902 is a reply to message #64891] Sun, 29 February 2004 05:19 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Hi Patrick,
You would use Dynamic sql ONLY if you need to . if you are building sql statements dynamically. Otherwise you should be using static SQL . PL/SQL will cache the cursor for you when using bind variables.

Again , Static SQL is preferred whenever possible,in PL/SQL. Why complicate ?
Lack of bind variables(ie hard coded literals in the predicate clause) , would cause unnecessary parses , resulting in library cache latch contention and increased cpu usage and this is especially important in OLTP environment where there could be tons of uniquely generated sql statements. This is taken care of , in PL/SQL procedures,pacakges etc when you are passing in those variables.

-Thiru
Re: Stored Procedure - Bind Variables - Performance [message #64903 is a reply to message #64902] Sun, 29 February 2004 22:12 Go to previous message
Patrick Tahiri
Messages: 119
Registered: January 2004
Senior Member
Thank you very much!!

Regards,

Patrick Tahiri.
Previous Topic: Oracle 9i optimizer
Next Topic: Limiting the number of hits to 1 with rownum.
Goto Forum:
  


Current Time: Wed Jan 27 14:04:40 CST 2021