Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Parse Vs Execute

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?

  1. We are using bind variable
  2. session_cached_cursors set to 100
  call     count       cpu    elapsed       disk      query    current 
      rows

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US