Yesterday and for part of today, I was able to insert into a table using hint /*+ APPEND NOLOGGING IGNORE_ROW_ON_DUPKEY() */ and I was getting a rate of about a million rows in 700K rows/minute. Today I get a wait event "cell single block physical read" and the job has gone to LaLa land.

I removed the IGNORE_ROW_ON_DUPKEY hint and replaced with SELECT DISTINCT. No improvement.

Looks like you're doing insert into table as select col1,col2.....from tab, here the select statement is doing index scan to pull the required data and that's the reason you're seeing wait event "single block physical read". If this is the case I would suggest you add full hint in you're select statement and see how the inserts goes..

Whatever the select is doing now is not what was happening for the other 30 times. So why the sudden change in the way the selct would work? Similar number of records in the source table ( range from 500K to 1200K). The select is

It could also be that sufficient quantity of table a is in the buffer cache that the optimizer is not choosing a full table scan.In such a case there might be some other index on a which is suboptimal but is chosen as you have prevented all the optimal indexes to be used. Try to get the runtime plan from the cursor cache to see what plan the optimizer has chosen.

In some cases we found that adding hint NO_INDEX helped but not when we had Text Indexes which we had to then allow with INDEX hint. In other cases we had to gather statistics for each partition as the data got loaded before doing any "query" (actually whatever Oracle Text does when building an Index) on that partition. We were finally able to get down to 10-15 minutes to Text Index a partition with about 1.5 million rows. This met our data migration data load assembly-line schedule!

Whatever the select is doing now is not what was happening for the other 30 times. So why the sudden change in the way the selct would work? Similar number of records in the source table ( range from 500K to 1200K). The select is

Since you've got an inline scalar subquery which isn't an aggregate and looks as if it's high precision and ought to run with an indexed access path, a simple explanation would be that yesterday the data needed by that part of the query was well-cached, but today it isn't.

Do you have any AWR content that could show you the execution paths from yesterday and today ?