Oracle-L: Re: Parse Vs Execute

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 29 Nov 2003 01:34:25 -0800
Message-ID: <F001.005D818D.20031129013425@fatcity.com>

You don't necessarily need to reduce the parse count unless you definitely have latch contention on the library cache latches, and other parse-related latches.

If you are using successfully using session_cached_cursors, then you will still see parse calls being counted, even though the parse calls are to pinned cursors and therefore very cheap.

There seem to be at least 3 variants of 'soft' parses:

The second parse call against a statement much cheaper than the hard parse

The third parse call against a statement - slightly cheaper than the second

        See James Morle's book for the differences between 2nd and 3rd
        This is when a cursor reference goes into the session cache
    Parse calls against a session cached statement
        Seems to be very close to the cost of a 'parse once' call.

Some latching results from a very simple-minded test, which needs further refinement, but is indicative of the relative costs. (v10.1)

Parse once, execute 1,000:

shared pool                       1,052
library cache                     2,078
library cache lock                   28
library cache pin                 2,044

Parse once per execute 1,000 (no session cache) so one hard, one (2nd parse) 998 (3rd parse).

shared pool                       3,133
library cache                     7,095
library cache lock                4,028
library cache pin                 4,048

Parse once per execute 1,000 (with session cache) so one hard, one (2nd parse) 1 (3rd parse), 997 cached

shared pool                       1,150
library cache                     2,122
library cache lock                   36
library cache pin                 2,068

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html ____UK___November

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, November 26, 2003 6:14 PM

> Hi List,
>
> Almost fro all SQLs I am getting Prase count is same as Execute count. How
to reduce parse count?
>
> 1) We are using bind variable
> 2) session_cached_cursors set to 100
>
>
> call count cpu elapsed disk query current
rows
> ------- ------ -------- ---------- ---------- ---------- ----------

> Parse 11 0.01 0.02 0 0 0
0
> Execute 11 0.00 0.00 0 0 0
0
> Fetch 22 0.01 0.00 0 33 44
110
> ------- ------ -------- ---------- ---------- ---------- ----------

> total 44 0.02 0.02 0 33 44
110
>
> Any somebody give more hint on this?
>
> Thanks
> Jay
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
> INET: jaysingh1_at_optonline.net
>
> 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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 Sat Nov 29 2003 - 03:34:25 CST