Oracle Wait Events !!!!!

                           Oracle Wait Events !!!!!

######################## v$session:

select username, SQL_ID, SID, event , status from v$session where EVENT='cr request retry'

;

select event, count(*) from v$session group by event order by 2 ;

select sql_id, count(*) from v$session where EVENT='cr request retry' group by sql_id ;

select sum (wait_time+ SECONDS_IN_WAIT) , event from v$session  group by event order by 1 ;

select sum (SECONDS_IN_WAIT) , event from v$session  group by event order by 1 ;

select sum (wait_time) , event from v$session  group by event order by 1 ;

select p1,p2, event , sum(wait_time)  from v$session

group by p1,p2,event

order by sum(wait_time);

SELECT a.sid, c.pid, c.spid, a.username, b.event, b.wait_time, b.seconds_in_wait, b.p1, b.p2, b.p3

FROM v$session a, v$session_wait b, v$process c

WHERE a.sid = b.sid

AND a.paddr = c.addr

AND b.event LIKE 'enq: RO%';

######################## v$session_wait

SELECT         p1 "file#", p2 "block#", p3 "class#" FROM v$session_wait WHERE event = 'read by

other session';

with temp as (select sid,event,WAIT_TIME+SECONDS_IN_WAIT "TIME_WAITED" from v$session_wait order by 3 desc) select * from temp where rownum < 50;

column sid format 990

column seq# format 99990

column wait_time heading 'WTime' format 99990

column event format a30

column p1 format 9999999990

column p2 format 9999999990

column p3 format 9990

select sid,event,seq#,p1,p2,p3,wait_time

from V$session_wait

order by sid

/

######################## v$session_wait_history

--> gives last ten waits for all active sessions

select seq# , event , wait_time , p1,p2,p3

from v$session_wait_history where sid=988

order by seq# ;

###################### v$session_event

select event,count(*) from v$session_event where event='cr request retry' group by event ;

select event, sum( time_waited) from v$session_event group by event ;

select event, total_waits from v$session_event where event = 'enq: TM - contention';;

select event,sum(TOTAL_WAITS) "TOTAL_WAITS" , sum(TOTAL_TIMEOUTS) "TOTAL_TIMEOUTS" ,sum(TIME_WAITED) "TIME_WAITED",avg (AVERAGE_WAIT) "AVERAGE_WAIT" from v$session_event where event is not null and wait_class <>'Idle' group by event order by 4;

###################### v$system_event

with temp as (select EVENT,TOTAL_WAITS,TOTAL_TIMEOUTS,TIME_WAITED,AVERAGE_WAIT from v$system_event where wait_class <> 'Idle' order by 4 desc ) select * from temp where rownum < 50;

select event, total_waits from v$system_event where event = 'enq: TM - contention';;

select event,count(*) from v$system_event where event='cr request retry' group by event ;

select event, sum( time_waited) from v$system_event group by event ;

/* session_events.sql */

column SID            format 9999

column TOTAL_WAITS    format 999999 heading TOT|WAIT

column AVERAGE_WAIT   format 99999  heading AVG|WAIT

column EVENT          format a30

select

   SID,

   EVENT,

   TOTAL_WAITS,

   TIME_WAITED,

   AVERAGE_WAIT,

   MAX_WAIT

from

   V$SESSION_EVENT

where

   SID in ("&sid_list") -- sid list generated from above query

order by

   TOTAL_WAITS;

##################### V$statname

select a.name,sum(b.value)

from V$statname a, v$sesstat b

where a.statistic#=b.statistic#

group by a.name

order by 2;

select a.name,sum(b.value)

from V$statname a, v$sysstat b

where a.statistic#=b.statistic#

group by a.name

order by 2;

############################# v$enqueue_statistics

select eq_name, eq_type, req_reason  from v$enqueue_statistics  3  where eq_type = 'TM';

