--通过session_cached_cursors 参数来减少软解析次数

调优的时候很多都是关注减少硬解析，其实oracle还提供了一些手段来减少软解析。
客户端缓存技术就是其中的一种。
参数session_cached_cursors用来启动客户端的缓存
其值为客户端可以缓存的不同SQL的个数。
9i时默认值为0,10g默认值为20
测试oralce 版本为9.2.0.8

--装备测试的脚本
1.sql.sql
set echo off;
set feedback off;
set termout off;
select /*+session_cursor_test_11*/count(*)  from scott.emp where EMPNO=11;
select /*+session_cursor_test_12*/count(*)  from scott.emp where EMPNO=12;
select /*+session_cursor_test_13*/count(*)  from scott.emp where EMPNO=13;
select /*+session_cursor_test_14*/count(*)  from scott.emp where EMPNO=14;
select /*+session_cursor_test_15*/count(*)  from scott.emp where EMPNO=15;
select /*+session_cursor_test_16*/count(*)  from scott.emp where EMPNO=16;
select /*+session_cursor_test_17*/count(*)  from scott.emp where EMPNO=17;
select /*+session_cursor_test_18*/count(*)  from scott.emp where EMPNO=18;
select /*+session_cursor_test_19*/count(*)  from scott.emp where EMPNO=19;
select /*+session_cursor_test_20*/count(*)  from scott.emp where EMPNO=20;
set termout on;
2.cursor.sql     
col name for a30
col value for 999999
SELECT sn.name, ss.value
    FROM v$statname sn, v$sesstat ss
    WHERE sn.statistic# = ss.statistic#
    AND sn.name IN ('session cursor cache hits', --缓存命中次数
                    'session cursor cache count',--缓存的SQL条数
                    'parse count (total)')      --parse的总次数
    AND ss.sid = (select sid from v$mystat where rownum<=1);
3.parse_count.sql
select substr(sql_text,-2) NO,users_opening,FETCHES,EXECUTIONS,PARSE_CALLS,LOADS from v$sql
where sql_text like '%+session_cursor_test_%'
and sql_text not like '%like%'
order by sql_text;

--准备好脚本可以开始了
--查看一下session_cached_cursors的参数值。
SLQ>show parameter session_cached_cursors 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors               integer     0

SQL>@sql
SQL>@sql
SQL>@sql

SQL>@parse_count
NO   USERS_OPENING PARSE_CALLS      LOADS
---- ------------- ----------- ----------
11               3           3          1
12               3           3          1
13               3           3          1
14               3           3          1
15               3           3          1
16               3           3          1
17               3           3          1
18               3           3          1
19               3           3          1
20               3           3          1


--关注PARSE_CALLS 和USERS_OPENING 的值都是3次 
--查看cursor的缓存情况
SQL>@cursor
NAME                             VALUE
------------------------------ -------
session cursor cache hits            0  
session cursor cache count           0  --为0 表示没有缓存
parse count (total)                151


--接下来修 session_cached_cursors 的值为9 (小于要SQL脚本中的SQL的条数)

SQL>alter session set session_cached_cursors = 5;
--运行3次SQL
SQL>@sql
SQL>@sql
SQL>@sql
--查看cursor的缓存情况
SQL>@cursor
NAME                             VALUE
------------------------------ -------
session cursor cache hits            0  --没有命中的
session cursor cache count           5  --缓存了5个cursor
parse count (total)                150
--查看解析的情况
SQL>@parse_count
NO   USERS_OPENING PARSE_CALLS      LOADS
---- ------------- ----------- ----------
11               6           6          1
12               6           6          1
13               6           6          1
14               6           6          1
15               6           6          1
16               6           6          1
17               6           6          1
18               6           6          1
19               6           6          1
20               6           6          1


--关注PARSE_CALLS 和USERS_OPENING 的值都是6次

--接下来修 session_cached_cursors 的值为15 (大于要SQL脚本中的SQL的条数)
SQL>alter session set session_cached_cursors = 15;
--运行3次SQL
SQL>@sql
SQL>@sql
SQL>@sql


--查看cursor的缓存情况
SQL>@cursor
NAME                             VALUE
------------------------------ -------
session cursor cache hits           24 --命中的24次
session cursor cache count          11 --缓存了11个cursor 因为还有其它SQL运行不一定等于sql文件里的sql个数
parse count (total)                183

--查看解析的情况
SQL>@parse_count
NO   USERS_OPENING PARSE_CALLS      LOADS
---- ------------- ----------- ----------
11               7           9          1
12               7           9          1
13               7           9          1
14               7           9          1
15               7           9          1
16               7           9          1
17               6           9          1
18               6           9          1
19               6           9          1
20               6           9          1

--关注USERS_OPENING 的值小于PARSE_CALLS 的值证明缓存已经生效了。


--再运行3次SQL
SQL>@sql
SQL>@sql
SQL>@sql
--查看cursor的缓存情况
NAME                             VALUE
------------------------------ -------
session cursor cache hits           56
session cursor cache count          11
parse count (total)                215

--查看解析的情况
SQL>@parse_count  
NO   USERS_OPENING PARSE_CALLS      LOADS
---- ------------- ----------- ----------
11               7          12          1
12               7          12          1
13               7          12          1
14               7          12          1
15               7          12          1
16               7          12          1
17               6          12          1
18               6          12          1
19               6          12          1
20               6          12          1    

--关注USERS_OPENING的值没有随着PARSE_CALLS 的增长而增长。

因为软解析是串行操作
所以可以通过将session_cached_cursors设置为一个适当的值
减少软解析的次数从而提高数据库的整体性能。

如何设置选择session_cached_cursors的值呢！
我们可以通过查v$sesstat中每个session 的
        session cursor cache hits              
        session cursor cache count             
        parse count (total)

  这三个事件来设置 session_cached_cursors
  1.如果 "session cursor cache count" 等于 "session_cached_cursors" 参数值 而且    
          "parse count (total)" 与 "session cursor cache hits" 相差很大的session 很多
          就证明需要增加 session_cached_cursors 的取值。
  
  2.在调整 session_cached_cursors的取值 时不应该大于 open_cursors 参数的值。
  
  3.如果存在大量的session "session cursor cache count"小于 session_cached_cursors 的取值
     "parse count (total)" 与 "session cursor cache hits" 相差又很大,说明是open_cursors参数
     限制了缓存的cursor 的个数 应适当增大open_cursors的取值。

最后提供一个好用的脚本
  SELECT ss.SID,
         sum(decode(sn.name,'session cursor cache hits',ss.value,0))  s_cursor_cache_hits,
         sum(decode(sn.name,'session cursor cache count',ss.value,0)) s_cursor_cache_counts,
         sum(decode(sn.name,'parse count (total)',ss.value,0))  parse_count
    FROM v$statname sn, v$sesstat ss
    WHERE sn.statistic# = ss.statistic#
    AND sn.name IN ('session cursor cache hits',
                    'session cursor cache count',
                    'parse count (total)')                     
    group by ss.sid
    order by 3

from：http://www.itpub.net/thread-1242611-1-1.html