Oracle 12c的大表自動快取特性可以使較大的表自動快取到buffer cache中，這極大增強了以往對大表掃描往往消耗過多物理IO的不足。在RAC環境下，這個特性僅僅支援並行查詢，在單例項環境下這個特性同時支援並行查詢和序列查詢。

大表快取主要是為提高資料倉儲效能而設計的，不過我們仍然可以在混合型的應用中使用它以提高效能。大表自動快取使用基於物件熱度的演算法來跟蹤大表的使用情況,Oracle不會快取很小的表。想要啟用大表快取特性，必須設定DB_BIG_TABLE_CACHE_PERCENT_TARGET引數為非零值。另外，如果是在RAC環境下，引數PARALLEL_DEGREE_POLICY值必須為AUTO或ADAPTIVE才能啟用該特性。在RAC環境下，一個大表可能會被分割槽快取至所有例項之上，如果無法完全快取大表的所有資料，那麼經常訪問的表將會被快取，餘下的資料將透過直接路徑讀的方式被讀取。

DB_BIG_TABLE_CACHE_PERCENT_TARGET引數用於設定被快取大表區域的大小，它的值是與buffer cache大小的百分比，如果你設定該引數為80(%)，那麼意味著buffer cache的80%將被用於快取大表使用，其餘20%被用於正常快取資料塊使用(透過針對OLTP型負載)。
DB_BIG_TABLE_CACHE_PERCENT_TARGET引數預設值為0，它的值可以是0-90(%)，該引數為動態引數。

動態效能檢視V$BT_SCAN_CACHE和V$BT_SCAN_OBJ_TEMPS提供了關於大表快取的相關資訊。

我的測試環境資訊如下:
Buffer Cache大小3G，測試表auditlog，大小936MB，存放在con_id為4的PDB上，db_big_table_cache_percent_target引數預設為0
SQL> select name,bytes/1024/1024 sizeMB from v$sgainfo;
NAME                                 SIZEMB
-------------------------------- ----------
Buffer Cache Size                      3216
Maximum SGA Size                       8192

SQL> select count(*) from auditlog;
COUNT(*)
----------
   3657117

SQL> select owner,segment_name,bytes/1024/1024 sizeMB from dba_segments where segment_name='AUDITLOG';
OWNER           SEGMENT_NAME             SIZEMB
--------------- -------------------- ----------
ORAUSER         AUDITLOG                    936

SQL> select owner,object_name,con_id from cdb_objects where data_object_id=209743;
OWNER OBJECT_NAME CON_ID
----------------- ----------------------- ----------
ORAUSER AUDITLOG 4

SQL> show parameter big_table
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_big_table_cache_percent_target string 0

根據我的buffer cache的大小，如果想將測試表全部快取在記憶體中，則引數db_big_table_cache_percent_target需要設定為30左右。3216*30%=964MB 使用下面的sql開啟大表自動快取特性。

SQL> alter system set db_big_table_cache_percent_target=30;
System altered.

使用序列全表掃描的方式查詢auditlog表,需要118832次物理讀。
SQL> set timing on
SQL> set autot traceonly
SQL> select /*+ full (auditlog) */ count(*) from auditlog;

Elapsed: 00:00:01.01

Execution Plan
----------------------------------------------------------
Plan hash value: 698576758

-----------------------------------------------------------------------
| Id | Operation          | Name     | Rows | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 | 32454   (1)| 00:00:02 |
|   1 | SORT AGGREGATE    |          |     1 |            |          |
|   2 |   TABLE ACCESS FULL| AUDITLOG | 3657K| 32454   (1)| 00:00:02 |
-----------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
     118089 consistent gets
     118832 physical reads
          0 redo size
        545 bytes sent via SQL*Net to client
        552 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

再次執行SQL時，物理讀已經變成9144次了，同時v$bt_scan_cache檢視中已經發現了1個物件。v$bt_scan_obj_temps檢視顯示的DATAOBJ#列209743,就是auditlog表所對應的data_object_id。
TEMPERATURE代表物件被訪問的熱度,這裡的3000代表我對該物件掃描了3次。POLICY為MEM_ONLY表示auditlog表全部都快取在記憶體中。那為什麼本次掃描還需要9144次物理讀呢？

