Re: parsing in a call to a stored procedure

From: Ron Rogers <RROGERS_at_galottery.org>
Date: Thu, 07 Sep 2000 10:45:59 -0400
Message-Id: <10612.116411@fatcity.com>

The sql statement that was found in the parse ans executed 100 times is = designed that way because of data integrity. To support this reasoning, = Suppose the data was changed between query 1 and query 2. All of the = results would be inaccurate if the execution was not done for each query = rather than use the results of the previous query. ROR >>> bronfin_at_VisualTop.com 09/06/00 04:37PM >>> 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 =

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

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 =

Misses in library cache during parse: 0 Received on Thu Sep 07 2000 - 09:45:59 CDT