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

Manipulating table statistics for the statement period

I’ve moved my blog from https://insanedba.blogspot.com to https://dincosman.com Please update your bookmarks and follow/subscribe at the new address for all the latest updates and content. More up-to-date content of this post may be available there.

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's blog post (Correct syntax for the table_stats hint ) offers up some useful information about it.

These are the variables that play a 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 affect 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 behavior is controlled by the "_direct_read_decision_statistics_driven" parameter.

Our table block size is larger than the medium table threshold but smaller than VLOT (500% of the buffer cache). The hard limit for VLOT here plays a role in  decision-taking process. For my sample table, full table scan is done via direct path read only when the 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 the 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 are 6419956. This is the actual block size of my table. Result is NoDirectRead, Cache_Read decision is taken with the table actual block size. The table block size is between MTT (387580)  and VLOT(19379100). 

We will run the same query with the table block size manipulated to 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 the object size is larger than VLOT. I have also tested the query with much larger amounts. For my query when the block size is larger than 65735829, the full table scan decision is replaced with an 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 like the one below;

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 cannot be overruled with OPT_PARAM. Then I contacted Frits Hoogland. He advised me to make sure that row source operation is compatible with a 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 behavior 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 behavior. I patched my query with the below command. Case solved.

Hope it helps.