What Wait Events Are Sessions Waiting On

Script shows what wait events are sessions waiting on :

set linesize 120

col username format a10

col event format a30

select sid, serial#,username, event,

seconds_in_wait, wait_time

from v$session where state = 'WAITING'

and wait_class != 'Idle'

order by event;

List status of all submitted DBMS jobs

Lists status of all submitted DBMS jobs:

set pagesize 100

set linesize 120

ttitle -

  center  'Submitted DBMS Jobs' skip 2

col job  format 99999  heading 'job#'

col subu format a10  heading 'Submitter'     trunc

col lsd  format a5   heading 'Last|Ok|Date'

col lst  format a5   heading 'Last|Ok|Time'

col nrd  format a5   heading 'Next|Run|Date'

col nrt  format a5   heading 'Next|Run|Time'

col fail format 999  heading 'Errs'

col ok   format a2   heading 'Ok'

select

  job,

  log_user                   subu,

  what                       proc,

  to_char(last_date,'MM/DD') lsd,

  substr(last_sec,1,5)       lst,

  to_char(next_date,'MM/DD') nrd,

  substr(next_sec,1,5)       nrt,

  failures                   fail,

  decode(broken,'Y','N','Y') ok

from

  sys.dba_jobs;

Segments with highest I/O activity

This script will list the top 10 segments in the database that have the most number of

physical reads against them. 

Script can also be changed to query on 'physical writes' instead.

set pagesize 200

setlinesize 120

col segment_name format a20

col owner format a10

select segment_name,object_type,total_physical_reads

from ( select owner||'.'||object_name as segment_name,object_type,

value as total_physical_reads

from v$segment_statistics

where statistic_name in ('physical reads')

order by total_physical_reads desc)

where rownum <=10;

Top SQL (Buffer Gets)

This script will list the top 5 SQL statements sorted by the most number of buffer gets or logical reads

set serverout on size 1000000

declare

top5 number;

text1 varchar2(4000);

x number;

len1 number;

Cursor c1 is

select buffer_gets,substr(sql_text,1,4000)

from v$sqlarea

order by buffer_gets desc;

begin

dbms_output.put_line('Reads'||'  '||'                          Text');

dbms_output.put_line ('-----'||'  '||'---------------------------------------------------');

dbms_output.put_line('      ');

open c1;

for i in 1 .. 5 loop

fetch c1 into top5, text1;

dbms_output.put_line(rpad(to_char(top5),9)|| ' '||substr(text1,1,66));

len1 :=length(text1);

x := 66;

while len1 > x-1 loop

dbms_output.put_line('"         '||substr(text1,x,64));

x := x+64;

end loop;

end loop;

end;

/

Top SQL (Physical Reads)

This script will list the top 5 SQL statements sorted by the most number of physical reads

set serverout on size 1000000
set feedback off
declare

top5 number;

text1 varchar2(4000);

x number;

len1 number;

Cursor c1 is

select disk_reads,substr(sql_text,1,4000)

from v$sqlarea

order by disk_reads desc;

begin

dbms_output.put_line('Reads'||'  '||'                          Text');

dbms_output.put_line ('-----'||'  '||'----------------------------------------------------');

dbms_output.put_line('      ');

open c1;

for i in 1 .. 5 loop

fetch c1 into top5, text1;

dbms_output.put_line(rpad(to_char(top5),9)|| ' '||substr(text1,1,66));

len1 :=length(text1);

x := 66;

while len1 > x-1 loop

dbms_output.put_line('"         '||substr(text1,x,64));

x := x+64;

end loop;

end loop;

end;

/