Smart Scan: Why is “_small_table_threshold” Important?

As we can see, 2% of the buffer cache is approximately 34406 blocks.   If we run a query against X$KSPPI and X$KSPPSV, we can confirm this:

One thing to note from the above is that the value for “_small_table_threshold” from X$KSPPSV is less than that predicted from the calculation of 2% of the buffer cache size, so we can assume that dynamic SGA sizing via sga_max_size had at one point increased the buffer cache size.

Let’s run some test queries on tables with varying block counts and see if Smart Scan kicks in.

Based on the above, none of the block counts are above “_small_table_threshold”, so let’s run some full-scans:

The two tests above showed that neither query was eligible for cell offload because there was no evidence of any bytes eligible for cell offload.  Take my word that the query that showed “cell physical IO interconnect bytes” was searching for all “cell-related” statistics for the given session.  Also note that there were no wait events for “cell smart table scan” in the bottom query output for each test.

Now let’s double the size of SOE.CUSTOMERS_TEST:

The table in question now has 45,305 blocks, so let’s re-test:

Perhaps surprisingly, we still did a normal full table scan, not a Smart Scan, contrary to what’s documented on MOS.  But oracle-l posts by Tanel Poder and other sources indicate that serial directs reads on full table scans are somewhat reliably enabled when the block count for the table is about 5 times “_small_table_threshold”.  So let’s insert enough rows into SOE.CUSTOMERS_TEST to validate this.  We need about 158,715 blocks to meet this requirement.

We’ll first insert just slightly less rows than we need, to get 158,249 blocks, and test:

As we can see, still no Smart Scan.  Now let’s insert a few more rows:

Now we can finally see Smart Scan kick in, and what a difference in our query performance!

These examples may lead you to think a bit about how to influence this behavior? First, can we reduce “_small_table_threshold” to a lower value?  Second, can we force serial direct reads?  Let’s test these scenarios.

First, we’ll create another copy of our customers table called CUSTOMERS_TEST2.

Now let’s test by altering our current session and running a full-scan against this new table:

As we can see, no cell offload was realized, which leads me to believe that setting the value of “_small_table_threshold” to a lower value at the session-level does not yield the intended result.    In fact, I was not able to get serial reads to work regardless of session level settings for “_small_table_threshold”.  After changing at the system-level, I was still not able to get serial reads to be enabled regardless of setting “_small_table_threshold”=1.

Now let’s try to force serial direct reads using “_serial_direct_read”=always instead of “auto”.  We’ll use our same test query from above:

As you can see, serial reads were done and Smart Scan kicked in.  This can be a useful tool for sessions in which you know your block counts are below the threshold and you still want to benefit from Smart Scan.

For the last test case, what if we wanted to force conventional buffer-cached full table scans instead of serial direct reads?  Setting event 10949 to 1 is a way to do this, so let’s test against SOE.CUSTOMERS_TEST, which has more blocks that 5 times “_small_table_threshold”:

To Summarize:

• Exadata cell offload requires serial direct reads.
• Oracle will favor serial direct reads over conventional full table scans into the buffer cache when the number of blocks requested via the full scan operation is greater than 5 times the value of “_small_table_threshold”.
• “_small_table_threshold” is calculated based on roughly 2% the size of the database buffer cache.
• Altering “_small_table_threshold” at either the session level or system-wide does not seem to have an impact on serial direct read behavior – Oracle still uses the derived setting times 5.
• You can force cell offload by setting “_serial_direct_read”=auto.
• Setting event 10949 to 1 will force conventional full table scans and bypass serial direct reads, and thus cell offload, regardless ofthe number of blocks in the underlying table.