1 问题的提出
所说的数据库缓冲区对执行计划的影响实际的意思是一个数据库对象（表）是否已被读入到缓冲区，多大比例的数据已被缓冲对Oracle优化器的成本估算是否会产生影响。
一个众所周知的事实是，内存的读写性能远远超过磁盘的读写性能，以延迟时间来说，内存在在10纳秒到100纳秒左右，而现在常用的SSD磁盘来说，延迟也在几十到几百微秒之间，机械磁盘的延迟更是在10毫秒左右。现在的问题是，对数据库来说，从磁盘读取数据和从内存读取数据（数据是否被缓存，缓存多少），其性能差异十分明显，Oracle优化器在生成一条语句的执行计划计算成本时，会考虑这个差异吗？
上面的问题再具体一下，如果数据库的一个表已经被缓存了，会影响到执行计划的选择吗？表在缓冲区的比例是否也会影响到执行计划的选择？这个问题，对表和索引会有不同的考虑吗？
分析上面的问题，需要经过几个步骤，每个步骤有涉及到一个问题。分别是：
- 关于数据库对象的缓存，数据库会收集哪些方面的信息？
- 这些信息的收集方式是什么？是怎么控制的（是否有数据库参数或者隐含参数来控制）？
- 收集到的信息会影响到执行计算的成本估算吗？是一直影响还是收到参数控制？
- 对于索引的处理会和表有区别吗？毕竟索引和表的访问方式有较大差异。
还有一个和数据库的缓存相关问题是，数据库io的相关等待时间如db file sequence read和缓冲区读有关吗？
上面几个问题，还是从数据库参数方面开始入手比较容易，不管是参数还是隐含参数都比较容易查询得到？而统计信息，首先要确认的是是否收集，如果是没有收集，就没有进一步分析的必要了。
2 两个隐含参数
和数据库缓冲区有关的隐含参数有两个，可以由下面的语句查询出来（这里数据库的版本是Oracle 23AI）
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
FROM SYS.x$ksppi x, SYS.x$ksppcv y
WHERE x.inst_id = USERENV ('Instance')
AND y.inst_id = USERENV ('Instance')
AND x.indx = y.indx
AND x.ksppinm in ('_optimizer_cache_stats','_cache_stats_monitor');
NAME |VALUE|DESCRIB |
------------------------+-----+-------------------------------------------+
_optimizer_cache_stats |FALSE|cost with cache statistics |
_cache_stats_monitor |FALSE|if TRUE, enable cache stats monitoring |
这两个参数的默认值在Oracle 23AI中都是false，在10g时_cache_stats_monitor为true，从参数的描述可以看到，_cache_stats_monitor控制是否进行cache stats的监控，_optimizer_cache_stats控制优化器在计算成本时是否考虑cache统计信息。_cache_stats_monitor是系统级参数，设置后可以立即生效。_optimizer_cache_stats可以再会话和实例级设置。使用下面语句设置_optimizer_cache_stats为true
SQL> alter system set "_cache_stats_monitor" = true;
System altered.
3 Oracle收集的关于cache的相关信息
Oracle收集的关于表和内存的cache相关信息可以Oracle的数据字典里获得一些端倪。和cache可能有关的视图有下面几个：
3.1 v$segstat中-对象物理和逻辑读信息
SELECT * FROM v$segstat WHERE OBJ#=475;
TS#|OBJ#|DATAOBJ#|STATISTIC_NAME |STATISTIC#|VALUE|CON_ID|
---+----+--------+-----------------------------+----------+-----+------+
0| 475| 475|logical reads | 0| 128| 3|
0| 475| 475|buffer busy waits | 1| 0| 3|
0| 475| 475|gc buffer busy | 2| 0| 3|
0| 475| 475|db block changes | 3| 0| 3|
0| 475| 475|physical reads | 4| 12| 3|
0| 475| 475|physical writes | 5| 0| 3|
--省去多行----
0| 475| 475|IM db block changes | 29| 0| 3|
视图v$segstat中可以查询到一个分段的物理读和逻辑读的值，一个数据块在第一次读入数据缓冲区时，在发生一个逻辑读的同时，也会发生一个物理读，因此，逻辑读和物理读的指标也会反映出了一个数据库对象是否已经被缓存，缓存的大致比例是多少。
3.2 DBA_TAB_STATISTICS里的相关列
SELECT * FROM DBA_TAB_STATISTICS WHERE table_name='SALES_NO_PAR';;
OWNER|TABLE_NAME |PARTITION_NAME|PARTITION_POSITION|SUBPARTITION_NAME|SUBPARTITION_POSITION|OBJECT_TYPE|NUM_ROWS|BLOCKS|EMPTY_BLOCKS|AVG_SPACE|CHAIN_CNT|AVG_ROW_LEN|AVG_SPACE_FREELIST_BLOCKS|NUM_FREELIST_BLOCKS|AVG_CACHED_BLOCKS|AVG_CACHE_HIT_RATIO|IM_IMCU_COUNT|IM_BLOCK_COUNT|IM_STAT_UPDATE_TIME|SCAN_RATE|SAMPLE_SIZE|LAST_ANALYZED |
-----+------------+--------------+------------------+-----------------+---------------------+-----------+--------+------+------------+---------+---------+-----------+-------------------------+-------------------+-----------------+-------------------+-------------+--------------+-------------------+---------+-----------+-----------------------+
SH |SALES_NO_PAR| | | | |TABLE | 724873| 4512| 0| 0| 0| 29| 0| 0| | | | | | | 724873|2025-02-25 07:42:59.000|
DBA_TAB_STATISTICS表里有AVG_CACHED_BLOCKS和AVG_CACHE_HIT_RATIO两列，从列的名称来看是和表的缓冲统计信息相关的，这两个都是平均值，应该都是基于某个时间段的。检查这时隐含参数_cache_stats_monitor的值为true，表中这两列的值却是空的。
3.3 CACHE_STATS_1$视图
SELECT * FROM "CACHE_STATS_1$";
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|
--------+-------+----------+--------------+---------+-------------+-------+-----------+------+----------+-------+--------+--------+------+------+------+------+------+
数据字典表CACHE_STATS_1$也存储与数据库对象缓存有关的信息，我的数据库是Oracle 23AI，部署的容器环境，在pdb里面查询，表的空的，在cdb里面查询，有368条数据，前面的几条数据如下
SELECT DATAOBJ#,o.OBJECT_NAME,INST_ID,CACHED_AVG,CACHED_NO, CHR_AVG FROM "CACHE_STATS_1$" c LEFT JOIN dba_objects o ON o.DATA_OBJECT_ID=c.DATAOBJ#;
DATAOBJ# OBJECT_NAME INST_ID CACHED_AVG CACHED_NO CHR_AVG
---------- -------------------------------- ---------- ---------- ---------- ----------
6 TS$ 1 20.6666667 10 1
2 ICOL$ 1 3599.6 10 .986879861
8 C_FILE#_BLOCK# 1 374.733333 10 .943379358
表里没有关于用户定义的表或索引的cache信息，有的只是Oracle数据库内置的数据库对象的信息。
3.4 检查数据库对象是否被缓存
select OBJD,STATUS,count(*) from v$bh where OBJD=77193 group by OBJD,STATUS;
OBJD|STATUS|COUNT(*)|
----+------+--------+
数据库刚启动，数据库还未被缓存时，v$BH视图中查询的该对象的数据块数为0。
select OBJD,STATUS,count(*) from v$bh where OBJD=77193 group by OBJD,STATUS;
OBJD STATUS COUNT(*)
---------- ---------- ----------
77193 xcur 4431
77193 free 3280
运行sql语句
select * from sh.sales_no_par where to_char(time_id,‘YYYY-MM-DD’) =‘2019-08-15’;
数据库执行了全表扫描，这是再查询v$BH视图，有4431个数据块为xcur状态，3280个块为free状态，官网上这两个状态值的说明如下
- free - Not currently in use
- xcur - Exclusive。
一个是现在未使用，一个是独占模式。清理一下数据缓冲区再看
SQL> alter system flush buffer_pool default;--清除默认的数据缓冲区
System altered.
SQL> select OBJD,STATUS,count(*) from v$bh where OBJD=77193 group by OBJD,STATUS;
OBJD STATUS COUNT(*)
---------- ---------- ----------
77193 free 7710 --所有状态都为缓冲区状态都为free
所有的数据块的状态都为free了，在下执行查询这个表的，就要重新缓存，发起物理读了。
3.5 收集cache统计信息
begin dbms_stats.gather_table_stats('SH','SALES_NO_PAR',stattype => 'CACHE'); end;
dbms_stats包的gather_table_stats关于stattype参数的值，官方文档里看到的是只支持’DATA’,但是在运行这个存储过程时，给这个参数传入’CACHE’也不会报错，运行完之后查询
DBA_TAB_STATISTICS视图既没有CACHE列的相关信息，LAST_ANALYZED的值也没有变化，好像这个过程的运行没有任何效果一样，或许和数据版版本和环境有关（Oracle 23AI，PDB）。
3.6 结论
虽然有相关的隐含参数，dbms_stats包里也有相关的存储过程，但在我的数据库环境里没有查到关于数据库对象（用户定义的）的缓存的信息，不能确定Oracle会收集数据库对象（用户定义的）缓存信息。
4 _optimizer_cache_stats参数的影响
虽然不能确定Oracle会收集数据库对象的缓存信息，但是隐含参数_optimizer_cache_stats却会影响数据库在访问表时的成本计算，这个隐含参数控制Oracle优化器在生成执行计划时是否考虑对象是否在数据缓冲区内，这个参数可以在实例和会话级设置，要看的是下面的语句的执行计划
select * from sh.sales_no_par where to_char(time_id,‘YYYY-MM-DD’) =‘2019-08-15’;
这个参数设置为true和false时，上面语句的执行计划对比如下
---值为false时
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 1233 | |
| 1 | TABLE ACCESS FULL | SALES_NO_PAR| 7249 | 227K | 1233 | 00:00:15 |
-----------------------------------------+-----------------------------------+
---值为true时
-----------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 1232 | |
| 1 | TABLE ACCESS FULL | SALES_NO_PAR| 7249 | 227K | 1232 | 00:00:15 |
-----------------------------------------+-----------------------------------+
设置为true时全表扫描的成本减少了1，反复试了几次，这个细微的差别一直存在，甚至在清除了数据库的默认缓冲区，SALES_NO_PAR对象完全没有缓存的时候，只要参数值为true，这条语句的执行计划也是1232，而不是1233。通过设置10053事件追踪语句执行计划的生成，在表的统计信息二者相同，CBK（cached block）都为0，只是在分析时扫描成本的值差了1，相关部分对比如下：
--参数值设置为false
kkecdn: Single Table Predicate:TO_CHAR("SALES_NO_PAR"."TIME_ID",'YYYY-MM-DD')='2019-08-15'
Table: SALES_NO_PAR Alias: SALES_NO_PAR
Card: Original: 724873.000000rsel = 0.010000 Rounded: 7249 Computed: 7248.730000 Non Adjusted: 7248.730000
Scan IO Cost (Disk) = 1223.000000
--参数值设置为true
kkecdn: Single Table Predicate:TO_CHAR("SALES_NO_PAR"."TIME_ID",'YYYY-MM-DD')='2019-08-15'
Table: SALES_NO_PAR Alias: SALES_NO_PAR
Card: Original: 724873.000000rsel = 0.010000 Rounded: 7249 Computed: 7248.730000 Non Adjusted: 7248.730000
Scan IO Cost (Disk) = 1222.000000
上面两者Scan IO Cost (Disk)的值差了1，这个差别最后也反映到了最后的执行计划中。
5 索引是否缓存的影响
Oracle优化器假设对索引的索引访问都是物理读，这个假设不总是正确的，因此，从Oracle 8i开始，Oracle提供了一个参数，名为optimizer_index_caching，可以对这个假设做一些纠正的余地，这个参数可以在实例和会话级进行设置。这个参数的设置对单表、简单的索引访问的成本计算没有影响，但是为影响到嵌套连接的内表的索引访问的成本计算。这个参数的默认值是0，即不考虑索引是否缓存。设置为非零值（1-100）则产生一定的影响。
SQL> show parameter optimizer_index_caching ;--显示这个参数的当前值
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_caching integer 0
--查看下面这条语句的执行计划
SELECT c.CUST_FIRST_NAME,c.CUST_LAST_NAME, s.AMOUNT_SOLD
FROM SH.CUSTOMERS c INNER JOIN SH.SALES_NO_PAR s ON c.CUST_ID=s.CUST_ID WHERE c.CUST_ID=6394;
--语句的执行计划
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 346 | 10380 | 14 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 346 | 10380 | 14 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 346 | 10380 | 14 (0)| 00:00:01 |
| 3 | INLIST ITERATOR | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 3 | 60 | 7 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 3 | | 4 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_SALE_N_C | 1 | | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | SALES_NO_PAR | 115 | 1150 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
SQL> alter session set optimizer_index_caching=50;--设置这个参数为非零值
Session altered.
--语句的执行计划
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 346 | 10380 | 7 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 346 | 10380 | 7 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 346 | 10380 | 7 (0)| 00:00:01 |
| 3 | INLIST ITERATOR | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 3 | 60 | 6 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 3 | | 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_SALE_N_C | 1 | | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | SALES_NO_PAR | 115 | 1150 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
参数设置前后的执行计划的操作都是相同的，优化器估算的成本却有相当的差异，其中两个索引访问INDEX RANGE SCAN ，INDEX UNIQUE SCAN成本的不同是两个执行计划最终成本有明显差距的原因。在设置参数为非零值时，索引访问的估算成本明显降低。但是在运行一条简单语句
SELECT c.CUST_FIRST_NAME,c.CUST_LAST_NAME, s.AMOUNT_SOLD
FROM SH.CUSTOMERS c INNER JOIN SH.SALES_NO_PAR s ON c.CUST_ID=s.CUST_ID WHERE c.CUST_ID = 6394
参数为零时索引IDX_SALE_N_C的成本反而更低，看了还是得具体情况具体分析，不能简单的一概而论。
6 小结
这篇文章写的比较乱，主要记录了验证问题的过程，对于遇到的有些问题（Oracle是否会收集数据库对象的缓存信息）也没有得到明确的结果，（受限于实验环境和个人水平），能够确切知道的是隐含参数_optimizer_cache_stats和参数optimizer_index_caching确实会Oracle对优化器的成本计算产生影响。
