SESSION_CACHED_CURSORS 参数

来源：互联网发布：我在明朝当王爷网络剧编辑：程序博客网时间：2024/05/21 22:21

SESSION_CACHED_CURSORS,就是说的是一个session可以缓存多少个cursor，让后续相同的SQL语句不再打开游标,从而避免软解析的过程来提高性能。(绑定变量是解决硬解析的问题)，软解析同硬解析一样,比较消耗资源.所以这个参数非常重要。

oracle有一个概念，那就是session cursor cache,中文描述就是有一块内存区域，用来存储关闭了的cursor。当一个cursor关闭之后，oracle会检查这个cursor的request次数是否超过3次，如果超过了三次，就会放入session cursor cache，这样在下次parse的时候，就可以从session cursor cache中找到这个statement, session cursor cache的管理也是使用LRU。
session_cached_cursors这个参数是控制session cursor cache的大小的。session_cached_cursors定义了session cursor cache中存储的cursor的个数。这个值越大，则会消耗的内存越多。
另外检查这个参数是否设置的合理，可以从两个statistic来检查。

SQL> select name,value from v$sysstat where name like ''%cursor%'';

NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 16439
opened cursors current 55
session cursor cache hits 8944
session cursor cache count 101
cursor authentications 353

SQL> select name,value from v$sysstat where name like ''%parse%'';

NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 0
parse time elapsed 0
parse count (total) 17211
parse count (hard) 1128
parse count (failures) 2

session cursor cache hits 和parse count（total）就是总的parse次数中，在session cursor cache中找到的次数，所占比例越高，性能越好。如果比例比较低，并且有剩余内存的话，可以考虑加大该参数。

Oracle 9i及以前,该参数缺省是0,10G上缺省是20。

open_cursors 是充许打开的游标的数量
session_cached_cursors 是充许放入缓存的游标的数量

当一个session打算关闭一个cursor时,如果这个cursor的parse count超过3次,那么这个cursor将会被加到session cursor cache list的MRU端.当一个session打算parse一个sql时,它会先去pga内搜索session cursor cache list,如果找到那么会把这个cursor脱离list,然后当关闭的时候再把这个cursor加到MRU端. session_cached_cursor提供了快速软分析的功能,提供了比soft parse更高的性能.

----------------------------------------------

这个参数本身不难理解，但是和这个参赛相关的很多话题值得我们深思：

1.cursor open之后oracle做了什么

2.cursor close之后oracle做了什么，资源真的释放了?其实没有，ursor被关闭之后，无非是它占用的资源可以被重复使用了而已

3.cursor被cache之后在uga中保存了什么

4.session和library cache以及uga是如何关联的...

要了解这些东西，需要对oracle的内存结构有清晰的了解...目前一团乱麻...

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 30000
session_cached_cursors integer 0
--验证是否设置了参数session_cached_cursors,重点关注
session cursor cache count，session cursor cache hits的变化

SQL> select a.value, s.username, s.sid, s.serial#,b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'opened cursors current',
6 'session cursor cache count',
7 'session cursor cache hits',
8 'parse count (total)',
9 'session uga memory',
10 'session uga memory max',
11 'session pga memory',
12 'session pga memory max'
13 )
14 and s.sid=159
15 order by name;

VALUE USERNAME SID SERIAL# NAME
---------- ---------- ---------- ---------- ------------------------------
4 SYS 159 3 opened cursors current
2145 SYS 159 3 parse count (total)
0 SYS 159 3 session cursor cache count
0 SYS 159 3 session cursor cache hits
1564244 SYS 159 3 session pga memory
2481748 SYS 159 3 session pga memory max
679992 SYS 159 3 session uga memory
844952 SYS 159 3 session uga memory max

已选择8行。
SQL> select count(*) from tt;

COUNT(*)
----------
11546

SQL> select count(*) from tt;

COUNT(*)
----------
11546

SQL> select parse_calls from v$sql where sql_text='select count(*) from tt';

PARSE_CALLS
-----------
2
--执行了2次 select count(*) from tt，产生了2次parse，一次harse parse，一次soft parse，
同时sql在执行之后确确实实cursor被关闭了，因为在v$open_cursor我们找不到刚刚执行过的sql：
select count(*) from tt
SQL> select sql_text from v$open_cursor where sid=159;

会话已更改。

SQL> select count(*) from tt;

COUNT(*)
----------
11546

SQL> select parse_calls from v$sql where sql_text='select count(*) from tt';

