Oracle SQL
  • LICENSE

Controlling “direct path reads” decision with INDEX_STATS/table_stats

Posted on March 7, 2013 by Sayan Malakshinov Posted in adaptive serial direct path reads, CBO, oracle 3 Comments

Since 11.2.0.2 direct path read decision on full scans(FTS/IFFS) can be based on the statistics.

And if my test is correct, it appears that we can control this behavior on query level with changing number of blocks through index_stats/table_stats outlines:

UPD: I did a little change of the test case for avoiding impact of hard parse on main test.

Test case

drop table xt_iffs purge;
spool &_spools/iffs_test.sql;
@param_ _direct_read_decision_statistics_driven
@param_ _small_table_threshold
SELECT name,block_size,buffers FROM v$buffer_pool;

create table xt_iffs as select level a,mod(level,100) b,lpad(1,100,1) c from dual connect by level<=1e5;
create index ix_iffs on xt_iffs(a);
exec dbms_stats.set_table_stats('','XT_IFFS',numrows => 1e6,numblks => 5e5,avgrlen => 800);
exec dbms_stats.set_index_stats('','IX_IFFS',numrows => 1e6,numlblks => 1e4);
set termout off echo off feed off timing off;
------------------- 1 run for avoiding hard parse in main test
alter system flush buffer_cache;
select/*+ index_ffs(t IX_IFFS) */ sum(a) from xt_iffs;

alter system flush buffer_cache;
select/*+ 
  BEGIN_OUTLINE_DATA
    INDEX_FFS(T IX_IFFS) 
    INDEX_STATS("XTENDER"."XT_IFFS", "IX_IFFS", scale, blocks=5, rows=10) 
  END_OUTLINE_DATA
  */ 
  sum(a) 
from xt_iffs t;

alter system flush buffer_cache;
select/*+ 
  BEGIN_OUTLINE_DATA
    INDEX_FFS(T IX_IFFS) 
    INDEX_STATS("XTENDER"."XT_IFFS", "IX_IFFS", scale, blocks=1, rows=50)  
  END_OUTLINE_DATA
  */ 
  sum(a) 
from xt_iffs t;
------------------- Main test with statistics: ----------------------------------------------
exec xt_runstats.init(p_latches => false);
alter system flush buffer_cache;
select/*+ index_ffs(t IX_IFFS) */ sum(a) from xt_iffs;
exec xt_runstats.snap;

alter system flush buffer_cache;
select/*+ 
  BEGIN_OUTLINE_DATA
    INDEX_FFS(T IX_IFFS) 
    INDEX_STATS("XTENDER"."XT_IFFS", "IX_IFFS", scale, blocks=5, rows=10) 
  END_OUTLINE_DATA
  */ 
  sum(a) 
from xt_iffs t;
exec xt_runstats.snap;

alter system flush buffer_cache;
select/*+ 
  BEGIN_OUTLINE_DATA
    INDEX_FFS(T IX_IFFS) 
    INDEX_STATS("XTENDER"."XT_IFFS", "IX_IFFS", scale, blocks=1, rows=50)  
  END_OUTLINE_DATA
  */ 
  sum(a) 
from xt_iffs t;
exec xt_runstats.snap;

set termout on echo on serverout on;

exec xt_runstats.print(p_stats_mask => 'reads|direct',p_sta_diff_pct => 0);
spool off;

[collapse]

Result


NAME                                     VALUE        DEFLT        TYPE         DESCRIPTION
---------------------------------------- ------------ ------------ ------------ ------------------------------------------------------------
_direct_read_decision_statistics_driven  TRUE         TRUE         boolean      enable direct read decision based on optimizer statistics

Elapsed: 00:00:00.20

NAME                                     VALUE        DEFLT        TYPE         DESCRIPTION
---------------------------------------- ------------ ------------ ------------ ------------------------------------------------------------
_small_table_threshold                   166          TRUE         number       lower threshold level of table size for direct reads

Elapsed: 00:00:00.21

NAME                                     BLOCK_SIZE    BUFFERS
---------------------------------------- ---------- ----------
DEFAULT                                        8192        491

Elapsed: 00:00:00.19

Table created.

Elapsed: 00:00:00.29

Index created.

