| Question about parsing and Bind variables [message #212514] |
Fri, 05 January 2007 11:23  |
kevin_oracle_2003
Messages: 2
Registered: October 2006
|
Junior Member |
|
|
Can someone please clarify why Oracle is paring this statment again and again even when i use bind variables.
CREATE OR REPLACE PROCEDURE test_binds
( i_empno IN NUMBER,
i_deptno IN NUMBER,
l_ref OUT SYS_REFCURSOR
)
AS
l_sql VARCHAR2(32000);
BEGIN
l_sql := 'SELECT
*
FROM
EMP
WHERE
EMPNO = :i_empno AND
org_pk = :i_deptno';
OPEN l_ref FOR
l_sql
USING
i_empno,
i_deptno;
END;
SQL to find parce_calls and executions
SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS FROM V$SQLAREA
WHERE
SQL_TEXT LIKE '%EMP%'
The number of parce_calls and executions continue to increase with each execution of this pl/sql block:
execution call:
DECLARE
l_ref sys_refcursor;
BEGIN
test_binds(10001, 10 l_ref );
END;
I was under the impression that the parse calls will remain the same if the sql uses bind variables. Can someone please clarify my doubt?
Thanks
Kev
|
|
|
|
|
|
| Re: Question about parsing and Bind variables [message #212596 is a reply to message #212514] |
Sat, 06 January 2007 05:50  |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
The problem is caused by NDS (native dynamic sql) usage:
when your cursor is closed NDS releases it's handle from session-level cache table, so the next time it's executed Oracle performs a search in SGA (soft parse).
AFAIK the problem is solved in 10g.
HTH.
Michael
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
