parsing in a call to a stored procedure

From: Andrey Bronfin <bronfin_at_VisualTop.com>
Date: Wed, 6 Sep 2000 22:13:57 +0200
Message-Id: <10611.116332@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_0007_01C0184F.C01EF070 Content-Type: text/plain;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Dear All !

It appears that every time i call a stored procedure , there is a parse = on a call to the stored procedure .
I can see it in the output of TKPROF .
In the example below i called the procedure "update_usr_all" 11 times = and the call to the procedure has been parsed=20 all the 11 times although the procedure is pinned into the shared_pool .

There is another problem that i'm facing . Let's say that i call an SQL SELECT statement ( with bind variables) = 100 times .
It appears in TKPROF output file that there are 1 parse , 100 executions = and 100 fetches for the statement .
But should there really be an execution for each SELECT call .

Would You please shed some light ?
Thanks a lot in advance .

BTW , it's Oracle 8.1.6 on Win2000 .
Thanks .

*************************************************************************=

call update_usr_all (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)

call count cpu elapsed disk query current =

rows
------- ------ -------- ---------- ---------- ---------- ---------- =

Parse       11      0.02       0.05          0          0          0     =
      0
Execute     11      0.01       0.08          0          0         11     =
      0
Fetch        0      0.00       0.00          0          0          0     =
      0

------- ------ -------- ---------- ---------- ---------- ---------- =

total       22      0.03       0.13          0          0         11     =
      0

*************************************************************************=

call get_topic_data (:1,:2,:3,:4,:5,:6,:7,:8 )

call count cpu elapsed disk query current =

rows
------- ------ -------- ---------- ---------- ---------- ---------- =

Parse        13      0.10       0.016          0          0          0   =
        0
Execute      13      0.05       0.15          0          0          0    =
       0
Fetch        0      0.00       0.00          0          0          0     =
      0

------- ------ -------- ---------- ---------- ---------- ---------- =

total        26      0.15       0.21          0          0          0    =
       0


*************************************************************************=

SELECT XML_REPRESENTATION =20
FROM
PROFILE_XML WHERE PID =3D :b1

call count cpu elapsed disk query current =

rows
------- ------ -------- ---------- ---------- ---------- ---------- =

Parse        1      0.00       0.01          0          0          0     =
      0
Execute     11      0.00       0.00          0          0          0     =
      0
Fetch       11      0.00       0.00          0         33         44     =
     11

------- ------ -------- ---------- ---------- ---------- ---------- =

total       23      0.00       0.01          0         33         44     =
     11

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 37 (USER2) (recursive depth: 1)

------=_NextPart_000_0007_01C0184F.C01EF070 Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" =
http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.2920.0" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#d4d0c8>
<DIV><FONT face=3DArial size=3D2>Dear All !</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>It appears that every time i call a =
stored=20
procedure , there is a parse on a call to the stored procedure = .</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>I can see it in the output of TKPROF =
.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>In the example below i called the =
procedure=20
"update_usr_all" 11 times and the call to the procedure has been parsed=20
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>all the 11 times although the procedure =
is pinned=20
into the shared_pool .</FONT></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>There is another problem that i'm =
facing=20
.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Let's say that i call an SQL SELECT =
statement (=20
with bind variables)  100 times .</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>It appears in TKPROF output file that =
there are 1=20
parse , 100 executions and 100 fetches for the statement .</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>But should there really be an =
execution=20
for each SELECT call .</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>Would You please shed some light =
?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Thanks a lot in advance .</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>BTW , it's Oracle 8.1.6 on Win2000 =
.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Thanks .</FONT></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2><FONT face=3DArial=20

size=3D2>****************************************************************=
****************</FONT></FONT></DIV>

<DIV><FONT face=3DArial size=3D2><BR>call update_usr_all=20
(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2><BR>call    =20
count       cpu   =20 elapsed       = disk     =20
query    =
current       =20 rows<BR>------- ------  -------- ---------- ---------- ----------=20 ----------  ----------<BR>Parse       =

11      =
0.02      =20 0.05         =20

0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20

0<BR>Execute     11     =20 0.01      =20 0.08         =20 0         =20 0        =20 11          =20 0<BR>Fetch       =20 0      0.00       =

0.00         =20

0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
0<BR>-------=20

------  -------- ---------- ---------- ---------- ---------- =20 ----------<BR>total      =20 22      =
0.03      =20 0.13         =20 0         =20 0        =20 11           = 0</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial=20

size=3D2>****************************************************************=
****************</FONT></DIV>

<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>call get_topic_data =
(:1,:2,:3,:4,:5,:6,:7,:8=20
)</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2><BR>call    =20
count       cpu   =20 elapsed       = disk     =20
query    =
current       =20

rows<BR>------- ------&nbsp; -------- ---------- ---------- ----------=20
----------&nbsp; =
----------<BR>Parse&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20

13      =
0.10      =20 0.016         =20

0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20

0<BR>Execute      = 13     =20
0.05      =20 0.15         =20

0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20

0<BR>Fetch       =20 0      0.00       =

0.00         =20

0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
0<BR>-------=20

------  -------- ---------- ---------- ---------- ---------- =20 ----------<BR>total       =20 26      =
0.15      =20 0.21         =20

0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =

0</FONT></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2><FONT face=3DArial=20

size=3D2>****************************************************************=
****************</FONT></FONT></DIV>

<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>SELECT XML_REPRESENTATION  =20
<BR>FROM<BR> PROFILE_XML  WHERE PID =3D :b1</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2><BR>call    =20
count       cpu   =20 elapsed       = disk     =20
query    =
current       =20

rows<BR>------- ------&nbsp; -------- ---------- ---------- ----------=20
----------&nbsp; =
----------<BR>Parse&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20

1      0.00       =

0.01         =20

0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20

0<BR>Execute     11     =20 0.00      =20 0.00         =20

0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20

0<BR>Fetch       = 11     =20
0.00      =20 0.00         =20 0        =20 33        =20 44          11<BR>-------=20 ------  -------- ---------- ---------- ---------- ---------- =20 ----------<BR>total      =20 23      =
0.00      =20 0.01         =20 0        =20 33        =20 44          11</FONT></DIV>
<DIV> </DIV>
Received on Wed Sep 06 2000 - 15:13:57 CDT