Search This Blog

Total Pageviews

Thursday 17 March 2022

snap info !!!!!


snap info !!!!!....


https://anuj-singh.blogspot.com/2011/09/oracle-awr-matrix-report.html




SET HEADING off PAGESIZE 0 linesize 200
COLUMN sort_ord NOPRINT
SELECT DISTINCT 001 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, NULL hour_of_day
, 'Sunday' sunday_snapid
, 'Monday' monday_snapid
, 'Tuesday' tuesday_snapid
, 'Wednesday' wednesday_snapid
, 'Thursday' thursday_snapid
, 'Friday' friday_snapid
, 'Saturday' saturday_snapid
FROM sys.wrm$_snapshot
UNION ALL
SELECT 010 sort_ord
, TO_CHAR(s.first_sunday, 'YYYYMMDD') snap_week
, NULL hour_of_day
, TO_CHAR(s.first_sunday, 'MM/DD/YY') sunday_snapid
, TO_CHAR(s.first_sunday+1, 'MM/DD/YY') monday_snapid
, TO_CHAR(s.first_sunday+2, 'MM/DD/YY') tuesday_snapid
, TO_CHAR(s.first_sunday+3, 'MM/DD/YY') wednesday_snapid
, TO_CHAR(s.first_sunday+4, 'MM/DD/YY') thursday_snapid
, TO_CHAR(s.first_sunday+5, 'MM/DD/YY') friday_snapid
, TO_CHAR(s.first_sunday+6, 'MM/DD/YY') saturday_snapid
FROM ( SELECT NEXT_DAY(MIN(end_interval_time) - 7, 'SUNDAY') first_sunday
FROM sys.wrm$_snapshot
WHERE dbid = (select DBID from v$database)
AND instance_number = sys_context ('userenv','INSTANCE') 
) s
UNION ALL
SELECT 011 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, NULL hour_of_day
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),1,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) sunday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),2,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) monday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),3,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) tuesday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),4,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) wednesday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),5,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) thursday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),6,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) friday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),7,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) saturday_of_week
FROM sys.wrm$_snapshot
WHERE dbid = (select DBID from v$database)
AND instance_number = sys_context ('userenv','INSTANCE') 
GROUP BY TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD')
HAVING MAX(DECODE(TO_CHAR(end_interval_time, 'D'),1,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) IS NOT NULL
UNION ALL
SELECT DISTINCT 020 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, '---------' hour_of_day
, '---------' sunday_snapid
, '---------' monday_snapid
, '---------' tuesday_snapid
, '---------' wednesday_snapid
, '---------' thursday_snapid
, '---------' friday_snapid
, '---------' saturday_snapid
FROM sys.wrm$_snapshot
WHERE dbid = (select DBID from v$database)
AND instance_number = sys_context ('userenv','INSTANCE') 
UNION ALL
SELECT 030 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, TO_CHAR(end_interval_time, 'hh24')||':00' hour_of_day
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),1,snap_id,NULL)),'999999') sunday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),2,snap_id,NULL)),'999999') monday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),3,snap_id,NULL)),'999999') tuesday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),4,snap_id,NULL)),'999999') wednesday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),5,snap_id,NULL)),'999999') thursday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),6,snap_id,NULL)),'999999') friday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),7,snap_id,NULL)),'999999') saturday_of_week
FROM sys.wrm$_snapshot
WHERE dbid = (select DBID from v$database)
AND instance_number = sys_context ('userenv','INSTANCE')
GROUP BY TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD'), TO_CHAR(end_interval_time, 'hh24')||':00'
UNION ALL
SELECT DISTINCT 999 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, NULL hour_of_day
, NULL sunday_snapid
, NULL monday_snapid
, NULL tuesday_snapid
, NULL wednesday_snapid
, NULL thursday_snapid
, NULL friday_snapid
, NULL saturday_snapid
FROM sys.wrm$_snapshot
WHERE dbid = (select DBID from v$database)
AND instance_number = sys_context ('userenv','INSTANCE') 
ORDER BY snap_week, sort_ord , hour_of_day
/

20211003           Sunday    Monday    Tuesday   Wednesday Thursday  Friday    Saturday
20211003           10/03/21  10/04/21  10/05/21  10/06/21  10/07/21  10/08/21  10/09/21
20211003 --------- --------- --------- --------- --------- --------- --------- ---------
20211003 00:00                                     36504     36528     36552     36576
20211003 01:00                                     36505     36529     36553     36577
20211003 02:00                                     36506     36530     36554     36578



=====
define p_inst=1
define p_days=1

set linesize 200
set pages 200
set verify off
column event_name format a40

column dt heading 'Date/Hour' format a11
set linesize 500
set pages 9999	 
select * from (
select min(snap_id) as snap_id,  
		     to_char(start_time,'MM/DD/YY') as dt, to_char(start_time,'HH24') as hr
	from (
	select snap_id, s.instance_number, begin_interval_time start_time, 
		   end_interval_time end_time, snap_level, flush_elapsed,
		   lag(s.startup_time) over (partition by s.dbid, s.instance_number 
		   					   order by s.snap_id) prev_startup_time,
		   s.startup_time
	from  dba_hist_snapshot s, gv$instance i
	where begin_interval_time between trunc(sysdate)-&p_days and sysdate 
	and   s.instance_number = i.instance_number
	and   s.instance_number = &p_inst
	order by snap_id
	)
	group by to_char(start_time,'MM/DD/YY') , to_char(start_time,'HH24') 
	order by snap_id, start_time )
	pivot
	(sum(snap_id)
	 for hr in ('00','01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23')
	 )
	 order by dt;
	 
