Hi Ron !
I have NO data integrity constraints in my DB .
And the statement is absolutely stand-alone , that is , it's not a part of
a transaction .
- Original Message -----
From: "Ron Rogers" <RROGERS_at_galottery.org>
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
Sent: Thursday, September 07, 2000 5:50 PM
Subject: Re: parsing in a call to a stored procedure
> 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
> 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
> FROM
> PROFILE_XML WHERE PID = :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)
>
>
>
> --
> Author: Ron Rogers
> INET: RROGERS_at_galottery.org
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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
Received on Thu Sep 07 2000 - 13:59:13 CDT