PARSE_CALLS
-----------
3
--sql在执行之后oracle会试图关闭cursor，关闭之前会检查是否设置了参数session_cached_cursors，
如果设置了，那么还会判断即将要关闭的cursor对用的sql的parse_calls次数，如果达到3次(注意包括3次)，
那么此时oracle不会关闭cursor了，而是把cursor相关的信息放到该session的uga中保存起来，以便该session下次
执行相同的sql而不需要重新产生soft parse，因为该sql的cursor信息直接可以从uga中找到，避免soft parse最终节约的资源
当然是cpu同时也减少了library cache latch事件的等待...
oracle把这种parse称为soft soft parse(softer parse)
这么这里uga中到底保存了cursor的什么信息，不得而知...我想无非是一些cursor的address信息而已，或者说
就是一些内存的地址，而绝对不会是sql的解析信息，sql的解析信息依然在library cache中...这个我们可以通过
dump library cache而获得...
SQL> select sql_text from v$open_cursor where sid=159;

SQL_TEXT
------------------------------------------------------------
select count(*) from tt
select ENQUEUE_ENABLED, DEQUEUE_ENABLED from dba_queues wher
select sql_text from v$open_cursor where sid=159
select wrm.last_ash_sample_id from WRM$_DATABASE_INSTANCE

VALUE USERNAME SID SERIAL# NAME
---------- ---------- ---------- ---------- ------------------------------
4 SYS 159 3 opened cursors current
2189 SYS 159 3 parse count (total)
2 SYS 159 3 session cursor cache count
7 SYS 159 3 session cursor cache hits
1171028 SYS 159 3 session pga memory
2481748 SYS 159 3 session pga memory max
679992 SYS 159 3 session uga memory
1486168 SYS 159 3 session uga memory max

已选择8行。
--设置了session_cached_cursors之后，很显然select count(*) from tt在执行之后被cache了，
同时cursor也处于打开状态，意味着cursor所占用的资源暂时不能被覆盖了...
同时session cursor cache count和session cursor cache hits的值都增加了，
session cursor cache hits表示uga中cached cursor被击中的次数，也就是说
是softer parse的次数，我们希望session cursor cache hits/parse count (total)的比例越大越好，说明
参数session_cached_cursors的设置发挥了作用
SQL> select count(*) from tt;

COUNT(*)
----------
11546

SQL> select parse_calls from v$sql where sql_text='select count(*) from tt';

PARSE_CALLS
-----------
4

VALUE USERNAME SID SERIAL# NAME
---------- ---------- ---------- ---------- ------------------------------
4 SYS 159 3 opened cursors current
2192 SYS 159 3 parse count (total)
4 SYS 159 3 session cursor cache count
8 SYS 159 3 session cursor cache hits
1171028 SYS 159 3 session pga memory
2481748 SYS 159 3 session pga memory max
679992 SYS 159 3 session uga memory
1486168 SYS 159 3 session uga memory max

已选择8行。

SQL> alter session set session_cached_cursors=0;

会话已更改。

VALUE USERNAME SID SERIAL# NAME
---------- ---------- ---------- ---------- ------------------------------
4 SYS 159 3 opened cursors current
2194 SYS 159 3 parse count (total)
5 SYS 159 3 session cursor cache count
8 SYS 159 3 session cursor cache hits
1171028 SYS 159 3 session pga memory
2481748 SYS 159 3 session pga memory max
679992 SYS 159 3 session uga memory
1486168 SYS 159 3 session uga memory max

已选择8行。

SQL> alter session set session_cached_cursors=0;

会话已更改。

已选择8行。

SQL> select count(*) from tt;

COUNT(*)
----------
11546

SQL> select sql_text from v$open_cursor where sid=159;

SQL_TEXT
------------------------------------------------------------
select ENQUEUE_ENABLED, DEQUEUE_ENABLED from dba_queues wher
select sql_text from v$open_cursor where sid=159
select wrm.last_ash_sample_id from WRM$_DATABASE_INSTANCE
--屏蔽session_cached_cursors之后，再次执行select count(*) from tt，
执行之后发现cursor被关闭了，同时session cursor cache count和session cursor cache hits的值不再变化
SQL> select a.value, s.username, s.sid, s.serial#,b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'opened cursors current',
6 'session cursor cache count',
7 'session cursor cache hits',
8 'parse count (total)',
9 'session uga memory',
10 'session uga memory max',
11 'session pga memory',
12 'session pga memory max'
13 )
14 and s.sid=159
15 order by name;

VALUE USERNAME SID SERIAL# NAME
---------- ---------- ---------- ---------- ------------------------------
4 SYS 159 3 opened cursors current
2197 SYS 159 3 parse count (total)
5 SYS 159 3 session cursor cache count
8 SYS 159 3 session cursor cache hits
1171028 SYS 159 3 session pga memory
2481748 SYS 159 3 session pga memory max
679992 SYS 159 3 session uga memory
1486168 SYS 159 3 session uga memory max

已选择8行。

