How to solve buffer busy waits due to Concurrent INSERTS? — oracle-tech

    Forum Stats

  • 3,714,819 Users
  • 2,242,634 Discussions
  • 7,845,078 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

How to solve buffer busy waits due to Concurrent INSERTS?

0614
0614 Member Posts: 114 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,076 Gold Crown
    edited November 2020

    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,076 Gold Crown

    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,058 Black Diamond
    edited November 2020

    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.

Sign In or Register to comment.