session_cached_cursors /v$session_cursor_cache的疑惑

显示全部楼层 · 2005-10-30 17:05:33

session_cached_cursors 初始化参数修改了之后，似乎不生效。
SQL> select * from v$session_cursor_cache;
MAXIMUMCOUNT OPENED_ONCE OPENOPENS HITSHIT_RATIO
---------- ---------- ----------- ---------- ---------- ---------- ----------
50
0 0
0
0
0
1
SQL> show parameter session_ca
NAME
TYPEVALUE
------------------------------------ ----------- -------------------
session_cached_cursors
integer 20
SQL> alter session set session_cached_cursors=10;
会话已更改。
SQL> select * from v$session_cursor_cache;
MAXIMUMCOUNT OPENED_ONCE OPENOPENS HITSHIT_RATIO
---------- ---------- ----------- ---------- ---------- ---------- ----------
10
0 0
0
0
0
1

最奇怪的是：
REFERENCE:v$session_cursor_cache 的注释里有这么一段：
Note: The V$SESSION_CURSOR_CACHE view is not a measure of
the effectiveness of the SESSION_CACHED_CURSORS initialization
parameter.
看来oracle比较智能，无论初始化参数修改了什么值，除非alter session修改，v$session_cursor_cache里查到的都是50（比较优化的数字），可是如果session_cached_cursors 初始化参数指定了之后，对session级别没有影响，那还要这个参数干吗呢？

千问 · 2005-10-30 17:05:33

9.2.0.6.0、10.2.0.1.0都看到了这样的现象。

千问 · 2005-10-30 17:05:33

有意思，确实如此，咋回事呢？

千问 · 2005-10-30 17:05:33

为什么不flush_database_monitoring_info试试呢

千问 · 2005-10-30 17:05:33

flush_database_monitoring_info 怎么做？
alter system flush shared_pool; 这样么？
v$open_cursor的信息会变化，
但是v$session_cursor_cache的信息不变；
v$sesstat里记录“opened cursors current”“opened cursors cumulative”的value都不会变。

千问 · 2005-10-30 17:05:33

show parameter session_cached_cursor
drop view user_cursors;
create view user_cursors as
select
ss.username||'('||se.sid||') ' user_process, sum(decode(name,'recursive calls',value)) "Recursive Calls",
sum(decode(name,'opened cursors cumulative',value)) "Opened Cursors", sum(decode(name,'opened cursors current',value)) "Current Cursors"
from v$session ss, v$sesstat se, v$statname sn
wherese.statistic# = sn.statistic#

and ( namelike '%opened cursors current%'

OR namelike '%recursive calls%'

OR namelike '%opened cursors cumulative%')

andse.sid = ss.sid

and ss.username is not null
group by ss.username||'('||se.sid||') ';
ttitle 'Per Session Current Cursor Usage '
column USER_PROCESS format a25;
column "Recursive Calls" format 999,999,999;
column "Opened Cursors"format 99,999;
column "Current Cursors"format 99,999;
select * from user_cursors
order by "Recursive Calls" desc;

千问 · 2005-10-30 17:05:33

BUG: 5143332
SQL> show parameter session_cached_cursors . NAME
TYPEVALUE
------------------------------------ ----------- ---------
session_cached_cursors
integer 20 .
SQL> select open, maximum from v$session_cursor_cache; .
OPENMAXIMUM -
--------- ----------

0 50 . .
SQL> show parameter session_cached_cursors . NAME
TYPEVALUE
------------------------------------ ----------- ---------
session_cached_cursors
integer 100 .
SQL> select open, maximum from v$session_cursor_cache; .
OPENMAXIMUM
---------- ----------

0100 .
SQL> show parameter session_cached_cursors . NAME
TYPEVALUE
------------------------------------ ----------- ---------
session_cached_cursors
integer 1000 .
SQL> select open, maximum from v$session_cursor_cache; .
OPENMAXIMUM -
--------- ----------

0 1000 .