Database - RAC/Scalability (MOSC)

MOSC Banner

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.

Tagged:

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center