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:
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');
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.
Want to talk with an expert? Schedule a call with our team to get the conversation started.
Smart Scan is an Exadata storage optimization that takes place after the Oracle kernel has chosen to use a direct read (one that will not populate buffers into the buffer cache). How then can Smart Scan free up buffer cache space?
Right. The benefits come from queries on these large datasets not polluting the buffer cache. Now granted, the buffer cache algorithms are already designed to try and avoid aging caused by a single large query, but smart scans and other direct-path reads can avoid the issue entirely by bypassing the cache.
Apologies for the late reply. I did some tests and it looks like you’re right: under 11gR2 without Exadata, the smart scans were done as equally unbuffered direct path reads. I’ll be putting out a revised article with these changes included.
It seems you are missing the point I’m making. Smart Scan is an Exadata optimization done after the decision to use a non-buffered direct read. The presence or absence of Smart Scan therefore has no impact on the buffer cache whatsoever and does not free up buffer cache space.
No argument there. My point of comparison is a non-direct path buffered read. Indeed there’s no difference buffer cache-wise between a smart scan and any other direct path read.
OK, take your point of comparison: if you are doing non-direct path buffered reads then Smart Scan is not present. So if Smart Scan is not present, then it surely can not have any impact on the buffer cache, right? In such a case, Exadata is simply serving blocks just like any other storage would.
Hi Greg and Marc .
One question related to the same topic.
When I have parallel_degree_policy set to AUTO , the direct path reads could be buffered in SGA , correct ??
My understanding is blocks retrieved through smart scan have completely different structure than the traditional oracle blocks and is much smaller in size for the same set of data not done through smart scans . In this case when those blocks buffered in SGA , they are really saving lot of SGA space , correct??
Or the blocks retrived through smart scans are not buffered at all even though we have parallel_degree_policy = AUTO ??
Please correct me if am wrong in these assumptions
I think Grey is right, Oracle 11g uses adaptive FTS, based on size of table it has to query, if the size of table is small and is in cache or can fit in cache, FTS will be done from buffer cache, otherwise oracle 11g r2 will go for direct path read which brings oracle exadata smart scan in action. The feature was introduce so that long fts dont spoil the hit ratio of buffer cache. I have observed this behavior during our last DWH project.