with temp as (select EQ_NAME,TOTAL_REQ#,TOTAL_WAIT#,SUCC_REQ#,FAILED_REQ# from v$enqueue_statistics order by 3 desc ) select * from temp where rownum <50;

########################### v$event_histogram

Select * from v$event_histogram

select * from  gV$EVENT_HISTOGRAM  ;

####################### v$active_session_history

SELECT sample_time, event, wait_time

FROM gv$active_session_history

WHERE session_id = 147

AND session_serial# = 1715;

select

       o.object_name obj,

       o.object_type otype,

       ash.SQL_ID,

       w.class

from v$active_session_history ash,

     ( select rownum class#, class from v$waitstat ) w,

      all_objects o

where event='buffer busy waits'

   and w.class#(+)=ash.p3

   and o.object_id (+)= ash.CURRENT_OBJ#

Order by sample_time;

select event, count(*) from v$active_session_history  group by event ;

with temp as (select session_id,event,(time_waited+wait_time) "TOTAL_TIME_WAITED" from v$active_session_history where event is not null order by time_waited+wait_time desc)

select * from temp where rownum <50;

select event ,sum(wait_time+time_waited) from v$active_session_history

##where snap_id=8987

and event='cr request retry'

group by event

order by 2 ;

select sum (wait_time+time_waited) , event from v$active_session_history   group by event

order by 1 ;

select sum (wait_time) , event from v$active_session_history   group by event order by 1 ;

select sum (time_waited) , event from v$active_session_history  

where sample_time between sysdate - 15/1440 and sysdate ;

group by event order by 1 ;

############################## dba_hist_active_sess_history

select sql_id ,sum(wait_time+time_waited) from dba_hist_active_sess_history where

snap_id=8987

and event='cr request retry' group by sql_id order by 2 ;

select event ,sum(wait_time+time_waited) from dba_hist_active_sess_history

##where snap_id=8987

and event='cr request retry'

group by event

order by 2 ;

with temp as (select session_id,event,(time_waited+wait_time) "TOTAL_TIME_WAITED" from

dba_hist_active_sess_history where event is not null order by time_waited+wait_time desc)

select * from temp where rownum <50;

####################### session wait on object

SELECT

  A.CURRENT_OBJ#,

  d.OBJECT_NAME,

  d.OBJECT_TYPE,

  A.EVENT,

  SUM(A.WAIT_TIME + A.TIME_WAITED) TOTAL_WAIT_TIME

FROM

  V$ACTIVE_SESSION_HISTORY A,

  DBA_OBJECTS D

#WHERE   A.SAMPLE_TIME BETWEEN SYSDATE – 30/2880 AND SYSDATE

 where A.CURRENT_OBJ# = D.OBJECT_ID

GROUP BY

  A.CURRENT_OBJ#,

  D.OBJECT_NAME,

  D.OBJECT_TYPE,

  A.EVENT

ORDER BY

  TOTAL_WAIT_TIME;

select * from (

SELECT

  A.inst_id,

  A.CURRENT_OBJ#,

  D.OBJECT_NAME,

  D.OBJECT_TYPE,

  d.owner,

  A.EVENT,

  a.sql_id,

  SUM(A.WAIT_TIME + A.TIME_WAITED) TOTAL_WAIT_TIME

FROM

  gV$ACTIVE_SESSION_HISTORY A,

  DBA_OBJECTS D

WHERE

  A.CURRENT_OBJ# = D.OBJECT_ID

GROUP BY

  A.inst_id,

  A.CURRENT_OBJ#,

  D.OBJECT_NAME,

  D.OBJECT_TYPE,

  A.EVENT,

 d.owner,

 a.sql_id

ORDER BY

  TOTAL_WAIT_TIME

)

where rownum<=20

/

###################### object having wait event

col object_name for a25

col statistic_name for a35

col owner for a15

with temp as (select a.owner,a.object_name,b.statistic_name,b.value from v$segstat b,

dba_objects a where a.object_id=b.obj# and b.statistic_name='db block changes' order by 4

desc) select * from temp where rownum < 30;

with temp as (select a.owner,a.object_name,b.statistic_name,b.value from v$segstat b,

dba_objects a where a.object_id=b.obj# and b.statistic_name='segment scans' order by 4

desc) select * from temp where rownum < 30;

with temp as (select a.owner,a.object_name,b.statistic_name,b.value from v$segstat b,

dba_objects a where a.object_id=b.obj# and b.statistic_name='row lock waits' order by 4

desc) select * from temp where rownum < 30;

with temp as (select a.owner,a.object_name,b.statistic_name,b.value from v$segstat b,

dba_objects a where a.object_id=b.obj# and b.statistic_name='buffer busy waits' order by 4

desc) select * from temp where rownum < 30;

with temp as (select a.owner,a.object_name,b.statistic_name,b.value from v$segstat b,

dba_objects a where a.object_id=b.obj# and b.statistic_name='physical reads direct' order

by 4 desc) select * from temp where rownum < 30;

with temp as (select a.owner,a.object_name,b.statistic_name,b.value from v$segstat b,

dba_objects a where a.object_id=b.obj# and b.statistic_name='physical reads' order by 4

desc) select * from temp where rownum < 30;

with temp as (select a.owner,a.object_name,b.statistic_name,b.value from v$segstat b,

dba_objects a where a.object_id=b.obj# and b.statistic_name='physical writes' order by 4

desc) select * from temp where rownum < 30;

with temp as (select a.owner,a.object_name,b.statistic_name,b.value from v$segstat b,

dba_objects a where a.object_id=b.obj# and b.statistic_name='physical writes direct' order

by 4 desc) select * from temp where rownum < 30;

with temp as (select a.owner,a.object_name,b.statistic_name,b.value from v$segstat b,

dba_objects a where a.object_id=b.obj# and b.statistic_name='logical reads' order by 4

desc) select * from temp where rownum < 30;

with temp as (select a.owner,a.object_name,b.statistic_name,b.value from v$segstat b,

dba_objects a where a.object_id=b.obj# and b.statistic_name='ITL waits' order by 4 desc)

select * from temp where rownum < 30;

SELECT object_name, value

FROM V$SEGMENT_STATISTICS

WHERE statistic_name = 'buffer busy waits' AND

value > 20000;

SELECT a.current_obj#, o.object_name, o.object_type, a.event,

SUM(a.wait_time +

a.time_waited) total_wait_time

FROM v$active_session_history a,

dba_objects o

WHERE a.sample_time between sysdate – 30/2880 and sysdate

AND a.current_obj# = o.object_id

GROUP BY a.current_obj#, o.object_name, o.object_type, a.event

ORDER BY total_wait_time;

select p1text, p1, p2text, p2, p3text, p3, a.event

from v$active_session_history a

WHERE a.sample_time between sysdate – 30/2880 and sysdate

AND a.current_obj# = 1938000

####################### session wait on file

while (true)

do

sqlplus -s "/ as sysdba" <<EOF | tee -a pteh.out

set lines 200

set heading off;

set feedback off;

set echo off;

set pages 1000

column event format a30

column file_name format a55

select '=============================================' || to_char(sysdate,'DD-MON-YY

HH24:MI:SS') from dual;

select a.sid, a.event, b.tablespace_name,b.file_name,a.p1, a.p2, a.p3, a.p1raw from

v\$session_wait a, dba_data_files b

where a.p1=b.file_id and  a.sid in (select sid from v\$session where username='TEH')order

by 2;

EOF

sleep 15

done

SELECT * FROM DBA_EXTENTS WHERE FILE_ID=&FILEID AND &BLOCKID BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS

è check buffer busy wait:

column buffer_busy_wait format 999,999,999

select sn.snap_id,

to_char(sn.end_interval_time,'dd Mon HH24:mi:ss') mydate,

avg(new.buffer_busy_wait-old.buffer_busy_wait) buffer_busy_wait

from

dba_hist_buffer_pool_stat old,

dba_hist_buffer_pool_stat new,

dba_hist_snapshot sn

where new.snap_id = sn.snap_id

and new.snap_id = sn.snap_id

and old.snap_id = sn.snap_id-1

having avg(new.buffer_busy_wait-old.buffer_busy_wait) > 100

group by to_char(sn.end_interval_time,'dd Mon HH24:mi:ss'), sn.snap_id;

select

obj.object_type,

obj.object_name,

to_char(sn.end_interval_time,'dd Mon HH24:mi:ss') mydate,

avg(new.buffer_busy_wait-old.buffer_busy_wait) buffer_busy_wait

from

dba_hist_buffer_pool_stat old,

dba_hist_buffer_pool_stat new,

dba_hist_snapshot sn,

dba_hist_active_sess_history ash,

dba_data_files df,

dba_objects obj

where new.snap_id = sn.snap_id

and new.snap_id = sn.snap_id

and old.snap_id = sn.snap_id-1

and df.file_id = ash.current_file#

and obj.object_id = ash.current_obj#

and ash.snap_id = sn.snap_id

and sn.snap_id = 15882

having avg(new.buffer_busy_wait-old.buffer_busy_wait) > 100

group by to_char(sn.end_interval_time,'dd Mon HH24:mi:ss'), obj.object_type,

select BUFFER_BUSY_WAITS_TOTAL,

BUFFER_BUSY_WAITS_DELTA,

obj.object_type,

obj.object_name,

to_char(sn.end_interval_time,'dd Mon HH24:mi:ss') mydate,

avg(new.buffer_busy_wait-old.buffer_busy_wait) buffer_busy_wait

from

dba_hist_buffer_pool_stat old,

dba_hist_buffer_pool_stat new,

dba_hist_snapshot sn,

dba_hist_active_sess_history ash,

dba_data_files df,

dba_objects obj,

DBA_HIST_SEG_STAT dss

where new.snap_id = sn.snap_id

and new.snap_id = sn.snap_id

and old.snap_id = sn.snap_id-1

and df.file_id = ash.current_file#

and obj.object_id = ash.current_obj#

and dss.snap_id = sn.snap_id

and dss.OBJ# = obj.object_id

and ash.snap_id = sn.snap_id

and sn.snap_id = 15882

having avg(new.buffer_busy_wait-old.buffer_busy_wait) > 100

group by

to_char(sn.end_interval_time,'dd Mon HH24:mi:ss'), obj.object_type, obj.object_name, BUFFER_BUSY_WAITS_TOTAL, BUFFER_BUSY_WAITS_DELTA;

è RAC waits :

######### node performance comparision ######

WITH sysstats AS (

SELECT inst_id,

 SUM(CASE WHEN name LIKE 'gc%received'

THEN VALUE END) gc_blocks_recieved,

 SUM(CASE WHEN name = 'session logical reads'

 THEN VALUE END) logical_reads,

SUM(CASE WHEN name = 'physical reads'

THEN VALUE END) physical_reads

 FROM gv$sysstat

 GROUP BY inst_id)

SELECT instance_name, logical_reads, gc_blocks_recieved, physical_reads,

ROUND(physical_reads*100/logical_reads,2) phys_to_logical_pct,

 ROUND(gc_blocks_recieved*100/logical_reads,2) gc_to_logical_pct

 FROM sysstats JOIN gv$instance

USING (inst_id);

#########  rac top wait event ######

 WITH sys_time AS (

SELECT inst_id, SUM(CASE stat_name WHEN 'DB time'

THEN VALUE END) db_time,

SUM(CASE WHEN stat_name IN ('DB CPU', 'background cpu time')

THEN VALUE END) cpu_time

FROM gv$sys_time_model

GROUP BY inst_id )

SELECT instance_name,

ROUND(db_time/1000000,2) db_time_secs,

 ROUND(db_time*100/SUM(db_time) over(),2) db_time_pct,

 ROUND(cpu_time/1000000,2) cpu_time_secs,

 ROUND(cpu_time*100/SUM(cpu_time) over(),2) cpu_time_pct

 FROM sys_time

 JOIN gv$instance USING (inst_id);