extensive_sql

col module for a20
select *
from (
select inst_id, substr(module,1,25) Module ,sql_id, count(*), rank() over ( partition by inst_id order by count(*) desc) rnk, row_number() over ( partition by inst_id order by count(*) desc) rn, (round(count(*) /sum(count(*)) over (), 4)) * 100 pctload
from gv$active_session_history
where sample_time > sysdate -15/24/60 and session_type <> 'BACKGROUND' group by inst_id,sql_id,module order by inst_id,count(*) desc )
--where pctload     > 0.4
where rnk < 6 and rn < 6
--and inst_id in ( 10,9,12)
order by inst_id,pctload desc