[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