I will explain DLM cross inst call completion and enq: RO – fast object reuse wait event during Truncate Table in Oracle 19c in this post.
You can read the following post to learn more details about Wait event and their solution.
DLM cross inst call completion
Truncate table command is waiting because of DLM cross inst call completion wait event, and this command is completed about 4-5 hours in Oracle 19c.
enq: RO – fast object reuse
The enq: RO – fast object reuse enqueue known as “Multiple object resue” enqueue, is used to synchronise operations between foreground process and a background process such as DBWR or CKPT. It is typically used when dropping objects or truncating tables.
Following is the sequence of events When a truncate/drop occurs:
- Foreground process acquires the “RO” enqueue in exclusive mode
- Cross instance calls (or one call if it is a single object) are issued (“CI” enqueue is acquired)
- CKPT processes on each of instances requests the DBWR to write the dirty buffers to the disk and invalidate all the clean buffers.
- After DBWR completes writing all blocks, the foreground process releases the RO enqueue
_db_fast_obj_truncate Parameter and Truncate Table
You can set the following parameter for workaround solution.
Alter system set "_db_fast_obj_truncate"=false scope=both sid='*';
If the _db_fast_obj_truncate parameter don’t solve your problem, then apply the permanent solution as follows.
This two wait events are being investigated by internal Bug 31208287 and Bug 31038220 in Oracle 19c.
Details of bug and patch are in the following Oracle Support Note.
To solve this error, download the following patches for Oracle 19c from Oracle Support, then apply this patches.
After applying these two patches, set the following parameter, then truncate table command will run fastly.
Our truncate table is run 4-5 minutes after these steps.
Alter system set "_db_fast_obj_truncate"=true scope=both sid='*';
Do you want to learn Oracle Database Performance Tuning detailed, then read the following articles.