关于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重新调整了。

Maclean Liu(刘相兵

我印象中  10g中  soft soft parse 就不算在这个parse count (total)里了， 但是目前没有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 中 一测便知

psufnxk2000

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

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)

复制代码

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>

复制代码

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

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

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

Maclean Liu(刘相兵

为什么不应该呢？

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(刘相兵

我的理解 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) ，其实一方面就是反应 软软解析的。

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文档早日面世。

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

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

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