Large number of deletes causing table fragmentation resulting in poor query performance

I was asked to look into a nightly script that had significantly increased in elapsed time. The script was rather simple in that it deleted rows older than (today’s date – 3 months) from multiple tables. I ran an ADDM report on the database (12.1.0.2.0 – 64bit Production), and one of the deletes popped up in the report:

   Action
      Run SQL Tuning Advisor on the DELETE statement with SQL_ID
      "75csungpxhvv6".
      Related Object
         SQL statement with SQL_ID 75csungpxhvv6.
         delete from PROD_SCHEMA.PROD_TABLE where ORDER_DT<20201212
   Rationale
      The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
      This part of database time may be improved by the SQL Tuning Advisor.
   Rationale
      Database time for this SQL was divided as follows: 100% for SQL
      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
      execution.
   Rationale
      I/O and Cluster wait for INDEX "PROD_SCHEMA.PROD_TABLE_ORDER_DT" with object ID
      322364 consumed 100% of the database time spent on this SQL statement.

The table in question was not large; a few 100 thousand rows with reasonable row length; no large varchars etc. The obvious issues were quickly eliminated; the table has current stats, there was an index to exactly match the predicate, and the optimizer was choosing the index. However, I did notice that the table was occupying 28 GB of space. A quick calculation of # of rows multiplied by row length indicated that it should be occupying less than 1 GB of space approx.

I ran the below query and confirmed that most of the space was empty

set line 200;
set pages 9999;
col owner                format a15               heading "Owner"
col table_name           format a30               heading "Table Name"
col avg_row_len          format 999,999,999       heading "Avg Row Len"
col num_rows             format 999,999,999,999   heading "Number of Rows"
col TOTAL_SIZE_MB        format 999,999,999       heading "Total|Size|(MB)"
col ACTUAL_SIZE_MB       format 999,999,999       heading "Actual|Size|(MB)"
col FRAGMENTED_SPACE_MB  format 999,999,999       heading "Frag|Size|(MB)"
col percentage           format 999.99            heading "Per|Cent|Frag"
SELECT   owner
        ,table_name
        ,avg_row_len
        ,num_rows
        ,Round((( blocks * 8 / 1024 )), 2) "TOTAL_SIZE_MB"
        ,Round(( num_rows * avg_row_len / 1024 / 1024 ), 2)  "ACTUAL_SIZE_MB"
        ,Round(( ( blocks * 8 / 1024 ) - ( num_rows * avg_row_len / 1024 / 1024 ) ), 2) "FRAGMENTED_SPACE_MB"
        ,Round(( Round(( ( blocks * 8 / 1024 ) - ( num_rows * avg_row_len / 1024
                                                  / 1024
                                                ) ), 2
                     )
                     / Round((( blocks * 8 / 1024 )), 2) ) * 100, 2)  "percentage"
FROM     dba_tables
WHERE    num_rows > 0
  and    table_name = 'PROD_TABLE'
  and    owner = ' PROD_SCHEMA '
order by FRAGMENTED_SPACE_MB desc 
;

We were able to ask the application for a small outage to export (data pump), truncate, and import that data from the table. After this, the query that used to take almost 8 minutes started executing in less than a second.