The Optimizer and cache related statistics.

Jonathan Lewis in his excellent book on the CBO talks about new functionality within Oracle’s optimizer wherein the optimizer can use cache related statistics of objects to evaluate a more optimal execution plan.

All the following testing has been done on 10.2.0.3 with the hidden parameter _cache_stats_monitor set to TRUE (default is false)

The table cache_stats_1$ which is used to monitor lifetime caching statistics (c0902000.sql) is populated by MMON periodically via a MERGE statement which does not seem to be using bind variables. You can also populate the same directly using

SQL> exec dbms_stats_internal.flush_cache_stats;

PL/SQL procedure successfully completed.

For the purpose of this discussion we will concentrate on a normal heap table SCOTT.T1 with data_object_id = 58417 which is a replica of dba_tables.


SQL> select * from cache_stats_1$ where DATAOBJ#=58417

DATAOBJ# INST_ID CACHED_AVG CACHED_SQR_AVG CACHED_NO CACHED_SEQ_NO
---------- ---------- ---------- -------------- ---------- -------------
CHR_AVG CHR_SQR_AVG CHR_NO CHR_SEQ_NO LGR_SUM LGR_LAST PHR_LAST
---------- ----------- ---------- ---------- ---------- ---------- ----------
SPARE1 SPARE2 SPARE3 SPARE4 SPARE5
---------- ---------- ---------- ---------- ----------
58417 1 118.889076 14192.4521 35 2644
0 0 1 2575 0 768 0

Now if we were to gather cache related statistics for this table

SQL> exec dbms_stats.gather_table_stats('SCOTT','T1',stattype=>'CACHE');

PL/SQL procedure successfully completed.

A trace on the above API call reveals the following statement

********************************************************
insert into tab_stats$ (obj#,cachedblk,cachehit,logicalread) values (:1,:2,:3,:4)

Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=07723aa8 bln=22 avl=04 flg=05
value=58417
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=07723a84 bln=24 avl=03 flg=05
value=118
Bind#2
oacdty=02 mxl=22(00) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=07723a60 bln=24 avl=00 flg=05
Bind#3
oacdty=02 mxl=22(00) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=07723a3c bln=24 avl=00 flg=05

********************************************************

Only the cachedblk column appears to be populated. (I haven’t had a chance to check this value against x$bh)
SQL> select * from tab_stats$ where obj#=58417;

OBJ# CACHEDBLK CACHEHIT LOGICALREAD ROWCNT BLKCNT EMPCNT
---------- ---------- ---------- ----------- ---------- ---------- ----------
AVGSPC CHNCNT AVGRLN AVGSPC_FLB FLBCNT ANALYZETI SAMPLESIZE
---------- ---------- ---------- ---------- ---------- --------- ----------
FLAGS SPARE1 SPARE2 SPARE3
---------- ---------- ---------- ----------
SPARE4
--------------------------------------------------------------------------------
SPARE5
--------------------------------------------------------------------------------
SPARE6
---------
58417 118



Checking the execution plans of statements with "_optimizer_cache_stats" set to TRUE and FALSE we find the cost of the table is almost half in the former.

SQL> conn scott/tiger
Connected.
SQL> set autotrace traceonly explain
SQL> alter session set "_optimizer_cache_stats" = true;

Session altered.

SQL> select count(*) from t1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 6446 | 29 (0)| 00:00:01 |
-------------------------------------------------------------------


SQL> alter session set "_optimizer_cache_stats" = false;

Session altered.

SQL> select count(*) from t1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 56 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 6446 | 56 (0)| 00:00:01 |
-------------------------------------------------------------------


Also for fixed objects the API dbms_stats.gather_fixed_objects_stats populates tab_stats$