ash2

 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 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