Manipulating table statistics for the statement period
On my blog post “Forcing Direct Path Read in Oracle Database“, 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.
| SQL> select blocks from dba_tables where table_name='PRODUCTS'; | |
| BLOCKS | |
| ————— | |
| 6419956 |
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.
| 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 | |
| … |
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.
| 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 | |
| … |
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;
| 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; |
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.
| 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; |
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.
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;
Hope it helps.
Osman’s DBlog 
Leave your comment