Elapsed: 00:00:00.46

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.15

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.13
SQL>
SQL> exec xt_runstats.print(p_stats_mask => 'reads|direct',p_sta_diff_pct => 0);
################     Results:      ##################
Run #  01 ran in 48 hsecs
Run #  02 ran in 47 hsecs
Run #  03 ran in 48 hsecs
############################################################################################
Statistics                               | Run # 1        | Run # 2        | Run # 3
############################################################################################
session logical reads................... |            229 |            227 |            229
session logical reads in local numa grou |              0 |              0 |              0
session logical reads in remote numa gro |              0 |              0 |              0
db block gets direct.................... |              0 |              0 |              0
consistent gets direct.................. |              0 |            223 |              0
physical reads.......................... |            224 |            224 |            224
physical reads cache.................... |            224 |              1 |            224
physical reads direct................... |              0 |            223 |              0
physical writes direct.................. |              0 |              0 |              0
physical reads direct temporary tablespa |              0 |              0 |              0
physical writes direct temporary tablesp |              0 |              0 |              0
recovery array reads.................... |              0 |              0 |              0
physical reads cache prefetch........... |            203 |              0 |            203
physical reads prefetch warmup.......... |              0 |              0 |              0
physical reads retry corrupt............ |              0 |              0 |              0
physical reads direct (lob)............. |              0 |              0 |              0
physical writes direct (lob)............ |              0 |              0 |              0
cold recycle reads...................... |              0 |              0 |              0
physical reads for flashback new........ |              0 |              0 |              0
flashback direct read optimizations for  |              0 |              0 |              0
redo size for direct writes............. |              0 |              0 |              0
cell physical IO bytes sent directly to  |              0 |              0 |              0
transaction tables consistent reads - un |              0 |              0 |              0
data blocks consistent reads - undo reco |              0 |              0 |              0
table scans (direct read)............... |              0 |              0 |              0
lob reads............................... |              0 |              0 |              0
index fast full scans (direct read)..... |              0 |              1 |              0
securefile direct read bytes............ |              0 |              0 |              0
securefile direct write bytes........... |              0 |              0 |              0
securefile direct read ops.............. |              0 |              0 |              0
securefile direct write ops............. |              0 |              0 |              0
############################################################################################

[collapse]
direct path reads
« Workaround for deadlock with select for update order by on 11.2.0.2-11.2.0.3
How works optimization of loops in PL/SQL in 11g: Part 1. Deterministic functions »
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • CBO and Partial indexing
  • Slow index access “COL=:N” where :N is NULL
  • Where does the commit or rollback happen in PL/SQL code?
  • :1 and SP2-0553: Illegal variable name “1”.
  • ORA exceptions that can’t be caught by exception handler

Recent Comments

  • Oracle SGA 값을 증가 시킬 때 발생 장애 원인 – DBA의 정석 on Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)
  • Oracle SQL | Oracle diagnostic events — Cheat sheet on Where does the commit or rollback happen in PL/SQL code?
  • Functions & Subqueries | Oracle Scratchpad on Deterministic function vs scalar subquery caching. Part 3
  • Materialized views state turns into compilation_error after refresh - kranar.top - Answering users questions... on Friday prank: select from join join join
  • Exadata Catalogue | Oracle Scratchpad on When bloggers get it wrong – part 1
  • Exadata Catalogue | Oracle Scratchpad on Serial Scans failing to offload
  • lateral join – decorrelation gone wrong – svenweller on Lateral view decorrelation(VW_DCL) causes wrong results with rownum
  • 255 column catalogue | Oracle Scratchpad on Intra-block row chaining optimization in 12.2
  • 255 column catalogue | Oracle Scratchpad on row pieces, 255 columns, intra-block row chaining in details
  • opt_estimate catalogue | Oracle Scratchpad on Correct syntax for the table_stats hint

Blogroll

  • Alex Fatkulin
  • Alexander Anokhin
  • Andrey Nikolaev
  • Charles Hooper
  • Christian Antognini
  • Coskan Gundogar
  • David Fitzjarrell
  • Igor Usoltsev
  • Jonathan Lewis
  • Karl Arao
  • Mark Bobak
  • Martin Bach
  • Martin Berger
  • Neil Chandler
  • Randolf Geist
  • Richard Foote
  • Riyaj Shamsudeen
  • Tanel Poder
  • Timur Akhmadeev
  • Valentin Nikotin

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
©Sayan Malakshinov. Oracle SQL