Unknown1

 

WITH sql_class AS
(select sql_id, state, count(*) occur
from
(select sql_id , CASE WHEN session_state = 'ON CPU' THEN 'CPU' WHEN session_state = 'WAITING' AND wait_class IN ('User I/O') THEN 'IO' ELSE 'WAIT' END state
from gv$active_session_history
where session_type IN ( 'FOREGROUND') and sample_time between trunc(sysdate,'MI') - 15/24/60 and trunc(sysdate,'MI') ) group by sql_id, state), ranked_sqls AS
(select SQL_ID, sum(occur) sql_occur , rank () over (order by sum(occur)desc) xrank
from sql_class group by sql_id )

select state, sum(occur)
from sql_class sc, ranked_sqls rs
where rs.sql_id = sc.sql_id group by state

 

 

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