DLM cross inst call completion | enq: RO – fast object reuse wait event and _db_fast_obj_truncate parameter during Truncate Table in Oracle 19c

Hi,

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.

Wait Events and Their Solutions in Oracle Database

 

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:

  1. Foreground process acquires the “RO” enqueue in exclusive mode
  2. Cross instance calls (or one call if it is a single object) are issued (“CI” enqueue is acquired)
  3. CKPT processes on each of instances requests the DBWR to write the dirty buffers to the disk and invalidate all the clean buffers.
  4. 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.

p31038220_198000DBRU_Linux-x86-64

p31208287_198000DBRU_Linux-x86-64

 

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.

Performance Tuning and SQL Tuning Tutorial in the Oracle Database

 

 3,016 views last month,  3 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience. I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource. I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks. I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients. If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected]. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Do NOT follow this link or you will be banned from the site!