How about using bind variables in your execute
immediate statement.
Please see doc id 34433.1 and 366753.999 on Metalink
as I followed 34433.1 for what I tested below.
366753.999 is a forum question which relates very well
to doc id 34433.1.
create table t (col1 number, col2 varchar2(10));
create or replace procedure sp_test (
p_col1 in number,
p_col2 in varchar2,
p_errcd out number,
p_errmsg out varchar2)
as
begin
execute IMMEDIATE
'insert into t
(col1,
Col2)
values (:b1, :b2)'
USING p_col1, p_col2;
p_errcd := SQLCODE;
p_errmsg := SQLERRM;
EXCEPTION
WHEN others THEN
ROLLBACK;
p_errcd := SQLCODE;
p_errmsg := SQLERRM;
end;
/
Then did this:
alter system flush shated_pool=true;
alter session set sql_trace=true;
var errcd number;
var errmsg varchar2(2000);
-- exec the following about 5 times
exec sp_test(1,'A',:errcd,:errmsg);
alter session set sql_trace=false;
--Now check the following:
select sql_text, loads, executions, PARSE_CALLS
from v$sql
where sql_text like 'insert into t%col1%'
;
LOADS EXECUTIONS PARSE_CALLS
-- -- ---
1 5 5
1 0 0
Check sql_trace output, I get the following for each
of the 5 executions (note that mis=0 indicating that
it is not a hard parse):
PARSING IN CURSOR #1 len=45 dep=0 uid=67 oct=47 lid=67
tim=2182033968 hv=1348535850 ad='79547da4'
BEGIN sp_test(1,'A', :errcd, :errmsg); END;
END OF STMT
PARSE
#1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=2182033968
=
PARSING IN CURSOR #2 len=65 dep=1 uid=67 oct=2 lid=67
tim=2182033968 hv=2052728044 ad='79d476b0'
insert into t
(col1,
Col2)
values (:b1, :b2)
END OF STMT
PARSE
#2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2182033968
EXEC
#2:c=0,e=0,p=0,cr=1,cu=1,mis=0,r=1,dep=1,og=4,tim=2182049968
EXEC
#1:c=15625,e=16000,p=0,cr=1,cu=1,mis=0,r=1,dep=0,og=4,tim=2182049968
So, in conclusion, by using bind var in my proc, I
have reduced the hard parse count.
hth
mkb
(Hoping that if I have misstated anything, someone
will correct me.)
--- chao_ping [EMAIL PROTECTED] wrote:
Chuan Zhang,
Since you are using execute immediate, you use
dynamic sql.
If you want to reduce the parse, can u try not
using the dynamic sql?
Keep it won't help at all.
Regards
zhu chao
Eachnet DBA
86-21-32174588-667
[EMAIL PROTECTED]
www.happyit.net
=== 2002-10-22 23:43:00 ,you wrote£º===
Hi, DBA Guru,
I have a stored procedure of a package which is
called with execute immediate in a loop with
runtime input parameters. I found that no. of parse
calls(451983) is equal to no. of executions
(451982). Is there any way such as set
cursor_sharing=force or keep this stored procedure
into shared pool to reduce the parse call down to
one or some no.?
TIA,
Chuan
= = = = = = = = = = = = = = = = = = = =
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: chao_ping
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051
http://www.fatcity.com
San Diego, California-- Mailing list and web
hosting services
-
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).
__
Do you Yahoo!?
Y! Web Hosting - Let the expert host your web site
http://webhosting.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: mkb
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
