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:
[sourcecode language=”sql”]
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’;
[/sourcecode]
You can see that our hint successfully changed number of blocks and forced direct path reads on 11.2.0.4:
[sourcecode language=”sql” highlight=”3″]
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
[/sourcecode]
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:
[sourcecode language=”sql” highlight=”1,2″]
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
[/sourcecode]
[sourcecode language=”sql” highlight=”1,2″]
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
[/sourcecode]
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
[sourcecode language=”sql”]
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;
[/sourcecode]
You can see that on 11.2.0.4 oracle gets number of blocks from the hint (7777700)
[sourcecode language=”sql” highlight=”2″]
NSMTIO: qerixFetchFastFullScan:DirectRead[OBJECT_SIZE > VLOT]:
NSMTIO: AdditionalInfo: Object_size: 7777700 (blocks), vlot=797445
SqlId=by2zv0k566hj5, plan_hash_value=3419274230,Object#=78375
[/sourcecode]
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:
[sourcecode language=”sql” highlight=”2″]
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
[/sourcecode]
[sourcecode language=”sql” highlight=”2″]
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
[/sourcecode]
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)