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).
