Re: high Block change tracking waits

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