We had an interesting case in Oracle 12c where a multi-table join on a merge statement, running in Parallel (degree of parallel = 8), was doing a full table scan on a fairly large table, and was spending most of the query time on “db file sequential reads”.
This isn’t supposed to happen on a full table scan, which should do a scattered read. Now, this table was suffering from excessive row migration (it used OLTP compression, with the unadvertised side-effect of massive row migration when there are updates). Even so, a full table scan doesn’t bother to follow the migrations, it scans on through knowing that it will eventually get to migrated rows.
So we opened a ticket with Oracle and discovered that full table scans will do sequential reads in this scenario:
1) Parallel DML
2) Chained rows
3) Full table scan of the table being modified.
This is because when the statement is divided into parallel threads, Oracle can’t rely on eventually getting to the block – it’s a DML statement so blocks could be changing, and it wants to keep the read and update of a row in a single thread. Thus it has to do db file sequential reads on migrated rows.
The solution was simple. We used dbms_redefinition to remove the OLTP compression, which eliminated the massive row migration. The problem disappeared.