Making the Most of Exadata, Revisited

Greg Rahn of Oracle’s real-world performance group posted a technical review of an article I wrote last summer, entitled Making the Most of Oracle Exadata. I have a few comments on the technical concerns Greg raised.

• On the benefits of smart scans to improve buffer cache efficiency: indeed smart scans bypass the buffer cache entirely, so don’t have a direct impact on the buffer cache. But by taking table scans that typically involve large data volumes, they free up buffer cache space for the smaller data sets that can better fit into the cache.
• On the benefits of storage indexes for partition pruning: while storage indexes and partition pruning work by different mechanisms, they can deliver the same benefits. With 100% correlated data sets, storage indexes can avoid the need to read non-matching partitions off disk, effectively “pruning” these non-matching partitions from the resultset. It’s probably easier to illustrate with an example:
CREATE TABLE orders (ORDER_DATE DATE, ORDER_ID NUMBER)
PARTITION BY RANGE (ORDER_DATE) (
PARTITION P200912 VALUES LESS THAN (TO_DATE('01-JAN-2010','DD-MON-YYYY')),
PARTITION P201001 VALUES LESS THAN (TO_DATE('01-FEB-2010','DD-MON-YYYY')));


  ORDER_DATE	ORDER_ID
  01-Dec-2009	20090004
  01-Jan-2010	20100001

  The following queries read the same data off disk, even though only ORDER_DATE is a partition key:

  1. SELECT * FROM orders WHERE ORDER_DATE >= TO_DATE('01-DEC-2009','DD-MON-YYYY') and ORDER_DATE < TO_DATE('01-JAN-2010','DD-MON-YYYY');
  2. SELECT * FROM orders WHERE ORDER_ID >= 20090001 and ORDER_ID < 20100001;
• On hybrid columnar compression and the effect of updates: in the original article, I mentioned that if a columnar-compressed row is updated, the entire compression unit would be written to disk in uncompressed form. This is incorrect: the individual row(s) updated are migrated and compressed with OLTP compression, but the remaining rows in the compression unit, if any, retain columnar compression.
• On the benefits of flash caching for commit-intensive applications: the current Exadata flash cache is implemented as a write-through rather than as a write-back cache as I originally believed. This means that the flash cache does not improve the performance of disk writes at all and actually does not speed up commits. In an Exadata environment writes are cached by a small battery-backed write cache on the disk controller however, which particularly helps commit-intensive applications.

I’ve put together a revised version of the original article with corrections and clarifications incorporated, as well as some new content added. It’s currently winding its way through the editing process, and I’ll post to this blog when it comes out.