参数 session_cached_cursors 与 open_cursors详解

一、OPEN_CURSORS

    就是一个session同时打开的cursor的数量上限。

   确定open_cursors
    select max(count(*)) max_cacheable_cursors
      from (select p.kglobt18 schema# -- parsing schema number
              from sys.x$kglcursor p
             where p.kglobt12 > 2 -- enough parse_calls
            union all
            select s.kglntsnm schema# -- authorized schema number
              from sys.x$kglcursor c, sys.x$kglsn s
             where c.kglobt12 > 2
               and s.kglhdadr = c.kglhdadr)
     group by schema#

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

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

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

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative                                          21577316
opened cursors current                                                 2399
session cursor cache hits                                           9992832
session cursor cache count                                             8254
cursor authentications                                               434123

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

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse time cpu                                                      1400526
parse time elapsed                                                 13979044
parse count (total)                                                20982181
parse count (hard)                                                  1396983
parse count (failures)                                                10958

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

    Oracle 9i及以前，该参数缺省是0，10G上缺省是20。

    设置pga端的cache list的长度，当session_cached_cursors设置为0时，pga的cache list长度为0，这时候当sga中的cursor关闭的时候它相关的library cache handle的lock位被清0，从v$open_cursor里看不到这个被关闭的cursor，它服从于shared pool的lru机制，当shared pool需要新的buffer空间时，它将会被flush出shared pool。当session_cached_cursors设置为非0值时，pga的cache list长度为session_cached_cursors值的大小，同时pga cache list将会保留一份拷贝，这时候即使sga中的cursor关闭的时候它相关的library cache handle始终被加了null mode lock，当shared pool空间紧张时library cache handle始终将会被保留在shared pool中，而新的应用访问这个cursor的时候会直接去自己的pga cache list里面搜索。

    cursor_space_for_time：

    当设置了session_cached_cursors为非0值后，如果cursor_space_for_time值被设为false，那么当shared pool空间紧张时，虽然library cache handle不会被flush出去，但是它指向的library cached object（lco，其中包含了handle和children handle的地址，权限，类型，状态，指向kgl block的指针，其中kgl block包含了真正的代码段和执行计划等内容）将会被flush出去而不管它相关的cursor有没关闭，如果需要lco的时候将要reloads。如果cursor_space_for_time值被设为true，那么当cursor在打开状态下，handle指向的lco将不会被flush出shared pool，这样就可以降低reloads出现的频率。不过对于sql共享做的不好的数据库，设置cursor_space_for_time将会带来一些问题，share pool可能会出现04031的错误。

    gets：

    当试图parse一句sql时，oracle要先获得一个handle，在handle上加载一个lock，gets表示handle request times。

    pin：

    当获得handle后，定位到lco，然后pin住lco使它在被执行的时候不被flush出去。

 

二、引用ASKTOM相关内容如下

Hi Tom
 Can you explain briefly the difference between soft and hard parse?
 你能不能给我详细介绍一下软解析与硬解析的区别啊?
