[20181009]12C FULL DATABASE CACHING.txt

[20181009]12C FULL DATABASE CACHING.txt

--//12C 提供一個新特性FULL DATABASE CACHING,測試看看.
--//官方文件:


6.5 Using Force Full Database Caching Mode

An Oracle Database instance can cache the full database in the buffer cache.

Note:

This feature is available starting with Oracle Database 12c Release 1 (12.1.0.2).

6.5.1 About Force Full Database Caching Mode

In default caching mode, Oracle Database does not always cache the underlying data when a user queries a large table
because doing so might remove more useful data from the buffer cache. Starting with Oracle Database 12c Release 1
(12.1.0.2), if the Oracle Database instance determines that there is enough space to cache the full database in the
buffer cache and that it would be beneficial to do so, then the instance automatically caches the full database in the
buffer cache.

Caching the full database in the buffer cache might result in performance improvements. You can force an instance to
cache the database in the buffer cache using an ALTER DATABASE FORCE FULL DATABASE CACHING statement. This statement
puts the instance in force full database caching mode. In this mode, Oracle Database assumes that the buffer cache is
large enough to cache the full database and tries to cache all blocks that are accessed subsequently.

When an Oracle Database instance is in force full database caching mode, the following query returns YES:

SELECT FORCE_FULL_DB_CACHING FROM V$DATABASE;

When an instance is in default caching mode, NOCACHE LOBs are not cached in the buffer cache. However, when an instance
is in force full database caching mode, NOCACHE LOBs can be cached in the buffer cache. Also, both LOBs that use
SecureFiles LOB storage and LOBs that use BasicFiles LOB storage can be cached in the buffer cache in force full
database caching mode only.

Note:

    When an instance is put in force full database caching mode, database objects are not loaded into the buffer cache
    immediately. Instead, they are cached in the buffer cache when they are accessed.

    In a multitenant environment, force full database caching mode applies to the entire multitenant container database
    (CDB), including all of its pluggable databases (PDBs).

    Information about force full database caching mode is stored in the control file. If the control file is replaced or
    recreated, then the information about the force full database caching mode is lost. A restored control file might or
    might not include this information, depending on when the control file was backed up.

1.環境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

2.測試:
SYS@test> show sga
Total System Global Area  805306368 bytes
Fixed Size                  8924064 bytes
Variable Size             213910624 bytes
Database Buffers          574619648 bytes
Redo Buffers                7852032 bytes

--//574619648/8192 = 70144

SYS@test> SELECT FORCE_FULL_DB_CACHING FROM V$DATABASE;
FOR
---
NO

SYS@test> ALTER DATABASE FORCE FULL DATABASE CACHING;
ALTER DATABASE FORCE FULL DATABASE CACHING
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any instance

--//必須在mount狀態執行.
--//先建立"大表"
SCOTT@test01p> create table t1 as select * from all_objects;
Table created.

SCOTT@test01p> insert into t1 select * from t1;
18521 rows created.

SCOTT@test01p> insert into t1 select * from t1;
37042 rows created.

SCOTT@test01p> commit ;
Commit complete.

SCOTT@test01p> insert into t1 select * from t1;
74084 rows created.

....

SCOTT@test01p> commit ;
Commit complete.

SCOTT@test01p> select SEGMENT_NAME,BYTES/1024/1024,blocks from dba_segments where owner=user and segment_name='T1';
SEGMENT_NAME         BYTES/1024/1024     BLOCKS
-------------------- --------------- ----------
T1                                88      11264

--//分析表:
execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't1',Estimate_Percent => NULL,
Method_Opt => 'FOR ALL COLUMNS SIZE repeat ',Cascade => True ,No_Invalidate => false);

SYS@test> alter system flush buffer_cache;
System altered.

