BCT query 隐含参数
| Name | Value | Desc | Comment | |
|---|---|---|---|---|
| 1 | _bct_public_dba_buffer_size | 16776960 | total size of all public change tracking dba buffers, in bytes | |
| 2 | _bct_initial_private_dba_buffer_size | 0 | initial number of entries in the private change tracking dba buffers | |
| 3 | _bct_bitmaps_per_file | 8 | number of bitmaps to store for each datafile | |
| 4 | _bct_file_block_size | 0 | block size of change tracking file, in bytes | |
| 5 | _bct_file_extent_size | 0 | extent size of change tracking file, in bytes | |
| 6 | _bct_chunk_size | 0 | change tracking datafile chunk size, in bytes | |
| 7 | _bct_crash_reserve_size | 262144 | change tracking reserved crash recovery SGA space, in bytes | |
| 8 | _bct_buffer_allocation_size | 2097152 | size of one change tracking buffer allocation, in bytes | |
| 9 | _bct_buffer_allocation_max | 1073741824 | maximum size of all change tracking buffer allocations, in bytes | |
| 10 | _bct_buffer_allocation_min_extents | 1 | mininum number of exents tents to allocate per buffer allocation | |
| 11 | _bct_fixtab_file | change tracking file for fixed tables | ||
| 12 | _bct_health_check_interval | 60 | CTWR health check interval (seconds), zero to disable | |
| 13 | _bct_public_dba_buffer_dynresize | 2 | allow dynamic resizing of public dba buffers, zero to disable |
|
| 14 | _bct_public_dba_buffer_dynresize_delay | 3600 | control frequency of delay dynamic resizing of public dba buffers | |
| 15 | _bct_public_dba_buffer_maxsize | 0 | max buffer size permitted for public dba buffers, in bytes | |
| 16 | _bct_mrp_timeout | 5 | CTWR MRP wait timeout (seconds), zero to wait forever | |
| 17 | _backup_min_ct_unused_optim | 2097152 | mimimun size in bytes of change tracking to apply unused space optimuzation |
List the parameter name and its value:
set lines 1000;
col name for a30;
col value for a20;
col description for a20;
select a.KSPPINM name,b.KSPPSTVL value , a.KSPPDESC description from x$ksppi a,x$ksppcv b where a.INDX=b.INDX and a.KSPPINM like '%_bct%';
List the value of the parameter:
select KSPPSTVL from x$ksppcv where INDX in (select INDX from x$ksppi where KSPPINM='&1')
等待事件问题解决：block change tracking buffer space:
Wait is related to the sizing / usage of the CTWR dba buffer in the Large Pool memory structure more ...
Solutions
Problem: When a session waits on the "Block Change Tracking Buffer" event, it indicates that there was a wait for space in the CTRW dba buffer. If this happens too often the performance of Backups and/or the entire database can suffer. There are several alternatives to fixing this if it occurs in your system. Solution 1: Review the location of the change-tracking file to ensure that it's not co-located on disks with other heavily used or "HOT" files. For example, do not locate the change-tracking file on the same disk with your redo or archive files. Solution 2: Consider changing the value of Large_pool_size or the hidden parameter _bct_public_dba_buffer_size to a larger value. NOTE: Always check with Oracle Support before using a hidden parameter value as Oracle may not support these types of changes ( _bct_public_dba_buffer_size = total size of all public change tracking dba buffers). Solution 3: Turn off the BCT feature so that it does not require the CTWR dba buffer.
问题:当会话等待“块更改跟踪缓冲区”事件时，它指示CTRW dba缓冲区中存在空间等待。如果这种情况经常发生，备份和/或整个数据库的性能都会受到影响。如果在您的系统中发生这种情况，有几种替代方法可以修复它。解决方案1:检查更改跟踪文件的位置，以确保它没有与其他频繁使用的或“热”文件一起位于磁盘上。例如，不要将更改跟踪文件与重做或存档文件放在同一个磁盘上。解决方案2:考虑将Large_pool_size或隐藏参数_bct_public_dba_buffer_size更改为更大的值。注意:在使用隐藏参数值之前，一定要检查Oracle支持，因为Oracle可能不支持这些类型的更改(_bct_public_dba_buffer_size =所有公共更改跟踪dba缓冲区的总大小)。解决方案3:关闭BCT功能，使其不需要CTWR dba缓冲区。
Expanded Definition
When data blocks change, shadow processes track the changed blocks in a private area of memory at the same time they generate redo. When a commit is issued, the BCT information is copied to a shared area in Large Pool called 'CTWR dba buffer'. At the checkpoint, a new background process, Change Tracking Writer (CTWR), writes the information from the buffer to the change-tracking file. If contention for space in the CTWR dba buffer occurs, a wait event called, 'Block Change Tracking Buffer Space' is recorded. By default, the CTWR process is disabled because it can introduce some minimal performance overhead on the database. You can query V$BLOCK_CHANGE_TRACKING table to determine whether change tracking is enabled and CTWR dba buffer is being used: SELECT status FROM v$block_change_tracking If the CTWR process is enabled, you can view the size of the CTWR dba buffer by looking at v$sgastat: SELECT * FROM v$sgastat WHERE name like 'CTWR%';
当数据块更改时，影子进程在生成 redo 的同时，在内存的一个私有区域中跟踪更改的块。当发出提交时，BCT信息被复制到名为“CTWR dba buffer”的大池中的共享区域。在检查点，一个新的后台进程，更改跟踪写入器(CTWR)，将信息从缓冲区写入更改跟踪文件。如果CTWR dba缓冲区中发生空间争用，将记录一个名为“块更改跟踪缓冲区空间”的等待事件。默认情况下，CTWR进程是禁用的，因为它会在数据库上引入一些最小的性能开销。你可以查询 V $ BLOCK_CHANGE_TRACKING 表来确定是否更改跟踪启用和CTWR dba使用缓冲区: SELECT status FROM v$block_change_tracking; 如果启用了 CTWR 过程,您可以查看CTWR dba缓冲区的大小通过观察V $ sgastat: SELECT * FROM v$sgastat WHERE name like 'CTWR%';