and we said...
 Here is a long winded answer, it is extracted in part from a new book oming out soon "beginning Oracle programming" that I collaborated on:
 这是一个很长的解释,是从我与几个同事一起合著的新书<>中提取出来的.

 Parsing
 解析
 This is the first step in the processing of any statement in Oracle.  Parsing is the act of breaking the submitted statement down into its component parts ? determining what type of statement it is (query, DML, DDL) and performing various checks on it. 
 这是Oracle处理所有语句的第一步,解析就是将用户提交的语句(根据一定的规则)分成不同的组件,来确定这条语句是什么类型的语句(query,查询,DML,数据操纵语言,DDL还是数据定义语言),以对这条语句执行各种不同的语法检测.
 The parsing process performs two main functions:
 解析过程主要有一下两个功能.
 1. Syntax Check: is the statement a valid one?  Does it make sense given the SQL grammar documented in the SQL Reference Manual.  Does it follow all of the rules for SQL.
    语法检查,这条语句是否合理? 它是否符合SQL语法文档<>的相关语法,是不是符合SQL语句的所有规则.
 2. Semantic Analysis:  Going beyond the syntax ? is the statement valid in light of the objects in the database (do the tables and columns referenced exist).  Do you have access to the objects ? are the proper privileges in place?  Are there ambiguities in the statement ? for example if there are two tables T1 and T2 and both have a column X, the query ?select X from T1, T2 where ?? is ambiguous, we don?t know which table to get X from.  And so on.
    语义分析: 已经处理完语法检查了吗? 这条语句是否正确的引用了数据库中对象(是否语句中所有的表和字段都存在). 是否有访问这些对象的权限? 是不是对应的权限都有效(主要指role对应的权限吧,)? 比如是否有如下的两个表T1,T2,而这两个表有相同的字段名column X,而查询语句 ? select X from T1,T2 where ??,(没有明确指定列名),我们无法知道从哪个表去取出字段X的值,以及类似的一系列问题.
   
 So, you can think of parsing as basically a two step process, that of a syntax check to check the validity of the statement and that of a semantic check ? to ensure the statement can execute properly.  The difference between the two types of checks are hard for you to see ? Oracle does not come back and say ?it failed the syntax check?, rather it returns the statement with a error code and message.  So for example, this statement fails with a syntax error:
 因此,你可以认为解析基本上是由这两个步骤组成的: 检查语句有效性的语法检查和确保语句能够正常运行的语义检查.这两种检查的区别就是你所要说的硬解析了.Oracle不会特别的指出这条语句没有通过语法检查,它给你返回这条语句对应的错误代码和错误信息. 比如: 下面这条语句没有通过语法检查.复制内容到剪贴板代码:
 SQL> select from where 2;
 select from where 2
         *
 ERROR at line 1:
 ORA-00936: missing expression
 
 While this statement failed with a semantic error ? if the table NOT_A_TABLE existed and we had permission to access it, this statement would have succeeded:
 而这条语句没有通过语义检查? 如果表NOT_A_TABLE存在,并且我们具有访问这个表的权限,这条语句就会正确执行了.复制内容到剪贴板代码:
 SQL> select * from not_a_table;
 select * from not_a_table
               *
 ERROR at line 1:
 ORA-00942: table or view does not exist
 That is the only way to really tell the difference between a semantic and syntactic error ? if the statement COULD have executed given the proper objects and privileges, you had a semantic error, otherwise if the statement could not execute under any circumstances, you have a syntax error.  Regardless ? Oracle will not execute the statement for you!
 只是我能用来解释语法错误和语义错误的唯一的办法了.如果在赋予合适的对象和权限的情况下,这条语句确实能够执行的话,我们称之为语义错误,否则,如果这条语句在任何条件下都不能执行的话,我们就称之为语法错误.无论如何,Oracle都不会为你运行这条语句.

 The next step in the parse operation is to see if the statement we are currently parsing has already in fact been processed by some other session.  If it has ? we may be in luck here, we can skip the next two steps in the process, that of optimization and row source generation.  If we can skip these next two steps in the process, we have done what is known as a Soft Parse ? a shorter process to getting our query going.  If we cannot, if we must do all of the steps, we are performing what is known as a Hard Parse ? we must parse, optimize, generate the plan for the query.  This distinction is very important.  When developing our applications we want a very high percentage of our queries to be Soft Parsed ? to be able to skip the optimize/generate phases ? as they are very CPU intensive as well as a point of contention (serialization).  If we have to Hard Parse a large percentage of our queries, our system will function slowly and in some cases ? not at all.
 解析中的下一个步骤就是检查我们现在正在解析的语句是否已经被其他的session执行过,在这一步我们可能很幸运,我们可以跳过下面的两个步骤: 语句的优化(生成执行计划) 和 生成执行编码. 如果我们能够跳过这两个步骤,我们就实现了一般意义上的软解析, 我们必须解析,优化,并为这条语句生成执行计划.这个特性(distinction)非常重要,当我们开发应用程序的时候,我们会希望大部分的语句都是软解析的,以跳过优化和生成编码的步骤,因为他们和争用(串行化,)(contention)一样,都是非常消耗CPU的操作.如果我们必须硬解析我们语句的大部分的话,在某些情况下我们的系统就会运行的很慢,并不是任何时候(在olap,dss系统应该是个例外吧)

 The way this sharing of SQL in Oracle is accomplished is via the shared pool, a piece of memory in the SGA maintained by Oracle.  We covered this topic in chapter 5 but will revisit it again in the context of processing a query.  After Oracle parses the query and it passes the syntax and semantic checks ? it will look in the shared pool component of the SGA to see if that same exact query has already been processed by another session.  Since it has performed the semantic check it has already figured out:
 在Oracle中共享SQL的方式是通过共享池--SGA中的一块内存(由Oracle系统来维护)--实现的。我们在第五章中讨论了这个主题，并且会在查询处理的章节再次讨论这个主题，当Oracle已经解析了这条语句，并且已经通过语法和语义检查的时候，Oracle就会在SGA的shared pool(共享池)组件中查询，看看是否已经有一个完全一样的语句已经被另外一个session执行过。因为语句已经通过了oracle的语义检查，并且oracle已经计算出：

