X

Everything about Table Scans including offload to Exadata, Big Data SQL, and External Tables

  • April 15, 2019

Correct syntax for the table_stats hint

Roger Macnicol
Software Architect

A friend contacted me to ask why they were having problems using the table_stats hint to influence optimizer decision making and also to influence the decision to use direct read or buffer cache scan so this is just a quick blog post to clarify the syntax as it is not well documented.

table_stats(<table_name> <method> {<keyword>=<value>} )

Method is one of: DEFAULT, SET, SCALE, SAMPLE

Keyword is one of: BLOCKS, ROWS, ROW_LENGTH

The most useful methods are SET which does for statement duration what dbms_stats.set_table_stats does globally; and SCALE which acts to scale up the current size of the segment and can therefore be used to try what if scenarios on the segment growing on performance

For example:

select /*+  table_stats(scott.emp set rows=14 blocks=1  row_length=10)  */ * from scott.emp;

Effect of the table_stats hint on table scans

Since this is a table scan blog, let's look at the impact on table scans. Using the Scale 1 customers table with 150,000 rows

SQL> exec dbms_stats.gather_table_stats(USER,'RDM');

SQL> select sum(BLOCKS) from user_segments where segment_name='RDM';
SUM(BLOCKS)
-----------
       1792

and use trace events

event="trace[NSMTIO] disk medium"                  # Direct I/O decision making
event="10358 trace name context forever, level 2"  # Buffer cache decision making

We see this segment is smaller than the small table threshold for this buffer cache (kcbstt=9458) and so decision making is short-circuited and will use the buffer cache :

kcbism: islarge 0 next 0 nblks 1689 type 2, bpid 3, kcbisdbfc 0 kcbnhl 8192 kcbstt 9458 keep_nb 0 kcbnbh 461198 kcbnwp 1 kcbpstt 0, BCRM_ON 0
NSMTIO: qertbFetch:NoDirectRead:[- STT < OBJECT_SIZE < MTT]:Obect's size: 1689 (blocks), Threshold: MTT(46119 blocks),

Now let's try the same query with the hint shown in the example above:

kcbism: islarge 1 next 0 nblks 66666666 type 2, bpid 3, kcbisdbfc 0 kcbnhl 8192 kcbstt 9458 keep_nb 0 kcbnbh 461198 kcbnwp 1 kcbpstt 0, BCRM_ON 0
kcbimd: nblks 66666666 kcbstt 9458 kcbnbh 46119 bpid 3 kcbisdbfc 0 is_medium 0
kcbivlo: nblks 66666666 vlot 500 pnb 461198 kcbisdbfc 0 is_large 1
NSMTIO: qertbFetch:DirectRead:[OBJECT_SIZE>VLOT]
NSMTIO: Additional Info: VLOT=2305990
Object# = 75638, Object_Size = 66666666 blocks

Now the size of the table in blocks is far larger than our small table threshold so we go on to evaluate whether it is a medium table and it is too large to be considered medium (cutoff is 10% cache i.e. kcbnbh=46119 blocks) so then it is evaluated as a very large table and that is true so direct read will be used.

Making the new value permanent

If for some reason we wanted to make some value permanent (caveat emptor) after doing experiments with the hint, we can set the table stats like this:

BEGIN
  DBMS_STATS.SET_TABLE_STATS( 
    ownname => 'TPCH'
  , tabname => 'RDM'
  , numrows => 2000000
  , numblks => 10000 );
END;

SQL>  select NUM_ROWS,BLOCKS,EMPTY_BLOCKS from DBA_TAB_STATISTICS where TABLE_NAME='RDM';
NUM_ROWS     BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
   2000000     10000             0

and now we see the size we decided upon after the needed experiments being used without a hint:

kcbism: islarge 1 next 0 nblks 10000 type 2, bpid 3, kcbisdbfc 0 kcbnhl 8192 kcbstt 9458 keep_nb 0 kcbnbh 461198 kcbnwp 1 kcbpstt 0, BCRM_ON 0
kcbimd: nblks 10000 kcbstt 9458 kcbnbh 46119 bpid 3 kcbisdbfc 0 is_medium 1
kcbcmt1: hit age_diff adjts last_ts nbuf nblk has_val kcbisdbfc cache_it 191 23693 23502 461198 10000 1 0 1
NSMTIO: qertbFetch:NoDirectRead:[- STT < OBJECT_SIZE < MTT]:Obect's size: 10000 (blocks), Threshold: MTT(46119 blocks),

Our table is no longer small as 10,000 blocks is larger than STT=9458 blocks so it is a medium table but as it is smaller than the medium table threshold it will still use the buffer cache.

I hope you found this useful.

Roger

 

Join the discussion

Comments ( 1 )
  • Kaley Friday, April 19, 2019
    Why is it that when the TABLE_STATS() hint is used in 11.2.0.4 with SCALE, it doesn't seem to influence the number of rows expected by the CBO in the explain plan.... But if you specify the same hint twice, it does?
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.