Using table stats hint to force a direct path read but with a rownum predicate surprise

Manipulating table statistics for the statement period

On my blog post "Who makes the decisions? I thought it was Optimizer", I used parallel hint to force a direct path read. This time i will try to make use of table_stats hint to force a direct path read. There is not much documentation about the usage of this hint. Roger Macnicol blog post ( Correct syntax for the table_stats hint ) offers up some useful information about it.

These are the variables play role in direct path read decision. 

STT stands for small table threshold = _db_block_buffers * 0,02 =  3875820 * 0,02 = 77516

MTT stands for medium table threshold = _small_table_threshold * 5 = 77516 * 5 = 387580

VLOT stands for Very Large Object Threshold = _db_block_buffers * 5  = 3875820 * 5 = 19379100

I will show how the thresholds effect the direct path decision. We will use nsmtio (non smart IO) event  to trace the direct path or non-direct path/buffered decision.

 

Before 11.2.0.2 release, the direct path read decision was based on the actual segment block count extracted from the segment header. After 11.2.0.2 release, Oracle takes this number from dba_tables.block count. This behaviour is controlled by the "_direct_read_decision_statistics_driven" parameter.

Our table block size is larger than medium table threshold but smaller than VLOT(500% of the buffer cache). The hard limit for VLOT here plays role in  decision taking process. For my sample table, full table scan is done via direct path read only  when database is opened freshly. After 1-2 days,  full table scan is done via the buffer cache. This difference is based on the % amount of blocks currently in buffer cache and the % amount of buffers dirty. I have created a decision tree diagram based on the blog post "Investigating the full table direct path / buffered decision" by Frits Hoogland. 

Here is a quick demo. 

nblks here is 6419956. This is the actual block size of my table. Result is NoDirectRead , Cache_Read decision is taken with the table actual block size. Table block size is between MTT (387580)  and VLOT(19379100). 

We will run same query with the table block size manipulated to a just a little bit larger amount (19379101) than VLOT. We expect a full table scan will be forced via direct path read.

Direct Read decision is taken as object size is larger than VLOT. I have also tested the query with much larger amounts. For my query when block size is larger than 65735829, the full table scan decision is replaced with index scan. 

I decided to patch the sql with a fixed large block size(60000000). The sample query i have used so far,  was a short form of the real problematic query.I did all the tests with the short form. When i patched the real query with the table_stats hint, it could not fire direct path read. The difference was that;  The real query has an outer query surrounding it for pagination purpose. Yet another challenge was on the way. The real query was alike below one;

I tried to understand why the hint is not working. Also i could not find any example of  table_stats hint usage within a subquery.  I tried /*+ OPT_PARAM('_serial_direct_read',always) */ hint, to force a direct path read. That did not work also. As Roger Macnicol says; "Another point to remember is that not all parameters get their value from the cursor environment For example the buffer cache gets the raw value of _serial_direct_read,so it can not be overruled with OPT_PARAM. Then I contacted with Frits Hoogland. He advised me to make sure that rowsource operation is compatible with direct path read. After talking to Frits, I changed my focus to rownum filter and optimizer_mode. And i could make it work with all_rows hint.  

This is due to the fact that Oracle converts optimizer mode to the first_rows mode internally when ROWNUM predicate is used. This behaviour is controlled by "_optimizer_rownum_pred_based_fkr" hidden parameter, whose default value is true. Dion Cho has a nice blog post(FIRST_ROWS vs. ALL_ROWS and ROWNUM predicate) about this behaviour. I patched my query with the below command. Case solved.

Hope it helps.