`

关于open_cursors和session_cached_cursors的参数值（转载）

 
阅读更多

关于open_cursors和session_cached_cursors的参数值
2010-01-26 15:32

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

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

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

SQL> show parameter cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------
open_cursors                         integer     300
session_cached_cursors               integer     20
SQL>

alter system set open_cursors=1500 scope=both;

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文件。
SQL> alter system set session_cached_cursors=20 scope=spfile;

ORA-02096: 此选项的指定初始化参数不可修改

2、要考虑共享池的大小

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

Select 'session_cached_cursors' Parameter,
    Lpad(Value, 5) Value,
    Decode(Value, 0, ' n/a', To_Char(100 * Used / Value, '990') || '%') Usage
From (Select Max(s.Value) Used
      From V$statname n, V$sesstat s
     Where n.Name = 'session cursor cache count'
      And s.Statistic# = n.Statistic#),
    (Select Value From V$parameter Where Name = 'session_cached_cursors')
Union All
Select 'open_cursors',
    Lpad(Value, 5),
    To_Char(100 * Used / Value, '990') || '%'
From (Select Max(Sum(s.Value)) Used
      From V$statname n, V$sesstat s
     Where n.Name In
        ('opened cursors current', 'session cursor cache count')
      And s.Statistic# = n.Statistic#
     Group By s.Sid),
    (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的视图,它的表结构如下
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
----------
        12


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         12 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
------------------------------
SYS

然后再执行

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
---------- ---------------------------------------------------------------- ----------
        12 opened cursors current                                                    1
        12 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         12 TAOBAO                         99CA4FFC 2584499260 select owner from test where rownum<2
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
SQL> select distinct sid from v$mystat;

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

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

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

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         12 TAOBAO                         99C6C628 1755089113 select owner from test where rownum<2
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

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参数的作用.

在这里再提一下另一个与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等参数搭配好.

 

分享到：
| Media queries 使用示例
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    oracle参数open_cursors与session_cached_cursors详解.pdf

    Oracle 数据库中，open_cursors 和 session_cached_cursors 是两个重要的参数，它们对 Oracle 的运行性能产生了直接的影响。本文将详细解释这两个参数的作用机理及其异同点，并探讨如何监控和调整它们的设定值，以...

    Oracle XE 允许连接的用户数不足

    这些参数包括 session_cached_cursors、session_max_open_files、sessions、license_max_sessions、license_sessions_warning 和 processes。 在解决这个问题之前，我们需要了解这些参数的含义： * session_cached...

    ORACLE技术文档\oracle cursor 游标.doc

    3. `session_cached_cursors`：这个参数指定Oracle服务器为每个会话缓存的预编译游标数量。缓存游标有助于提高性能，因为它减少了编译SQL语句的次数。然而，它也会占用`OPEN_CURSORS`的配额。 `cursor_space_for_...

    如何对AIX、ORACLE、WebLogic和WebSphere进行调优

    修改`open_cursors`和`session_cached_cursors`的参数值 - **命令行调整方法**： - 查看当前设置：`SQL&gt; show parameter cursors` - 调整`open_cursors`的值：`SQL&gt; alter system set open_cursors=1500 scope=...

    Oracle 12c r2优化参数设置.docx

    6. **session_cached_cursors** 和 **open_cursors**: 控制会话缓存的游标数和允许打开的游标数，优化游标管理可以减少磁盘I/O并提升性能。 7. **open_links** 和 **open_links_per_instance**: 设定单个会话可打开...

    浅谈ORACLE数据库系统性能优化方案.pdf

    同时，监测session_clilshl_cache_hits以调整session_cached_cursors和open_cursors参数。数据字典缓冲区的命中率同样重要，通过V$cache视图检查并调整分配内存。 除此之外，文章还提到了SQL级别的调整，这是四级...

    Oracle原理，ppt文档。

    当Shared Pool碎片化严重时，可能导致ORA-04031错误，表现为SQL语句无法获取足够的内存空间，此时可能需要调整SQL语句的复用策略、监控open_cursors和session_cached_cursors参数，以及定期进行Shared Pool的清理。...

    数据库参数设置技术手册.doc

    16. **SESSION_CACHED_CURSORS**：预分配的游标数，减少打开和关闭游标的开销。 17. **CURSOR_SPACE_FOR_TIME**：控制游标空间的生命周期，以提高性能。 18. **SGA_MAX_SIZE**：设置系统全局区域（SGA）的最大大小...

    Oracle数据库安装及运维工作指南.pdf

    * `SESSION_CACHED_CURSORS`：设置会话缓存游标的数量，以提高数据库的性能。 * `AUDIT_TRAIL`：设置审计跟踪，以确保数据库的安全。 * `RECYCLEBIN`：设置回收站，以避免回收站过大。 * `LOG_ARCHIVE_DEST_1`：设置...

    Oracle Goldengate性能优化方案

    绑定变量相关的参数，例如cursor_sharing（光标共享）、OPEN_CURSORS（打开光标数）、SESSION_CACHED_CURSORS（会话缓存光标数）也是优化的一部分。 优化器相关的参数，包括optimizer_index_caching（索引缓存优化...

    Oracle9i的init.ora参数中文说明

    说明: 与 NLS_TIME_TZ_FORMAT 相似, 其中的一对值指定 TIMESTAMP 数据类型的默认值, 该类型除存储 YEAR, MONTH 和 DAY 日期值, HOUR, MINUTE 和 SECOND 时间值, 还存储 TIMEZONE_HOUR 和 TIMEZONE_MINUTE。...

    数据库性能调整数据库性能调整.ppt

    比如，通过调整OPEN_CURSORS参数限制打开的游标数量，减少SESSION_CACHED_CURSORS以节省内存，或者启用CURSOR_SPACE_FOR_TIME以根据使用频率决定是否保存游标。 此外，监控和使用报告如V$SGASTAT可以帮助识别SGA各...

    ArcSDE for Oracle教程

    - 设置其他参数：例如`shared_pool_size`、`cursor_sharing`、`cursor_space_for_time`、`open_cursors`、`session_cached_cursors`和`pre_page_sga`等参数，这些参数的调整有助于优化Oracle性能。 - **重启...

    Statspack工具

    Shared Pool Statistics提供了关于共享池的信息，包括共享池的使用情况、游标数量（如SESSION_CACHED_CURSORS、OPEN_CURSORS）等。共享池的管理对于减少SQL语句解析和提高系统响应时间至关重要。 ##### 6. SQL ...

    oracle游标溢出调优

    其次，`open_cursors` 参数定义了一个会话可以同时打开的最大游标数量，是系统层面的重要限制。适当设置该参数可以帮助避免游标溢出错误，确保数据库能有效地处理并发请求。 `cursor_sharing` 参数则控制SQL语句的...

    oracle数据库系统健康检查及优化.pdf

    - 根据监控结果，调整数据库参数如`open_cursors`和`sessiorL cached_cursors`以减少软解析，提高性能。 6. **优化效果**： - 参数调整后，数据库性能得到提升，如导入操作不再卡顿，I/O消耗减少，缓存命中率和硬...

    linux 手动建oracle数据库

    *.session_cached_cursors=100 *.sga_target=500m *.shared_pool_size=100m *.undo_management='AUTO' *.undo_retention=0 *.undo_tablespace='UNDOTS1' *.user_dump_dest='/home/oracle/admin/bipdb/udump' ``` ...

    Linux_oracle命令大全

    session_cached_cursors integer 0 session_max_open_files integer 10 sessions integer 225 shared_server_sessions integer ``` **作用：** - 显示当前数据库实例中的 session 相关参数设置。 ##### 2. 修改 ...

    KDB和Oracle的性能pk小记

    4. Open Cursors：提高并发连接数，增加open_cursors和session_cached_cursors的值。 5. 审计：若无审计需求，将`audit_trail`设为none，减少不必要的资源消耗。 6. 异步I/O：启用异步I/O（filesystemio_options设为...

    Windows2008安装oracle11g

    11. 修改其他高级初始化参数，如Audit_trail设为none，Open_cursors设为3000，Sec_case_sensitive_logon设为false，Session_cached_cursors设为200，Undo_retention设为1800秒。 12. 确认设置无误后，点击“下一步”...

Magicbox
Global site tag (gtag.js) - Google Analytics