DB : 12.2
X7-2L exadata
Query:
delete from TAB_NAME where COLUMN_NAME=:1;
explain plan:
------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | | 2 (100)| |
| 1 | DELETE | table_name | | | | |
|* 2 | INDEX UNIQUE SCAN| index_name | 1 | 85 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
There are 90 sessions running same delete with bind variables and all of them are with same wait event gc current request. All the sessions running more 20000+ seconds, still running and seems to be hang with wait event - cluster waits.
there are no other sqls running in PDB, 1 hour ASH shows this one delete dml.
Looked at ASH and AWRs , its major contributor.
| Event | % Event | Remote Instance# | % Activity |
|---|
| gc current request | 97.04 | | 97.04 |
| gc buffer busy acquire | 1.21 | | 1.12 |
How to find exact cause and get these gc current request and gc buffer busy acquire fixed.
Please help. thanks.