重庆思庄Oracle、Redhat认证学习论坛

标题: 12c 新特性之大表自动缓存 Automatic Big Table Caching [打印本页]

作者: 郑全 时间: 2023-8-23 12:03
标题: 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

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)