SQL> select * from v$bt_scan_cache;

BT_CACHE_ALLOC BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP     CON_ID
-------------- --------------- ------------ ---------------- --------------- ----------
    .300025693              30            1            98505            1000          0

SQL> select * from V$BT_SCAN_OBJ_TEMPS;

       TS#   DATAOBJ# SIZE_IN_BLKS TEMPERATURE POLICY     CACHED_IN_MEM     CON_ID
---------- ---------- ------------ ----------- ---------- ------------- ----------
    262149     209743       119600        3000 MEM_ONLY          119600          0

Elapsed: 00:00:00.28

Execution Plan
----------------------------------------------------------
Plan hash value: 698576758

-----------------------------------------------------------------------
| Id | Operation          | Name     | Rows | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 | 32454   (1)| 00:00:02 |
|   1 | SORT AGGREGATE    |          |     1 |            |          |
|   2 |   TABLE ACCESS FULL| AUDITLOG | 3657K| 32454   (1)| 00:00:02 |
-----------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
     118089 consistent gets
       9144 physical reads
          0 redo size
        545 bytes sent via SQL*Net to client
        552 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

將db_big_table_cache_percent_target引數調整到35(%)之後，就完全沒有物理讀了。執行時間也從1.01秒下降至0.27秒。
SQL> alter system set db_big_table_cache_percent_target=35;
System altered.

SQL> select /*+ full (auditlog) */ count(*) from auditlog;

Elapsed: 00:00:00.27

Execution Plan
----------------------------------------------------------
Plan hash value: 698576758

-----------------------------------------------------------------------
| Id | Operation          | Name     | Rows | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 | 32454   (1)| 00:00:02 |
|   1 | SORT AGGREGATE    |          |     1 |            |          |
|   2 |   TABLE ACCESS FULL| AUDITLOG | 3657K| 32454   (1)| 00:00:02 |
-----------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
     118089 consistent gets
          0 physical reads
          0 redo size
        545 bytes sent via SQL*Net to client
        552 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed


SQL> select * from V$BT_SCAN_OBJ_TEMPS;

TS# DATAOBJ# SIZE_IN_BLKS TEMPERATURE POLICY CACHED_IN_MEM CON_ID
---------- ---------- ------------ ----------- ---------- ------------- ----------
262149 209743 119600 4000 MEM_ONLY 119600 0

SQL> select * from v$bt_scan_cache;

BT_CACHE_ALLOC BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP CON_ID
-------------- --------------- ------------ ---------------- --------------- ----------
.350011753 35 1 88663 1000 0

如果將引數調小，還可以看到POLICY為MEM_PART，表示auditlog表只有部分資料cache在記憶體中，這樣其餘不在記憶體中的部分就透過直接路徑讀的方式訪問，這裡存在11444次物理讀。
SQL> select * from V$BT_SCAN_OBJ_TEMPS;

TS# DATAOBJ# SIZE_IN_BLKS TEMPERATURE POLICY CACHED_IN_MEM CON_ID
---------- ---------- ------------ ----------- ---------- ------------- ----------
262149 209743 119600 3000 MEM_PART 107478 0

SQL> select * from v$bt_scan_cache;
BT_CACHE_ALLOC BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP     CON_ID
-------------- --------------- ------------ ---------------- --------------- ----------
    .249500317              25            1           107478            1000          0

SQL> set timing on
SQL> select * from auditlog;

3657117 rows selected.

Elapsed: 00:00:00.27

Execution Plan
----------------------------------------------------------
Plan hash value: 698576758

-----------------------------------------------------------------------
| Id | Operation          | Name     | Rows | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 | 32454   (1)| 00:00:02 |
|   1 | SORT AGGREGATE    |          |     1 |            |          |
|   2 |   TABLE ACCESS FULL| AUDITLOG | 3657K| 32454   (1)| 00:00:02 |
-----------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
     118089 consistent gets
      11444 physical reads
          0 redo size
        545 bytes sent via SQL*Net to client
        552 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

相關的官方文件：

for information about automatic big table caching
for information about automatic big table caching
for information about the DB_BIG_TABLE_CACHE_PERCENT_TARGET initialization parameter
for information about the V$BT_SCAN* views

12c 新特性之大表自動快取 Automatic Big Table Caching

相關文章