| My Reputation Is At Stake Over Bind Variables! [message #372159] |
Tue, 16 January 2001 21:53  |
Mike Freeney
Messages: 1
Registered: January 2001
|
Junior Member |
|
|
Please Help me save my good name.
I'm sure that I read somewhere that one of Oracle's recent enhancements to the SQL parser is that it will automatically turn literals in the where clause into bind variables so that the query plan can be re-used. However, now that I've been called on it by one of my peers I cannot find any documentation on it.
Can anyone help?
Thanks--
|
|
|
|
| Re: My Reputation ... Quote [message #372169 is a reply to message #372159] |
Wed, 17 January 2001 11:33  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
From oradoc.photo.net/ora81/DOC/server.815/a68003/01_09dyn.htm
Performance Tip
When using either native dynamic SQL or the DBMS_SQL package, you can improve performance by using bind variables, because using bind variables allows Oracle to share a single cursor for multiple SQL statements.
For example, the following native dynamic SQL code does not use bind variables:
CREATE OR REPLACE PROCEDURE del_dept (
my_deptno dept.deptno%TYPE) IS
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = ' || to_char (my_deptno);
END;
/
For each distinct my_deptno variable, a new cursor is created, which can cause resource contention and poor performance. Instead, bind my_deptno as a bind variable, as in the following example:
CREATE OR REPLACE PROCEDURE del_dept (
my_deptno dept.deptno%TYPE) IS
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :1' USING my_deptno;
END;
/
Here, the same cursor is reused for different values of the bind my_deptno, thereby improving performance and scalabilty.
|
|
|
|
| Re: My Reputation ... Quote [message #372171 is a reply to message #372159] |
Wed, 17 January 2001 11:41 |
Jan G
Messages: 6
Registered: December 2000
|
Junior Member |
|
|
There is also a new init param - cursor_sharing. When set to force, it considers sql statements equivalent if the only difference is the literal value.
There was a presentation at Openworld, the paper should be availabe still at Oracle's site, it gives some advice on when to use it.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
