Insert is running dead slow with latch: cache buffers chains wait event in oracle 19c
inserts are happening from both rac instances, this wait event is seen in only one instance.
same sql id is running with plan hash values,
it is simple insert with values clause.
PLAN_TABLE_OUTPUT
----------------------------
, :22 )
---------------------------------------------------------------
| Id | Operation | Name | Cost |
---------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 |
| 1 | LOAD TABLE CONVENTIONAL | xxxxxxxxxxxxxxxxxx | |
---------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
PLAN_TABLE_OUTPUT
----------------------------
1 - INS$1
Note
-----
- cpu costing is off (consider enabling it)
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
PLAN_TABLE_OUTPUT
----------------------------
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[INS$1]]></n><f><h><t><![CDATA[D1_xxxxxxxxxx]]></t><s><![CDATA[INS$1]]></s></h></f></q>
same query used to work faster , it is started giving below wait events, can u please help me to find out the root issue for this.
latch: cache buffers chains
gc buffer busy acquire