SQL Utility

--to check the pending sessions.
select * from v$session s, v$sqlstats t where s.STATUS = 'ACTIVE' and s.SQL_ID = t.SQL_ID and s.MACHINE='';

--to check 2 phase committed pending sessions.
select * from dba_2pc_pending;

-- for checking the sql run time cost
select t.SQL_FULLTEXT, t.SQL_TEXT, t.EXECUTIONS,t.ELAPSED_TIME/1000000/(t.EXECUTIONS+1),t.LAST_LOAD_TIME
from v$sql t where t.LAST_ACTIVE_TIME > '19-Jan-2010'
order by t.ELAPSED_TIME/1000000/(t.EXECUTIONS+1) desc;