| session_cached_cursors [message #513507] |
Mon, 27 June 2011 22:18  |
fairdaus
Messages: 14
Registered: May 2009
Location: KL
|
Junior Member |
|
|
hi..
I run one sql
column parameter format a29
column value format a7
column usage format a7
select 'session_cached_cursors' parameter, lpad(value, 5) value,
decode(value, 0, ' n/a', to_char(100 * used / value, '9990') || '%') usage
from ( select max(s.value) used from V$STATNAME n, V$SESSTAT s
where n.name = 'session cursor cache count' and s.statistic# = n.statistic# ),
( select value from V$PARAMETER where name = 'session_cached_cursors' )
union all
select 'open_cursors', lpad(value, 5), to_char(100 * used / value, '9990') || '%'
from ( select max(sum(s.value)) used from V$STATNAME n, V$SESSTAT s
where n.name in ('opened cursors current', 'session cursor cache count') and s.statistic# = n.statistic# group by s.sid ),
( select value from V$PARAMETER where name = 'open_cursors' );
and the output is
PARAMETER VALUE USAGE
----------------------------- ------- -------
session_cached_cursors 50 1014%
open_cursors 1500 34%
my question is "usage value" for session_cached_cursors is normal?? or i need to be tuning for session_cached_cursors parameter??
|
|
|
|
|
|
|
|
| Re: session_cached_cursors [message #523086 is a reply to message #513523] |
Thu, 15 September 2011 09:00  |
dbaoracleinin
Messages: 19
Registered: November 2010
Location: abc
|
Junior Member |
|
|
Michel Cadot and black swan are you here for contributing in solving issues or for only discouraging people,ur replies to this question is just confusing and discouraging person asking question like for fairdaus
,ur making simple things complicated and diverting topic and wasting time and efforts
|
|
|
|
|
|
| Re: session_cached_cursors [message #523134 is a reply to message #523109] |
Thu, 15 September 2011 13:55 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I've just run this query:
orcl> column parameter format a29
orcl> column value format a7
orcl> column usage format a7
orcl> select 'session_cached_cursors' parameter, lpad(value, 5) value,
2 decode(value, 0, ' n/a', to_char(100 * used / value, '9990') || '%') usage
3 from ( select max(s.value) used from V$STATNAME n, V$SESSTAT s
4 where n.name = 'session cursor cache count' and s.statistic# = n.statistic# ),
5 ( select value from V$PARAMETER where name = 'session_cached_cursors' )
6 union all
7 select 'open_cursors', lpad(value, 5), to_char(100 * used / value, '9990') || '%'
8 from ( select max(sum(s.value)) used from V$STATNAME n, V$SESSTAT s
9 where n.name in ('opened cursors current', 'session cursor cache count') and s.statistic# = n.statistic# group by s.sid ),
10 ( select value from V$PARAMETER where name = 'open_cursors' );
PARAMETER VALUE USAGE
----------------------------- ------- -------
session_cached_cursors 50 14%
open_cursors 300 8%
orcl>
@fairdaus, please can you explain?
|
|
|
|
| Re: session_cached_cursors [message #523193 is a reply to message #523134] |
Fri, 16 September 2011 02:27 |
dbaoracleinin
Messages: 19
Registered: November 2010
Location: abc
|
Junior Member |
|
|
PARAMETER VALUE USAGE
----------------------------- ------- -------
session_cached_cursors 50 14%
open_cursors 300 8%
open_cursors-(at sql prompt show parameter open_cursors)
cursor opens in application,value column telling
total configured (init.ora) and usage saying how much % of its currently being used ,8% in your case
session_cached_cursors-there many cursors which are opened and closed by application,some are reused,depending on which of cursors being reused (3 times),oracle put into cache for future use again this is session_cached_cursors,
in above query result value-50 (at sql prompt show parameter session_cached_cursors) is configured on your database
and 14% is used in current sessions(this may change during
different time depending on usage)
increasing this will take more memory from shared pool
|
|
|
|
|
|
|
|
|
|
| Re: session_cached_cursors [message #523251 is a reply to message #523210] |
Fri, 16 September 2011 07:19 |
dbaoracleinin
Messages: 19
Registered: November 2010
Location: abc
|
Junior Member |
|
|
try this out:
olumn parameter format a29
column value format a5
column usage format a5
select 'session_cached_cursors' parameter, lpad(value, 5) value,
decode(value, 0, ' n/a', to_char(100 * used / value, '990') || '%') usage
from ( select max(s.value) used from V$STATNAME n, V$SESSTAT s
where n.name = 'session cursor cache count' and s.statistic# = n.statistic# ),
( select value from V$PARAMETER where name = 'session_cached_cursors' )
union all
select 'open_cursors', lpad(value, 5), to_char(100 * used / value, '990') || '%'
from ( select max(sum(s.value)) used from V$STATNAME n, V$SESSTAT s
where n.name in ('opened cursors current', 'session cursor cache count') and s.statistic# = n.statistic# group by s.sid ),
( select value from V$PARAMETER where name = 'open_cursors' );
but i think fairdus is seeking explanation on this parameter values returned from the view instead of bothering actual values
(trying to interpret output)
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
