首页 > 代码库 > 关于open_cursors和session_cached_cursors的参数值(转)

关于open_cursors和session_cached_cursors的参数值(转)

2024-07-29 22:34:00   225人阅读

一、如何正确设置session_cached_cursors参数：

正确设置open_cursors和session_cached_cursors 可以减少sql解析，提高系统性能，那么，如何正确设置session_cached_cursors这个参数呢？我们可以把握下面的原则：

1、session_cached_cursors 数量要小于open_cursor，设置方法：

 

[html] view plaincopy
 
  1. SQL> show parameter cursors  
  2. NAME                                 TYPE        VALUE  
  3. ------------------------------------ ----------- -----------------------  
  4. open_cursors                         integer     300  
  5. session_cached_cursors               integer     20  
  6. SQL>  
  7. alter system set open_cursors=1500 scope=both;  
  8. alter system set session_cached_cursors=1000 scope=spfile; （初始化文件使用了spfile）  

 

open_cursors:该参数含义是同一个session同时打开最多在使用的游标数。在Oracle10.2.0.1.0版本中默认为300。

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

在oracle 9.2.0.1.0里修改会报下面的报，这是9i版本的一个bug，可在先在init文件里改好，再修改spfile文件。

[html] view plaincopy
 
  1. SQL> alter system set session_cached_cursors=20 scope=spfile;  
  2. ORA-02096: 此选项的指定初始化参数不可修改  

 

2、要考虑共享池的大小

3、使用下面的sql判断session_cached_cursors的使用情况。如果使用率为100%则增大这个参数值。

 

