ITPub博客

首页 > Linux操作系统 > Linux操作系统 > parse count(hard) 大于 parse count(total)

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

原创 Linux操作系统 作者:warmbreeze 时间:2012-02-20 14:24:40 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-02-15

  • 博文量
    45
  • 访问量
    39136