/**********************************************************************
 * File:        sp_gc_waits.sql
 * Type:        SQL*Plus script
 * Author:      Tim Gorman (Evergreen Database Technologies, Inc.)
 * Date:        12-Aug-04
 *
 * Description:
 *      SQL*Plus script to display two reports from the STATSPACK
 *      repository of segment wait information captured by the Oracle9i
 *      (and higher) versions of the product.
 *
 *      The first report displays the 10 segments (tables, indexes, etc)
 *      with the most "global cache waits" for each day.
 *
 *      The second report displays the 3 segments (tables, indexes, etc)
 *      with the most "global cache waits" for each hour of each day.
 *
 *      The report will prompt the user for the number of days of
 *      STATSPACK data to examine.     
 *
 * Modifications:
 *********************************************************************/
set echo off feedback off timing off pagesize 200 lines 130 trimout on trimspool on verify off
col sort0 noprint
col sort1 noprint
col day heading "Day"
col hr heading "Hour"
col instance_number format 90 heading "Inst|Nbr"
col object_type heading "Object|Type"
col owner format a15 heading "Owner"
col object_name format a30 heading "Object|Name"
col gc_waits format 999,999,999,990 heading "GC|Waits"

clear breaks computes
break on day skip 1 on object_type on report

accept V_INSTANCE prompt "Please enter the ORACLE_SID value [wildcards accepted]: "
accept V_NBR_DAYS prompt "How many days of data to examine? "

column instance_stripped new_value V_INSTANCE_STRIPPED noprint
select	replace('&&V_INSTANCE','%','') instance_stripped
from	dual;

spool sp_gc_waits_&&V_INSTANCE_STRIPPED
select	yyyymmdd sort0,
	daily_ranking sort1,
	day,
	instance_number,
	object_type,
	owner,
	object_name,
	gc_waits
from	(select	to_char(ss.snap_time, 'YYYYMMDD') yyyymmdd,
		to_char(ss.snap_time, 'DD-MON') day,
		s.instance_number,
		o.object_type,
		o.owner,
		o.object_name,
		sum(s.gc_waits) gc_waits,
		rank () over (partition by to_char(ss.snap_time, 'YYYYMMDD')
			      order by sum(s.gc_waits) desc) daily_ranking
	 from	(select	dbid,
			instance_number,
			dataobj#,
			obj#,
			snap_id,
			decode(greatest(global_cache_cr_blocks_served+global_cache_cu_blocks_served,
					lag(global_cache_cr_blocks_served+global_cache_cu_blocks_served,1,0)
						over (partition by dbid, instance_number, dataobj#, obj#
						      order by snap_id)),
				(global_cache_cr_blocks_served+global_cache_cu_blocks_served),
				(global_cache_cr_blocks_served+global_cache_cu_blocks_served) -
				lag(global_cache_cr_blocks_served+global_cache_cu_blocks_served,1,0)
					over (partition by dbid, instance_number, dataobj#, obj#
					      order by snap_id),
				(global_cache_cr_blocks_served+global_cache_cu_blocks_served)) gc_waits
		 from	stats$seg_stat)				s,
		stats$seg_stat_obj				o,
		stats$snapshot					ss,
		(select	distinct dbid, instance_number
		 from	stats$database_instance
		 where	instance_name like '&&V_INSTANCE')	i
	 where	s.dbid = i.dbid
	 and	s.instance_number = i.instance_number
	 and	s.gc_waits > 0
	 and	ss.snap_id = s.snap_id
	 and	ss.dbid = s.dbid
	 and	ss.instance_number = s.instance_number
	 and	ss.snap_time between trunc(sysdate - &&V_NBR_DAYS) and sysdate
	 and	o.dataobj# = s.dataobj#
	 and	o.obj# = s.obj#
	 and	o.dbid = s.dbid
	 group by to_char(ss.snap_time, 'YYYYMMDD'),
		  to_char(ss.snap_time, 'DD-MON'),
		  s.instance_number,
		  o.object_type,
		  o.owner,
		  o.object_name
	 having   sum(s.gc_waits) > 0
	 order by yyyymmdd, gc_waits)
where	daily_ranking <= 10
order by sort0, sort1;

clear breaks computes
break on day on hr skip 1 on object_type on report
select	yyyymmddhh24 sort0,
	hourly_ranking sort1,
	day,
	hr,
	instance_number,
	object_type,
	owner,
	object_name,
	gc_waits
from	(select	to_char(ss.snap_time, 'YYYYMMDDHH24') yyyymmddhh24,
		to_char(ss.snap_time, 'DD-MON') day,
		to_char(ss.snap_time, 'HH24')||':00' hr,
		s.instance_number,
		o.object_type,
		o.owner,
		o.object_name,
		sum(s.gc_waits) gc_waits,
		rank () over (partition by to_char(ss.snap_time, 'YYYYMMDDHH24')
			      order by sum(s.gc_waits) desc) hourly_ranking
	 from	(select	dbid,
			instance_number,
			dataobj#,
			obj#,
			snap_id,
			decode(greatest(global_cache_cr_blocks_served+global_cache_cu_blocks_served,
					lag(global_cache_cr_blocks_served+global_cache_cu_blocks_served,1,0)
						over (partition by dbid, instance_number, dataobj#, obj#
						      order by snap_id)),
				(global_cache_cr_blocks_served+global_cache_cu_blocks_served),
				(global_cache_cr_blocks_served+global_cache_cu_blocks_served) -
				lag(global_cache_cr_blocks_served+global_cache_cu_blocks_served,1,0)
					over (partition by dbid, instance_number, dataobj#, obj#
					      order by snap_id),
				(global_cache_cr_blocks_served+global_cache_cu_blocks_served)) gc_waits
		 from	stats$seg_stat)		s,
		stats$seg_stat_obj		o,
		stats$snapshot			ss,
		(select	distinct dbid, instance_number
		 from	stats$database_instance
		 where	instance_name like '&&V_INSTANCE')	i
	 where	s.dbid = i.dbid
	 and	s.instance_number = i.instance_number
	 and	s.gc_waits > 0
	 and	ss.snap_id = s.snap_id
	 and	ss.dbid = s.dbid
	 and	ss.instance_number = s.instance_number
	 and	ss.snap_time between trunc(sysdate - &&V_NBR_DAYS) and sysdate
	 and	o.dataobj# = s.dataobj#
	 and	o.obj# = s.obj#
	 and	o.dbid = s.dbid
	 group by to_char(ss.snap_time, 'YYYYMMDDHH24'),
		  to_char(ss.snap_time, 'DD-MON'),
		  to_char(ss.snap_time, 'HH24')||':00',
		  s.instance_number,
		  o.object_type,
		  o.owner,
		  o.object_name
	 having   sum(s.gc_waits) > 0
	 order by yyyymmddhh24, gc_waits)
where	hourly_ranking <= 3
order by sort0, sort1;
spool off
set verify on