[html] view plaincopy
 
  1. Select ‘session_cached_cursors‘ Parameter,  
  2.     Lpad(Value, 5) Value,  
  3.     Decode(Value, 0, ‘ n/a‘, To_Char(100 * Used / Value, ‘990‘) || ‘%‘) Usage  
  4. From (Select Max(s.Value) Used  
  5.       From V$statname n, V$sesstat s  
  6.      Where n.Name = ‘session cursor cache count‘  
  7.       And s.Statistic# = n.Statistic#),  
  8.     (Select Value From V$parameter Where Name = ‘session_cached_cursors‘)  
  9. Union All  
  10. Select ‘open_cursors‘,  
  11.     Lpad(Value, 5),  
  12.     To_Char(100 * Used / Value, ‘990‘) || ‘%‘  
  13. From (Select Max(Sum(s.Value)) Used  
  14.       From V$statname n, V$sesstat s  
  15.      Where n.Name In  
  16.         (‘opened cursors current‘, ‘session cursor cache count‘)  
  17.       And s.Statistic# = n.Statistic#  
  18.      Group By s.Sid),  
  19.     (Select Value From V$parameter Where Name = ‘open_cursors‘);  

 

 

二、下面是一篇关于v$open_cursor与session_cached_cursor文件章，引用一下：

v$open_cursor与session_cached_cursor

v$open_cursor是oracle提供的一张查看每个session打开过的cursor的视图,它的表结构如下

[html] view plaincopy
 
  1. SQL> desc v$open_cursor  
  2. Name                                                  Null?    Type  
  3. ----------------------------------------------------- -------- ------------------------------------  
  4. SADDR                                                          RAW(4)  
  5. SID                                                            NUMBER  
  6. USER_NAME                                                      VARCHAR2(30)  
  7. ADDRESS                                                        RAW(4)  
  8. HASH_VALUE                                                     NUMBER  
  9. 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更高的性能.

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

 

[html] view plaincopy
 
  1. SQL> select distinct sid from v$mystat;  
  2.        SID  
  3. ----------  
  4.         12  
  5.   
  6. SQL> show parameter session_cached_cursors  
  7. NAME                                 TYPE        VALUE  
  8. ------------------------------------ ----------- ------------------------------  
  9. session_cached_cursors               integer     0  

 

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

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

 

[html] view plaincopy
 
  1. SQL> select * from v$open_cursor where sid=12;  
  2. SADDR           SID USER_NAME                      ADDRESS HASH_VALUE SQL_TEXT  
  3. -------- ---------- ------------------------------ -------- ---------- ------------------------------------------------------------  
  4. 95D5AFCC         12 TAOBAO                         99DA7F20 745629022 select distinct sid from v$mystat  

能在v$open_cursor里看到这条sql

 

执行下一条语句

[html] view plaincopy
 
  1. SQL> select owner from test where rownum=1;  
  2. OWNER  
  3. ------------------------------  
  4. SYS  

在来看v$open_cursor有什么变化

 

SQL> /

 

[html] view plaincopy
 
  1. SADDR           SID USER_NAME                      ADDRESS HASH_VALUE SQL_TEXT  
  2. -------- ---------- ------------------------------ -------- ---------- ------------------------------------------------------------  
  3. 95D5AFCC         12 TAOBAO                         99C9AEF8 608211958 select owner from test where rownum=1  

 

可以看到语句变了,来看看current open cursor和session cached cursor的数目

[html] view plaincopy
 
  1. SQL> select sid,n.name,s.value used  
  2. 2   from  
  3. 3     sys.v_$statname n,  
  4. 4     sys.v_$sesstat s  
  5. 5   where  
  6. 6     n.name in (‘opened cursors current‘, ‘session cursor cache count‘) and  
  7. 7     s.statistic# = n.statistic#  
  8. 8     and sid=12;  
  9.        SID NAME                                                                   USED  
  10. ---------- ---------------------------------------------------------------- ----------  
  11.         12 opened cursors current                                                    1  
  12.         12 session cursor cache count                                                0  
我们来把session_cached_cursors修改成1

 

 

[html] view plaincopy
 
  1. SQL> alter session set session_cached_cursors=1;  
  2. Session altered.  

 

再执行上面的语句

 

[html] view plaincopy
 
  1. SQL> select owner from test where rownum=1;  
  2. OWNER  
  3. ------------------------------  
  4. SYS  

 

看看v$open_cursor

 

[html] view plaincopy
 
  1. SQL> select * from v$open_cursor where sid=12;  
  2. SADDR           SID USER_NAME                      ADDRESS HASH_VALUE SQL_TEXT  
  3. -------- ---------- ------------------------------ -------- ---------- ------------------------------------------------------------  
  4. 95D5AFCC         12 TAOBAO                         99C9AEF8 608211958 select owner from test where rownum=1  

 

此处执行3次 select * from v$open_cursor where sid=12,oracle进行了3次soft parse

[html] view plaincopy
 
  1. SQL> select owner from test where rownum=1;  
  2. OWNER  
  3. ------------------------------  
  4. SYS  
  5. SQL> select owner from test where rownum=1;  
  6. OWNER  
  7. ------------------------------  
  8. SYS  

然后再执行

 

 

[html] view plaincopy
 
  1. SQL> select distinct sid from v$mystat;  
  2.        SID  
  3. ----------  
  4.         12  

 

再来看看v$open_cursor有什么变化

[html] view plaincopy
 
  1. SQL> /  
  2. SADDR           SID USER_NAME                      ADDRESS HASH_VALUE SQL_TEXT  
  3. -------- ---------- ------------------------------ -------- ---------- ------------------------------------------------------------  
  4. 95D5AFCC         12 TAOBAO                         99C9AEF8 608211958 select owner from test where rownum=1  
  5. 95D5AFCC         12 TAOBAO                         99DA7F20 745629022 select distinct sid from v$mystat  

 

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

 

[html] view plaincopy
 
  1. SQL> select sid,n.name,s.value used  
  2. 2   from  
  3. 3     sys.v_$statname n,  
  4. 4     sys.v_$sesstat s  
  5. 5   where  
  6. 6     n.name in (‘opened cursors current‘, ‘session cursor cache count‘) and  
  7. 7     s.statistic# = n.statistic#  
  8. 8     and sid=12;  
  9.        SID NAME                                                                   USED  
  10. ---------- ---------------------------------------------------------------- ----------  
  11.         12 opened cursors current                                                    1  
  12.         12 session cursor cache count                                                1  

 

果然有一句sql进了session cursor cache,"select owner from test where rownum=1"这个sql相关的cursor就被cache起来了.

再执行一条新的语句

[html] view plaincopy
 
  1. SQL> select owner from test where rownum<2;  
  2. OWNER  
  3. ------------------------------  
  4. SYS  

 

看一下v$open_cursor

[html] view plaincopy
 
  1. SQL> select * from v$open_cursor where sid=12;  
  2. SADDR           SID USER_NAME                      ADDRESS HASH_VALUE SQL_TEXT  
  3. -------- ---------- ------------------------------ -------- ---------- ------------------------------------------------------------  
  4. 95D5AFCC         12 TAOBAO                         99CA4FFC 2584499260 select owner from test where rownum<2  
  5. 95D5AFCC         12 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

 

[html] view plaincopy
 
  1. SQL> select distinct sid from v$mystat;  
  2.        SID  
  3. ----------  
  4.         12  
  5. SQL> select distinct sid from v$mystat;  
  6.        SID  
  7. ----------  
  8.         12  
  9. SQL> select distinct sid from v$mystat;  
  10.        SID  
  11. ----------  
  12.         12  

 

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

 

[html] view plaincopy
 
  1. SQL> select owner from test where rownum<2;  
  2. OWNER  
  3. ------------------------------  
  4. SYS  

 

再去查看v$open_cursor

 

[html] view plaincopy
 
  1. SQL> select * from v$open_cursor where sid=12;  
  2. SADDR           SID USER_NAME                      ADDRESS HASH_VALUE SQL_TEXT  
  3. -------- ---------- ------------------------------ -------- ---------- ------------------------------------------------------------  
  4. 95D5AFCC         12 TAOBAO                         99C6C628 1755089113 select owner from test where rownum<2  
  5. 95D5AFCC         12 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

 

[html] view plaincopy
 
  1. alter session set session_cached_cursors=2;  

 

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

 

[html] view plaincopy
 
  1. SQL> /  
  2. SADDR           SID USER_NAME                      ADDRESS HASH_VALUE SQL_TEXT  
  3. -------- ---------- ------------------------------ -------- ---------- ------------------------------------------------------------  
  4. 95D5AFCC         12 TAOBAO                         99CA4FFC 2584499260 select owner from test where rownum<2  
  5. 95D5AFCC         12 TAOBAO                         99C9AEF8 608211958 select owner from test where rownum=1  
  6. 95D5AFCC         12 TAOBAO                         99DA7F20 745629022 select distinct sid from v$mystat  

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

 

 

[html] view plaincopy
 
  1. SQL> select sid,n.name,s.value used  
  2. 2   from  
  3. 3     sys.v_$statname n,  
  4. 4     sys.v_$sesstat s  
  5. 5   where  
  6. 6     n.name in (‘opened cursors current‘, ‘session cursor cache count‘) and  
  7. 7     s.statistic# = n.statistic#  
  8. 8     and sid=12;  
  9.        SID NAME                                                                   USED  
  10. ---------- ---------------------------------------------------------------- ----------  
  11.         12 opened cursors current                                                    1  
  12.         12 session cursor cache count                                                2  
  13.           

这下有2个cached cursor,一个current open cursor.

 

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

在这里再提一下另一个与cursor有关的参数open_cursors,这个参数限制了每个session可以打开的最多cursor,如果当前打开的cursor数超过了open_cursor就会报ORA-01000: maximum open cursors exceeded,通常在使用java connection pool时如果处理不当将会导致ORA-01000,如果java应用服务器端使用软关闭cursor,并保留这些cursor在在cache size,那么数据库端认为这个cursor还是打开的,.所以在open_cursors,session_cached_cursors等参数时一定要和应用端的cursor cache size等参数搭配好.

关于open_cursors和session_cached_cursors的参数值(转)


联系
我们