DB_BIG_TABLE_CACHE_PERCENT_TARGET

转载: http://blog.itpub.net/26753337/viewspace-2122362

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