关于优化oracle 中LRU算法和 cours有关的探讨

一、简介
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 81920
opened cursors current 256
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中找到的次数，所占比例越高，性能越好。如果比例比较低，并且有剩余内存的话，可以考虑加大该参数。

10G上缺省是10。

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更高的性能.

二、关于session_cached_cursors 修改方法

SQL>alter session set session_cached_cursors=1 scope=spfile;


敬告：注意修改之前一定要谨慎！！！！


三、和session_cached_cursors 有关的测试例子

在看之前先看看与之相关的视图v$open_cursor

SQL> desc v$open_cursor
Name                                                  Null?    Type
----------------------------------------------------- -------- ------------------------------------
SADDR                                                          RAW(4)
SID                                                            NUMBER
USER_NAME                                                      VARCHAR2(30)
ADDRESS                                                        RAW(4)
HASH_VALUE                                                     NUMBER
SQL_TEXT                                                       VARCHAR2(60)

当我们执行一条sql语句的时候,我们将会在shared pool产生一个library cache object,cursor就是其中针对于sql语句的一种library cache object.另外我们会在pga有一个cursor的拷贝,同时在客户端会有一个statement handle,这些都被称为cursor,在v$open_cursor里面我们可以看到当前打开的cursor和pga内cached cursor.

session_cached_cursor
这个参数限制了在pga内session cursor cache list的长度,session cursor cache list是一条双向的lru链表,当一个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更高的性能.

下面做个实验来证明以上这些理

SQL> select distinct sid from v$mystat;

       SID
----------
        15


SQL> show parameter session_cached_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors               integer     0

这是设置session_cached_cursors为0,禁止了cache cursor的功能

我们另开一个sqlplus来看v$open_cursor

SQL> select * from v$open_cursor where sid=12;

SADDR           SID USER_NAME                      ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------ -------- ---------- ------------------------------------------------------------
95D5AFCC         12 TAOBAO                         99DA7F20  745629022 select distinct sid from v$mystat

能在v$open_cursor里看到这条sql

执行下一条语句
SQL> select owner from test where rownum=1;

OWNER
------------------------------
SYS

在来看v$open_cursor有什么变化

SQL> /

SADDR           SID USER_NAME                      ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------ -------- ---------- ------------------------------------------------------------
95D5AFCC         12 TAOBAO                         99C9AEF8  608211958 select owner from test where rownum=1

可以看到语句变了,来看看current open cursor和session cached cursor的数目
SQL>  select sid,n.name,s.value  used
  2   from
  3     sys.v_$statname  n,
  4     sys.v_$sesstat  s
  5   where
  6     n.name in ('opened cursors current', 'session cursor cache count') and
  7     s.statistic# = n.statistic#
  8     and sid=12;

       SID NAME                                                                   USED
---------- ---------------------------------------------------------------- ----------
        12 opened cursors current                                                    1
        12 session cursor cache count                                                0

我们来把session_cached_cursors修改成1
SQL> alter session set session_cached_cursors=1;

Session altered.

再执行上面的语句

SQL> select owner from test where rownum=1;

OWNER
------------------------------
SYS

看看v$open_cursor

SQL> select * from v$open_cursor where sid=12;

SADDR           SID USER_NAME                      ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------ -------- ---------- ------------------------------------------------------------
95D5AFCC         15 TAOBAO                         99C9AEF8  608211958 select owner from test where rownum=1



此处执行3次 select * from v$open_cursor where sid=12,oracle进行了3次soft parse
SQL> select owner from test where rownum=1;

OWNER
------------------------------
SYS
SQL> select owner from test where rownum=1;

OWNER
------------------------------
SYSTEM

然后再执行

SQL> select distinct sid from v$mystat;

       SID
----------
        12

再来看看v$open_cursor有什么变化
SQL> /

SADDR           SID USER_NAME                      ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------ -------- ---------- ------------------------------------------------------------
95D5AFCC         12 TAOBAO                         99C9AEF8  608211958 select owner from test where rownum=1
95D5AFCC         12 TAOBAO                         99DA7F20  745629022 select distinct sid from v$mystat

v$open_cursor有了两条记录,再看一下current open cursor和session cached cursor的数目

SQL>  select sid,n.name,s.value  used
  2   from
  3     sys.v_$statname  n,
  4     sys.v_$sesstat  s
  5   where
  6     n.name in ('opened cursors current', 'session cursor cache count') and
  7     s.statistic# = n.statistic#
  8     and sid=12;

       SID NAME                                                                   USED