SQL>
--=========================================
--下面是又一个测试案例，和上面的测试不是一起做的，所以看到的statistics可能会出现一点不连贯
SQL> select a.value, s.username, s.sid, s.serial#,b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'opened cursors current',
6 'session cursor cache count',
7 'session cursor cache hits',
8 'parse count (total)',
9 'session uga memory',
10 'session uga memory max',
11 'session pga memory',
12 'session pga memory max'
13 )
14 and s.sid=159
15 order by name;

VALUE USERNAME SID SERIAL# NAME
---------- ---------- ---------- ---------- ------------------------------
4 SYS 159 3 opened cursors current
2225 SYS 159 3 parse count (total)
1 SYS 159 3 session cursor cache count
6 SYS 159 3 session cursor cache hits
1433172 SYS 159 3 session pga memory
2350676 SYS 159 3 session pga memory max
549064 SYS 159 3 session uga memory
1420704 SYS 159 3 session uga memory max

已选择8行。

SQL> alter session set session_cached_cursors=100000;

会话已更改。

VALUE USERNAME SID SERIAL# NAME
---------- ---------- ---------- ---------- ------------------------------
4 SYS 159 3 opened cursors current
2227 SYS 159 3 parse count (total)
1 SYS 159 3 session cursor cache count
6 SYS 159 3 session cursor cache hits
1039956 SYS 159 3 session pga memory
2350676 SYS 159 3 session pga memory max
549064 SYS 159 3 session uga memory
1420704 SYS 159 3 session uga memory max

已选择8行。

SQL> declare
2 msql varchar2(500);
3 mcur number;
4 mstat number;
5 jg varchar2(4000);
6 begin
7 for j in 1..4 loop
8 for i in 1..100 loop
9 mcur:=dbms_sql.open_cursor;
10 msql:='select object_id from t where object_id='||i;
11 dbms_sql.parse(mcur,msql,dbms_sql.native);
12 dbms_sql.define_column(mcur,1,jg,4000);
13 mstat:=dbms_sql.execute(mcur);
14 dbms_sql.close_cursor(mcur);
15 end loop;
16 end loop;
17 end;
18 /

PL/SQL 过程已成功完成。

VALUE USERNAME SID SERIAL# NAME
---------- ---------- ---------- ---------- ------------------------------
4 SYS 159 3 opened cursors current
3469 SYS 159 3 parse count (total)
115 SYS 159 3 session cursor cache count
934 SYS 159 3 session cursor cache hits
1039956 SYS 159 3 session pga memory
2350676 SYS 159 3 session pga memory max
614528 SYS 159 3 session uga memory
1420704 SYS 159 3 session uga memory max

已选择8行。

SQL>
--对比cursor cache前后的uga memory的值，确实发生了变化,不过这个信息很多时候观察不到
--===============================
SQL> select sql_text from v$open_cursor where sid=159 and sql_text like 'select
object_id from t%'
2 ;

SQL_TEXT
------------------------------------------------------------
select object_id from t where object_id=59
select object_id from t where object_id=29
select object_id from t where object_id=56
select object_id from t where object_id=80
select object_id from t where object_id=39
select object_id from t where object_id=93
select object_id from t where object_id=64
select object_id from t where object_id=81
select object_id from t where object_id=87
select object_id from t where object_id=18
select object_id from t where object_id=25

SQL_TEXT
------------------------------------------------------------
select object_id from t where object_id=27
select object_id from t where object_id=24
select object_id from t where object_id=16
select object_id from t where object_id=40
select object_id from t where object_id=62
select object_id from t where object_id=76
select object_id from t where object_id=26
select object_id from t where object_id=77
select object_id from t where object_id=23
select object_id from t where object_id=95
select object_id from t where object_id=92

SQL_TEXT
------------------------------------------------------------
select object_id from t where object_id=34
select object_id from t where object_id=53
select object_id from t where object_id=86
select object_id from t where object_id=48
select object_id from t where object_id=15
select object_id from t where object_id=65
select object_id from t where object_id=74
select object_id from t where object_id=78
select object_id from t where object_id=89
select object_id from t where object_id=46
select object_id from t where object_id=51

SQL_TEXT
------------------------------------------------------------
select object_id from t where object_id=67
select object_id from t where object_id=69
select object_id from t where object_id=70
select object_id from t where object_id=75
select object_id from t where object_id=96
select object_id from t where object_id=79
select object_id from t where object_id=83
select object_id from t where object_id=85
select object_id from t where object_id=50
select object_id from t where object_id=57
select object_id from t where object_id=61

SQL_TEXT
------------------------------------------------------------
select object_id from t where object_id=94
select object_id from t where object_id=100
select object_id from t where object_id=88
select object_id from t where object_id=12
select object_id from t where object_id=42
select object_id from t where object_id=52
select object_id from t where object_id=20
select object_id from t where object_id=66
select object_id from t where object_id=8
select object_id from t where object_id=71
select object_id from t where object_id=6

