ITPub博客

首页 > Linux操作系统 > Linux操作系统 > session_cached_cursors的研究

session_cached_cursors的研究

原创 Linux操作系统 作者:oracle_ace 时间:2008-01-25 18:00:15 0 删除 编辑

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, v$session 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

/

解释一下v$sesstat中相关取值的重要概念
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.

转载:http://foreverlee.itpub.net/index.php

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12361284/viewspace-160127/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-10

  • 博文量
    284
  • 访问量
    796861