Re: high Block change tracking waits
Date: Thu, 4 Feb 2021 11:55:00 -0500
Message-Id: <89551E8A-C447-4F20-951A-7D99C7DE40F1_at_yahoo.com>
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 <mailto: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 <mailto: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 <mailto: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 <mailto: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 <mailto: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
Received on Thu Feb 04 2021 - 17:55:00 CET