10g中v$librarycache GETHITRATIO统计值失效

显示全部楼层 · 2017-3-27 08:04:23

关于10g中v$librarycache GETHITRATIO的认识
很多人资料上说shared pool中sql语句的命中率是看v$librarycache中的GETHITRATIO列的值，但是在10g中这种情况发生了改变。
请看下列情况：
第一个session登陆，并查询当前gets和gethits的值。
SQL> conn / as sysdba
Connected.
SQL> select namespace,gets,gethits,gethitratio from v$librarycache where namespace='SQL AREA';
NAMESPACE
GETSGETHITS GETHITRATIO
--------------- ---------- ---------- -----------
SQL AREA
2037122.059891998
SQL>
发出SQL语句
SQL> select count(*) from me.t_person;
COUNT(*)
----------
100
SQL>
再次观察gets和gethits的值
SQL> select namespace,gets,gethits,gethitratio from v$librarycache where namespace='SQL AREA';
NAMESPACE
GETSGETHITS GETHITRATIO
--------------- ---------- ---------- -----------
SQL AREA
2038122 .05986261
SQL>
发现gets增加1，GETHITS无变化。表示刚才发出的SQL语句未命中，属于正常情况。
现在第2个session登陆，并执行相同的SQL语句
SQL> conn / as sysdba
Connected.
SQL> select count(*) from me.t_person;
COUNT(*)
----------
100
SQL>
回到第一个session中，再次查询gets和gethits的值。
SQL> select namespace,gets,gethits,gethitratio from v$librarycache where namespace='SQL AREA';
NAMESPACE
GETSGETHITS GETHITRATIO
--------------- ---------- ---------- -----------
SQL AREA
2038122 .05986261
SQL>
现在 GETS和GETHITS的值没有发生变化，
由此可见，如果SQL在library cache中命中，GETS和GETHITS并没有增加，如果SQL在library cache中未命中，则GETS递增1，GETHITS不变。
那么SQL在library cache中的命中率又如何计算？
注意我的GETHITRATIO的值，只有5%的命中率，看起来这是极不正常的。
但实际上是由于GETHITRATIO统计失效引发的。

千问 · 2017-3-27 08:04:23

You probably tested that more than 3 times so the SQL is in session cursor cache, which can hold 20 cursors by default. Try a new SQL, or alter session set session_cached_cursors = 0 and test again.
Yong Huang

千问 · 2017-3-27 08:04:23

将session_cached_cursors = 0后，情况依然没有发生变化。
SQL> show parameter session_cached;
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors
integer 20
SQL> alter session set session_cached_cursors = 0;
Session altered.
SQL> select * from v$librarycache where namespace='SQL AREA';
NAMESPACE
GETSGETHITS GETHITRATIO PINSPINHITS
--------------- ---------- ---------- ----------- ---------- ----------
PINHITRATIORELOADS INVALIDATIONS DLM_LOCK_REQUESTS DLM_PIN_REQUESTS
----------- ---------- ------------- ----------------- ----------------
DLM_PIN_RELEASES DLM_INVALIDATION_REQUESTS DLM_INVALIDATIONS
---------------- ------------------------- -----------------
SQL AREA
1663 41.024654239 9939 8924
.89787705129
0
0
0

0
0
0

SQL> select * from me.t_book;
ID NAME
P_ID
---------- -------------------- ----------
1 abc
1
2 ccc
1
SQL> select * from v$librarycache where namespace='SQL AREA';
NAMESPACE
GETSGETHITS GETHITRATIO PINSPINHITS
--------------- ---------- ---------- ----------- ---------- ----------
PINHITRATIORELOADS INVALIDATIONS DLM_LOCK_REQUESTS DLM_PIN_REQUESTS
----------- ---------- ------------- ----------------- ----------------
DLM_PIN_RELEASES DLM_INVALIDATION_REQUESTS DLM_INVALIDATIONS
---------------- ------------------------- -----------------
SQL AREA
1664 41.02463942310008 8991
.898381295129
0
0
0

0
0
0

SQL> select * from me.t_book;
ID NAME
P_ID
---------- -------------------- ----------
1 abc
1
2 ccc
1
SQL> select * from v$librarycache where namespace='SQL AREA';
NAMESPACE
GETSGETHITS GETHITRATIO PINSPINHITS
--------------- ---------- ---------- ----------- ---------- ----------
PINHITRATIORELOADS INVALIDATIONS DLM_LOCK_REQUESTS DLM_PIN_REQUESTS
----------- ---------- ------------- ----------------- ----------------
DLM_PIN_RELEASES DLM_INVALIDATION_REQUESTS DLM_INVALIDATIONS
---------------- ------------------------- -----------------
SQL AREA
1664 41.02463942310014 8997
.898442181129
0
0
0

0
0
0

SQL> select * from me.t_book;
ID NAME
P_ID
---------- -------------------- ----------
1 abc
1
2 ccc
1
SQL> select * from v$librarycache where namespace='SQL AREA';
NAMESPACE
GETSGETHITS GETHITRATIO PINSPINHITS
--------------- ---------- ---------- ----------- ---------- ----------
PINHITRATIORELOADS INVALIDATIONS DLM_LOCK_REQUESTS DLM_PIN_REQUESTS
----------- ---------- ------------- ----------------- ----------------
DLM_PIN_RELEASES DLM_INVALIDATION_REQUESTS DLM_INVALIDATIONS
---------------- ------------------------- -----------------
SQL AREA
1664 41.02463942310025 9008
.898553616129
0
0
0

0
0
0

SQL>
现在问题的关键是在library cache中搜索父游标的次数，以及命中情况在哪里查看？
难道没有搜索父游标吗？不可能啊。

千问 · 2017-3-27 08:04:23

> 将session_cached_cursors = 0后，情况依然没有发生变化。
I can't reproduce it in 10.2.0.4 or 11.2.0.1. Always tell us exactly what version you use.
Yong Huang

千问 · 2017-3-27 08:04:23

我的版本是
10.2.0.1.0

千问 · 2017-3-27 08:04:23

Looks like there's difference between 10.2.0.3 and 10.2.0.4. I can reproduce your problem in 10.2.0.1 and 10.2.0.3 but not 10.2.0.4 (or 11.2.0.1).
Yong Huang

千问 · 2017-3-27 08:04:23

谢谢Yong Huang 的解答