Re: Columns AVG_CACHE_HIT_RATIO and AVG_CACHED_BLOCKS from DBA_TAB_STATISTCS

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_at_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_at_T0012C.brux0221> _at_undoc _cache_stats_monitor

Parameter                                               Is Default

------------------------------------------------------- ---------------
--------------- --------------------------------------------------
_cache_stats_monitor                                    TRUE
 TRUE            if TRUE, enable cache stats monitoring

​The script undoc.sql is:​

SYS AS SYSDBA_at_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_at_jlcomp.demon.co.uk>:

>
>
> 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_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Luis Santos <lsantos_at_pobox.com>
> 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]
>

--
http://www.freelists.org/webpage/oracle-l