Thanks for the question, Kay.
Asked: September 01, 2023 - 9:00 am UTC
Last updated: September 01, 2023 - 1:08 pm UTC
Version: 19.19
Viewed 1000+ times
You Asked
Hi Tom,
we run an application that writes thousands of inserts per minute to a table. The database runs in a 2-node RAC. This insert generates a very high system load due to the global block wait events, since the block into which an insert is to take place is potentially currently in use at the other instance. And even if this block belongs to the current instance, there are index blocks that are potentially not active on the instance performing the insert. In particular, how can we speed up the insert so that the global block waits evens are reduced?
Thanks a lot!
Kay.
and Chris said...
There are several global wait events; the solution will depend on what exactly you're seeing. MOS note 2764824.1 outlines common problems and provides links to detailed solutions.
That said a common problem with lots of inserts is contention on sequence-generated primary key indexes. You can address this by changing these sequences to be either:
- Hash partitioned
- A scalable sequence
These spread inserts throughout the primary key index, instead of them all accessing the same right-hand edge.
Is this answer out of date? If it is, please let us know via a Comment