Thanks much.
It's a big database when I sum up all the USER_BYTES in dba_data_files it
comes as ~140TB when and the count of total number of files in
dba_data_file is ~3788.
Doing show parameter _bct_buffer_allocation_max; giving no results , so
hope this is not set. and _bct_public_dba_buffer_size is set as ~128MB.
We will raise SR with Oracle to have the correct size of these underscore
parameters so as to avoid the wait "block change tracking buffer space".
On Thu, Feb 4, 2021 at 10:25 PM Shane Borden <dmarc-noreply@xxxxxxxxxxxxx>
wrote:
That below note says this:
NOTE: 1G is the maximum value which can be allocated to
"_bct_public_dba_buffer_size”.
So increasing it some and seeing what happens should be an option you try
before you change your entire backup strategy to offload it elsewhere.
---
Thanks,
Shane Borden
sborden76@xxxxxxxxx
On Feb 4, 2021, at 10:40 AM, Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
I see you found the parameter while the buffer size parameter while I was
writing a reply. 128MB x 8 = 1GB, so you can buffer most of what you need
if your change activity it limited to about 5% (1/22) of the total datafile
space.
I've just started up a tiny 11.2.0.4 database and found the buffer
allocation size is 100MB for a 5GB database - so 128MB for your database
sounds rather small, and I'd seriously consider checking with Oracle
Support what they think about increasing it to something in the gigabyte
range (1GB - 3GB maybe if you've got the SGA).
Regards
Jonathan Lewis
On Thu, 4 Feb 2021 at 15:16, Lok P <loknath.73@xxxxxxxxx> wrote:
Found below doc which seems matching our symptom i.e. the top waits in
gv$session_wait showing as "rdbms ipc message" . So we have the current
size of "_bct_public_dba_buffer_size" set as ~128MB. Should we increase
that to some higher value? Or Should we just disable and enable it again to
reset the BCT?
High Waits On 'block change tracking buffer space' - Checkpoint
Contention With BLOCK CHANGE TRACKING or RMAN Incremental Backup (Doc ID
2094946.1)
On Thu, Feb 4, 2021 at 8:16 PM Al B. <albert.y.balbekov@xxxxxxxxx> wrote:
22gb seems like a lot of tracking.
Could it be a backup stopped running causing changed blocks accumulation
?
Albert
On Thu, Feb 4, 2021, 1:54 AM Lok P <loknath.73@xxxxxxxxx> wrote:
One thing we noticed from the wait event pattern , there was a very low
amount of those waits(block change tracking buffer space) in the past . but
gradually it started increasing and now it's enough to impact the
application queries. How to debug/fix this one? or it can be related to the
slowness of the underlying disk on which the BCT file lies?
The BCT file size is 22GB.
On Thu, Feb 4, 2021 at 2:35 AM Lok P <loknath.73@xxxxxxxxx> wrote:
Below is a sample batch insert query with its sql monitor and it shows
almost all the time is in OTHERs wait which is nothing but "block change
tracking buffer space".
SQL Text
------------------------------
INSERT INTO TABLE1 (.....) VALUES(:1 , :2 , :3 , :4 , :5 ,
:6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 ,
:18 , :19 , :20 , :21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 ,
:30
, :31 , :32 , :33 , :34 , :35 , :36 , :37 , :38 , :39 , :40 , :41 , :42 ,
:43 , :44 , :45 , :46 ,:47 , :48 , :49 , :50 , :51 , :52 , :53 , :54 , :55
, :56 , :57 , :58 , :59 , :60 , :61 , :62 , :63 , :64 , :65 , :66 , :67 ,
:68 , :69 , :70 , :71 ,:72 ,:73 ,:74 ,:75 )
Global Information
------------------------------
Status : DONE
Instance ID : 2
SQL Execution ID : 36104551
Execution Started : 02/03/2021 15:56:51
First Refresh Time : 02/03/2021 15:56:55
Last Refresh Time : 02/03/2021 15:58:43
Duration : 112s
Module/Action : JDBC Thin Client/-
Program : JDBC Thin Client
Global Stats
============================================================================================
| Elapsed | Cpu | IO | Concurrency | Cluster | Other |
Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) |
Gets | Reqs | Bytes |
============================================================================================
| 112 | 0.19 | 1.23 | 0.00 | 0.00 | 111 |
17090 | 555 | 4MB |
============================================================================================
SQL Plan Monitoring Details (Plan Hash Value=0)
==============================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time |
Start | Execs | Rows | Read | Read | Activity | Activity Detail |
| | | | (Estim) | | Active(s) |
Active | | (Actual) | Reqs | Bytes | (%) | (# samples) |
==============================================================================================================================================
| 0 | INSERT STATEMENT | | | | |
| 1 | | | | | |
| 1 | LOAD TABLE CONVENTIONAL | | | | 1 |
+112 | 1 | 0 | 54 | 432KB | | |
==============================================================================================================================================
On Thu, Feb 4, 2021 at 2:25 AM Lok P <loknath.73@xxxxxxxxx> wrote:
Hi , We are on version 11.2.0.4 Oracle database and suddenly starting
the last 5-10 days we are seeing many queries(mostly Inserts) are
running
longer and the waits it's showing as "block change tracking buffer
space" .
and it's going worse day by day. We do have BCT ON on this database. So
wondering why these waits were not there previously as because we have
the
database kept on BCT enabled since long back, but suddenly appeared since
the last few days and impacting application queries?
Even simple insert queries like insert into table(c1,c2, c3)
values(:1, :2, :3) (which are executing with batch size of ~1000 ) are
suffering. What can be the cause for this?
Regards
Lok
