Oracle Performance Tuning

SQL_ID and CPU Usage
SQL> select * from (
             select sql_id, inst_id,
                sum(decode(vash.session_state, 'ON CPU', 1,0)) as "Number on CPU",
                sum(decode(vash.session_state, 'WAITING', 1, 0)) as "Number Waiting on CPU"
             from gv$active_session_history cash
             where sample_time > sys date -5/(60*24)
             group by sql_id, instead
             order by 3 desc
             ) where rownum <11;

Query top 10 SQL_ID's in the last 10 minutes

select * from
(select NVL(SQL_ID,'NULL') AS SQL_ID,
SUM(1) AS "DBTime IN SECONDES" FROM V$ACTIVE_SESSION_HISTORY
WHERE sample_time > sys date - 10/(24*60)
GROUP BY SQL_ID
ORDER BY 2 DESC)
WHERE ROWNUM < 11;

Wait Events Across Nodes
select * from (
select sql_id, inst_id,
                sum(decode(vash.session_state, 'ON CPU', 1,0)) as "Number on CPU",
                sum(decode(vash.session_state, 'WAITING', 1, 0)) as "Number Waiting on CPU",

                event , count(distinct(session_id||session_serial#)) as "SESSION COUNT"

             from gv$active_session_history cash
             where sample_time > sys date -5/(60*24)
             group by event,sql_id, instead
             order by 3 desc
             ) where rownum <11;

IO Waits by object from ASH

SELECT TW.*, DO.object_name from (
select current_obj#,
ROUND(SUM(CASE WHEN TIME_WAITED >= 1000000 THEN 1
ELSE 1000000 / TIME_WAITED END)) AS "Estimated IO Waits",
SUM(1) AS "ESTIMATED DBTIME"
FROM V$ACTIVE_SESSION_HISTORY
WHERE sample_time > sysdate - 5/(24*60) and TIME_WAITED > 0
and WAIT_CLASS = 'User I/O'
GROUP BY CURRENT_OBJ#
ORDER BY 2 DESC) TW, DBA_OBJECTS DO
WHERE DO.object_id=TW.current_obj#
and ROWNUM < 11;