########################################################################################################### ## This file is part of http://www.gplivna.eu/papers/v$session_longops.htm ########################################################################################################### ########################################################################################################### ## Creating a small table and preparing environment ########################################################################################################## SQL> create table ParodyOfBig as select owner||name||type||line ukrow 2 from dba_source where rownum <= 1300; Table created. Elapsed: 00:00:13.03 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> alter session set hash_area_size = 50000; Session altered. Elapsed: 00:00:00.00 SQL> set autotrace on ########################################################################################################### ## Join is done instantaneously ## And it took 20 physical reads second time (keep that in mind) ########################################################################################################## SQL> select count(*) from ( 2 select /*+ use_hash(a, b) */ a.* 3 from parodyofbig a, parodyofbig b 4 where a.ukrow = b.ukrow); COUNT(*) ---------- 1300 Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=103 Card=1 Bytes=4 52) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=103 Card=899 Bytes=406348) 3 2 TABLE ACCESS (FULL) OF 'PARODYOFBIG' (Cost=3 Card=899 Bytes=203174) 4 2 TABLE ACCESS (FULL) OF 'PARODYOFBIG' (Cost=3 Card=899 Bytes=203174) Statistics ---------------------------------------------------------- 22 recursive calls 0 db block gets 26 consistent gets 28 physical reads 0 redo size 406 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> / COUNT(*) ---------- 1300 Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=103 Card=1 Bytes=4 52) 1 0 SORT (AGGREGATE) 2 1 HASH JOIN (Cost=103 Card=899 Bytes=406348) 3 2 TABLE ACCESS (FULL) OF 'PARODYOFBIG' (Cost=3 Card=899 Bytes=203174) 4 2 TABLE ACCESS (FULL) OF 'PARODYOFBIG' (Cost=3 Card=899 Bytes=203174) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 22 consistent gets 20 physical reads 0 redo size 406 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> set autotrace off SQL> select sid, serial# from v$session where username = 'GINTS'; SID SERIAL# ---------- ---------- 35 1180 Elapsed: 00:00:00.00 SQL> select count(*) from v$session_longops; COUNT(*) ---------- 107 Elapsed: 00:00:00.07 ########################################################################################################### ## To make things run slower just add some brake using dbms_lock.sleep ########################################################################################################## SQL> ed Wrote file afiedt.buf 1 declare 2 cursor c is 3 select /*+ use_hash(a, b) */ a.* 4 from parodyofbig a, parodyofbig b 5 where a.ukrow = b.ukrow; 6 begin 7 for r in c loop 8 dbms_lock.sleep(0.03); 9 end loop; 10* end; SQL> / PL/SQL procedure successfully completed. Elapsed: 00:00:39.05 ########################################################################################################### ## One more hash join in v$session_longops ## And it took 20 blocks - the same amount as previously physical reads by autotrace ########################################################################################################## SQL> select count(*) from v$session_longops; COUNT(*) ---------- 108 Elapsed: 00:00:00.00 SQL> select * from ( 2 select opname, target, sofar, totalwork, units, elapsed_seconds, message 3 from v$session_longops 4 where sid = 35 and serial# = 1180 5 order by start_time desc) 6 where rownum <=1 7 / OPNAME ---------------------------------------------------------------- TARGET SOFAR ---------------------------------------------------------------- ---------- TOTALWORK UNITS ELAPSED_SECONDS ---------- -------------------------------- --------------- MESSAGE -------------------------------------------------------------------------------- Hash Join 20 20 Blocks 15 Hash Join: : 20 out of 20 Blocks done Elapsed: 00:00:00.00