Date/Hour             '00'           '01'           '02'           '03'           '04'           '05'           '06'           '07'           '08'           '09'           '10'           '11'           '12'           '13'           '14'           '15'           '16'           '17'           '18'           '19'           '20'           '21'           '22'           '23'
----------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- -------------- --------------
03/22/22             40513          40514          40515          40516          40517          40518          40519          40520          40521          40522          40523          40524          40525          40526          40527          40528          40529          40530          40531          40532          40533          40534          40535          40536
03/23/22             40537          40538          40539          40540          40541          40542          40543

--os stats !!!!

col end_snap_time format a30
col load        format 990.00           heading "OS|Load"
col num_cpus    format 90               heading "CPU"
col mem         format 999990.00        heading "Memory|(GB)"
col oscpupct    format 990              heading "OS|CPU%"
col oscpuusr    format 990              heading "USR%"
col oscpusys    format 990              heading "SYS%"
col oscpuio     format 990              heading "IO%"



set ver off pages 50000 lines 140 tab off  linesize 300  pages 9999

 define days_history=1                     
define inst=1
BREAK ON instance_number SKIP 1


WITH
  base_line AS
  (
		SELECT
		*
			FROM
				(
				SELECT
				  snp.instance_number,
				  snp.end_interval_time ,
				  sst.snap_id,
				  sst.stat_name,
				  sst.value
				FROM
				  dba_hist_snapshot snp,
				  dba_hist_osstat sst
				WHERE
				  sst.instance_number = snp.instance_number
				AND sst.snap_id       = snp.snap_id
				AND snp.instance_number = decode(&inst,0,snp.instance_number,&inst)
				AND snp.begin_interval_time >= TRUNC(sysdate)- &days_history
			   )
		  pivot (SUM(value) FOR (stat_name) IN (
		  'LOAD'									   AS LOAD,
		  'NUM_CPUS'								   AS NUM_CPUS,
		  'PHYSICAL_MEMORY_BYTES'                      AS PHYSICAL_MEMORY_BYTES, 
		  'BUSY_TIME'           					   AS BUSY_TIME,
		  'USER_TIME'                                  AS USER_TIME,
		  'SYS_TIME'                                   AS SYS_TIME,
		  'IOWAIT_TIME'                                AS IOWAIT_TIME))
  )
SELECT
    b2.instance_number,
	to_char(b2.end_interval_time,'MM/DD/YY HH24:MI:SS') end_snap_time,
	b2.NUM_CPUS,
	round(b2.LOAD,1) LOAD,
	round(b2.PHYSICAL_MEMORY_BYTES/1024/1024/1024,0) mem,
	(((b2.busy_time - b1.busy_time)/100) / ((round(EXTRACT(DAY FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) * 1440 
                                                                                              + EXTRACT(HOUR FROM b2.END_INTERVAL_TIME   - b1.END_INTERVAL_TIME) * 60 
                                                                                              + EXTRACT(MINUTE FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) 
                                                                                              + EXTRACT(SECOND FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) / 60, 2)*60)*b2.NUM_CPUS))*100 as oscpupct,
	(((b2.user_time - b1.user_time)/100) / ((round(EXTRACT(DAY FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) * 1440 
                                                                                              + EXTRACT(HOUR FROM b2.END_INTERVAL_TIME   - b1.END_INTERVAL_TIME) * 60 
                                                                                              + EXTRACT(MINUTE FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) 
                                                                                              + EXTRACT(SECOND FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) / 60, 2)*60)*b2.NUM_CPUS))*100 as  oscpuusr,
    (((b2.sys_time - b1.sys_time)/100) / ((round(EXTRACT(DAY FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) * 1440 
                                                                                              + EXTRACT(HOUR FROM b2.END_INTERVAL_TIME   - b1.END_INTERVAL_TIME) * 60 
                                                                                              + EXTRACT(MINUTE FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) 
                                                                                              + EXTRACT(SECOND FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) / 60, 2)*60)*b2.NUM_CPUS))*100 as  oscpusys,
    (((b2.iowait_time - b1.iowait_time)/100) / ((round(EXTRACT(DAY FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) * 1440 
                                                                                              + EXTRACT(HOUR FROM b2.END_INTERVAL_TIME   - b1.END_INTERVAL_TIME) * 60 
                                                                                              + EXTRACT(MINUTE FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) 
                                                                                              + EXTRACT(SECOND FROM b2.END_INTERVAL_TIME - b1.END_INTERVAL_TIME) / 60, 2)*60)*b2.NUM_CPUS))*100 as  oscpuio
FROM
  base_line b1,
  base_line b2
WHERE
     b1.instance_number 	= b2.instance_number
AND  b1.snap_id + 1         = b2.snap_id
ORDER BY 
  1,2   ;


undef inst
undef fileno
undef days_history
undef interval_minutes



                                                       OS     Memory   OS
INSTANCE_NUMBER END_SNAP_TIME                  CPU    Load       (GB) CPU% USR% SYS%  IO%
--------------- ------------------------------ --- ------- ---------- ---- ---- ---- ----
              1 03/22/22 02:00:13               16    1.00      71.00    6    4    2    4
                03/22/22 03:00:08               16    1.20      71.00    6    4    2    3
                03/22/22 04:00:16               16    0.70      71.00    6    4    2    3

Oracle DBA

anuj blog Archive