Re: Reduce parse call for stored procedure?

From: mkb <mkb125_at_yahoo.com>
Date: Wed, 23 Oct 2002 09:08:37 -0800
Message-ID: <F001.004F143C.20021023090837@fatcity.com>

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 <chao_ping_at_vip.163.com> 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 > chao_ping_at_vip.163.com > 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: chao_ping_at_vip.163.com > > 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: ListGuru_at_fatcity.com (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: mkb125_at_yahoo.com

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: ListGuru_at_fatcity.com (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).

Received on Wed Oct 23 2002 - 12:08:37 CDT