########################################################################################################### ## This file is part of http://www.gplivna.eu/papers/v$session_longops.htm ########################################################################################################### ########################################################################################################### ## Initializing environment ########################################################################################################### SQL> alter session set optimizer_mode = all_rows; Session altered. Elapsed: 00:00:00.00 SQL> alter session set workarea_size_policy = manual; Session altered. Elapsed: 00:00:00.00 SQL> drop table big; Table dropped. Elapsed: 00:00:00.02 ########################################################################################################### ## Creating example table ########################################################################################################### SQL> create table big as select * from dba_source where rownum <= 50000; Table created. Elapsed: 00:00:08.04 ########################################################################################################### ## Now doing the same select with different hash_area_size ## See how much time it takes and how many physical reads it makes ########################################################################################################### SQL> alter session set hash_area_size = 100000; Session altered. Elapsed: 00:00:00.00 SQL> select count(*) from ( 2 select /*+ use_hash(a, b) */ * from big a, big b 3 where a.owner = b.owner 4 and a.name = b.name 5 and a.type = b.type 6 and a.line = b.line 7 and a.text = b.text 8 ) 9 / COUNT(*) ---------- 50000 Elapsed: 00:01:05.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4386572 Card=1 Byt es=4114) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=4386572 Card=35376 Bytes=145536864) 3 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) 4 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) Statistics ---------------------------------------------------------- 146 recursive calls 0 db block gets 2572 consistent gets 34862 physical reads 0 redo size 412 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1 rows processed SQL> alter session set hash_area_size = 90000; Session altered. Elapsed: 00:00:00.00 SQL> select count(*) from ( 2 select /*+ use_hash(a, b) */ * from big a, big b 3 where a.owner = b.owner 4 and a.name = b.name 5 and a.type = b.type 6 and a.line = b.line 7 and a.text = b.text 8 ) 9 / COUNT(*) ---------- 50000 Elapsed: 00:01:05.06 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5175835 Card=1 Byt es=4114) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=5175835 Card=35376 Bytes=145536864) 3 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) 4 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) Statistics ---------------------------------------------------------- 23 recursive calls 2 db block gets 2560 consistent gets 40052 physical reads 0 redo size 412 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1 rows processed SQL> alter session set hash_area_size = 80000; Session altered. Elapsed: 00:00:00.00 SQL> select count(*) from ( 2 select /*+ use_hash(a, b) */ * from big a, big b 3 where a.owner = b.owner 4 and a.name = b.name 5 and a.type = b.type 6 and a.line = b.line 7 and a.text = b.text 8 ) 9 / COUNT(*) ---------- 50000 Elapsed: 00:01:22.02 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5688856 Card=1 Byt es=4114) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=5688856 Card=35376 Bytes=145536864) 3 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) 4 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) Statistics ---------------------------------------------------------- 23 recursive calls 0 db block gets 2560 consistent gets 49732 physical reads 0 redo size 412 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1 rows processed SQL> alter session set hash_area_size = 70000; Session altered. Elapsed: 00:00:00.00 SQL> select count(*) from ( 2 select /*+ use_hash(a, b) */ * from big a, big b 3 where a.owner = b.owner 4 and a.name = b.name 5 and a.type = b.type 6 and a.line = b.line 7 and a.text = b.text 8 ) 9 / COUNT(*) ---------- 50000 Elapsed: 00:01:33.05 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6315881 Card=1 Byt es=4114) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=6315881 Card=35376 Bytes=145536864) 3 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) 4 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) Statistics ---------------------------------------------------------- 23 recursive calls 0 db block gets 2560 consistent gets 65798 physical reads 0 redo size 412 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1 rows processed SQL> alter session set hash_area_size = 60000; Session altered. Elapsed: 00:00:00.00 SQL> select count(*) from ( 2 select /*+ use_hash(a, b) */ * from big a, big b 3 where a.owner = b.owner 4 and a.name = b.name 5 and a.type = b.type 6 and a.line = b.line 7 and a.text = b.text 8 ) 9 / COUNT(*) ---------- 50000 Elapsed: 00:08:38.05 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=14153696 Card=1 By tes=4114) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=14153696 Card=35376 Bytes=145536864) 3 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) 4 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) Statistics ---------------------------------------------------------- 23 recursive calls 0 db block gets 2560 consistent gets 388112 physical reads 0 redo size 412 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1 rows processed SQL> alter session set hash_area_size = 50000; Session altered. Elapsed: 00:00:00.00 SQL> select count(*) from ( 2 select /*+ use_hash(a, b) */ * from big a, big b 3 where a.owner = b.owner 4 and a.name = b.name 5 and a.type = b.type 6 and a.line = b.line 7 and a.text = b.text 8 ) 9 / COUNT(*) ---------- 50000 Elapsed: 00:05:56.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16169134 Card=1 By tes=4114) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=16169134 Card=35376 Bytes=145536864) 3 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) 4 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) Statistics ---------------------------------------------------------- 23 recursive calls 0 db block gets 2560 consistent gets 388112 physical reads 0 redo size 412 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1 rows processed SQL> alter session set hash_area_size = 100000; Session altered. Elapsed: 00:00:00.00 SQL> select count(*) from ( 2 select /*+ use_hash(a, b) */ * from big a, big b 3 where a.owner = b.owner 4 and a.name = b.name 5 and a.type = b.type 6 and a.line = b.line 7 and a.text = b.text 8 ) 9 / COUNT(*) ---------- 50000 Elapsed: 00:00:54.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4386572 Card=1 Byt es=4114) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=4386572 Card=35376 Bytes=145536864) 3 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) 4 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) Statistics ---------------------------------------------------------- 21 recursive calls 0 db block gets 2560 consistent gets 33666 physical reads 0 redo size 412 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1 rows processed SQL> alter session set hash_area_size = 110000; Session altered. Elapsed: 00:00:00.00 SQL> select count(*) from ( 2 select /*+ use_hash(a, b) */ * from big a, big b 3 where a.owner = b.owner 4 and a.name = b.name 5 and a.type = b.type 6 and a.line = b.line 7 and a.text = b.text 8 ) 9 / COUNT(*) ---------- 50000 Elapsed: 00:00:16.03 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2061067 Card=1 Byt es=4114) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=2061067 Card=35376 Bytes=145536864) 3 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) 4 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) Statistics ---------------------------------------------------------- 23 recursive calls 0 db block gets 2560 consistent gets 15262 physical reads 0 redo size 412 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1 rows processed SQL> alter session set hash_area_size = 120000; Session altered. Elapsed: 00:00:00.00 SQL> select count(*) from ( 2 select /*+ use_hash(a, b) */ * from big a, big b 3 where a.owner = b.owner 4 and a.name = b.name 5 and a.type = b.type 6 and a.line = b.line 7 and a.text = b.text 8 ) 9 / COUNT(*) ---------- 50000 Elapsed: 00:00:19.05 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1926704 Card=1 Byt es=4114) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=1926704 Card=35376 Bytes=145536864) 3 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) 4 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) Statistics ---------------------------------------------------------- 23 recursive calls 0 db block gets 2560 consistent gets 13376 physical reads 0 redo size 412 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1 rows processed SQL> alter session set hash_area_size = 150000; Session altered. Elapsed: 00:00:00.00 SQL> select count(*) from ( 2 select /*+ use_hash(a, b) */ * from big a, big b 3 where a.owner = b.owner 4 and a.name = b.name 5 and a.type = b.type 6 and a.line = b.line 7 and a.text = b.text 8 ) 9 / COUNT(*) ---------- 50000 Elapsed: 00:00:16.05 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1530688 Card=1 Byt es=4114) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=1530688 Card=35376 Bytes=145536864) 3 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) 4 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) Statistics ---------------------------------------------------------- 23 recursive calls 0 db block gets 2560 consistent gets 9288 physical reads 0 redo size 412 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1 rows processed SQL> alter session set hash_area_size = 300000; Session altered. Elapsed: 00:00:00.00 SQL> select count(*) from ( 2 select /*+ use_hash(a, b) */ * from big a, big b 3 where a.owner = b.owner 4 and a.name = b.name 5 and a.type = b.type 6 and a.line = b.line 7 and a.text = b.text 8 ) 9 / COUNT(*) ---------- 50000 Elapsed: 00:00:03.04 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=426928 Card=1 Byte s=4114) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=426928 Card=35376 Bytes=145536864) 3 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) 4 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) Statistics ---------------------------------------------------------- 23 recursive calls 0 db block gets 2560 consistent gets 2722 physical reads 0 redo size 412 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1 rows processed SQL> alter session set hash_area_size = 500000; Session altered. Elapsed: 00:00:00.00 SQL> select count(*) from ( 2 select /*+ use_hash(a, b) */ * from big a, big b 3 where a.owner = b.owner 4 and a.name = b.name 5 and a.type = b.type 6 and a.line = b.line 7 and a.text = b.text 8 ) 9 / COUNT(*) ---------- 50000 Elapsed: 00:00:04.01 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=159404 Card=1 Byte s=4114) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=159404 Card=35376 Bytes=145536864) 3 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) 4 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) Statistics ---------------------------------------------------------- 23 recursive calls 0 db block gets 2560 consistent gets 1786 physical reads 0 redo size 412 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1 rows processed SQL> alter session set hash_area_size = 1000000; Session altered. Elapsed: 00:00:00.00 SQL> select count(*) from ( 2 select /*+ use_hash(a, b) */ * from big a, big b 3 where a.owner = b.owner 4 and a.name = b.name 5 and a.type = b.type 6 and a.line = b.line 7 and a.text = b.text 8 ) 9 / COUNT(*) ---------- 50000 Elapsed: 00:00:04.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=74304 Card=1 Bytes =4114) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=74304 Card=35376 Bytes=145536864) 3 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) 4 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) Statistics ---------------------------------------------------------- 23 recursive calls 0 db block gets 2560 consistent gets 1718 physical reads 0 redo size 412 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1 rows processed SQL> alter session set hash_area_size = 4000000; Session altered. Elapsed: 00:00:00.00 SQL> select count(*) from ( 2 select /*+ use_hash(a, b) */ * from big a, big b 3 where a.owner = b.owner 4 and a.name = b.name 5 and a.type = b.type 6 and a.line = b.line 7 and a.text = b.text 8 ) 9 / COUNT(*) ---------- 50000 Elapsed: 00:00:01.03 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=22368 Card=1 Bytes =4114) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=22368 Card=35376 Bytes=145536864) 3 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) 4 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) Statistics ---------------------------------------------------------- 21 recursive calls 0 db block gets 2560 consistent gets 1472 physical reads 0 redo size 412 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1 rows processed SQL> alter session set hash_area_size = 8000000; Session altered. Elapsed: 00:00:00.00 SQL> select count(*) from ( 2 select /*+ use_hash(a, b) */ * from big a, big b 3 where a.owner = b.owner 4 and a.name = b.name 5 and a.type = b.type 6 and a.line = b.line 7 and a.text = b.text 8 ) 9 / COUNT(*) ---------- 50000 Elapsed: 00:00:00.03 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=12534 Card=1 Bytes =4114) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=12534 Card=35376 Bytes=145536864) 3 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) 4 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) Statistics ---------------------------------------------------------- 17 recursive calls 0 db block gets 2560 consistent gets 936 physical reads 0 redo size 412 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1 rows processed SQL> alter session set hash_area_size = 16000000; Session altered. Elapsed: 00:00:00.00 SQL> select count(*) from ( 2 select /*+ use_hash(a, b) */ * from big a, big b 3 where a.owner = b.owner 4 and a.name = b.name 5 and a.type = b.type 6 and a.line = b.line 7 and a.text = b.text 8 ) 9 / COUNT(*) ---------- 50000 Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=10641 Card=1 Bytes =4114) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=10641 Card=35376 Bytes=145536864) 3 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) 4 2 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=273 Card=59 471 Bytes=122331847) Statistics ---------------------------------------------------------- 9 recursive calls 0 db block gets 2560 consistent gets 0 physical reads 0 redo size 412 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1 rows processed ########################################################################################################### ## Overview of v$session_longops entries ########################################################################################################### SQL> ed Wrote file afiedt.buf 1 select * from ( 2 select opname, target, sofar, totalwork, units, elapsed_seconds, message 3 from v$session_longops order by start_time desc) 4* where rownum <=10 SQL> / OPNAME ---------------------------------------------------------------- TARGET SOFAR ---------------------------------------------------------------- ---------- TOTALWORK UNITS ELAPSED_SECONDS ---------- -------------------------------- --------------- MESSAGE -------------------------------------------------------------------------------- Hash Join 9288 9288 Blocks 15 Hash Join: : 9288 out of 9288 Blocks done Hash Join 13376 13376 Blocks 18 Hash Join: : 13376 out of 13376 Blocks done Hash Join 15262 15262 Blocks 15 Hash Join: : 15262 out of 15262 Blocks done Hash Join 33666 33666 Blocks 54 Hash Join: : 33666 out of 33666 Blocks done Hash Join 388112 388112 Blocks 354 Hash Join: : 388112 out of 388112 Blocks done Hash Join 388112 388112 Blocks 516 Hash Join: : 388112 out of 388112 Blocks done Hash Join 65798 65798 Blocks 93 Hash Join: : 65798 out of 65798 Blocks done Hash Join 49732 49732 Blocks 81 Hash Join: : 49732 out of 49732 Blocks done Hash Join 40052 40052 Blocks 65 Hash Join: : 40052 out of 40052 Blocks done Hash Join 33666 33666 Blocks 60 Hash Join: : 33666 out of 33666 Blocks done 10 rows selected. Elapsed: 00:00:00.02