关于session cursor cache hits和parse count (total)的问题

dexter

Redhat Linux 5 X64
11.2.0.3
发现一个比较怪异的现象：

1. scott@DEX11g> select m.*, n.NAME
   
2.   2    from v$mystat m, v$statname n
   
3.   3   where m.STATISTIC# = n.STATISTIC#
   
4.   4     and (n.name in ('session cursor cache hits','session cursor cache count','parse count (total)')) ;
   
5. 
   
6.        SID STATISTIC#      VALUE NAME
   
7. ---------- ---------- ---------- ----------------------------------------------------------------
   
8.         96        532        229 session cursor cache hits
   
9.         96        533         42 session cursor cache count
   
10.         96        583         72 parse count (total)
    
11. 
    
12. scott@DEX11g> declare
    
13.   2  c number ;
    
14.   3  begin
    
15.   4  for i in 1 .. 10000 loop
    
16.   5  execute immediate 'select count(*) from emp' into c  ;
    
17.   6  end loop ;
    
18.   7  end ;
    
19.   8  /
    
20. 
    
21. PL/SQL procedure successfully completed.
    
22. 
    
23. scott@DEX11g> select m.*, n.NAME
    
24.   2    from v$mystat m, v$statname n
    
25.   3   where m.STATISTIC# = n.STATISTIC#
    
26.   4     and (n.name in ('session cursor cache hits','session cursor cache count','parse count (total)')) ;
    
27. 
    
28.        SID STATISTIC#      VALUE NAME
    
29. ---------- ---------- ---------- ----------------------------------------------------------------
    
30.         96        532      10244 session cursor cache hits
    
31.         96        533         45 session cursor cache count
    
32.         96        583         76 parse count (total)
    

复制代码

对于session cursor cache hits ，parse count 不增加了（PLSQL和普通的SQL一样的现象），10g的时候不是这样的，并且对比两个版本的官方文档没有明显变化。

不知道这是个bug还是11.2.0.3重新调整了。

dexter

Stone 发表于 2013-3-31 12:19
应该是从10g以后，这个计算的情况就有变化啦。Oracle官方的文档应该是没有及时更新反映这些新的变化。另 ...

多谢兄弟！看了一下，看来官方文档上的东西也不怎么准啊。

Stone

dexter 发表于 2013-3-31 11:48
感谢您的回答，对于官方文档上说的Subtract this statistic from "parse count (total)" to determine th ...

应该是从10g以后，这个计算的情况就有变化啦。Oracle官方的文档应该是没有及时更新反映这些新的变化。另外Jonathan Lewis的一篇博客中评论部分也有读者反映到这个问题，可以参考一下。

Hi Jonathan,

Can be session cursor cache hits > parse count (total)? Why?

> @sid_parses 630

CURSOR_CACHE_HITS PARSE_CALLS HARD_PARSE CURSOR_CACHE_HITS SOFT_PARSES HARD_PARSES
----------------- ----------- ---------- ----------------- ----------- -----------
            46250       35158       8064           131.55%     -54.49%      22.94%

1 fila seleccionada.

