[20181009]12C FULL DATABASE CACHING.txt

[20181009]12C FULL DATABASE CACHING.txt

--//12C 提供一个新特性FULL DATABASE CACHING,测试看看.
--//官方文档:
https://docs.oracle.com/database/121/ADMIN/memory.htm#ADMIN14235

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2215923/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2215923/