why is there an index range scan on max() query?
Drop that index and create without desc:
create index idx_transfer_log_status on transfer_log (transfer_status, last_sent_time);

The database can decide to read an ascending index in descending order. The last entry can be read as quickly as the first entry.
But even better, it can recognize a case when you search for a MIN/MAX value and use the operation for that:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6q8sbnqkajz74, child number 1
-------------------------------------
select   max(d.last_sent_time) from   transfer_log d where
d.transfer_status = 200

Plan hash value: 3076158531

--------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                         |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |                         |     1 |    11 |            |          |
|   2 |   FIRST ROW                  |                         |     1 |    11 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| IDX_TRANSFER_LOG_STATUS |     1 |    11 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  3 - access(&quot;D&quot;.&quot;TRANSFER_STATUS&quot;=200)

Unfortunately this does not work with a desc index.