12c 新特性之大表自动缓存 Automatic Big Table Caching

郑全

在12.1.0.2之前，当进行全表扫描时，如果表的大小超过_small_table_threshold (块数)
的值时，oracle直接使用direct path read的方式.随着内存越来越大，如果继续使用这种方式那么就
不能有效利用内存.在之前的版本中，可以使用cache选项，但是使用cache选项会把表的所有块放到buffer
cache的MRU端，这样无形会造成buffer cache的泛洪，另外一个方法就是使用keep池，而keep池使用的
单独的一个池，基于lru和块级别的，如果keep池过小，会使部分块被移出去，而另外的一部分块则又
存在于keep池中。所有都有一定的局限性.

SYS@PRODCDB> select a.ksppinm,b.ksppstvl,a.ksppdesc
  2      from x$ksppi a,x$ksppcv b
  3      where a.indx=b.indx
  4      and a.ksppinm like '\_small%' escape '\'
  5  ;


KSPPINM              KSPPSTVL           KSPPDESC
------------------------- -------------------- -----------------------------------------------------
_small_table_threshold    3602          lower threshold level of table size for direct reads


创建一个对像，对像大的小超过_small_table_threshold所定义的大小

SYS@PRODCDB> select CON_ID,TABLE_NAME, blocks
        from cdb_tables
         where table_name='BIGTAB';


    CON_ID TABLE_NAME            BLOCKS
---------- --------------------------- ----------
         3 BIGTAB            104782


查看当前direct path read的计数

SYS@pdbprod1> select name,value from v$sysstat where name like '%table scans (direct read)%';


NAME                                VALUE
------------------------------ ----------
table scans (direct read)               0


执行一次全表扫描
SYS@pdbprod1> select count(*) from apps.bigtab;


Elapsed: 00:00:00.52


Execution Plan
----------------------------------------------------------
Plan hash value: 2140185107


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




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


SYS@pdbprod1> select name,value from v$sysstat where name like '%table scans (direct read)%';


NAME                                VALUE
------------------------------ ----------
table scans (direct read)               1

由于该表被oracle认为是大表，因些使用了direct path read的方式来读取

刷新一下buffer cache

SYS@pdbprod1> alter system flush buffer_cache;


System altered.


SYS@pdbprod1> show sga;


Total System Global Area 2147482384 bytes
Fixed Size                  9136912 bytes
Variable Size             520093696 bytes
Database Buffers         1493172224 bytes
Redo Buffers                7639040 bytes
In-Memory Area            117440512 bytes
SYS@pdbprod1>

设置Big Table Cache percent,该参数可以动态修改，当前修改为buffer cache的百分之30

SYS@PRODCDB> alter system set db_big_table_cache_percent_target=30;


System altered.



再次对表BIGTAB表做全表扫描
SYS@pdbprod1> select count(*) from apps.bigtab;


Elapsed: 00:00:00.48


Execution Plan
----------------------------------------------------------
Plan hash value: 2140185107


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




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

SYS@PRODCDB> select name,value from v$sysstat where name like '%table scans (direct read)%';


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
table scans (direct read)                                                 2




SYS@PRODCDB> R
  1  select a.*,b.object_name from
  2      v$bt_scan_obj_temps a,cdb_objects b
  3       where a.DATAOBJ#=b.object_id
  4       and b.object_name='BIGTAB'
  5*


       TS#   DATAOBJ# SIZE_IN_BLKS TEMPERATURE POLICY     CACHED_IN_MEM     CON_ID OBJECT_NAME
---------- ---------- ------------ ----------- ---------- ------------- ---------- ------------------------------
        10      74114       104782        8000 MEM_PART           48754          3 BIGTAB


SYS@PRODCDB>  select * from v$bt_scan_cache;


BT_CACHE_ALLOC BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP     CON_ID
-------------- --------------- ------------ ---------------- --------------- ----------
    .303644834              30            1            55443            1000          0



通过查询v$bt_scan_obj_temps发现，CACHED_IN_MEM =MEM_PART 部分使用 Big Table Cache.
再加大 到65
SYS@PRODCDB> ALTER SYSTEM SET db_big_table_cache_percent_target=65;

System altered.

SYS@pdbprod1> select count(*) from apps.bigtab;

Elapsed: 00:00:00.44

Execution Plan
----------------------------------------------------------
Plan hash value: 2140185107

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


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

SYS@pdbprod1>


   物理读已经为零，

   Big Table Cache和以前的keep池的特性非常相似，都是用来尽可能的保持重要的对像块在内存，而不影响其数据块
但是Big Table Cache是default buffer cache的一部分，使用是基于对像的，temperatuer的替换方式.



注意：如果并行查询 要使用automatic big table cache，parallel_degree_policy 要为 auto，或 ADAPTIVE