Oracle-L: Re: Parse Vs Execute

From: Stephen Andert <StephenAndert_at_firsthealth.com>
Date: Wed, 26 Nov 2003 11:14:33 -0800
Message-ID: <F001.005D7E98.20031126111433@fatcity.com>

Here is what I use to monitor my cursor use. If session_cached_cursors is at or near 100%, I increase is and continue to monitor. On the system I just checked I'm up to 500. This reduced my parse counts for some operations.

The other thing is whether there are any compiler flags that need to be set to allow client connections to reuse cursors. Setting that and recompiling will make a huge impact.

Good luck and Happy Thanksgiving.
Stephen

  1 select
  2 'session_cached_cursors' parameter,   3 lpad(value, 5) value,
  4 decode(value, 0, ' n/a', to_char(100 * used / value, '990') || '%') usage
  5 from
  6 ( select

  7        max(s.value)  used
  8      from
  9        sys.v_$statname  n,
 10        sys.v_$sesstat  s
 11      where
 12        n.name = 'session cursor cache count' and
 13        s.statistic# = n.statistic#

14 ),
15 ( select

 16        value
 17      from
 18        sys.v_$parameter
 19      where
 20        name = 'session_cached_cursors'

21 )
22 union all
23 select
24 'open_cursors',
25 lpad(value, 5),
26 to_char(100 * used / value, '990') || '%' 27 from
28 ( select

 29        max(sum(s.value))  used
 30      from
 31        sys.v_$statname  n,
 32        sys.v_$sesstat  s
 33      where
 34        n.name in ('opened cursors current', 'session cursor cache
count') and
 35        s.statistic# = n.statistic#
 36      group by
 37        s.sid

38 ),
39 ( select

 40        value
 41      from
 42        sys.v_$parameter
 43      where
 44        name = 'open_cursors'

45* )

>>> jaysingh1_at_optonline.net 11/26/03 11:14AM >>>
Hi List,

Almost fro all SQLs I am getting Prase count is same as Execute count. How to reduce parse count?

We are using bind variable
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: Stephen Andert
  INET: StephenAndert_at_firsthealth.com

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 Wed Nov 26 2003 - 13:14:33 CST