SCOTT@test01p> @ viewsess physical%read%direct
NAME                                                                   STATISTIC#    VALUE        SID
---------------------------------------------------------------------- ---------- -------- ----------
physical reads direct                                                         151        0        258
physical reads direct temporary tablespace                                    164        0        258
physical reads direct (lob)                                                   247        0        258
physical reads direct for securefile flashback block new                      258        0        258

SCOTT@test01p> select count(*) from t1;
  COUNT(*)
----------
    592672

SCOTT@test01p> @ viewsess physical%read%direct
NAME                                                                   STATISTIC#    VALUE        SID
---------------------------------------------------------------------- ---------- -------- ----------
physical reads direct                                                         151    10609        258
physical reads direct temporary tablespace                                    164        0        258
physical reads direct (lob)                                                   247        0        258
physical reads direct for securefile flashback block new                      258        0        258
--//走直接路徑讀,視乎oracle 12c改變了直接路徑讀的閾值.大於_small_table_threshold還不行.

SYS@test> @ hide _small_table_threshold
NAME                   DESCRIPTION                                          DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
---------------------- ---------------------------------------------------- ------------- ------------- --------------
_small_table_threshold lower threshold level of table size for direct reads TRUE          1141          1141

--//要佔用許多塊才會出現直接路徑讀.

3.修改FORCE_FULL_DB_CACHING=YES看看.

SYS@test> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@test> startup mount ;
ORACLE instance started.
Total System Global Area  805306368 bytes
Fixed Size                  8924064 bytes
Variable Size             297796704 bytes
Database Buffers          490733568 bytes
Redo Buffers                7852032 bytes
Database mounted.

SYS@test> ALTER DATABASE FORCE FULL DATABASE CACHING;
Database altered.

SYS@test> SELECT FORCE_FULL_DB_CACHING FROM V$DATABASE;
FOR
---
YES

SYS@test> alter database open ;
Database altered.

SYS@test> # alter pluggable database all open;
Pluggable database altered.

SCOTT@test01p> column value format 9999999
SCOTT@test01p> @ viewsess physical%read%direct
NAME                                                                   STATISTIC#    VALUE        SID
---------------------------------------------------------------------- ---------- -------- ----------
physical reads direct                                                         151        0         96
physical reads direct temporary tablespace                                    164        0         96
physical reads direct (lob)                                                   247        0         96
physical reads direct for securefile flashback block new                      258        0         96

SCOTT@test01p> select count(*) from t1;
  COUNT(*)
----------
    592672

SCOTT@test01p> @ viewsess physical%read%direct
NAME                                                                   STATISTIC#    VALUE        SID
---------------------------------------------------------------------- ---------- -------- ----------
physical reads direct                                                         151        0         96
physical reads direct temporary tablespace                                    164        0         96
physical reads direct (lob)                                                   247        0         96
physical reads direct for securefile flashback block new                      258        0         96

--//使用這個特性,視乎會關閉直接路徑讀,只要buffer cache能容下資料段.

SCOTT@test01p> select data_object_id,object_id from dba_objects where owner=user and object_name='T1';
DATA_OBJECT_ID  OBJECT_ID
-------------- ----------
         22571      22571

SCOTT@test01p> select count(*) from v$bh where OBJD=22571;
  COUNT(*)
----------
     11986

SCOTT@test01p> select count(*) from v$bh where OBJD=22571 and STATUS<>'free';
  COUNT(*)
----------
     10616    

SCOTT@test01p> select blocks from dba_tables where owner=user and table_name='T1';
    BLOCKS
----------
     11131

4.建立更大的表看看是否出現直接路徑讀.

SYS@test> show sga
Total System Global Area  805306368 bytes
Fixed Size                  8924064 bytes
Variable Size             230687840 bytes
Database Buffers          557842432 bytes
Redo Buffers                7852032 bytes
SYS@test> select 557842432/8192 from dual ;
557842432/8192
--------------
         68096

--//資料快取大致相當於70000塊. 要建立70000*2 = 140000記錄,換一個方式建立.這樣產生日誌小一些.