SQL_TEXT
------------------------------------------------------------
select object_id from t where object_id=73
select object_id from t where object_id=82
select object_id from t where object_id=1
select object_id from t where object_id=33
select object_id from t where object_id=72
select object_id from t where object_id=99
select object_id from t where object_id=22
select object_id from t where object_id=45
select object_id from t where object_id=55
select object_id from t where object_id=4
select object_id from t where object_id=37

SQL_TEXT
------------------------------------------------------------
select object_id from t where object_id=97
select object_id from t where object_id=10
select object_id from t where object_id=43
select object_id from t where object_id=38
select object_id from t where object_id=68
select object_id from t where object_id=47
select object_id from t where object_id=9
select object_id from t where object_id=31
select object_id from t where object_id=84
select object_id from t where object_id=63
select object_id from t where object_id=49

SQL_TEXT
------------------------------------------------------------
select object_id from t where object_id=32
select object_id from t where object_id=98
select object_id from t where object_id=17
select object_id from t where object_id=54
select object_id from t where object_id=35
select object_id from t where object_id=7
select object_id from t where object_id=21
select object_id from t where object_id=36
select object_id from t where object_id=2
select object_id from t where object_id=3
select object_id from t where object_id=14

SQL_TEXT
------------------------------------------------------------
select object_id from t where object_id=28
select object_id from t where object_id=13
select object_id from t where object_id=91
select object_id from t where object_id=44
select object_id from t where object_id=30
select object_id from t where object_id=58
select object_id from t where object_id=11
select object_id from t where object_id=5
select object_id from t where object_id=41
select object_id from t where object_id=19
select object_id from t where object_id=90

已选择99行。

SQL>
--========================================
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 88081764 bytes
Database Buffers 75497472 bytes
Redo Buffers 2945024 bytes
数据库装载完毕。
数据库已经打开。
SQL> select a.value, s.username, s.sid, s.serial#,b.name
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name in (
5 'opened cursors current',
6 'session cursor cache count',
7 'session cursor cache hits',
8 'parse count (total)',
9 'session uga memory',
10 'session uga memory max',
11 'session pga memory',
12 'session pga memory max'
13 )
14 and s.sid=159
15 order by name;

已选择8行。

SQL> show parameter cursor

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 30000
session_cached_cursors integer 0
SQL> declare
2 msql varchar2(500);
3 mcur number;
4 mstat number;
5 jg varchar2(4000);
6 begin
7 for j in 1..4 loop
8 for i in 1..1000 loop
9 mcur:=dbms_sql.open_cursor;
10 msql:='select object_id from t where object_id='||i;
11 dbms_sql.parse(mcur,msql,dbms_sql.native);
12 dbms_sql.define_column(mcur,1,jg,4000);
13 mstat:=dbms_sql.execute(mcur);
14 dbms_sql.close_cursor(mcur);
15 end loop;
16 end loop;
17 end;
18 /

PL/SQL 过程已成功完成。

SQL> declare
2 msql varchar2(500);
3 mcur number;
4 mstat number;
5 jg varchar2(4000);
6 begin
7 for j in 1..4 loop
8 for i in 1..1000 loop
9 mcur:=dbms_sql.open_cursor;
10 msql:='select object_id from t where object_id='||i;
11 dbms_sql.parse(mcur,msql,dbms_sql.native);
12 dbms_sql.define_column(mcur,1,jg,4000);
13 mstat:=dbms_sql.execute(mcur);
14 dbms_sql.close_cursor(mcur);
15 end loop;
16 end loop;
17 end;
18 /

PL/SQL 过程已成功完成。

VALUE USERNAME SID SERIAL# NAME
---------- ---------- ---------- ---------- ------------------------------
4 SYS 159 3 opened cursors current
10213 SYS 159 3 parse count (total)
0 SYS 159 3 session cursor cache count
0 SYS 159 3 session cursor cache hits
1564244 SYS 159 3 session pga memory
2481748 SYS 159 3 session pga memory max
679992 SYS 159 3 session uga memory
1486168 SYS 159 3 session uga memory max

已选择8行。

PL/SQL 过程已成功完成。

SQL> alter session set session_cached_cursors=2000;

会话已更改。

PL/SQL 过程已成功完成。

VALUE USERNAME SID SERIAL# NAME
---------- ---------- ---------- ---------- ------------------------------
4 SYS 159 3 opened cursors current
18226 SYS 159 3 parse count (total)
2 SYS 159 3 session cursor cache count
2 SYS 159 3 session cursor cache hits
1564244 SYS 159 3 session pga memory
2481748 SYS 159 3 session pga memory max
679992 SYS 159 3 session uga memory
1486168 SYS 159 3 session uga memory max

已选择8行。

SQL>
--让人不解的事出现了，为什么for i in 1..100中循环100次cursor进行了cache，而改为1000之后cursor不
cache了？