Re: block change tracking buffer space !

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 13 Mar 2018 16:43:29 +0000
Message-ID: <LO1P123MB09771550ACE59FEA122A180EA5D20_at_LO1P123MB0977.GBRP123.PROD.OUTLOOK.COM>

Nitin,

I think I'd look at a couple of other figures before trying to fiddle with undocumented parameters.

What does the wait event histogram say about change tracking writes and reads ? Any clues that there's an I/O problem there ?

How about checking the size and free space in the large pool - changing the allocation size may make no difference if the large pool can't supply it. Maybe setting a larger large_pool_size would be sufficient.

It's a long time since I looked at BCT, but maybe your data files are growing and this is putting some odd pressure on your change tracking file that's causing delays with writes to the file.

Regards
Jonathan Lewis

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Nitin Saxena <ntnsxn7_at_gmail.com> Sent: 13 March 2018 00:48
To: ORACLE-L
Subject: block change tracking buffer space !

Hello Everyone,

I am facing lot of concurrency wait events due to "block change tracking buffer space" when rman L0 backup is run. I see that database gets overwhelmed with concurrency due to less space for CTWR buffer as shown in event. I see following problem area . Any recommendations if we should bump "_bct_public_dba_buffer_size" to higher value ?

NAME                                          VALUE                          DESCRIPTION
--------------------------------------------- ------------------------------ -------------------------------------------------------


_bct_public_dba_buffer_size                   0                              total size of all public change tracking dba buffers,


                                                                             in bytes


NAME                                          VALUE                          DESCRIPTION
--------------------------------------------- ------------------------------ -------------------------------------------------------


_bct_buffer_allocation_max                    104857600                      maximum size of all change tracking buffer allocations,


                                                                             in bytes

SQL> select dba_buffer_count_public*dba_entry_count_public*dba_entry_size from X$KRCSTAT;

DBA_BUFFER_COUNT_PUBLIC*DBA_ENTRY_COUNT_PUBLIC*DBA_ENTRY_SIZE

---

                                                     25276400

SQL> SELECT * FROM v$sgastat WHERE name like 'CTWR%';

POOL         NAME                            BYTES
------------ -------------------------- ----------
large pool   CTWR dba buffer              11386880

snippet from ash and dash is as follows:

%This SECONDS WAIT_CHAIN

------ ---------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  12%       14650 -> 1vyayutd1117p:buffer busy waits -> 1vyayutd1117p:block change tracking buffer space

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

Received on Tue Mar 13 2018 - 17:43:29 CET