Re: high Block change tracking waits

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