- 3,714,819 Users
- 2,242,634 Discussions
- 7,845,078 Comments
Forum Stats
Discussions
Categories
- 12 Data
- 362.2K Big Data Appliance
- 6 Data Science
- 1.5K Databases
- 447 General Database Discussions
- 3.7K Java and JavaScript in the Database
- 22 Multilingual Engine
- 487 MySQL Community Space
- 3 NoSQL Database
- 7.6K Oracle Database Express Edition (XE)
- 2.7K ORDS, SODA & JSON in the Database
- 415 SQLcl
- 42 SQL Developer Data Modeler
- 184.8K SQL & PL/SQL
- 21K SQL Developer
- 1.8K Development
- 3 Developer Projects
- 32 Programming Languages
- 135K Development Tools
- 7 DevOps
- 3K QA/Testing
- 236 Java
- 4 Java Learning Subscription
- 10 Database Connectivity
- 65 Java Community Process
- Java 25
- 8 Java APIs
- 141.1K Java Development Tools
- 5 Java EE (Java Enterprise Edition)
- 153K Java Essentials
- 134 Java 8 Questions
- 86.2K Java Programming
- 270 Java Lambda MOOC
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 10 Java SE
- 13.8K Java Security
- 3 Java User Groups
- 22 JavaScript - Nashorn
- 18 Programs
- 118 LiveLabs
- 28 Workshops
- 9 Software
- 3 Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 3 Deutsche Oracle Community
- 10 Español
- 1.9K Japanese
- 2 Portuguese
How to solve buffer busy waits due to Concurrent INSERTS?

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
-
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
Answers
-
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.
-
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.
-
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.
-
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.
-
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