ARRAYSIZE has a dramatic impact on LIO figures. reproducible on a simple "select * from t" as well

script_arraysize_lio_impact.sql

spool spool_arraysize_lio_impact.log 

create table t1 (id, fill) as select rownum, lpad('x', 2000, 'y') from dual connect by rownum <= 1e5;

create table t2 as select * from t1;

exec dbms_stats.gather_table_stats(user, 't1');

exec dbms_stats.gather_table_stats(user, 't2');

alter system flush shared_pool;

alter system flush buffer_cache;

set autotrace traceonly statistics;

set arraysize 1

select /*+use_hash(t1) */ t1.*, t2.id from t1 join t2 on t1.id = t2.id;

alter system flush shared_pool;
alter system flush buffer_cache;

set arraysize 5000

select /*+use_hash(t1) */ t1.*, t2.id from t1 join t2 on t1.id = t2.id;

spool_arraysize_lio_impact.log

SQL> create table t1 (id, fill) as select rownum, lpad('x', 2000, 'y') from dual connect by rownum <= 1e5;

Table created.

Elapsed: 00:00:01.80
SQL> create table t2 as select * from t1;

Table created.

Elapsed: 00:00:01.32
SQL>
SQL> exec dbms_stats.gather_table_stats(user, 't1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.40
SQL> exec dbms_stats.gather_table_stats(user, 't2');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.74
SQL>
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.54
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.08
SQL>
SQL> set autotrace traceonly statistics;
SQL>
SQL> set arraysize 1
SQL>
SQL> select /*+use_hash(t1) */ t1.*, t2.id from t1 join t2 on t1.id = t2.id;

100000 rows selected.

Elapsed: 00:00:31.51

Statistics
----------------------------------------------------------
        158  recursive calls
          8  db block gets
     100288  consistent gets
      66709  physical reads
       1064  redo size
    6149287  bytes sent via SQL*Net to client
     550381  bytes received via SQL*Net from client
      50001  SQL*Net roundtrips to/from client
         20  sorts (memory)
          0  sorts (disk)
     100000  rows processed

SQL>
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.42
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.05
SQL>
SQL> set arraysize 5000
SQL>
SQL> select /*+use_hash(t1) */ t1.*, t2.id from t1 join t2 on t1.id = t2.id;

100000 rows selected.

Elapsed: 00:00:01.55

Statistics
----------------------------------------------------------
        127  recursive calls
          0  db block gets
      66929  consistent gets
      66707  physical reads
          0  redo size
    1484048  bytes sent via SQL*Net to client
        601  bytes received via SQL*Net from client
         21  SQL*Net roundtrips to/from client
         18  sorts (memory)
          0  sorts (disk)
     100000  rows processed

https://gist.github.com/xtender/93f3edb06fea2b86e98ab2a0e0533b53