SCOTT@test01p> drop table t1 purge ;
Table dropped.

SCOTT@test01p> create table t1 as select rownum id from dual connect by level<=2;
Table created.

SCOTT@test01p> ALTER TABLE t1 MINIMIZE RECORDS_PER_BLOCK ;
Table altered.

SCOTT@test01p> insert into t1 select rownum+2 from dual connect by level <=140000-2;
139998 rows created.

SCOTT@test01p> commit;
Commit complete.

SCOTT@test01p> select SEGMENT_NAME,BYTES/1024/1024,blocks from dba_segments where owner=user and segment_name='T1';
SEGMENT_NAME         BYTES/1024/1024     BLOCKS
-------------------- --------------- ----------
T1                               557      71296

--//557M,佔用71296塊.

SYS@test> alter system flush buffer_cache;
System altered.

SCOTT@test01p> @ viewsess %physical%read%direct
NAME                                                                   STATISTIC#         VALUE        SID
---------------------------------------------------------------------- ---------- ------------- ----------
physical reads direct                                                         151             0        262
physical reads direct temporary tablespace                                    164             0        262
physical reads direct (lob)                                                   247             0        262
physical reads direct for securefile flashback block new                      258             0        262

SCOTT@test01p> select count(*) from t1;
  COUNT(*)
----------
    140000

SCOTT@test01p> @ viewsess %physical%read%direct
NAME                                                                   STATISTIC#         VALUE        SID
---------------------------------------------------------------------- ---------- ------------- ----------
physical reads direct                                                         151             0        262
physical reads direct temporary tablespace                                    164             0        262
physical reads direct (lob)                                                   247             0        262
physical reads direct for securefile flashback block new                      258             0        262

--//沒有走physical reads direct.

SCOTT@test01p> column value format 999999999999
SCOTT@test01p> @ viewsess %logical%read%

NAME                                                                   STATISTIC#         VALUE        SID
---------------------------------------------------------------------- ---------- ------------- ----------
session logical reads                                                          14            44        262
session logical reads in local numa group                                      15             0        262
session logical reads in remote numa group                                     16             0        262
logical read bytes from cache                                                 147        360448        262
session logical reads - IM                                                   1385             0        262

SCOTT@test01p> select count(*) from t1;
  COUNT(*)
----------
    140000

SCOTT@test01p> @ viewsess %logical%reads%
NAME                                                                   STATISTIC#         VALUE        SID
---------------------------------------------------------------------- ---------- ------------- ----------
session logical reads                                                          14         70216        262
session logical reads in local numa group                                      15             0        262
session logical reads in remote numa group                                     16             0        262
session logical reads - IM                                                   1385             0        262
--//走邏輯讀.

SCOTT@test01p> select data_object_id,object_id from dba_objects where owner=user and object_name='T1';
DATA_OBJECT_ID  OBJECT_ID
-------------- ----------
         22575      22575

SCOTT@test01p> select count(*) from v$bh where OBJD=22575 and STATUS<>'free';
  COUNT(*)
----------
     54685

SCOTT@test01p> select count(*) from v$bh where OBJD=22575 ;
  COUNT(*)
----------
     54683

--//大部分資料塊都在資料快取中.這種模式要資料快取充足,才能發揮作用.同時也關閉直接路徑讀.
--//實際上如果這樣設定,exadata的優勢無法充分出來.

5.收尾:
--//在mount狀態執行:
SYS@test> startup mount ;
ORACLE instance started.
Total System Global Area  805306368 bytes
Fixed Size                  8924064 bytes
Variable Size             297796704 bytes
Database Buffers          490733568 bytes
Redo Buffers                7852032 bytes
Database mounted.

SYS@test> ALTER DATABASE NO FORCE FULL DATABASE CACHING;
Database altered.

SYS@test> SELECT FORCE_FULL_DB_CACHING FROM V$DATABASE;
FOR
---
NO