Oracle active waits info  .. 

ash oracle waiting info 

set pagesize 200

select
ash.inst_id,
ash.sql_id ,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "WAIT" ,
sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "IO" ,
sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"
from gv$active_session_history ash,v$event_name en
where SQL_ID is not NULL 
and en.event#=ash.event#
and USER_ID not in ( select USER_ID from dba_users where USERNAME in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) ) 
group by ash.inst_id,ash.sql_id
having sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) !=0
order by 6 desc ;



select * from (
 select inst_id,
 sql_id ,
 sum(decode(session_state,'ON CPU',1,0)) as CPU,
 sum(decode(session_state,'WAITING',1,0)) - sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as WAIT,
 sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as IO,
 sum(decode(session_state,'ON CPU',1,1)) as TOTAL
 from gv$active_session_history
 where 1=1 
 and SQL_ID is not NULL
 and USER_ID not in ( select USER_ID from dba_users where USERNAME in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ) ) 
 and SESSION_TYPE!='BACKGROUND'
 group by inst_id,sql_id
 order by sum(decode(session_state,'ON CPU',1,1)) desc
 )
where rownum <21 ;

 INST_ID SQL_ID CPU WAIT IO TOTAL
---------- ------------- ---------- ---------- ---------- ----------
 1 8gnjy50gxbwnc 0 3 0 3
 1 fj7ft4r6nathc 1 0 0 1
 1 6jxvh6g4t6xfw 0 1 0 1
 1 0fuvy5t4x00xd 0 1 0 1
 1 f58bxv021xat3 0 1 0 1
 1 axwq64tsktbnx 1 0 0 1

6 rows selected.



set long 10000 pagesize 1000
select sql_fulltext from gv$sql where sql_id='&sql_id';


set pages 300 lines 200
-- Shared Pool
select * from table(dbms_xplan.display_cursor('&SQL_ID',null,'ALL'));
--select * from table(dbms_xplan.display_cursor('&SQL_ID'));
--select * from table(dbms_xplan.display_cursor('&SQL_ID',0,'ALLSTATS LAST'));
--select * from table(dbms_xplan.display_cursor('&SQL_ID',0,'TYPICAL OUTLINE'));
--select * from table(dbms_xplan.display_cursor('&SQL_ID',null,'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS'));

-- AWR
select * from table(dbms_xplan.display_awr('&SQL_ID',null,null,'ALL'));
select * from table(dbms_xplan.display_awr('&SQL_ID',null,DBID,'ALL'));
-- select * from table(dbms_xplan.display_awr('&1',null,null,'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS'));




set pages 300 lines 200
col PLAN_TABLE_OUTPUT for a200
select plan_table_output
from gv$sql s, table(dbms_xplan.display_cursor(s.sql_id, s.child_number,'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS')) t
where sql_id='&SQL_ID';