Last active
April 22, 2022 06:01
-
-
Save kleontev/c3c22497fed2db0e16cd81b79054c519 to your computer and use it in GitHub Desktop.
ARRAYSIZE has a dramatic impact on LIO figures. reproducible on a simple "select * from t" as well
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
https://gist.github.com/xtender/93f3edb06fea2b86e98ab2a0e0533b53