背景:

      Oracle10g之后引入的BCT(Block Change Tracking)功能，可以用以提高增量备份的效率，减少扫描的块数量时间，但对于开启了BCT功能的数据库，在业务高并发的期间可能会出现性能的缓慢问题，经常可以观察到等待事件block change tracking buffer space，change tracking file synchronous read/write，除了尽可能的应用最新的补丁来规避bug问题，Oracle官方建议可以通过调整BCT的相关隐含参数进行优化以尽可能的规避出现的性能问题。

        本文接下来主要讲解BCT涉及的主要优化参数。

BCT隐含参数(Version:Oracle 19.3):

select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from sys.x$ksppi a, sys.x$ksppcv b
where a.indx = b.indx and a.ksppinm like '%bct%'
order by name
​
NAME                                     VALUE                DESCRIPTION
---------------------------------------- -------------------- ------------------------------------------------------------------------------------------
_bct_bitmaps_per_file                    8                    number of bitmaps to store for each datafile
_bct_buffer_allocation_max               104857600            maximum size of all change tracking buffer allocations, in bytes
_bct_buffer_allocation_min_extents       1                    mininum number of extents to allocate per buffer allocation
_bct_buffer_allocation_size              2097152              size of one change tracking buffer allocation, in bytes
_bct_chunk_size                          0                    change tracking datafile chunk size, in bytes
_bct_crash_reserve_size                  262144               change tracking reserved crash recovery SGA space, in bytes
_bct_file_block_size                     0                    block size of change tracking file, in bytes
_bct_file_extent_size                    0                    extent size of change tracking file, in bytes
_bct_fixtab_file                                              change tracking file for fixed tables
_bct_health_check_interval               60                   CTWR health check interval (seconds), zero to disable
_bct_initial_private_dba_buffer_size     0                    initial number of entries in the private change tracking dba buffers
_bct_mrp_timeout                         600                  CTWR MRP wait timeout (seconds), zero to wait forever
_bct_public_dba_buffer_dynresize         2                    allow dynamic resizing of public dba buffers, zero to disable
_bct_public_dba_buffer_maxsize           0                    max buffer size permitted for public dba buffers, in bytes
_bct_public_dba_buffer_size              0                    total size of all public change tracking dba buffers, in bytes
​

 _bct_buffer_allocation_max:   

        参数说明:全部change tracking buffer分配的最大值

        默认值为:104857600

        建议值:根据Oracle MOS(Doc ID 2094946.1)

        推荐为4倍dba_buffer_count_public*dba_entry_count_public*dba_entry_size并且小于large_pool池大小

--可以通过表x$krcstat查询当前值
select dba_buffer_count_public*dba_entry_count_public*dba_entry_size from x$krcstat;

        参数测试:在Oracle19.3版本测试，_bct_buffer_allocation_max参数无法限制CTWR dba buffer的大小

--设置_bct_buffer_allocation_max为5M
SQL> alter system set "_bct_buffer_allocation_max"=5242880;
​
System altered.
--当前CTWR dba buffer大小为1.79M
SQL> select inst_id,pool,name,bytes/1024/1024 as MB ,con_id from gv$sgastat where pool='large pool';
​INST_ID POOL           NAME                                             MB     CON_ID
---------- -------------- ---------------------------------------- ---------- ----------1 large pool     free memory                              2028.84766          01 large pool     ASM map operations hashta                      .375          01 large pool     krcc extent chunk                        1.98046875          01 large pool     PX msg pool                                      15          01 large pool     CTWR dba buffer                            1.796875          0
--执行大批量的块更新之后，CTWR dba buffer大小变为14.359MB，超过了5MB，参数并不能限制CTWR dba buffer的大小
SQL> r1* select inst_id,pool,name,bytes/1024/1024 as MB ,con_id from gv$sgastat where pool='large pool'
​INST_ID POOL           NAME                                             MB     CON_ID
---------- -------------- ---------------------------------------- ---------- ----------1 large pool     free memory                              2016.28516          01 large pool     ASM map operations hashta                      .375          01 large pool     krcc extent chunk                        1.98046875          01 large pool     PX msg pool                                      15          01 large pool     CTWR dba buffer                           14.359375          0
​
SQL> 
NAME                                     VALUE                DESCRIPTION
---------------------------------------- -------------------- ------------------------------------------------------------------------------------------
​
_bct_buffer_allocation_max               5242880              maximum size of all change tracking buffer allocations, in bytes

_bct_public_dba_buffer_maxsize:

        参数说明:允许分配全部public change tracking dba buffer的最大值

        默认值为:0，自动分配

        建议值:

        _bct_public_dba_buffer_size<=建议值<=2*_bct_public_dba_buffer_size 并且小于large_pool池大小

        参数测试:在Oracle19.3版本测试，_bct_public_dba_buffer_maxsize参数控制CTWR dba buffer的最大值

