#StackBounty: #oracle #performance #wait-types #oracle-19c Reduce file header block

Bounty: 50

I’m in the middle of my first Statspack analysis, and one of the most time consuming events is Buffer Busy Waits (around 30% time). I checked out the Buffer Wait statistics, and it seems like the most time it’s waiting in File Header Block (about 10x more time than the second wait event).
Could you give me a hint how can I approach this issue? The only advice I found online in this site saying

File Header Block – Most likely extent allocation problems, look at extent size on tablespace and increase the extent size to there are few extent allocations and less contention on the File Header Block.

I was googling again about increasing extent size and I’m bit lost here. Seems that extent size is assigned to particular tablespace, and I would need to replace my tablespaces in order to change that, but that is only for the initial one. How can I increase it without replacing all tablespaces in my DB?

Is there maybe any other stuff I should look into when I see lot of File Header Block waiting time?

Cheers


Update1:

Tablespace
------------------------------
                 Av      Av     Av                    Av        Buffer Av Buf
         Reads Reads/s Rd(ms) Blks/Rd       Writes Writes/s      Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
AA_AAA
       438,217     220    3.8     1.0      788,012      396      2,007   14.3
UNDOTBS1
             0       0    0.0               21,104       11     21,647  993.6
TBS_PERFSTAT
           143       0   40.1     1.0        3,340        2          0    0.0
SYSTEM
            26       0   14.6     1.0          114        0          0    0.0
SYSAUX
            14       0    4.3     1.0           98        0          0    0.0
AA_AAA_IDX
            74       0   15.7     1.0            2        0          4    7.5
BB_BBB
             0       0    0.0                   54        0        820  993.0
BB_BBB_IDX
             0       0    0.0                   40        0      1,527  993.5
          -------------------------------------------------------------
File IO Stats  DB/Inst: ORCL/orcl  Snaps: 9833-9834
->Mx Rd Bkt: Max bucket time for single block read
->ordered by Tablespace, File

Tablespace               Filename
------------------------ ----------------------------------------------------
                        Av   Mx                                             Av
                 Av     Rd   Rd    Av                    Av        Buffer BufWt
         Reads Reads/s (ms)  Bkt Blks/Rd       Writes Writes/s      Waits  (ms)
-------------- ------- ----- --- ------- ------------ -------- ---------- ------
AA_AAA                   /oradata/data_files/ORCL/eim.dbf
             0       0                             54        0        820  993.0

AA_AAA_IDX               /oradata/data_files/ORCL/eim_idx.dbf
             0       0                             40        0      1,527  993.5

BB_BBB                   /oradata/data_files/ORCL/uel.dbf
       438,217     220   3.8   1     1.0      788,012      396      2,007   14.3

BB_BBB_IDX               /oradata/data_files/ORCL/uel_idx.dbf
            74       0  15.7   1     1.0            2        0          4    7.5

SYSAUX                   /oradata/data_files/ORCL/sysaux01.dbf
            14       0   4.3   1     1.0           98        0          0

SYSTEM                   /oradata/data_files/ORCL/system01.dbf
            26       0  14.6   1     1.0          114        0          0

TBS_PERFSTAT             /oradata/data_files/ORCL/statspack_data01.dbf
           143       0  40.1   1     1.0        3,340        2          0

UNDOTBS1                 /oradata/data_files/ORCL/undotbs01.dbf
             0       0                         21,104       11     21,647  993.6

          -------------------------------------------------------------
File Read Histogram Stats  DB/Inst: ORCL/orcl  Snaps: 9833-9834
->Number of single block reads in each time range
->Tempfiles are not included
->ordered by Tablespace, File

Tablespace               Filename
------------------------ ----------------------------------------------------
    0 - 2 ms     2 - 4 ms    4 - 8 ms     8 - 16 ms   16 - 32 ms       32+ ms
------------ ------------ ------------ ------------ ------------ ------------
AA_AAA_IDX               /oradata/data_files/ORCL/uel_idx.dbf
          19            0            0            0            0            0

SYSAUX                   /oradata/data_files/ORCL/sysaux01.dbf
          10            0            0            0            0            0

AA_AAA                   /oradata/data_files/ORCL/uel.dbf
     392,355            0            0            0            0            0

TBS_PERFSTAT             /oradata/data_files/ORCL/statspack_data01.dbf
          29            0            0            0            0            0

SYSTEM                   /oradata/data_files/ORCL/system01.dbf
          18            0            0            0            0            0

          -------------------------------------------------------------


Get this bounty!!!