Re: high Block change tracking waits

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