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. 

	SYS@entity1> col name format a25
	SYS@entity1> col value format a15
	SYS@entity1> col description format a60
	SYS@entity1> SELECT a.ksppinm name, b.ksppstvl VALUE, a.ksppdesc description
	FROM x$ksppi a, x$ksppcv b
	WHERE a.indx = b.indx
	AND a.ksppinm in ('_db_block_buffers','_small_table_threshold');
	NAME VALUE DESCRIPTION
	------------------------- --------------- ------------------------------------------------------------
	_db_block_buffers 3875820 Number of database blocks cached in memory: hidden parameter
	_small_table_threshold 77516 lower threshold level of table size for direct reads

view raw stt_hidden_parameter.txt hosted with ❤ by GitHub

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.

	SQL> select blocks from dba_tables where table_name='PRODUCTS';
	BLOCKS
	---------------
	6419956

view raw query_block_count.txt hosted with ❤ by GitHub

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. 

	SQL> alter session set tracefile_identifier='direct_path_read_decision';
	SQL> alter session set events 'trace[nsmtio]'; # Direct path decision making
	SQL> SELECT * FROM PRODUCTS PRODUCT1
	LEFT OUTER JOIN SPECS parameter1
	ON PRODUCT1.LENGTH = parameter1.ID
	LEFT OUTER JOIN PRODUCTS PRODUCT2
	ON PRODUCT1.CONTENT_ID = PRODUCT2.ID
	WHERE (PRODUCT1.HTML_CONTENT IS NOT NULL)
	AND (PRODUCT1.CONTENT_ID IS NULL)
	[oracle@exadb03 trace]$ vi entity1_ora_167532_direct_path_read_decision.trc
	...
	NSMTIO: kcbism: islarge 1 next 0 nblks 6419956 type 2, bpid 3, kcbisdbfc 0 kcbnhl 65536 kcbstt 77516 keep_nb 0 nbuf 3875820 kcbnwp 12 pstt 0 Exadata 1 bsz 8192 objd 356469
	NSMTIO: kcbimd: nblks 6419956 kcbstt 77516 kcbnbh 387582 kcbisdbfc 3 is_medium 0 objd 0
	NSMTIO: kcbivlo: nblks 6419956 vlot 500 pnb 3875820 kcbisdbfc 0 is_large 0
	NSMTIO: qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from caches(local/remote) and checking storage reduction factors (OLTP/EHCC Comp)
	NSMTIO: kcbdpc:NoDirectRead:[CACHE_READ]: tsn: 6, objd: 356469, objn: 74234
	ckpt: 0, nblks: 6419956, ntcache: 1837, ntdist:1837, Exadata:1, bsz:8192, rule 0, threshold 104857600, rule_enabled 0\nDirect Path for pdb 0 tsn 6 objd 356469 objn 74234
	...

view raw Trace_nsmtio.txt hosted with ❤ by GitHub

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.

	SQL> SELECT /*+ table_stats(EDMS.PRODUCTS set blocks=19379101) */ * FROM PRODUCTS PRODUCT1
	LEFT OUTER JOIN SPECS parameter1
	ON PRODUCT1.LENGTH = parameter1.ID
	LEFT OUTER JOIN PRODUCTS PRODUCT2
	ON PRODUCT1.CONTENT_ID = PRODUCT2.ID
	WHERE (PRODUCT1.HTML_CONTENT IS NOT NULL)
	AND (PRODUCT1.CONTENT_ID IS NULL)
	[oracle@exadb03 trace]$ vi entity1_ora_167532_direct_path_read_decision.trc
	...
	NSMTIO: kcbism: islarge 1 next 0 nblks 19379101 type 2, bpid 3, kcbisdbfc 0 kcbnhl 65536 kcbstt 77516 keep_nb 0 nbuf 3875820 kcbnwp 12 pstt 0 Exadata 1 bsz 8192 objd 356469
	NSMTIO: kcbimd: nblks 19379101 kcbstt 77516 kcbnbh 387582 kcbisdbfc 3 is_medium 0 objd 0
	NSMTIO: kcbivlo: nblks 19379101 vlot 500 pnb 3875820 kcbisdbfc 0 is_large 1
	NSMTIO: qertbFetch:DirectRead:[OBJECT_SIZE>VLOT]
	NSMTIO: Additional Info: VLOT=19379100
	Object# = 356469, Object_Size = 19379101 blocks
	...

view raw table_stats_hint_trace.txt hosted with ❤ by GitHub

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;

	SQL> SELECT /*+ table_stats(EDMS.PRODUCTS set blocks=60000000) */ *
	FROM (SELECT * FROM PRODUCTS PRODUCT1
	LEFT OUTER JOIN SPECS parameter1
	ON PRODUCT1.LENGTH = parameter1.ID
	LEFT OUTER JOIN PRODUCTS PRODUCT2
	ON PRODUCT1.CONTENT_ID = PRODUCT2.ID
	WHERE (PRODUCT1.HTML_CONTENT IS NOT NULL)
	AND (PRODUCT1.CONTENT_ID IS NULL))
	WHERE ROWNUM < 10;

view raw problematic_query_with_rownum_predicate.txt hosted with ❤ by GitHub

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.  

	SQL> SELECT /*+ table_stats(EDMS.PRODUCTS set blocks=60000000) all_rows */ *
	FROM (SELECT * FROM PRODUCTS PRODUCT1
	LEFT OUTER JOIN SPECS parameter1
	ON PRODUCT1.LENGTH = parameter1.ID
	LEFT OUTER JOIN PRODUCTS PRODUCT2
	ON PRODUCT1.CONTENT_ID = PRODUCT2.ID
	WHERE (PRODUCT1.HTML_CONTENT IS NOT NULL)
	AND (PRODUCT1.CONTENT_ID IS NULL))
	WHERE ROWNUM < 10;

view raw change_table_stats_optim_mode.txt hosted with ❤ by GitHub

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.

DECLARE
patch_name VARCHAR2 (4000);
BEGIN
patch_name := DBMS_SQLDIAG.create_sql_patch (
sql_id => '16rzwmtc4wpqu',
hint_text => 'table_stats(EDMS.PRODUCTS set blocks=60000000) all_rows',
name=> 'SQL_PATCH_PRODUCTS_TABLESTATS
');
END;

view raw sql_patching_tablestats.md hosted with ❤ by GitHub

Hope it helps.