Re: high Block change tracking waits

Or you can just disable it and offload your backups to standby database if you have one

În joi, 4 feb. 2021 la 17:41, Jonathan Lewis <jlewisoracle_at_gmail.com> a scris:

>
> 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