/* hashopensql.sql
      see all the sids holding the specified hash value open 
	uses buffer gets per exec, disk reads per exec
*/
set verify off
set lines 132
break on report
compute sum of curscnt on report
col bgexec format 99999999.90
col drexec format 99999999.90
col module format a15 trunc
accept trgthash number default 0 prompt 'What is the SQL Hash Value <0> : '
select s.sid, s.module,
	count(*) curscnt,
  	sum(a.executions) execs, 
	sum(a.buffer_gets)/sum(executions) bgexec,
	sum(a.disk_reads)/sum(executions) drexec, 
	sum(sorts) sortcnt ,
	c.sql_text
from v$sqlarea a, v$open_cursor c, v$session s
where c.hash_value = a.hash_value
and s.sid = c.sid
and (c.hash_value = &trgthash and &trgthash != 0)
group by s.sid, s.module, c.sql_text
order by 2,3;
clear breaks
clear computes