Re: high Block change tracking waits

And the thing which I am surprised thus database was always been 100+ TB so how this wait event was in control with small bct buffer till a month back and then started increasing day by day in recent past And now impacting application queries.

On Fri, 5 Feb 2021, 2:46 am Lok P, <loknath.73_at_gmail.com> wrote:

> 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_at_freelists.org>
> 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_at_yahoo.com
>>
>>
>> On Feb 4, 2021, at 10:40 AM, Jonathan Lewis <jlewisoracle_at_gmail.com>
>> 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_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