Oracle性能提升：session_cached_cursor和open_cursors参数

IT那活儿 2024-10-28

1326

点击上方“IT那活儿”公众号--专注于企业全栈运维技术分享，不管IT什么活儿，干就完了！！！

一

open_cursors参数

1.1 参数open_cursors概况

open_cursors 设定每个 session（会话）最多能同时打开多少个 cursor（游标）。

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

作用是让后续相同的 sql 语句不在打开游标，从而避免软解析过程来提供应用程序的效率。

1.2 参数open_cursors大小

查询当前数据库设定的值：

SQL> show parameter open_cursors

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 300

修改该参数：

SQL> alter system set open_cursors =1000;

System altered

该参数为动态参数，修改后立即生效。

1.3 参数open_cursors应该设置多大

通过以下语句查询：

SQL> SELECT MAX(A.VALUE) AS HIGHEST_OPEN_CUR, P.VALUE AS MAX_OPEN_CUR
2    FROM V$SESSTAT A, V$STATNAME B, V$PARAMETER P
3   WHERE A.STATISTIC# = B.STATISTIC#
4     AND B.NAME = 'opened cursors current'
5     AND P.NAME = 'open_cursors'
6   GROUP BY P.VALUE;

HIGHEST_OPEN_CUR MAX_OPEN_CUR
---------------- --------------------------------------------------------------------------------
43 300

字段含义：

HIGHEST_OPEN_CUR 是实际打开的 cursors 的最大值；
MAXOPEN CUR 是参数 Open_cursors 的设定值。

如果二者太接近，甚至触发 ORA-01000 错误，那么你就应该调大参数 open_cursors 的设定值。

如果问题依旧没有解决，盲目增大 open_cursors 也是不对的，这个时候你得检查应用程序的代码是否合理，比如说应用程序是否打开了游标，却没有在它完成工作后没有及时关闭。

二

session_cached_cursor参数

2.1 参数session_cached_cursor概况

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的个数。这个值越大，则会消耗的内存越多。

2.2 参数session_cached_cursor大小和修改

查询当前数据库设定的值：

SQL> show parameter session_cached_cursor

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 50

修改该参数：

SQL> alter system set session_cached_cursors=100 scope=spfile;

System altered

该参数为静态参数，调整后要重启库才生效。

2.3 参数session_cached_cursors应该设置多大

检查这个参数是否设置的合理，可以从两个statistic来检查。

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

NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 50946
opened cursors current 72
pinned cursors current 17
session cursor cache hits 44279
session cursor cache count 3173
cursor reload failures 11
cursor authentications 809

7 rows selected

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

NAME VALUE
---------------------------------------------------------------- ----------
ADG parselock X get attempts 0
ADG parselock X get successes 0
parse time cpu 996
parse time elapsed 2246
parse count (total) 20356
parse count (hard) 3340
parse count (failures) 17
parse count (describe) 14

8 rows selected

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

当一个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更高的性能。

问

open_cursors与session_cached_cursor有什么关系？

若要理解参数open_cursors和session_cached_cursor的关系，最好先弄清楚oracle是如何执行每个sql语句的：

通过分析上图我们可以得出：

a、两个参数之间没有任何关系，相互也不会有任何影响。
b、两个参数有着相同的作用：让后续相同的sql语句不在打开游标，从而避免软解析过程来提供应用程序的效率。

2.4 session_cached_cursors与绑定变量

在Oracle中存在两种类型的SQL语句，一类为DDL语句，不共享使用，也就是每次执行都需要进行硬解析。还有一类就是DML语句，会进行硬解析或软解析。

硬解析变成软解析：绑定变量；
软解析变成软软解析：设置session_cached_cursors。

session_cached_cursors参数的设置：

SQL> select a.name,b.value from v$statname a,v$sesstat  b where a.statistic#=b.statistic# and a.name in('session cursor cache hits','session cursor cache count','parse count (total)') and b.sid=(select c.sid from v$mystat c where rownum=1);

NAME VALUE
---------------------------------------------------------------- ----------
session cursor cache hits 40
session cursor cache count 16
parse count (total) 65

session cursor cache count表示指定会话缓存的游标数，session_cached_cursors参数是系统当前每个会话最多能缓存的游标数。
session cursor cache count小于session_cached_cursors，不用增加。session_cached_cursors大小。如相等，则有可能需要增加。
session cursor cache hits 表示从UGA中命中的次数--软软解析次数。
parse count (total)指定会话的总解析次数。
如果session cursor cache hits接近parsecount (total)，无需调整session_cached_cursors。
如果session cursor cache hits远小于parsecount(total)，则可能需要调整session_cached_cursors。
session_cached_cursors对所有会话生效，如果需要调优的会话占所有会话比例很小，调整意义不大。
绑定变量可以提高SQL执行效率，但也会出现由于一些原因，比如bind graduation导致的bind-mismatch,即产生N多无法共享的子游标。
子游标过多会对SQL parse有影响。
针对一些特别的表或者查询列特别多的SQL,可以通过给字符串变量绑定固定的长度，如to_char(4000),来避免因为bind graduation导致child cursor过多的问题．
例如：对于字符类型的字段，进行绑定变量的时候，第一次会使用32字节的BUFFER，如果该值小于32字节的话，第二次执行这个SQL的时候，如果小于 32字节，那么可以共享这个CURSOR,如果大于，就无法共享，原因就是BIND_MISMATCH，此时会产生一个子CURSOR，同时分配128字节的BIND BUFFER，以此类推。

可以通过v$sql_bind_capture视图查看一下每次绑定变量的值：

SQL> select position,LAST_CAPTURED,datatype_string,value_string from v$sql_bind_capture where sql_id='&sql_id' and rownum<50;

POSITION LAST_CAPTURED DATATYPE_STRING VALUE_STRING---------- ------------- ------------------------------------------------------------ --------------------------------------------------------------------------------

三

关于cursor_sharing

根据oracle官方建议：

在11g中不推荐使用cursor_sharing=SIMILAR,其实在所有版本中都不推荐,设置为该值很容易导致高版本问题.而且该值会出现莫名其妙的,无法解释的高版本问题.而且根据oracle相关文档,在即将发布的12c版本中,将除掉SIMILAR值.对于客户库的该问题,因为很多sql未绑定参数,为了减少硬解析,建议在业务低谷时设置cursor_sharing=FORCE,并刷新sharedpool.

如果cursor_sharing 参数是设置为similar的，这样会将SQL 中的谓词值自动用变量来代替。这样会增加cursor的数量。

为了减少cursor对library cache的占用，先将cursor_shring 参数改成了默认的exact模式。

这样version_count 会减少很多，但是硬解析的次数也会增加，可能会增加Library Cache Latch等待。

END

本文作者：胡伟(上海新炬中北团队）

本文来源：“IT那活儿”公众号

文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。