| Stored Procedure - Bind Variables - Performance [message #64890] |
Thu, 26 February 2004 05:02  |
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  |
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 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
