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" <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
