Just finding time to look at this - it's something I'd need to research
since I don't have anything but the barest of old notes in my library.
I know I sent you an indication of how large the BCT file probably ought to
be for your size database, but I can't find that message at present and I
may have a correction to it that explains the 22GB / 9GB.
How big is the database - sum(bytes)/(2^9), count(*) from dba_data_files
How many nodes (4 ?)
How many backups are you keeping before obsoleting them.
What was the version of Oracle (I keep forgetting)
I think I said that you need to estimate 1 bit per 32KB of data file,
multiplied by 8 as the total number of bitmaps - then allow rounding errors.
I've just found a note on MOS which says you also need to multiply by the
number of threads - and I don't think I knew that (though it makes sense
because you don't want the the instances to have to tell each other about
changes they've made and you don't want CTWRs to collide while trying to
write the same bits of bitmap).
-----
The "checkpoint pattern" for waits for reliable message is one that could
be repeated here - process A calls process B to get something done and
waits on a reliable message, process B calls process C to perform an
action, takes an exclusive lock, then messages process A which tries to
take a shared lock; process B waits for process C to do its thing and
release its lock when process C completes, which allows process A to get
(and release) its lock.
What we need to find out is (a) is this what's happening then (b) who are
process B and C, then (c) why is it taking so long for process B to get
acknowledgement from process C that it's working on the required task. (If
process A is waiting on reliable message this means process B hasn't heard
from process C or process B is unable to gets its exclusive lock).
A first step would be to get the wait chain analysis from
g$active_session_history as soon as possible after one of these long waits
has happened.
I'll be reading your previous couple of emails over the weekend and hunting
around MOS to see what I can find. I am a little curious that you mention
that CTWR writes on a checkpoint - this doesn't seem to be necessary (or a
good idea) - but that may be explained in one of the notes/bugs you've
found.
A couple of speculative thoughts:
1) possibly the problem appears when the BCT file has to be extended by
multiple instances at the same time and they have to queue.
2) maybe the buffer space waits follow the reliable message wait because a
lot of change has happened in the interval and the bitmap changes have been
queued up so long that they keep filling the buffer and waiting for space,
so maybe increasing the publc buffer size again would reduce those waits
(while we try to find out why the reliable message waits are happening).
Regards
Jonathan Lewis
On Fri, 16 Apr 2021 at 20:36, Lok P <loknath.73@xxxxxxxxx> wrote:
We have re-initiated the BCT by disabling and then enabling it again and
then we saw the BCT file size decreased from 22GB to ~9GB and the wait
events "bct buffer space" and "reliable message" went away, everyone was
happy. But again after 2 days we again started seeing the "reliable
message" wait event popping up along with a few "bct buffer space" waits.
We are thinking of reaching out to Oracle through SR to see what is causing
this issue. If it's anything related to our current ZDLRA backup strategy
which has only one L0 and all L1's or something else is causing this issue?
Or we really have to increase the BCT buffer(
_bct_public_dba_buffer_size ) till ~1GB(which is the max size for it).
On Sun, Apr 11, 2021 at 12:53 AM Lok P <loknath.73@xxxxxxxxx> wrote:
As I read it , when a checkpoint occurs the change tracking writer i.e.
CTWR will write the data from the CTWR DBA buffer to the BCT file, so we
are seeing this BCT Buffer space wait + reliable message waits means the
check point is not finishing up and getting stuck. So does it mean that
when we issue COMMIT during that point itself the changed data
is struggling to get written to CTWR buffer because of its so small space?
But again if it's because of the small size of CTWR buffer being small then
how come initiating BCT file fixing such an issue?
On Sun, Apr 11, 2021 at 12:25 AM Lok P <loknath.73@xxxxxxxxx> wrote:
Below is the bug that seems to be exactly what we are suffering from as
it shows how "check point not completing" and "reliable message" are
associated with this BCT issue. In past we had suffered mainly from lot of
"BCT Buffer space waits" and then we found BCT file size was 22GB and as
suggested by Oracle, we endup increasing the size of
_bct_public_dba_buffer_size from ~128MB to ~256MB and re-initialized the
BCT i.e. disbaled+enabled and that fixed the issue then. The size of the
BCT file after reinitialization was down to ~9 GB.
But it seems now within ~2months the BCT file has again grown back to
same ~22GB and this time we are seeing combination of waits like "reliable
message" +"BCT buffer space waits", even with that
_bct_public_dba_buffer_size being set as ~256MB, so it seems this doubling
of buffer size has not helped then rather re-initialization of BCT helped.
So this time we are going to do the same reinitializing of BCT to have a
temporary fix.But now wondering what will be a permanent fix for this issue?
High Waits On 'block change tracking buffer space' - Checkpoint
Contention With BLOCK CHANGE TRACKING or RMAN Incremental Backup (Doc ID
2094946.1)
On Sat, Apr 10, 2021 at 12:53 AM Lok P <loknath.73@xxxxxxxxx> wrote:
We have verified many occurrences and it looks like each time the
"reliable message" is followed by many "block change tracking buffer
space" wait events. We are thinking of initiating BCT by disabling +
enabling the BCT. but still unable to relate how come the BCT Buffer space
can cause "reliable message" wait events making the database freeze?
