Controlling “direct path reads” decision with "table_stats" hint

test-case-out.sql

SQL> select name,block_size,buffers from v$buffer_pool;

NAME                 BLOCK_SIZE    BUFFERS
-------------------- ---------- ----------
DEFAULT                    8192      41118

SQL> col vlot new_value vlot;
SQL> select buffers*5+1 as vlot from v$buffer_pool;

      VLOT
----------
    205591

SQL> create table t_direct as select 1 x from dual;

Table created.

SQL> select name,value from v$statname n,v$sesstat s where s.sid=userenv('sid') and s.statistic#=n.statistic# and name='physical reads direct';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical reads direct                                                     0

SQL> select count(*) from t_direct;

  COUNT(*)
----------
         1

SQL> select name,value from v$statname n,v$sesstat s where s.sid=userenv('sid') and s.statistic#=n.statistic# and name='physical reads direct';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical reads direct                                                     0

SQL> select/*+ table_stats(t_direct set rows=1000000 blocks=&vlot) */ count(*) from t_direct;

  COUNT(*)
----------
         1

SQL> select name,value from v$statname n,v$sesstat s where s.sid=userenv('sid') and s.statistic#=n.statistic# and name='physical reads direct';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical reads direct                                                     1

test-case.sql

@vparam_ _very_large_object_threshold
select name,block_size,buffers from v$buffer_pool;

col vlot new_value vlot;
select buffers*5+1 as vlot from v$buffer_pool;

create table t_direct as select 1 x from dual;

select name,value from v$statname n,v$sesstat s where s.sid=userenv('sid') and s.statistic#=n.statistic# and name='physical reads direct';
select count(*) from t_direct;
select name,value from v$statname n,v$sesstat s where s.sid=userenv('sid') and s.statistic#=n.statistic# and name='physical reads direct';
select/*+ table_stats(t_direct set rows=1000000 blocks=&vlot) */ count(*) from t_direct;
select name,value from v$statname n,v$sesstat s where s.sid=userenv('sid') and s.statistic#=n.statistic# and name='physical reads direct';