--参数都设置为512K
alter system set "_bct_buffer_allocation_max"=5242880;
alter system set "_bct_public_dba_buffer_maxsize"=524288;
alter system set "_bct_public_dba_buffer_size"=524288;
NAME                                     VALUE                                    DESCRIPTION
---------------------------------------- ---------------------------------------- ------------------------------------------------------------
_bct_buffer_allocation_max               524288                                   maximum size of all change tracking buffer allocations, in b
_bct_public_dba_buffer_maxsize           524288                                   max buffer size permitted for public dba buffers, in bytes
_bct_public_dba_buffer_size              524288                                   total size of all public change tracking dba buffers, in byt
--当前CTWR dba buffer为1MB，接近2倍_bct_public_dba_buffer_size
SQL> select inst_id,pool,name,bytes/1024/1024 as MB ,con_id from gv$sgastat where pool='large pool';
​INST_ID POOL           NAME                                             MB     CON_ID
---------- -------------- ---------------------------------------- ---------- ----------1 large pool     free memory                              189.636719          01 large pool     ASM map operations hashta                      .375          01 large pool     krcc extent chunk                        1.98046875          01 large pool     PX msg pool                                      15          01 large pool     CTWR dba buffer                           1.0078125          0
​
--进行大批量的块操作，可以看到产生大量的block change tracking buffer space等待事件
SAMPLE_TIME                              EVENT                                    SESSION_ID BLOCKING_SESSION SESSION PROGRAM                                  TIME_WAITED/1000
---------------------------------------- ---------------------------------------- ---------- ---------------- ------- ---------------------------------------- ----------------
19-MAR-23 03.51.12.745 PM                control file sequential read                    497                  WAITING oracle@rac19a (CKPT)                                 .297
19-MAR-23 03.54.42.856 PM                change tracking file synchronous read           510                  WAITING rman@rac19a (TNS V1-V3)                              .233
19-MAR-23 03.58.58.979 PM                                                                 10                  ON CPU  oracle@rac19a (CTWR)                                    0
19-MAR-23 03.59.11.009 PM                change tracking file synchronous read           750                  WAITING sqlplus@rac19a (TNS V1-V3)                          5.282
19-MAR-23 03.59.19.020 PM                block change tracking buffer space              507                  WAITING sqlplus@rac19a (TNS V1-V3)                        999.912
19-MAR-23 03.59.19.020 PM                block change tracking buffer space              382                  WAITING sqlplus@rac19a (TNS V1-V3)                        999.727
19-MAR-23 03.59.19.020 PM                block change tracking buffer space              626                  WAITING oracle@rac19a (W002)                              999.692
19-MAR-23 03.59.19.020 PM                block change tracking buffer space              750                  WAITING sqlplus@rac19a (TNS V1-V3)                        999.642
19-MAR-23 03.59.19.020 PM                block change tracking buffer space              139                  WAITING sqlplus@rac19a (TNS V1-V3)                        999.587
19-MAR-23 03.59.20.020 PM                block change tracking buffer space              626                  WAITING oracle@rac19a (W002)                              780.752
19-MAR-23 03.59.20.020 PM                block change tracking buffer space              507                  WAITING sqlplus@rac19a (TNS V1-V3)                        962.714
19-MAR-23 03.59.20.020 PM                block change tracking buffer space              382                  WAITING sqlplus@rac19a (TNS V1-V3)                        793.489
19-MAR-23 03.59.20.020 PM                block change tracking buffer space              139                  WAITING sqlplus@rac19a (TNS V1-V3)                        968.197
19-MAR-23 03.59.20.020 PM                block change tracking buffer space              750                  WAITING sqlplus@rac19a (TNS V1-V3)                        966.412
--当前CTWR dba buffer为1MB没有任何动态调整
--调整_bct_public_dba_buffer_maxsize为100MB
alter system set "_bct_public_dba_buffer_maxsize"=104857600;
--当前CTWR dba buffer动态调整为CTWR dba buffer，block change tracking buffer space等待事件消失1* select inst_id,pool,name,bytes/1024/1024 as MB ,con_id from gv$sgastat where pool='large pool'
​INST_ID POOL           NAME                                             MB     CON_ID
---------- -------------- ---------------------------------------- ---------- ----------1 large pool     free memory                              2016.28516          01 large pool     ASM map operations hashta                      .375          01 large pool     krcc extent chunk                        1.98046875          01 large pool     PX msg pool                                      15          01 large pool     CTWR dba buffer                           14.359375          0

_bct_public_dba_buffer_size:

        参数说明:全部public change tracking dba buffer的总值

        默认值为:0，自动分配

        建议值:根据Oracle MOS(Doc ID 2094946.1)

        推荐为2倍dba_buffer_count_public*dba_entry_count_public*dba_entry_size并且小于large_pool池大小，减少block change tracking buffer space的等待产生

