session_cached_cursors的研究

884.session_cached_cursors的研究
Oracle 10g后 默认将session_cached_cursors设置为20(之前为0) 表明了什么？
Oracle建议在session中cache cursors.
当然前提是应用中是sharable SQL(充分绑定变量)
Why?
大量重复执行相同sql可以:
1 节省解析时间
2 降低cpu资源
If SESSION_CACHED_CURSORS is not set, it defaults to 0 and no cursors will be cached for your session. (Your cursors will still be cached in the shared pool, but your session will have to find them there.) If it is set, then when a parse request is issued, Oracle checks the library cache to see whether more than 3 parse requests have been issued for that statement. If so, Oracle moves the session cursor associated with that statement into the session cursor cache. Subsequent parse requests for that statement by the same session are then filled from the session cursor cache, thus avoiding even a soft parse. (Technically, a parse can’t be completely avoided; a “softer” soft parse is done that’s faster and requires less CPU.)
Since a session doesn’t have to go looking in the library cache for previously parsed SQL, caching cursors by session results in less use of the library cache and shared pool latches. These are often points of contention for busy OLTP systems. Cutting down on latch use cuts down on latch waits, providing not only an increase in speed but an increase in scalability.
In the session cursor cache, Oracle manages the cached cursors using a LRU list. Once more than SESSION_CACHED_CURSORS closed cursors are cached, Oracle starts dropping cached cursors off the LRU end of the list whenever it needs to make room to cache a new cursor.
用以下脚本监控单个session的解析情况.我们可以看到"softer" soft parse 是怎么产生的
–To show sql’s parse infomations by a session when session_cached_cursors parameter has been set–
col name for a30
select a.value,b.name,s.sid, s.serial#
from v$sesstat a, v$statname b, vsession s where a.statistic# = b.statistic# and s.sid=a.sid and b.name in ('opened cursors current','parse count (hard)','parse count (total)','session cursor cache count','session cursor cache hits') and a.sid=&sid / 解释一下vsesstat中相关取值的重要概念
1 > opened cursors current (当前单个session打开的cursors数量)
2 > parse count (hard) (当前session硬解析次数)
3 > parse count (total) (当前session解析总次数)
4 > session cursor cache count (设置session_cached_cursors参数后,"相同"sql被解析3次后被cache在session cursor cache中的数量
5 > session cursor cache hits (软软解析的次数 a “softer” soft parse )
6 > 手工计算一下软解析次数parse count (total) - session cursor cache hits - parse count (hard) = parse count(soft)
Ok 开始正式给出测试步骤
开启两个session
在Session B中监控session A每一步运行情况

SESSION A :
03:27:39 SQL> alter session set session_cached_cursors=100;
会话已更改。
03:28:04 SQL> show parameter cursor
NAME TYPE VALUE

cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 100
03:28:32 SQL> show user
USER 为 “LYON”
03:28:40 SQL> select distinct sid from v$mystat;
SID

80
SESSION B:
在没有运行sql前,SID为93的session相关sql解析信息
03:27:17 SQL> --To show sql’s parse infomations by a session when session_cached
_cursors parameter has been set–
03:31:35 SQL> col name for a30
03:31:35 SQL> select a.value,b.name,s.sid, s.serial#
03:31:35 2 from v$sesstat a, v$statname b, v$session s
03:31:35 3 where a.statistic# = b.statistic#
03:31:35 4 and s.sid=a.sid
03:31:35 5 and b.name in (‘opened cursors current’,‘parse count (hard)’,‘pa
rse count (total)’,‘session cursor cache count’,‘session cursor cache hits’)
03:31:35 6 and a.sid=&sid
03:31:35 7 /
输入 sid 的值: 80
原值 6: and a.sid=&sid
新值 6: and a.sid=80
VALUE NAME SID SERIAL#

1 opened cursors current 80 3
159 session cursor cache hits 80 3
58 session cursor cache count 80 3
184 parse count (total) 80 3
24 parse count (hard) 80 3
SESSION A :
( test表由create table test as select * from dba_objects;在此试验前已经创建 )

session A中运行一个简单的sql语句
select count() from test where object_id=200;
03:30:01 SQL> select count() from test where object_id=200;
COUNT(*)

0

SESSION B:
03:34:33 SQL> /
输入 sid 的值: 80
原值 6: and a.sid=&sid
新值 6: and a.sid=80
VALUE NAME SID SERIAL#

1 opened cursors current 80 3
159 session cursor cache hits 80 3
58 session cursor cache count 80 3
186 parse count (total) 80 3
25 parse count (hard) 80 3

第一次执行select count(*) from test where object_id=50000;
Oracle执行一次hard parse .
相关数据字典视图操作导致一次软分析
parse count (total) 由184增加到186

第二次执行相同sql
SESSION A:
03:37:23 SQL> /
COUNT(*)

1

SESSION B:
03:36:57 SQL> /
输入 sid 的值: 80
原值 6: and a.sid=&sid
新值 6: and a.sid=80
VALUE NAME SID SERIAL#

1 opened cursors current 80 3
159 session cursor cache hits 80 3
58 session cursor cache count 80 3
187 parse count (total) 80 3
25 parse count (hard) 80 3
03:37:45 SQL>
Oracle内部执行一次软分析

第三次执行相同sql
SESSION A
03:37:25 SQL> /
COUNT(*)

1
03:39:48 SQL>
SESSION B
03:37:45 SQL> /
输入 sid 的值: 80
原值 6: and a.sid=&sid
新值 6: and a.sid=80
VALUE NAME SID SERIAL#

1 opened cursors current 80 3
159 session cursor cache hits 80 3
60 session cursor cache count 80 3
188 parse count (total) 80 3
25 parse count (hard) 80 3
03:40:04 SQL>
Oracle内部执行一次软分析 同时由于相同sql被执行三次
所以session cursor cache count加1 (表示此sql已经被cache到了session的session cursor cache中)由于内部相关数据字典操作sql也被执行了3次所以session cursor cache count再加1
第四次执行相同sql
SESSION A
03:39:48 SQL> /
COUNT(*)

1
03:40:36 SQL>
SESSION B
03:40:04 SQL> /
输入 sid 的值: 80
原值 6: and a.sid=&sid
新值 6: and a.sid=80
VALUE NAME SID SERIAL#

1 opened cursors current 80 3
160 session cursor cache hits 80 3
60 session cursor cache count 80 3
189 parse count (total) 80 3
25 parse count (hard) 80 3
03:40:53 SQL>
Oracle内部执行一次"softer" soft parse
我们看到session cursor cache hits加1

第五次执行相同sql
SESSION A
03:40:36 SQL> /
COUNT(*)

1
03:41:12 SQL>
SESSION B
03:40:53 SQL> /
输入 sid 的值: 80
原值 6: and a.sid=&sid
新值 6: and a.sid=80
VALUE NAME SID SERIAL#

1 opened cursors current 80 3
161 session cursor cache hits 80 3
60 session cursor cache count 80 3
190 parse count (total) 80 3
25 parse count (hard) 80 3
03:41:44 SQL>
Oracle内部执行一次"softer" soft parse
至此我们可以看到Oracle使用"softer" soft parse代替soft parse.

oracle session_cached_cursors 与 open_cursors参数详解及配置语句
0.原则
正确设置open_cursors和’session_cached_cursors’ 可以减少sql解析，提高系统性能，那么，如何正确设置’session_cached_cursors’ 这个参数呢？我们可以把握下面的原则：
1、‘session_cached_cursors’ 数量要小于open_cursor
2、要考虑共享池的大小
3、使用下面的sql判断’session_cached_cursors’ 的使用情况。如果使用率为100%则增大这个参数值。
1.查询是否需要修改

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 vparameter 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 vstatname n, vsesstat s where n.name in ('opened cursors current', 'session cursor cache count') and s.statistic# = n.statistic# group by s.sid), (select value from vparameter where name = ‘open_cursors’)

结果如图：

2.修改方法
alter system set session_cached_cursors = 100;
alter system set open_cursors = 500;
ps
出现ora-02096错误使用以下方法：

alter system set backup_tape_io_slaves = false;
会报相应的错误, 因为backup_tape_io_slaves属性issys_modifiable的值为deferred;
alter system set backup_tape_io_slaves = false deferred;
加上deferred就不会报错了, 但是表示这次修改对当前会话不发生作用, 在以后打开的会话中起作用, 故它有"推迟"影响的效果.