Re: high Block change tracking waits
Date: Thu, 4 Feb 2021 22:55:05 +0000
Message-ID: <CAGtsp8kfnFY36z2QV5op_N8oUjgf2FNx_T6vAK6sE-w136=bMA_at_mail.gmail.com>
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
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
Jonathan Lewis
> 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-lReceived on Thu Feb 04 2021 - 23:55:05 CET