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* )
>>> [EMAIL PROTECTED] 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
------- ------ -------- ---------- ---------- ----------
---------- ----------
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: Stephen Andert
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).
