Hello Everyone,
Database Version : 12.1.0.2
Machine OS : Linux 6
Database Type : Production
Server Box : Exadata ( 2 node RAC database )
We see a DELETE operation trying to delete records olders than an year ( 365 days) from an 148M rows (148723389) table . We are running this delete for the very first time and when we see the long_operations it shows its gonna take very long to complete it.
longops.
sofar - 54213
totalwork - 673760
I am not quite 100% sure if can run the SQL Tuning Advisor on a DELETE ( or any DML ) operation. I ran but it does not show any recommendations.
Also, we have couple of indexes created on the table and it looks its trying to make use of it. when I check it shows event as Index Fast Full Scan in the execution plan. Can anyone suggest what best can be done to improve the performance of the DELETE operation.
SQL - Delete from table1 where dt_tm_date < trunc(sysdata) -365;
can I use the parallel option or hints to improve the delete perf.