Hi We are using version 12.1.0.2.0 of oracle Exadata. We have one table which is holding ~1billion rows currently.This table is list-range composite partitioned. We have got a requirement as per which we need to delete ~5million rows daily. We are not able to use partitioning option(drop partition) here as because the delete is based on some other attributes but not simply on the basis of partition key. The current way of delete is taking ~40minutes for ~3million rows. And initially i was thinking if selecting the rows/rowids is taking time, but as i ran the SELECT part of the query, i see that is finishing in~30 seconds. So it means the delete itself is consuming all the time and resources. And then querying dba_hist_active_sess_history , its pointing that in the delete step itself the query is spending 90% of the time and the current_obj# is pointing to two of the indexes(one is primary key i.e oracle generated sequence and other is a composite key index), the waitevent is showing all "cell single block physical read" for these index. We want to make the delete in faster time (within <~10minutes). I have below questions
1)some teammates suggesting to make it in chunks of 100k rather ~5million in one shot, something like below. But i doubt that is going to help us here. Please correct me if wrong?
while <condition>
LOOP
DELETE FROM TRANSACTION
WHERE ROWNUM <= 100k;
COMMIT;
<increment counter>
end loop;
2)I was thinking of Parallel DML option as the only option left, but as this table is being in use(INSERT/UPDATE) 24/7, so i think parallel DML will create locking issue so we cant go by that option. So want experts advice , If any other way possible to make this delete in faster time?
3)As these data are residing over mainly two subpartitions, Some team mates suggesting atleast deleting one subpartition at a time will make the delete faster as that will be one segment at a time, but my understanding was partition pruning only can make the data SELECTION faster but not the DELETE. And here we have no issue with data/rowid SELECTION part. Please correct me if wrong here?
4)If we somehow make the subpartition drop option feasible, as this table has two global indexes so the DML will also face locking issue till the drop+update index finishes. Is there any way to cater this scenario?
Below is the DELETE query and its sql monitor. And also the SELECT part of the query and its sql monitor.
SQL Text
------------------------------
DELETE /*+ full(a) */ FROM TRANSACTION A WHERE A.SCODE IN (SELECT B.SCODE FROM REFDATA B WHERE B.INDICATOR = 1 ) AND A.DT < SYSDATE-10 AND FID IS NULL AND ROWNUM < 3000000
Global Information
------------------------------
Status : DONE
Instance ID : 1
SQL Execution ID : 16777216
Execution Started : 02/02/2019 10:26:00
First Refresh Time : 02/02/2019 10:26:03
Last Refresh Time : 02/02/2019 10:58:26
Duration : 1946s
PLSQL Entry Ids (Object/Subprogram) : 99190,1
PLSQL Current Ids (Object/Subprogram) : 99190,1
Global Stats
=========================================================================================================
| Elapsed | Cpu | IO | Application | Concurrency | Cluster | Buffer | Read | Read | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Offload |
=========================================================================================================
| 2039 | 1303 | 735 | 0.00 | 0.00 | 0.45 | 92M | 2M | 60GB | 80.66% |
=========================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=476885801)
=======================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Cell | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Offload | (Max) | (%) | (# samples) |
=======================================================================================================================================================================================
| 0 | DELETE STATEMENT | | | | 1944 | +3 | 1 | 0 | | | | | | |
| 1 | DELETE | TRANSACTION | | | 1945 | +2 | 1 | 0 | 1M | 11GB | | | | |
| 2 | COUNT STOPKEY | | | | 1944 | +3 | 1 | 3M | | | | | | |
| 3 | HASH JOIN | | 279M | 47M | 1944 | +3 | 1 | 3M | | | | 2M | | |
| 4 | INDEX RANGE SCAN | REFDATA_IX1 | 254 | 3 | 1 | +3 | 1 | 255 | | | | | | |
| 5 | PARTITION LIST ALL | | 421M | 47M | 1944 | +3 | 1 | 3M | | | | | | |
| 6 | PARTITION RANGE ALL | | 421M | 47M | 1944 | +3 | 1 | 3M | | | | | | |
| 7 | TABLE ACCESS STORAGE FULL | TRANSACTION | 421M | 47M | 1944 | +3 | 3 | 3M | 50015 | 49GB | 99.40% | 15M | | |
=======================================================================================================================================================================================
SQl Monitor for the SELECT query:-
***************************************
SQL Text
------------------------------
select /*+ full(a) MONITOR*/ rowid FROM TRANSACTION A WHERE A.SCODE IN (SELECT B.SCODE FROM REFDATA B WHERE B.INDICATOR = 1 ) AND A.DT < SYSDATE-10 AND FID IS NULL AND ROWNUM < 3000000
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 2
SQL ID : 064zr8k9shh91
SQL Execution ID : 33554433
Execution Started : 02/03/2019 05:01:12
First Refresh Time : 02/03/2019 05:01:12
Last Refresh Time : 02/03/2019 05:01:41
Duration : 29s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 601
Global Stats
====================================================================================================
| Elapsed | Cpu | IO | Application | Other | Fetch | Buffer | Read | Read | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Offload |
====================================================================================================
| 2.16 | 1.68 | 0.15 | 0.00 | 0.33 | 601 | 7M | 52387 | 51GB | 99.69% |
====================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=185281617)
=================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Cell | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Offload | (Max) | (%) | (# samples) |
=================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 30 | +0 | 1 | 3M | | | | | | |
| 1 | COUNT STOPKEY | | | | 30 | +0 | 1 | 3M | | | | | | |
| 2 | HASH JOIN | | 3M | 514K | 30 | +0 | 1 | 3M | | | | 2M | | |
| 3 | INDEX RANGE SCAN | REFDATA_IX1 | 254 | 3 | 1 | +0 | 1 | 255 | | | | | | |
| 4 | PARTITION LIST ALL | | 4M | 514K | 30 | +0 | 1 | 3M | | | | | | |
| 5 | PARTITION RANGE ALL | | 4M | 514K | 30 | +0 | 1 | 3M | | | | | | |
| 6 | TABLE ACCESS STORAGE FULL FIRST ROWS | TRANSACTION | 4M | 514K | 30 | +0 | 3 | 3M | 52387 | 51GB | 99.69% | 15M | | |
=================================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<3000000)
2 - access("A"."SCODE"="B"."SCODE")
3 - access("B"."INDICATOR"=1)
6 - storage("FID" IS NULL AND "A"."DT"<SYSDATE@!-10)
filter("FID" IS NULL AND "A"."DT"<SYSDATE@!-10)