ORACLE 12C的 FORCE FULL DATABASE CACHING

                        ORACLE 12C的 FORCE FULL DATABASE CACHING

    在oracle 12c以前，当oracle前台进程每次进行大表的全扫描时，为了避免大量的
数据块对BUFFER CACHE进行冲击，于是就使用了DIRECT PATH READ的方式，由前台
进程直接读到了进程自己PGA中，跳过BUFFER CACHE.但是这种方式带来的不好地方
是下次当其他进程需要重新读取该表的数据块时，又需要从磁盘中去读，从而增加了IO负担.
来看一个例子

SQL> create table t1 pctfree 99 pctused 1  as select * from dba_objects;

Table created.

SQL> exec dbms_stats.gather_table_stats('SYS','T1');

PL/SQL procedure successfully completed.

查看该表的数据块总数和OBJECT_ID
SQL> select count(distinct(dbms_rowid.rowid_block_number(rowid))) from t1;

COUNT(DISTINCT(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)))
-----------------------------------------------------
                                                91409

SQL> select object_id from dba_objects where object_name='T1' and owner='SYS';

 OBJECT_ID
----------
    100252

重启实例，这样表T1就是第一次从磁盘中读取

SQL> select count(*) from t1;

  COUNT(*)
----------
     91409

SQL> select count(*) from v$bh where objd=100252;

  COUNT(*)
----------
         1

可以看到当前的BUFFER CACHE中只有一个数据块，说明前台进程使用的是DIRECT PATH READ方式，不在BUFFER CACHE中存放数据块.

  在12C中引入了一种方式叫作full database caching.对于使用DIRECT PATH READ的全表扫描这种情况数据库也会对表进行全缓存.
出现这一特性的背景主要是由于现在内存的价格比较低，部分数据库配置的内存的可能比整个数据库
都要大，这种情况下把块都缓冲到BUFFER CACHE中就比较有用.

In force full database caching mode, Oracle Database caches the entire database in memory
when the size of the database is smaller than the database BUFFER CACHE size.
All data files, including NOCACHE LOBs and LOBS
that use SecureFiles, are loaded into the BUFFER CACHE as they are being accessed.

修改数据库到full database caching模式

SQL> startup mount; 
ORACLE instance started.

Total System Global Area  994050048 bytes
Fixed Size                  2931712 bytes
Variable Size             473957376 bytes
Database Buffers          406847488 bytes
Redo Buffers                5455872 bytes
In-Memory Area            104857600 bytes
Database mounted.
SQL> alter database force full database caching;

Database altered.

SQL> select force_full_db_caching from v$database;

FOR
---
YES

修改成功，然后打开数据库
SQL> alter database open;

Database altered.

再次对表t1进行全表扫描

SQL> select count(*) from t1;

  COUNT(*)
----------
     91409

SQL> select count(*) from v$bh where objd=100252;

  COUNT(*)
----------
     43502

通过对v$bh的查询可以看出，在force database caching模式下，以前的使用的DIRECT PATH READ
方式，现在也会把数据库缓存到BUFFER CACHE中来了.