ITPub博客

首页 > 数据库 > 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博客 ” ,链接:http://blog.itpub.net/25105315/viewspace-694248/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-12-18

  • 博文量
    92
  • 访问量
    446720