Home » RDBMS Server » Performance Tuning » Question about parsing and Bind variables
Question about parsing and Bind variables [message #212514] Fri, 05 January 2007 11:23 Go to next message
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 #212565 is a reply to message #212514] Fri, 05 January 2007 17:59 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You might find that they are soft parses. Try tracing it and finding out how many parses there are from the TK*Prof output.

Ross Leishman
Re: Question about parsing and Bind variables [message #212596 is a reply to message #212514] Sat, 06 January 2007 05:50 Go to previous message
michael_bialik
Messages: 611
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
Previous Topic: Database Down
Next Topic: Performance Forms 6i - Where clause in block
Goto Forum:
  


Current Time: Sun Dec 04 04:30:58 CST 2016

Total time taken to generate the page: 0.11161 seconds