> l
  1  select
  2    cursor_cache_hits,
  3    parse_calls,
  4    hard_parse,
  5    to_char(100 * cursor_cache_hits / parse_calls, '999999999990.00' ) || '%' cursor_cache_hits,
  6    to_char(100 * (parse_calls - cursor_cache_hits - hard_parse) / parse_calls, '999990.00' ) || '%' soft_parses,
  7    to_char(100 * hard_parse / parse_calls, '999990.00' ) || '%' hard_parses
  8  from
  9    ( select value parse_calls from v$statname a, v$sesstat b where a.statistic# = b.statistic# and name = 'parse count (total)' and sid=:p1 ),
10    ( select value hard_parse  from v$statname a, v$sesstat b where a.statistic# = b.statistic# and name = 'parse count (hard)' and sid=:p1 ),
11*  ( select value cursor_cache_hits  from v$statname a, v$sesstat b where a.statistic# = b.statistic# and name = 'session cursor cache hits' and sid=:p1)
  
(:p1 = 630 )
Is there anything wrong?

I got the query from Metalink:

SCRIPT – to Gauge the Impact of the SESSION_CACHED_CURSORS Parameter
Doc ID: Note:208918.1

Comment by Joaquín González — June 4, 2008 @ 5:03 pm UTC Jun 4,2008 | Reply


Joaquín,

Thanks for the note. I think you must be running 10.2. I hadn’t noticed it before but there seems to be a new code optimisation in 10.2 (possibly 10.1) that changes the way that some cursors can be cached.

After seeing your comment, I ran a couple of quick tests that show very different behaviour between 9.2 and 10.2 – but I haven’t yet pinned down what type of action leaves the cache hits greater than the parse calls. (I did notice, though, that a simple “startup force” left the statistics for the starting session in exactly this state). I guess a detailed examination of a 10046 trace file might reveal more of what’s going on.

This means, by the way, that comments I have made in other posts about substracting ‘session cursor cache hits’ from ‘parse count (total)’ are less useful 10g than they are for earlier versions.

Comment by Jonathan Lewis — June 10, 2008 @ 10:37 am UTC Jun 10,2008 | Reply

http://jonathanlewis.wordpress.com/2007/07/03/parse-calls/

dexter

Maclean Liu(刘相兵 发表于 2013-3-31 11:33
我的理解 10g以后对于execute immediate动态SQL 若session cached cursor缓存了游标地址 形成软软解析soft  ...

感谢您的回答，对于官方文档上说的Subtract this statistic from "parse count (total)" to determine the real number of parses that occurred。在这里也就不适用了。现在真正的解析次数=parse count(total)不需要减去软软解析的个数。

期待12c文档早日面世。

Maclean Liu(刘相兵

我的理解 10g以后对于execute immediate动态SQL 若session cached cursor缓存了游标地址 形成软软解析soft soft parse时 ， parse count(total)是不增加的， 因为 parse count(count)包括soft parse 和 hard parse， 但不包括soft soft parse 。

AWR 中的 Execute to Parse %:反应执行解析比 这个比例的公式 1-(parse count(count) /execute) ，其实一方面就是反应 软软解析的。

Maclean Liu(刘相兵

1. SQL> conn / as sysdba
   
2. Connected.
   
3. SQL> select m.*, n.NAME
   
4.   2        from v$mystat m, v$statname n
   
5.   3       where m.STATISTIC# = n.STATISTIC#
   
6.   4         and (n.name in ('session cursor cache hits','session cursor cache count','parse count (total)')) ;
   
7. 
   
8.        SID STATISTIC#      VALUE     CON_ID NAME
   
9. ---------- ---------- ---------- ---------- ------------------------------
   
10.          1        643        366          0 session cursor cache hits
    
11.          1        644         50          0 session cursor cache count
    
12.          1        694        114          0 parse count (total)
    
13. 
    
14. SQL> show parameter session_cached
    
15. 
    
16. NAME                                 TYPE        VALUE
    
17. ------------------------------------ ----------- ------------------------------
    
18. session_cached_cursors               integer     50
    
19. SQL> conn / as sysdba
    
20. Connected.
    
21. SQL> select m.*, n.NAME
    
22.   2        from v$mystat m, v$statname n
    
23.   3       where m.STATISTIC# = n.STATISTIC#
    
24.   4         and (n.name in ('session cursor cache hits','session cursor cache count','parse count (total)')) ;
    
25. 
    
26.        SID STATISTIC#      VALUE     CON_ID NAME
    
27. ---------- ---------- ---------- ---------- ------------------------------
    
28.          1        643          0          0 session cursor cache hits
    
29.          1        644          1          0 session cursor cache count
    
30.          1        694          3          0 parse count (total)
    
31. 
    
32. SQL>    declare
    
33.   2      c number ;
    
34.   3      begin
    
35.   4      for i in 1 .. 10000 loop
    
36.   5      execute immediate 'select count(*) from mac_test' into c  ;
    
37.   6      end loop ;
    
38.   7      end ;
    
39.   8  /
    
40. 
    
41. PL/SQL procedure successfully completed.
    
42. 
    
43. SQL> select m.*, n.NAME
    
44.   2        from v$mystat m, v$statname n
    
45.   3       where m.STATISTIC# = n.STATISTIC#
    
46.   4         and (n.name in ('session cursor cache hits','session cursor cache count','parse count (total)')) ;
    
47. 
    
48.        SID STATISTIC#      VALUE     CON_ID NAME
    
49. ---------- ---------- ---------- ---------- ------------------------------
    
50.          1        643      10000          0 session cursor cache hits
    
51.          1        644          3          0 session cursor cache count
    
52.          1        694          7          0 parse count (total)

复制代码

以上是12c中的测试结果

Maclean Liu(刘相兵

为什么不应该呢？

dexter

Maclean Liu(刘相兵 发表于 2013-3-31 11:05

11g里面对于session cursor cache hints的描述是否和实际情况有冲突？
session cursor cache hints是不应该大于parse count (total)的。

Maclean Liu(刘相兵

1. SQL> select * from v$version;
   
2. 
   
3. BANNER
   
4. ----------------------------------------------------------------
   
5. Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
   
6. PL/SQL Release 10.2.0.5.0 - Production
   
7. CORE    10.2.0.5.0      Production
   
8. TNS for Linux: Version 10.2.0.5.0 - Production
   
9. NLSRTL Version 10.2.0.5.0 - Production
   
10. 
    
11. SQL> show parameter session_cache
    
12. 
    
13. NAME                                 TYPE        VALUE
    
14. ------------------------------------ ----------- ------------------------------
    
15. session_cached_cursors               integer     20
    
16. 
    
17. 
    
18. 
    
19.        SID STATISTIC#      VALUE NAME
    
20. ---------- ---------- ---------- ------------------------------
    
21.        141        295          0 session cursor cache hits
    
22.        141        296          3 session cursor cache count
    
23.        141        346         11 parse count (total)
    
24. 
    
25. SQL>    declare
    
26.   2      c number ;
    
27.   3      begin
    
28.   4      for i in 1 .. 10000 loop
    
29.   5      execute immediate 'select count(*) from sys.obj$' into c  ;
    
30.   6      end loop ;
    
31.   7      end ;
    
32.   8  /
    
33. 
    
34. PL/SQL procedure successfully completed.
    
35. 
    
36. SQL> select m.*, n.NAME
    
37.   2        from v$mystat m, v$statname n
    
38.   3       where m.STATISTIC# = n.STATISTIC#
    
39.   4         and (n.name in ('session cursor cache hits','session cursor cache count','parse count (total)')) ;
    
40. 
    
41.        SID STATISTIC#      VALUE NAME
    
42. ---------- ---------- ---------- ------------------------------
    
43.        141        295          1 session cursor cache hits
    
44.        141        296          5 session cursor cache count
    
45.        141        346         14 parse count (total)

复制代码

dexter

psufnxk2000 发表于 2013-3-31 10:51
show parameter session_cached_cur
10.2里这个参数 默认是0
11.2里默认是 50

两边我已经进行了修改
10g：

1. sys@DEX10g> show parameter session_cached_cursor
   
2. 
   
3. NAME                                 TYPE        VALUE
   
4. ------------------------------------ ----------- ------------------------------
   
5. session_cached_cursors               integer     100

复制代码

11g：

1. sys@DEX11g> show parameter session_cached_cursor
   
2. 
   
3. NAME                                 TYPE        VALUE
   
4. ------------------------------------ ----------- ------------------------------
   
5. session_cached_cursors               integer     100
   
6. sys@DEX11g>

复制代码

dexter

Maclean Liu(刘相兵 发表于 2013-3-31 10:50
不要这样测试 ，拿你上面的测试脚本到10g 中 一测便知

10g里面对于plsql里面的动态sql是不算在session cursor cache hints里面的。所以才拿普通sql做的实验。

下面是相应的11.2.0.3的

1. scott@DEX11g> select m.*, n.NAME
   
2.   2    from v$mystat m, v$statname n
   
3.   3   where m.STATISTIC# = n.STATISTIC#
   
4.   4     and (n.name in ('session cursor cache hits','session cursor cache count','parse count (total)')) ;
   
5. 
   
6.        SID STATISTIC#      VALUE NAME
   
7. ---------- ---------- ---------- ----------------------------------------------------------------
   
8.         96        532      10255 session cursor cache hits
   
9.         96        533          2 session cursor cache count
   
10.         96        583         77 parse count (total)
    
11. 
    
12. scott@DEX11g> select m.*, n.NAME
    
13.   2    from v$mystat m, v$statname n
    
14.   3   where m.STATISTIC# = n.STATISTIC#
    
15.   4     and (n.name in ('session cursor cache hits','session cursor cache count','parse count (total)')) ;
    
16. 
    
17.        SID STATISTIC#      VALUE NAME
    
18. ---------- ---------- ---------- ----------------------------------------------------------------
    
19.         96        532      10257 session cursor cache hits
    
20.         96        533          2 session cursor cache count
    
21.         96        583         77 parse count (total)
    
22. 
    
23. scott@DEX11g> select m.*, n.NAME
    
24.   2    from v$mystat m, v$statname n
    
25.   3   where m.STATISTIC# = n.STATISTIC#
    
26.   4     and (n.name in ('session cursor cache hits','session cursor cache count','parse count (total)')) ;
    
27. 
    
28.        SID STATISTIC#      VALUE NAME
    
29. ---------- ---------- ---------- ----------------------------------------------------------------
    
30.         96        532      10259 session cursor cache hits
    
31.         96        533          2 session cursor cache count
    
32.         96        583         77 parse count (total)

复制代码

psufnxk2000

show parameter session_cached_cur
10.2里这个参数 默认是0
11.2里默认是 50

Maclean Liu(刘相兵

不要这样测试 ，拿你上面的测试脚本到10g 中 一测便知

dexter

Maclean Liu(刘相兵 发表于 2013-3-31 10:26
我印象中  10g中  soft soft parse 就不算在这个parse count (total)里了， 但是目前没有10g的环境测试证明 ...

还是算的

1. scott@DEX10g> select m.*, n.NAME
   
2.   2    from v$mystat m, v$statname n
   
3.   3   where m.STATISTIC# = n.STATISTIC#
   
4.   4     and (n.name in ('session cursor cache hits','session cursor cache count','parse count (total)')) ;
   
5. 
   
6.        SID STATISTIC#      VALUE NAME
   
7. ---------- ---------- ---------- ----------------------------------------------------------------
   
8.        144        279        160 session cursor cache hits
   
9.        144        280         90 session cursor cache count
   
10.        144        330        217 parse count (total)
    
11. 
    
12. scott@DEX10g> select m.*, n.NAME
    
13.   2    from v$mystat m, v$statname n
    
14.   3   where m.STATISTIC# = n.STATISTIC#
    
15.   4     and (n.name in ('session cursor cache hits','session cursor cache count','parse count (total)')) ;
    
16. 
    
17.        SID STATISTIC#      VALUE NAME
    
18. ---------- ---------- ---------- ----------------------------------------------------------------
    
19.        144        279        162 session cursor cache hits
    
20.        144        280         90 session cursor cache count
    
21.        144        330        219 parse count (total)
    
22. 
    
23. scott@DEX10g> select m.*, n.NAME
    
24.   2    from v$mystat m, v$statname n
    
25.   3   where m.STATISTIC# = n.STATISTIC#
    
26.   4     and (n.name in ('session cursor cache hits','session cursor cache count','parse count (total)')) ;
    
27. 
    
28.        SID STATISTIC#      VALUE NAME
    
29. ---------- ---------- ---------- ----------------------------------------------------------------
    
30.        144        279        164 session cursor cache hits
    
31.        144        280         90 session cursor cache count
    
32.        144        330        221 parse count (total)
    
33. 
    
34. scott@DEX10g> select m.*, n.NAME
    
35.   2    from v$mystat m, v$statname n
    
36.   3   where m.STATISTIC# = n.STATISTIC#
    
37.   4     and (n.name in ('session cursor cache hits','session cursor cache count','parse count (total)')) ;
    
38. 
    
39.        SID STATISTIC#      VALUE NAME
    
40. ---------- ---------- ---------- ----------------------------------------------------------------
    
41.        144        279        166 session cursor cache hits
    
42.        144        280         90 session cursor cache count
    
43.        144        330        223 parse count (total)

复制代码

select m.*, n.NAME
  from v$mystat m, v$statname n
where m.STATISTIC# = n.STATISTIC#
   and (n.name in ('session cursor cache hits','session cursor cache count','parse count (total)')) ;
这条sql已经cache 到 pga中了。

官方文档中关于session cursor cache hints 有这样的描述

Number of hits in the session cursor cache. A hit means that the SQL (including recursive SQL) or PL/SQL statement did not have to be reparsed. Subtract this statistic from "parse count (total)" to determine the real number of parses that occurred.

相减变成负数了…

Maclean Liu(刘相兵

我印象中  10g中  soft soft parse 就不算在这个parse count (total)里了， 但是目前没有10g的环境测试证明。