o Exactly what tables are involved
具体涉及到哪些表.

o That we have access to the tables (the proper privileges are there)
我们拥有访问对应表的个相应权限.等等...

And so on.  Now, it can look at all of the queries in the shared pool that have already been parsed/optimized and generated to see if the work has already been done. 
现在，可以在所有已经经过解析和优化并且生成的可执行编码的SQL语句搜索，来看看对应的语句是否已经经过解析。

软解析与session_cashed_cursor参数
can you explain what means the default value (0) of session_ casched_cursor parameter? Oracle always has a cache of sqls in sga and size of this cache is determinated through init.ora parameter shared_pool_size.
tom：你能不能给我解释一下，session_cashed_cursor(默认值为0)参数的含义，Oracle在SGA中始终保持一个sql语句的缓存，这个缓存的大小是由初始化参数shared_pool_size的大小决定的。

Followup: 
The default value of ZERO means that by default, ZERO cursors will be cached for your session.
默认值为0表示,在默认情况下,Oracle不会给你的session缓存游标.

They will be cached in the shared pool -- but your session will have to find
them there.  Session Cached Cursors can remove the need to have to "ind" them.  Consider this example that shows the difference spent when soft parsing without session cached cursors vs soft parsing WITH session cached cursors:
他们会被缓存在共享池(Shared Pool)中，但是你的session必须在共享池中去查找他们,session cached cursors可以省略掉去共享池中查找这一步。下面这个例子给你演示了没有缓存游标和缓存了游标之后软解析之间的差别和系统消耗资源的情况
ops$[email]tkyte@ORA817DEV.US.ORACLE.COM[/email]> create table emp as select * from scott.emp;
Table created.

ops$[email]tkyte@ORA817DEV.US.ORACLE.COM[/email]> create table run_stats ( runid varchar2(15),
name varchar2(80), value int );
Table created.

ops$[email]tkyte@ORA817DEV.US.ORACLE.COM[/email]> create or replace view stats
  2  as select 'STAT...' || a.name name, b.value
  3        from v$statname a, v$mystat b
  4       where a.statistic# = b.statistic#
  5      union all
  6      select 'LATCH.' || name,  gets
  7        from v$latch;

View created.

ops$[email]tkyte@ORA817DEV.US.ORACLE.COM[/email]> column name format a40
ops$[email]tkyte@ORA817DEV.US.ORACLE.COM[/email]> declare
  2      l_start number;
  3          l_cnt   number;
  4  begin
  5      execute immediate 'alter session set session_cached_cursors=0';
  6      insert into run_stats select 'before', stats.* from stats;
  7 
  8      l_start := dbms_utility.get_time;
  9      for i in 1 .. 1000
