On versions 11.2.0.2 – 11.2.0.4 Oracle uses objects’ statistics to make direct path reads decision (of course, if “_direct_read_decision_statistics_driven” haven’t changed it to “false”), and we can force serial direct reads on statement level using sql profiles with hints INDEX_STATS/TABLES_STATS, but since at least 12.1.0.2 this decision ignores statistics.
Btw, thanks to Jure Bratina, we know now, that we need to repeat hint TABLE_STATS at least twice to make it work ๐ And from the following test case we know that it takes parameters from second one ๐
Compare trace files of the following test cases:
drop table t1;
create table t1 as select * from dual;
----------------------------
pro ######################################;
exec dbms_stats.gather_table_stats('','T1');
exec dbms_stats.set_table_stats(user,'T1',numblks => 33333333);
col value new_val oldval noprint;
select value from v$statname n, v$mystat s
where n.statistic#=s.statistic# and n.name ='physical reads direct';
----------------------------
alter session set tracefile_identifier='table_stats2';
--alter session set events '10053 trace name context forever, level 1';
alter session set events 'trace[nsmtio] disk highest';
select/*+ table_stats(t1, scale, blocks=66666666 rows=2222222222)
table_stats(t1, scale, blocks=99999999 rows=4444444444)
*/
*
from t1;
select value-&oldval directreads, value from v$statname n, v$mystat s
where n.statistic#=s.statistic# and n.name ='physical reads direct';
You can see that our hint successfully changed number of blocks and forced direct path reads on 11.2.0.4:
NSMTIO: qertbFetch:DirectRead:[OBJECT_SIZE>VLOT] NSMTIO: Additional Info: VLOT=797445 Object# = 78376, Object_Size = 66666666 blocks SqlId = 7naawntkc57yx, plan_hash_value = 3617692013, Partition# = 0
But on 12.1.0.2 and 12.2.0.1 we can see 2 lines with “NSMTIO: kcbism” with the different types(2 and 3) and different number of blocks, and the direct path read decision was based on second one – from segment header:
NSMTIO: kcbism: islarge 1 next 0 nblks 66666666 type 3, bpid 65535, kcbisdbfc 0 kcbnhl 4096 kcbstt 983 keep_nb 0 kcbnbh 59010 kcbnwp 1 NSMTIO: kcbism: islarge 0 next 0 nblks 4 type 2, bpid 3, kcbisdbfc 0 kcbnhl 4096 kcbstt 983 keep_nb 0 kcbnbh 59010 kcbnwp 1 NSMTIO: qertbFetch:NoDirectRead:[- STT < OBJECT_SIZE < MTT]:Obect's size: 4 (blocks), Threshold: MTT(4917 blocks), _object_statistics: enabled, Sage: enabled, Direct Read for serial qry: enabled(::::::), Ascending SCN table scan: FALSE flashback_table_scan: FALSE, Row Versions Query: FALSE SqlId: 7naawntkc57yx, plan_hash_value: 3617692013, Object#: 302342, Parition#: 0 DW_scan: disabled
NSMTIO: kcbism: islarge 1 next 0 nblks 66666666 type 3, bpid 65535, kcbisdbfc 0 kcbnhl 4096 kcbstt 1214 keep_nb 0 kcbnbh 45026 kcbnwp 1 NSMTIO: kcbism: islarge 0 next 0 nblks 4 type 2, bpid 3, kcbisdbfc 0 kcbnhl 4096 kcbstt 1214 keep_nb 0 kcbnbh 45026 kcbnwp 1 NSMTIO: qertbFetch:NoDirectRead:[- STT < OBJECT_SIZE < MTT]:Obect's size: 4 (blocks), Threshold: MTT(6072 blocks), _object_statistics: enabled, Sage: enabled, Direct Read for serial qry: enabled(:::::::), Ascending SCN table scan: FALSE flashback_table_scan: FALSE, Row Versions Query: FALSE SqlId: 7naawntkc57yx, plan_hash_value: 3617692013, Object#: 174411, Parition#: 0 DW_scan: disabled
And similar example, but for IFFS(index fast full scan):
nb: I set the number of index blocks using dbms_stats to 33333000 and hinted the query with 77777700
drop table t2 purge;
ALTER SESSION SET optimizer_dynamic_sampling = 0;
ALTER SESSION SET "_optimizer_use_feedback" = FALSE;
ALTER SESSION SET optimizer_adaptive_features = FALSE;
ALTER SESSION SET optimizer_adaptive_plans=FALSE;
create table t2(x) as select level from dual connect by level<=1000;
create index t2_ix on t2(1,x,rpad(x,100));
begin
dbms_stats.gather_table_stats('','T2',cascade => true);
dbms_stats.set_table_stats(user,'T2' ,numblks => 33333333);
dbms_stats.set_index_stats(user,'T2_IX',numlblks => 33333000);
end;
/
col value new_val oldval noprint;
select value from v$statname n, v$mystat s
where n.statistic#=s.statistic# and n.name ='physical reads direct';
alter session set tracefile_identifier='index_stats2';
alter session set events 'trace[nsmtio] disk highest';
select/*+ index_stats(t2, t2_ix, scale, blocks=7777700)
index_ffs(t2 t2_ix)
dynamic_sampling(0)
*/
count(*) cnt2
from t2;
select value-&oldval directreads, value from v$statname n, v$mystat s
where n.statistic#=s.statistic# and n.name ='physical reads direct';
disc;
You can see that on 11.2.0.4 oracle gets number of blocks from the hint (7777700)
NSMTIO: qerixFetchFastFullScan:DirectRead[OBJECT_SIZE > VLOT]: NSMTIO: AdditionalInfo: Object_size: 7777700 (blocks), vlot=797445 SqlId=by2zv0k566hj5, plan_hash_value=3419274230,Object#=78375
From the first line we can see that kcbism takes the hinted number of blocks, but later kcbivlo rewrites it with the number from segment header:
NSMTIO: kcbism: islarge 1 next 0 nblks 7777700 type 3, bpid 65535, kcbisdbfc 0 kcbnhl 4096 kcbstt 983 keep_nb 0 kcbnbh 59010 kcbnwp 1 NSMTIO: kcbivlo: nblks 22 vlot 500 pnb 49175 kcbisdbfc 0 is_large 0 NSMTIO: qerixFetchFastFullScan:[MTT < OBJECT_SIZE < VLOT]:NSMTIO: AdditionalInfo: Object_size: 22 (blocks), vlot=245875 SqlId=by2zv0k566hj5, plan_hash_value=3419274230,Object#=302347
NSMTIO: kcbism: islarge 1 next 0 nblks 7777700 type 3, bpid 65535, kcbisdbfc 0 kcbnhl 4096 kcbstt 1214 keep_nb 0 kcbnbh 45026 kcbnwp 1 NSMTIO: kcbivlo: nblks 22 vlot 500 pnb 60729 kcbisdbfc 0 is_large 0 NSMTIO: qerixFetchFastFullScan:[MTT < OBJECT_SIZE < VLOT]:NSMTIO: AdditionalInfo: Object_size: 22 (blocks), vlot=303645 SqlId=by2zv0k566hj5, plan_hash_value=3419274230,Object#=174409
So we have 2 options to force direct reads:
1. to execute alter session set “_serial_direct_read”=’ALWAYS’;
2. or to force parallel plan to get parallel direct path reads (we can do it with even with dop=1)