Re: Executions/Parse_calls

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 23 Jul 1998 20:46:31 GMT
Message-ID: <35baa0f0.30771256@192.86.155.100>

A copy of this was sent to "Martin Krivak" <martin.krivak_at_berit.cz> (if that email address didn't require changing) On 23 Jul 1998 10:36:51 GMT, you wrote:

>Could anybody give me a example (SQL or PL/SQL ) of statement(s) which has
>more executions than parse_calls?
>
>Thanks

Sure, the following procedure:

create procedure aprocedure
as
begin

    for x in ( select * from all_users ) loop

        for y in ( select * from dual ) loop
            dbms_output.put_line( '....' );
        end loop;

    end loop;
end;
/

shows in tkprof that:

SELECT *
FROM
 DUAL call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0          0           0
Execute   3320      0.00       0.00          0          0          0           0
Fetch     6640      0.00       0.00          0       3320       9960        3320
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     9961      0.00       0.00          0       3320       9960        3320

Misses in library cache during parse: 1 Optimizer goal: CHOOSE
Parsing user id: 4036 (TKYTE) (recursive depth: 1)

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE

   3320 TABLE ACCESS (FULL) OF 'DUAL' So pl/sql parsed the inner cursor once but executed (opened) it 3,320 times...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Jul 23 1998 - 15:46:31 CDT