這種Ratio超過100的是正常的嗎？

目前我們的監控對這種超過的處理有點問題

11g到19c各個版本都有這個情況。

這是正常現象， Cursor Cache Hit Ratio 指標的來源是： session cursor cache hits / (parse count (total) - parse count (hard)) 。其中 session cursor cache hits 是指在在 session cursor cache 中找到 cursor 的次數，這個可能會比較大。例如我們迴圈查詢某條 sql ，每一次從 session cursor cache 找到一次便是增加一次，而解析次數是不用再增加的。因此得到的 session cursor cache hits / (parse count (total) - parse count (hard)) 值是可能超過 100% 的。

SQL> select m.*, n.NAME

2 from v$mystat m, v$statname n

3 where m.STATISTIC# = n.STATISTIC#

4 and (n.name in ('session cursor cache hits','session cursor cache count','parse count (total)')) ;

SID STATISTIC# VALUE CON_ID NAME

---------- ---------- ---------- ---------- ------------------------------

1 643 0 0 session cursor cache hits

1 644 1 0 session cursor cache count

1 694 3 0 parse count (total)

declare

c number ;

begin

for i in 1 .. 10000 loop

execute immediate 'select count(*) from dba_objects' into c ;

end loop ;

end ;

/

PL/SQL procedure successfully completed.

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)')) ;

SID STATISTIC# VALUE CON_ID NAME

---------- ---------- ---------- ---------- ------------------------------

1 643 10000 0 session cursor cache hits

1 644 3 0 session cursor cache count

1 694 7 0 parse coun

當前這個資料庫中的總體命中率也是達到 314.79% 的。（這個指標是指例項啟動以來的總體指標統計，與 sysmetric 不同的是， sysmetric 是每一個間隔時間內的統計）

select a.value cache_hits,

b.value total_parses,

c.value hard_parses,

(b.value - c.value) soft_parses,

round((a.value / (b.value - c.value))*100,2)||'%' ratio

from v$sysstat a, v$sysstat b, v$sysstat c

where a.name = 'session cursor cache hits'

and b.name = 'parse count (total)'

and c.name = 'parse count (hard)';

CACHE_HITS TOTAL_PARSES HARD_PARSES SOFT_PARSES RATIO

---------- ------------ ----------- ----------- -----------------------------------------

4333516791 1444991516 68350276 1376641240 314.79%

參考： (Bug ID 6200422)

文件中開發已說明這是正常行為。

** NLEE 07/09/08 04:00 pm ***
@ I see the same behavior in 11.2.0 (label 'RDBMS_MAIN_LINUX_080708')
@ and am currently investigating.
*** NLEE 07/15/08 07:21 pm *** (CHG: Sta->32)
*** NLEE 07/15/08 07:21 pm ***
@ I spoke with base development and they confirmed that this
@ is the expected behavior. This is because the cache hit count
@ now includes kqd cursors but the parse count does not include
@ them. The documentation will need to be updated. Please
@ open a 'doc' bug for this.
@ .
@ Since this is the expected behavior, I am now closing this
@ bug with status 32.

Cursor Cache Hit Ratio超過100%

相關文章