---------- ---------------------------------------------------------------- ----------
        15 opened cursors current                                                    1
        15 session cursor cache count                                                1
        
果然有一句sql进了session cursor cache,"select owner from test where rownum=1"这个sql相关的cursor就被cache起来了.

再执行一条新的语句
SQL> select owner from test where rownum<2;

OWNER
------------------------------
SYS

看一下v$open_cursor
SQL> select * from v$open_cursor where sid=12;

SADDR           SID USER_NAME                      ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------ -------- ---------- ------------------------------------------------------------
95D5AFCC         15 TAOBAO                         99CA4FFC 2584499260 select owner from test where rownum<2
95D5AFCC         15 TAOBAO                         99C9AEF8  608211958 select owner from test where rownum=1

可以看到上一次的"select distinct sid from v$mystat"已经被关闭,在v$open_cursor中不可见,但是cached cursor "select owner from test where rownum=1"还能看到

这时候运行3次select distinct sid from v$mystat
SQL> select distinct sid from v$mystat;

       SID
----------
        15
SQL> select distinct sid from v$mystat;

       SID
----------
        15
SQL> select distinct sid from v$mystat;

       SID
----------
        15
        
然后再运行select owner from test where rownum<2

SQL>  select owner from test where rownum<2;

OWNER
------------------------------
SYSTEM


再去查看v$open_cursor

SQL> select * from v$open_cursor where sid=12;

SADDR           SID USER_NAME                      ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------ -------- ---------- ------------------------------------------------------------
95D5AFCC         15 TAOBAO                         99C6C628 1755089113 select owner from test where rownum<2
95D5AFCC         15 TAOBAO                         99DA7F20  745629022 select distinct sid from v$mystat

发现原来cached cursor"select owner from test where rownum=1"已经被替换出了session cursor cache,而变成了"select distinct sid from v$mystat"

我们再修改session_cached_cursors

alter session set session_cached_cursors=2;

再重复一下上面的过程,再去看v$open_cursor

SQL> /

SADDR           SID USER_NAME                      ADDRESS  HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------ -------- ---------- ------------------------------------------------------------
95D5AFCC         12 TAOBAO                         99CA4FFC 2584499260 select owner from test where rownum<2
95D5AFCC         12 TAOBAO                         99C9AEF8  608211958 select owner from test where rownum=1
95D5AFCC         12 TAOBAO                         99DA7F20  745629022 select distinct sid from v$mystat

可以看到现在有3个cursor,看一下current open cursor和session cached cursor的数目


SQL>  select sid,n.name,s.value  used
  2   from
  3     sys.v_$statname  n,
  4     sys.v_$sesstat  s
  5   where
  6     n.name in ('opened cursors current', 'session cursor cache count') and
  7     s.statistic# = n.statistic#
  8     and sid=12;

       SID NAME                                                                   USED
---------- ---------------------------------------------------------------- ----------
        12 opened cursors current                                                    1
        12 session cursor cache count                                                2
        
这下有2个cached cursor,一个current open cursor.

从这个实验我们可以清楚得看到v$open_cursor视图和session_cached_cursors参数的作用.

 

很不错的文章

附加一段来自AskTOM的关于parse的解释。

o HARD parse -- the query has never been seen before, isn't in the shared pool.  
We must parse it, hash it, look in the shared pool for it, don't find it,
security check it, optimize it, etc (lots of work).

o SOFT parse -- the query has been seen before, is in the shared poo.  We have
to parse it, hash it, look in the shared pool for it and find it (less work then
a hard parse but work none the less)

o a kinder, softer SOFT parse -- you are using session_cached_cursors (search
this site for that word for info).  We take your query, look in the sessions
cursor cache -- find the cursor and reuse it.  Very very little work.

So, after "startup", you go into sqlplus and do this:

SQL> alter session set session_cached_cursors=0; -- just making sure this is off
SQL> select * from emp;  -- hard parse
SQL> select * from emp; -- soft parse
SQL> select * from emp; -- soft parse
SQL> alter session set session_cached_cursors=100; -- enable this feature
SQL> select * from emp;  -- soft parse
SQL> select * from emp; -- kinder, gentler, soft parse

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

转载于:http://blog.itpub.net/14876437/viewspace-561427/