parse count(hard) 大于 parse count(total)

Oracle Core Essential Internals for DBAs and Developers

上有这样一段话:

 

A few pages back I pointed out in a list of “surprises” that parsing and optimization can occur on an
execute call, this is a side effect of holding cursors whether through explicit coding or because of the
hidden PL/SQL optimizations. When you hold a cursor the user code is only handling a simple numeric
variable—but internally this identifies various structures that, eventually, lead to a child cursor and its
parent cursor in the library cache. If there is a demand from other sessions for free memory Oracle is
allowed to clear almost everything about that child cursor from memory—even when it’s a held cursor—
leaving only enough information for your session to be able to recreate the execution plan. When this
happens, the next time you execute the statement you will record a Miss in library cache during
execute and increment the parse count (hard) value even though you won’t increment the parse count
(total) value. Counter-intuitively, it is possible to get see a value of parse count (hard) that is higher
than the parse count (total).

 

模拟一下：

 

db:10.2.0.3

 

准备:

session 1:

SQL> alter session set session_cached_cursors=5000;

SQL> select sid from v$mystat where rownum=1;

       SID
----------
       134

 

session 2:

select  b.name,a.value
from v$sesstat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
and b.name in ('parse count (hard)', 'parse count (total)')
and a.sid =134;

 

session 3:

alter system flush shared_pool;

 

测试步骤:

1.session 1 执行

select object_id from eee where object_id=1;

3次

 

循环

begin

 

2.session 3 执行

alter system flush shared_pool;

 

3.session 1 执行

select object_id from eee where object_id=1;

 

end

 

4.session 2执行

select  b.name,a.value
from v$sesstat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
and b.name in ('parse count (hard)', 'parse count (total)')
and a.sid =134;

 

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse count (total)                                                     341
parse count (hard)                                                      349

 

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

转载于:http://blog.itpub.net/37279/viewspace-716801/