--可以通过表x$krcstat查询当前值
select dba_buffer_count_public*dba_entry_count_public*dba_entry_size from x$krcstat;

        ​参数测试:在Oracle19.3版本测试，_bct_public_dba_buffer_size参数影响CTWR dba buffer的当前大小        

        2倍dba_buffer_count_public*dba_entry_count_public*dba_entry_size<CTWR dba buffer<=2倍_bct_public_dba_buffer_size

--设置_bct_public_dba_buffer_size参数之前，要先设置_bct_public_dba_buffer_maxsize>=_bct_public_dba_buffer_size
否则可能会出现错误02097, 00000, "parameter cannot be modified because specified value is invalid"
// *Cause: Though the initialization parameter is modifiable, the modified
//         value is not acceptable to the parameter
--当前CTWR dba buffer 14MB
SQL> select inst_id,pool,name,bytes/1024/1024 as MB ,con_id from gv$sgastat where pool='large pool'
​INST_ID POOL           NAME                               MB     CON_ID
---------- -------------- -------------------------- ---------- ----------1 large pool     free memory                2016.28516          01 large pool     ASM map operations hashta        .375          01 large pool     krcc extent chunk          1.98046875          01 large pool     PX msg pool                        15          01 large pool     CTWR dba buffer             14.359375          0
--查看dba_buffer_count_public*dba_entry_count_public*dba_entry_size当前大小
SQL> select dba_buffer_count_public*dba_entry_count_public*dba_entry_size from x$krcstat;
​
DBA_BUFFER_COUNT_PUBLIC*DBA_ENTRY_COUNT_PUBLIC*DBA_ENTRY_SIZE
-------------------------------------------------------------7528320
--设置为2倍DBA_BUFFER_COUNT_PUBLIC*DBA_ENTRY_COUNT_PUBLIC*DBA_ENTRY_SIZE
SQL> alter system set "_bct_public_dba_buffer_maxsize"=15056640;    
​
System altered.
​
SQL> alter system set "_bct_public_dba_buffer_size"=15056640;
​
System altered.
--当前CTWR dba buffer为28MB，基本等于两倍_bct_public_dba_buffer_size 
SQL> select inst_id,pool,name,bytes ,con_id from gv$sgastat where pool='large pool';
​INST_ID POOL           NAME                            BYTES     CON_ID
---------- -------------- -------------------------- ---------- ----------1 large pool     free memory                2099171328          01 large pool     ASM map operations hashta      393216          01 large pool     krcc extent chunk             2076672          01 large pool     PX msg pool                  15728640          01 large pool     CTWR dba buffer              30113792          0
--设置为4倍DBA_BUFFER_COUNT_PUBLIC*DBA_ENTRY_COUNT_PUBLIC*DBA_ENTRY_SIZE
SQL> alter system set "_bct_public_dba_buffer_maxsize"=60227584;
​
System altered.
​
SQL> alter system set "_bct_public_dba_buffer_size"=60227584;
​
System altered.
--当前CTWR dba buffer为60MB，大于当前两倍dba_buffer_count_public*dba_entry_count_public*dba_entry_size
--小于2倍_bct_public_dba_buffer_size
SQL> select inst_id,pool,name,bytes ,con_id from gv$sgastat where pool='large pool';
​INST_ID POOL           NAME                            BYTES     CON_ID
---------- -------------- -------------------------- ---------- ----------1 large pool     free memory                2065534976          01 large pool     ASM map operations hashta      393216          01 large pool     krcc extent chunk             2076672          01 large pool     PX msg pool                  15728640          01 large pool     CTWR dba buffer              63750144          0
SQL> select dba_buffer_count_public*dba_entry_count_public*dba_entry_size from x$krcstat;
​
DBA_BUFFER_COUNT_PUBLIC*DBA_ENTRY_COUNT_PUBLIC*DBA_ENTRY_SIZE
-------------------------------------------------------------31874688 

        参数说明:CTWR健康心跳检查间隔

        默认值为:60秒，19.17及以后版本默认为0，禁用健康心跳检查

        建议值:存在Bug33239980导致由于CTWR健康心跳检查导致CKPT进程被堵塞的问题，如果CTWR存在性能问题，建议设置为0，禁用健康心跳检查或者应用最新的RU补丁

_bct_bitmaps_per_file:

        参数说明:bct块追踪文件里面每个数据文件保留的块追踪位图版本

        默认值为:8

        建议值:8

        参数测试:每一次全量备份或者增量备份，BCT文件都会重新创建一个新的版本用于记录新的块变化，如果版本的个数超过8，旧的版本会被覆盖，这可能会导致新的增量备份无法查看之前增量备份的历史版本，这时增量备份会恢复到全数据库块扫描，备份效率变低，所以至少每周进行一次全量备份，每天进行一次增量备份，这样的备份策略对于BCT比较合理