Latch Contention (top 5 latches)

This script will display the top 5 latches with the most sleeps.
Script can be changed to even sort the display on misses instead.

set linesize 120

col name format a30

select * from 

 (select name, gets,misses,  sleeps

 from   v$latch

 order by sleeps desc)

 where rownum < 6;

Script – Sessions with high physical reads

-- Very handy script

set linesize 120

col os_user format a10

col username format a15

col pid format 9999999999

PROMPT SESSIONS SORTED BY PHYSICAL READS

PROMPT

select

  OSUSER os_user,username,

    PROCESS pid,

    ses.SID sid,

    SERIAL#,

    PHYSICAL_READS,

     BLOCK_CHANGES

 from       v$session ses,

   v$sess_io sio

  where      ses.SID = sio.SID

and username is not null

and status='ACTIVE'

 order      by PHYSICAL_READS;

Top 5 Queries for past week based on ADDM recommendations

col SQL_ID form a16

col Benefit form 9999999999999

select * from (

select b.ATTR1 as SQL_ID, max(a.BENEFIT) as "Benefit" 

from DBA_ADVISOR_RECOMMENDATIONS a, DBA_ADVISOR_OBJECTS b 

where a.REC_ID = b.OBJECT_ID

and a.TASK_ID = b.TASK_ID

and a.TASK_ID in (select distinct b.task_id

from dba_hist_snapshot a, dba_advisor_tasks b, dba_advisor_log l

where a.begin_interval_time > sysdate - 7 

and  a.dbid = (select dbid from v$database) 

and a.INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance) 

and to_char(a.begin_interval_time, 'yyyymmddHH24') = to_char(b.created, 'yyyymmddHH24') 

and b.advisor_name = 'ADDM' 

and b.task_id = l.task_id 

and l.status = 'COMPLETED') 

and length(b.ATTR4) > 1 group by b.ATTR1

order by max(a.BENEFIT) desc) where rownum < 6;

Analyse a particular SQL ID and see the trends for the past day

select

 s.snap_id,

 to_char(s.begin_interval_time,'HH24:MI') c1,

 sql.executions_delta c2,

 sql.buffer_gets_delta c3,

 sql.disk_reads_delta c4,

 sql.iowait_delta c5,

sql.cpu_time_delta c6,

 sql.elapsed_time_delta c7

 from

 dba_hist_sqlstat sql,

 dba_hist_snapshot s

 where s.snap_id = sql.snap_id

 and s.begin_interval_time > sysdate -1

 and sql.sql_id='&sqlid'

 order by c7

 /

Top CPU consuming queries since past one day

select * from (

select 

        SQL_ID, 

        sum(CPU_TIME_DELTA), 

        sum(DISK_READS_DELTA),

        count(*)

from 

        DBA_HIST_SQLSTAT a, dba_hist_snapshot s

where

 s.snap_id = a.snap_id

 and s.begin_interval_time > sysdate -1

        group by 

        SQL_ID

order by 

        sum(CPU_TIME_DELTA) desc)

where rownum < 10;

SQL with the highest I/O in the past one day

select * from 

(

SELECT /*+LEADING(x h) USE_NL(h)*/ 

       h.sql_id

,      SUM(10) ash_secs

FROM   dba_hist_snapshot x

,      dba_hist_active_sess_history h

WHERE   x.begin_interval_time > sysdate -1

AND    h.SNAP_id = X.SNAP_id

AND    h.dbid = x.dbid

AND    h.instance_number = x.instance_number

AND    h.event in  ('db file sequential read','db file scattered read')

GROUP BY h.sql_id

ORDER BY ash_secs desc )

where rownum < 10;