RAC/Exadata: 4 TB table, 99% offloading sometimes, almost no offloading + cluster waits other times
Oracle Database 19c (19.14), Exadata, RAC 12 nodes, 36 storage servers. Queries against very large tables always has the same plan hash value, and execution plan step always says "TABLE STORAGE ACCESS FULL". I know this is not always guaranteed, but there are no chained rows, CPU-starved storage cells, and so forth.
When it runs fast:
But with same data volumes and resource availability, I/O rate steadily decreases and it runs for hours.
The only thing that's different when it runs bad is that there is a huge percentage of cluster waits:
Is the runtime engine deciding that buffer gets are cheaper than an offloaded full table scan? (not true). SGA is 80 GB. Table is definitely past the "large table threshold". Serial direct path reads also occur elsewhere. When it runs for hours, I capture V$SESSTAT at regular intervals, and the DIFF column shows the value of the metric for 400 minutes in this case. Way too slow, even for a non-partitioned 4 TB table.