How to solve buffer busy waits due to Concurrent INSERTS?

0614

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?

Jonathan Lewis

@0614

It's important to know exactly what's causing a problem before suggesting a solution, so when posing a question like this you really need to say something about HOW you identified the statement and object.

I might guess, for example, that you got an AWR report that included am ASH section that identified an SQL statement with the impact of buffer busy waits. Or maybe you got a basic statspack / AWR report that showed buffer busy waits in the Top 10 timed events, and then went looking through v$sql for the statements with the highest concurrency_wait_time. If we don't know what you've seen our assumptions about your problem might make us point you in the wrong direction.

Having made that point: the commonest cause of buffer busy waits for an insert with primary key is a sequence or time-based primary key that results in all inserts on the primary key index becing targetted at the "right-hand" (i.e. high value) leaf block. If you want to check that your buffer busy waits are on the index rather than the table then you can examine v$segstat (or v$segment_statistics, or the AWR/Statspack level 7 report for "Top segment by,,") for information about objects subject to most buffer busy waits.

If this matches your application and you're licensed for the partitioning option then the simplest workaround is to recreate the PK as a hash-partitioned global index with a number of partitions similar to the expected concurrency (but set to the closest power of 2). Alternatively if it's an Oracle sequence that you're using then you could switch to the newer "scalable sequence", though this makes the stored value longer by prefixing the value with the session id multipled by a large power to 10 (and, for RAC, the same with the instance number):

see:

https://oracle-base.com/articles/18c/scalable-sequences-18c

https://richardfoote.wordpress.com/2018/04/30/18c-scalable-sequences-part-i-saviour-machine/

Regards

Jonathan Lewis

Paulzip

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.

DeepC

https://community.oracle.com/tech/developers/discussion/4477940/how-to-solve-buffer-busy-waits-due-to-concurrent-inserts

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

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

https://community.oracle.com/tech/developers/discussion/comment/16776544#Comment_16776544

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.

Jonathan Lewis

@0614

It's important to know exactly what's causing a problem before suggesting a solution, so when posing a question like this you really need to say something about HOW you identified the statement and object.

I might guess, for example, that you got an AWR report that included am ASH section that identified an SQL statement with the impact of buffer busy waits. Or maybe you got a basic statspack / AWR report that showed buffer busy waits in the Top 10 timed events, and then went looking through v$sql for the statements with the highest concurrency_wait_time. If we don't know what you've seen our assumptions about your problem might make us point you in the wrong direction.

Having made that point: the commonest cause of buffer busy waits for an insert with primary key is a sequence or time-based primary key that results in all inserts on the primary key index becing targetted at the "right-hand" (i.e. high value) leaf block. If you want to check that your buffer busy waits are on the index rather than the table then you can examine v$segstat (or v$segment_statistics, or the AWR/Statspack level 7 report for "Top segment by,,") for information about objects subject to most buffer busy waits.

If this matches your application and you're licensed for the partitioning option then the simplest workaround is to recreate the PK as a hash-partitioned global index with a number of partitions similar to the expected concurrency (but set to the closest power of 2). Alternatively if it's an Oracle sequence that you're using then you could switch to the newer "scalable sequence", though this makes the stored value longer by prefixing the value with the session id multipled by a large power to 10 (and, for RAC, the same with the instance number):

see:

https://oracle-base.com/articles/18c/scalable-sequences-18c

https://richardfoote.wordpress.com/2018/04/30/18c-scalable-sequences-part-i-saviour-machine/

Regards

Jonathan Lewis