Forum Stats

  • 3,769,004 Users
  • 2,252,898 Discussions
  • 7,874,837 Comments

Discussions

How to solve buffer busy waits due to Concurrent INSERTS?

0614
0614 Member Posts: 121 Red Ribbon

Hi,

we are using 19C DB ,

we got top event buffer busy waits , identified most wait time on insert .

culprit table insert - having one PK


after identifying insert as culprit , i don't have any clue how to proceed further?


please help how to solve this kind of problem?

Best Answer

Answers

  • Paulzip
    Paulzip Member Posts: 8,494 Blue Diamond
    edited Nov 25, 2020 6:05PM

    culprit table insert - having one PK

    What do you mean by that, it's not entirely clear?

    Buffer busy waits often occur when another session has the buffer block locked and a culprit for this is often unindexed foreign keys (although not always).

    Does the table in question have a FK to another table (FK from it) or is it a parent of child tables (FK to it)? Check both for FK indexes.

    evgenyg
  • DeepC
    DeepC Member Posts: 158 Blue Ribbon

    You can try with APPEND hint - INSERT /*+ APPEND */ INTO

    The APPEND hint tells the optimizer to perform a direct-path insert, which improves the performance of INSERT .. SELECT operations :

    Data is appended to the end of the table, rather than attempting to use existing free space within the table.

    Data is written directly to the data files, by-passing the buffer cache.

  • Paulzip
    Paulzip Member Posts: 8,494 Blue Diamond

    DeepC said...

    You can try with APPEND hint - INSERT /*+ APPEND */ INTO

    Doesn't really get to the root cause though does it? It's simply an attempt to avoid it.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,914 Red Diamond
    edited Nov 26, 2020 7:33PM

    It can occur with indexes too, For that OP needs to check db file sequential reads. But I'd start with table INITRANS and FREELISTS.

    SY.