Re: high Block change tracking waits

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_at_gmail.com> 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_at_gmail.com> 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_at_gmail.com> 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_at_gmail.com> 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_at_gmail.com> 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
>>>>>
>>>>>
>>>>>

--
http://www.freelists.org/webpage/oracle-l