HRT_FAMILY_MEMBER table shown below is 312 MB in size and has 2.3 million rows.
When I run the following DELETE, it is running for more than 4 hours and hung.
The stats are not stale. There are no blocking locks in the DB. OEM bar graph shows only blue (user I/O) and green (CPU)
Following is the execution plan. Any idea what is wrong with this query ?
Any room for improvement ?
SQL> delete from HRT_FAMILY_MEMBER where FAMILY_MEMBER_ID in (
select FAMILY_MEMBER_ID from HRT_FAMILY where QRMSMATIC_CONTROL_ID in (
select QRMSMATIC_CONTROL_ID from HRT_QRMSMATIC_CONTROL where INCOME_YEAR =2023));
Execution plan
SQL> select * from TABLE(dbms_xplan.display_cursor(SQL_ID => 'cm8byrxd7j061', CURSOR_CHILD_NO => NULL));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
SQL_ID cm8byrxd7j061, child number 0
-------------------------------------
delete from HRT_FAMILY_MEMBER where FAMILY_MEMBER_ID in ( select
FAMILY_MEMBER_ID from HRT_FAMILY where QRMSMATIC_CONTROL_ID in (
select QRMSMATIC_CONTROL_ID from HRT_QRMSMATIC_CONTROL where
INCOME_YEAR =2023))
Plan hash value: 270540979
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | | 6050M(100)| |
| 1 | DELETE | HRT_FAMILY_MEMBER | | | | |
|* 2 | FILTER | | | | | |
| 3 | INDEX FAST FULL SCAN | HRT_FAMILY_MEMBER_PK | 2326K| 241M| 1532 (1)| 00:00:01 |
|* 4 | FILTER | | | | | |
|* 5 | HASH JOIN SEMI | | 1 | 11 | 2611 (1)| 00:00:01 |
| 6 | VIEW | index$_join$_003 | 1 | 9 | 2610 (1)| 00:00:01 |
|* 7 | HASH JOIN | | | | | |
| 8 | INDEX FAST FULL SCAN| HRT_QRMSMATIC_CONTROL_PK | 1 | 9 | 545 (1)| 00:00:01 |
|* 9 | INDEX FAST FULL SCAN| IDX$$_06EB0001 | 1 | 9 | 1999 (1)| 00:00:01 |
|* 10 | INDEX FULL SCAN | HRT_FAMILY_IF3 | 215K| 420K| 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( IS NOT NULL)
4 - filter(:B1=:B2)
5 - access("QRMSMATIC_CONTROL_ID"="QRMSMATIC_CONTROL_ID")
7 - access(ROWID=ROWID)
9 - filter("INCOME_YEAR"=2023)
10 - filter("QRMSMATIC_CONTROL_ID" IS NOT NULL)
Note
-----
- this is an adaptive plan
39 rows selected.
SQL> select count(*) from HRT_FAMILY_MEMBER;
COUNT(*)
----------
2326184