JL, thanks for your kindly reply.
I have turned on _cache_stats_monitor three days ago in a 12c test
environment, but still see no info.
SYS AS SYSDBA@T0012C.brux0221> r
1 select OWNER, TABLE_NAME, AVG_CACHE_HIT_RATIO, AVG_CACHED_BLOCKS
2 from DBA_TAB_STATISTICS
3 where
4 AVG_CACHE_HIT_RATIO is not null
5 or
6* AVG_CACHED_BLOCKS is not null
no rows selected
SYS AS SYSDBA@T0012C.brux0221> @undoc _cache_stats_monitor
Parameter Is Default
Instance Value Description
------------------------------------------------------- ---------------
--------------- --------------------------------------------------
_cache_stats_monitor TRUE
TRUE if TRUE, enable cache stats monitoring
The script undoc.sql is:
SYS AS SYSDBA@T0012C.brux0221> l
1 select a.ksppinm "Parameter", b.KSPPSTDF "Is Default", c.ksppstvl
"Instance Value", a.KSPPDESC "Description"
2 from x$ksppi a, x$ksppcv b, x$ksppsv c
3 where a.indx = b.indx and a.indx = c.indx
4 and substr(ksppinm,1,1)='_'
5 and a.ksppinm like '%&1%'
6 order by a.ksppinm
I was also thinking on doing some experimentation with
dbms_stats.set_table_stats,
as you suggested, but using data collected from V$SEGMENT_STATISTICS, to
get some near real AVG_CACHE_HIT_RATIO and AVG_CACHED_BLOCKS computed from
statistics_name='physical reads'' and 'logical reads'...
*--*
*Att*
*Luis Santos*
2017-03-03 7:59 GMT-03:00 Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>:
I'd have to go back and do some reading (and new testing) to get the
details sorted, but these stats are for a feature that appeared in 10g as
an undocumneted option (and that I mentioned - including the threat - in
Cost Based Oracle Fundamentals).
According to a brief note I've just checked you have to set one parameter
to make Oracle record the stats and another parameter to use them. I
haven't found the notes yet but I have a vague memory of writing something
about Oracle using a rolling average mechanism (in 10g, at least) to keep
these figures up to date with the recent past.
If you want to do some experimentation first you can use
dbms_stats.set_table_stats() to set explicit values, parameters cachedblk
and cachehit.
If you want Oracle to use the cache stats (even the ones you've set with
set_table_stats() you have to set the parameter: _optimizer_cache_stats=true
If you want Oracle record the cache stats you have to set the parameter:
_cache_stats_monitor=true
If you don't set or monitor any stats but enable usage then you may find
that Oracle assumes 10% of anything will be cached.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Luis Santos <lsantos@xxxxxxxxx>
Sent: 02 March 2017 13:57:14
To: ORACLE-L
Subject: Columns AVG_CACHE_HIT_RATIO and AVG_CACHED_BLOCKS from
DBA_TAB_STATISTCS
I checked several 11g version databases
here
(we still don´t have a 12c database in production, shame)
querying
both
AVG_CACHE_HIT_RATIO and AVG_CACHED_BLOCKS
columns
from DBA_TAB_STATISTCS, which I was not aware since few minutes ago (shame
2).
In none of them I could find any info. These columns has always NULL
values.
Is there a setting to be enabled to have fill data filled?
1 select OWNER, TABLE_NAME, AVG_CACHE_HIT_RATIO, AVG_CACHED_BLOCKS
2 from DBA_TAB_STATISTICS
3 where
4 AVG_CACHE_HIT_RATIO is not null
5 or
6* AVG_CACHED_BLOCKS is not null
no rows selected
SQL> select * from v$version;
BANNER
------------------------------------------------------------
--------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit
Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
--
Att
Luis Santos
[https://lh3.googleusercontent.com/-AD-URpt0jeE/AAAAAAAAAAI/AAAAAAAB-
9c/LrffscVVpf8/s90-c-k/photo.jpg]
