ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > SESSION_CACHED_CURSORS的应用

SESSION_CACHED_CURSORS的应用

原创 Oracle 作者：dbs101 时间：2011-05-02 16:35:12 0 删除 编辑

什么是SESSION_CACHED_CURSORS

SESSION_CACHED_CURSORS: If an application repeatedly issues parse calls on the same set of SQL statements, then the reopening of the session cursors can affect system performance. To minimize the impact on performance, session cursors can be stored in a session cursor cache. These cursors are those that have been closed by the application and can be reused.

Oracle checks the library cache to determine whether more than three parse requests have been issued on a given statement. If so, then Oracle assumes that the session cursor associated with the statement should be cached and moves the cursor into the session cursor cache. Subsequent requests to parse that SQL statement by the same session then find the cursor in the session cursor cache.

测试案例：

Below is test case by using java program:

1. SESSION_CACHED_CURSORS =100

点击(此处)折叠或打开

  1.    sql = "alter session set SESSION_CACHED_CURSORS =100";
  2.    ps = aconnection.prepareStatement(sql);
  3.    ps.executeQuery();
  4.       sql = "SELECT COUNT(*) FROM TT";
  5. for (int i=0;i<30;i++ )
  6. {
  7.    ps = aconnection.prepareStatement(sql);      --repeatedly issue parse calls
  8.    rs = ps.executeQuery();
  9.    Thread.sleep(1000,0);
  10.    ps.close();                                  --close statement(cursor)
  11. }


--get open cursor, and only one sql statement

点击(此处)折叠或打开

  1. select * from v$open_cursor t where t.SQL_TEXT like 'SELECT COUNT(*) FROM TT%'
  2. ---------------------------------------------------------------------------------------------------------------
  3. 3432EB34 145 SYS 27FA93D0 560338920 294ugjshqc5z8 SELECT COUNT(*) FROM TT

--parse count (total) is 33, and session cursor cache hits is 29

点击(此处)折叠或打开

  1.         RUN DATE                SID     NAME                                  VALUE
  2. -----------------------------------------------------------------------------------------
  3. before 2008-07-20 22:45:16 145 STAT...session cursor cache hits 2
  4. before 2008-07-20 22:45:16 145 STAT...parse count (total)         29
  5. before 2008-07-20 22:45:16 145 STAT...parse count (hard)         3
  6. after 1 2008-07-20 22:45:46 145 STAT...session cursor cache hits 32
  7. after 1 2008-07-20 22:45:46 145 STAT...parse count (total)         62
  8. after 1 2008-07-20 22:45:46 145 STAT...parse count (hard)         4

2. SESSION_CACHED_CURSORS =0


点击(此处)折叠或打开

  1. select * from v$open_cursor t where t.SQL_TEXT like 'SELECT COUNT(*) FROM TT%'
  2. --------------------------------------------------------------------------------------------------------------
  3. no rows

--parse count (total) is 38, and session cursor cache hits is 0

点击(此处)折叠或打开

  1.         RUN DATE                SID     NAME                                  VALUE
  2. -----------------------------------------------------------------------------------------
  3. before 2008-07-20 22:54:06 145 STAT...session cursor cache hits 754
  4. before 2008-07-20 22:54:06 145 STAT...parse count (total)         350
  5. before 2008-07-20 22:54:06 145 STAT...parse count (hard)         99
  6. after 1 2008-07-20 22:54:37 145 STAT...session cursor cache hits 754
  7. after 1 2008-07-20 22:54:37 145 STAT...parse count (total)         388
  8. after 1 2008-07-20 22:54:37 145 STAT...parse count (hard)         102

: If an application repeatedly issues parse calls on the same set of SQL statements, then the reopening of the session cursors can affect system performance. To minimize the impact on performance, session cursors can be stored in a session cursor cache. These cursors are those that have been closed by the application and can be reused.

Oracle checks the library cache to determine whether more than three parse requests have been issued on a given statement. If so, then Oracle assumes that the session cursor associated with the statement should be cached and moves the cursor into the session cursor cache. Subsequent requests to parse that SQL statement by the same session then find the cursor in the session cursor cache.

Below is test case by using java program:

1. SESSION_CACHED_CURSORS =100

点击(此处)折叠或打开

  1.    sql = "alter session set SESSION_CACHED_CURSORS =100";
  2.    ps = aconnection.prepareStatement(sql);
  3.    ps.executeQuery();
  4.       sql = "SELECT COUNT(*) FROM TT";
  5. for (int i=0;i<30;i++ )
  6. {
  7.    ps = aconnection.prepareStatement(sql);      --repeatedly issue parse calls
  8.    rs = ps.executeQuery();
  9.    Thread.sleep(1000,0);
  10.    ps.close();                                  --close statement(cursor)
  11. }

--get open cursor, and only one sql statement

点击(此处)折叠或打开

  1. select * from v$open_cursor t where t.SQL_TEXT like 'SELECT COUNT(*) FROM TT%'
  2. ---------------------------------------------------------------------------------------------------------------
  3. 3432EB34 145 SYS 27FA93D0 560338920 294ugjshqc5z8 SELECT COUNT(*) FROM TT

--parse count (total) is 33, and session cursor cache hits is 29

点击(此处)折叠或打开

  1.         RUN DATE                SID     NAME                                  VALUE
  2. -----------------------------------------------------------------------------------------
  3. before 2008-07-20 22:45:16 145 STAT...session cursor cache hits 2
  4. before 2008-07-20 22:45:16 145 STAT...parse count (total)         29
  5. before 2008-07-20 22:45:16 145 STAT...parse count (hard)         3
  6. after 1 2008-07-20 22:45:46 145 STAT...session cursor cache hits 32
  7. after 1 2008-07-20 22:45:46 145 STAT...parse count (total)         62
  8. after 1 2008-07-20 22:45:46 145 STAT...parse count (hard)         4

2. SESSION_CACHED_CURSORS =0


点击(此处)折叠或打开

  1. select * from v$open_cursor t where t.SQL_TEXT like 'SELECT COUNT(*) FROM TT%'


no rows

--parse count (total) is 38, and session cursor cache hits is 0

点击(此处)折叠或打开

  1.         RUN DATE                SID     NAME                                  VALUE
  2. -----------------------------------------------------------------------------------------
  3. before 2008-07-20 22:54:06 145 STAT...session cursor cache hits 754
  4. before 2008-07-20 22:54:06 145 STAT...parse count (total)         350
  5. before 2008-07-20 22:54:06 145 STAT...parse count (hard)         99
  6. after 1 2008-07-20 22:54:37 145 STAT...session cursor cache hits 754
  7. after 1 2008-07-20 22:54:37 145 STAT...parse count (total)         388
  8. after 1 2008-07-20 22:54:37 145 STAT...parse count (hard)         102



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

0
0
上一篇： flashback database在测试中的应用
下一篇： Orion - oracle提供的测试io性能的工具
请登录后发表评论 登录
全部评论
dbs101
专家姓名：栾长苗
喜欢oracle、redis、mysql、pg数据库

注册时间：2010-12-18

  • 博文量
    94
  • 访问量
    473124

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593