10      loop
11            execute immediate 'select count(*) from emp' into l_cnt;
12      end loop;
13      dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
14 
15      execute immediate 'alter session set session_cached_cursors=100';
16      insert into run_stats select 'after 1', stats.* from stats;
17 
18      l_start := dbms_utility.get_time;
19      for i in 1 .. 1000
20      loop
21            execute immediate 'select count(*) from emp' into l_cnt;
22      end loop;
23      dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
24 
25      insert into run_stats select 'after 2', stats.* from stats;
26  end;
27  /
45 hsecs
35 hsecs

PL/SQL procedure successfully completed.so, session cached cursors RAN faster (i ran this a couple of times, there
were no hard parses going on.  But the real good news is:
因此，在session中缓存游标可以运行的更快(我运行了好几次，没有硬解析的发生)，真正可喜的消息是：
ops$[email]tkyte@ORA817DEV.US.ORACLE.COM[/email]> select a.name, b.value-a.value run1,
c.value-b.value run2,
  2         ( (c.value-b.value)-(b.value-a.value)) diff
  3    from run_stats a, run_stats b, run_stats c
  4   where a.name = b.name
  5     and b.name = c.name
  6     and a.runid = 'before'
  7     and b.runid = 'after 1'
  8     and c.runid = 'after 2'
  9     and (c.value-a.value) > 0
10     and (c.value-b.value) <> (b.value-a.value)
11   order by abs( (c.value-b.value)-(b.value-a.value))
12  /

NAME                                           RUN1       RUN2       DIFF
---------------------------------------- ---------- ---------- ----------
LATCH.checkpoint queue latch                      3          4          1
LATCH.redo allocation                            30         31          1
STAT...consistent gets                         5088       5089          1
STAT...deferred (CURRENT) block cleanout          2          3          1
applications

STAT...calls to get snapshot scn: kcmgss       5019       5018         -1
STAT...enqueue releases                          10          9         -1
STAT...execute count                           1015       1014         -1
STAT...opened cursors cumulative               1015       1014         -1
STAT...parse count (total)                     1015       1014         -1
STAT...session cursor cache count                 0          1          1
STAT...redo entries                              28         27         -1
STAT...recursive calls                         1180       1179         -1
STAT...physical reads                             1          0         -1
LATCH.direct msg latch                            2          0         -2
LATCH.session queue latch                         2          0         -2
LATCH.done queue latch                            2          0         -2
STAT...free buffer requested                      8          6         -2
STAT...enqueue requests                          11          9         -2
LATCH.messages                                    3          0         -3
STAT...db block changes                          47         44         -3
LATCH.redo writing                                3          0         -3
LATCH.ksfv messages                               4          0         -4
STAT...session logical reads                  17128      17123         -5
LATCH.row cache objects                         184        178         -6
STAT...db block gets                          12040      12034         -6
STAT...parse time elapsed                         9          3         -6
STAT...parse time cpu                            13          4         -9
STAT...recursive cpu usage                       51         38        -13
LATCH.cache buffers chains                    34315      34335         20
STAT...redo size                              23900      24000        100
STAT...session cursor cache hits                  3       1002        999
LATCH.shared pool                              2142       1097      -1045
LATCH.library cache                           17361       2388     -14973

34 rows selected.

see the significantly REDUCED number of LATCH counts on the library and shared pool.  Since a latch is a lock, a lock is a serialization device, serialization
implies WAITS -- using the session cached cursors will increase scalability and performance as you add more and more users.  Its not only FASTER, but more scalable as well...
看到库缓存和共享池中锁存(Latch)数目的明显的下降了吗，因为锁存(Latch)就是锁(Lock)，而锁(Lock)就是只能串行执行的设备，串行就意味着等待--使用session缓存的游标可以提高系统的扩展性(scalability)和性能，当你的系统的用户数不断增加的时候，它不仅可以运行的更加快捷，而且可以提高了系统的扩展性。

 

   

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

转载于:http://blog.itpub.net/9399028/viewspace-682179/