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
No comments:
Post a Comment