Re: high Block change tracking waits

Two thoughts that might reduce your level of surprise;

First - I said it's also dependent on the number of files: maybe you've added a few extra files over the last few months

Second - read https://jonathanlewis.wordpress.com/2007/07/29/nls/ (It has nothing to do with BCT, but makes the point that a small change in size can have a big impact in response time). In both cases (the blog note and your BCT behaviour) a relatively small change in the data size (in the blog the table size, in your case the size/number of files) could have been enough to have a big impact on the caching.

If you want to check the current value of _bct_buffer_allocation_max there's a quick and dirty script at _bct_buffer_allocation_max - you'll need to set up some page and column format setting for a tidy output, and change the search predicate to '%bct%' rather than '%trace%' of course. It looks as if Vinicius has exactly the right previous experience to help you, and has previously so I won't make any further comment.

Regards
Jonathan Lewis

On Thu, 4 Feb 2021 at 21:26, Lok P <loknath.73_at_gmail.com> wrote:

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

--
http://www.freelists.org/webpage/oracle-l