Snap id
snapshot id
awr snapshot information
awr snapshot info
awr_snapid.sql
Check the current AWR interval time and retention period
snapshot id
awr snapshot information
awr snapshot info
awr_snapid.sql
Check the current AWR interval time and retention period
select snapshot_Interval,retention_interval_minute,retention_interval_minute/60 retention_interval_hour,(retention_interval_minute/60)/24 retention_interval_day
from (SELECT extract(day from snap_interval) *24*60+extract(hour from snap_interval) *60+extract(minute from snap_interval) snapshot_Interval,
extract(day from retention) *24*60+extract(hour from retention) *60+extract(minute from retention) retention_interval_minute
FROM dba_hist_wr_control
)
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
/
20160410 Sunday Monday Tuesday Wednesday Thursday Friday Saturday
20160410 04/10/16 04/11/16 04/12/16 04/13/16 04/14/16 04/15/16 04/16/16
20160410 --------- --------- --------- --------- --------- --------- --------- ---------
20160410 00:00 4296 4363 4398 4422
20160410 01:00 4297 4369 4399 4423
20160410 02:00 4298 4375 4400 4424
20160410 03:00 4299 4377 4401 4425
20160410 04:00 4300 4378 4402 4426
20160410 05:00 4301 4379 4403 4427
20160410 06:00 4302 4380 4404 4428
20160410 07:00 4303 4381 4405 4429
20160410 08:00 4304 4382 4406 4430
20160410 09:00 4305 4383 4407 4431
20160410 10:00 4306 4384 4408 4432
20160410 11:00 4307 4385 4409 4433
20160410 12:00 4308 4386 4410 4434
20160410 13:00 4309 4387 4411 4435
20160410 14:00 4310 4388 4412 4436
20160410 15:00 4311 4389 4413 4437
20160410 16:00 4315 4390 4414 4438
20160410 17:00 4321 4391 4415 4439
20160410 18:00 4327 4392 4416 4440
20160410 19:00 4333 4393 4417 4441
20160410 20:00 4339 4394 4418 4442
20160410 21:00 4293 4345 4395 4419 4443
20160410 22:00 4294 4351 4396 4420 4444
20160410 23:00 4295 4357 4397 4421 4445
20160410
20160417 Sunday Monday Tuesday Wednesday Thursday Friday Saturday
20160417 04/17/16 04/18/16 04/19/16 04/20/16 04/21/16
20160417 --------- --------- --------- --------- --------- --------- --------- ---------
20160417 00:00 4446 4470 4494 4518 4542
20160417 01:00 4447 4471 4495 4519 4543
20160417 02:00 4448 4472 4496 4520 4544
20160417 03:00 4449 4473 4497 4521 4545
20160417 04:00 4450 4474 4498 4522 4546
20160417 05:00 4451 4475 4499 4523 4547
20160417 06:00 4452 4476 4500 4524 4548
20160417 07:00 4453 4477 4501 4525
20160417 08:00 4454 4478 4502 4526
20160417 09:00 4455 4479 4503 4527
20160417 10:00 4456 4480 4504 4528
20160417 11:00 4457 4481 4505 4529
20160417 12:00 4458 4482 4506 4530
20160417 13:00 4459 4483 4507 4531
20160417 14:00 4460 4484 4508 4532
20160417 15:00 4461 4485 4509 4533
20160417 16:00 4462 4486 4510 4534
20160417 17:00 4463 4487 4511 4535
20160417 18:00 4464 4488 4512 4536
20160417 19:00 4465 4489 4513 4537
20160417 20:00 4466 4490 4514 4538
20160417 21:00 4467 4491 4515 4539
20160417 22:00 4468 4492 4516 4540
20160417 23:00 4469 4493 4517 4541
20160417
56 rows selected.
====
define p_inst=1
define p_days =3
set linesize 400 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;
===
define p_days=2
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;
set linesize 200 pagesize 200 col snaptime for a25 select dhdi.instance_name, dhdi.db_name, dhdi.DBID, dhs.snap_id, to_char(dhs.begin_interval_time,'dd/MM/YYYY:HH24:MI') begin_snap_time, to_char(dhs.end_interval_time,'DD/MM/YYYY:HH24:MI') end_snap_time, decode(dhs.startup_time,dhs.begin_interval_time,'**db restart**',null) db_bounce from dba_hist_snapshot dhs, dba_hist_database_instance dhdi where dhdi.dbid = dhs.dbid and dhdi.instance_number = dhs.instance_number and dhdi.startup_time = dhs.startup_time and dhs.end_interval_time >= sysdate -2 order by db_name, instance_name, snap_id;
define num_days = 2; define db_name = 'RAC'; define dbid = 1222414252; define begin_snap = 10319; define end_snap = 10320; define report_type = 'html'; define instance_numbers_or_ALL = 'ALL' define report_name = awrrpt_RAC_&&begin_snap._&&end_snap..&&report_type @?/rdbms/admin/awrgrpti
====
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
col Max_SAMPLE_TIME for a27
col Min_SAMPLE_TIME for a27
select min(SAMPLE_TIME) Min_SAMPLE_TIME,max(SAMPLE_TIME) Max_SAMPLE_TIME from gv$active_session_history
where 1=1
and IS_AWR_SAMPLE='N'
/
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
set linesize 200 pagesize 200
col BEGIN_INTERVAL_TIME for a28
col END_INTERVAL_TIME for a28
select snap_id,begin_interval_time,end_interval_time from dba_hist_snapshot
where 1=1
-- to_char(begin_interval_time,'DD-MON-YYYY')='26-AUG-2017'
and begin_interval_time> sysdate -1 ;
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
set linesize 200 pagesize 200
col min_BEGIN_INTERVAL_TIME for a28
col MAX_BEGIN_INTERVAL_TIME for a28
select min(snap_id),min(begin_interval_time) min_BEGIN_INTERVAL_TIME,max(snap_id),max(begin_interval_time) MAX_BEGIN_INTERVAL_TIME from dba_hist_snapshot
where 1=1
-- to_char(begin_interval_time,'DD-MON-YYYY')='26-AUG-2017'
and begin_interval_time> sysdate -1 ;
====
from web awr_os_stat.sql
define days_history=1
define inst=1
set ver off pages 50000 lines 140 tab off linesize 300 pages 9999
col end_snap_time format a30
col load format 990.00 heading "OS|Load"
col num_cpus format 9999 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%"
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
;
OS Memory OS
INSTANCE_NUMBER END_SNAP_TIME CPU Load (GB) CPU% USR% SYS% IO%
--------------- ------------------------------ ----- ------- ---------- ---- ---- ---- ----
1 03/23/23 02:00:01 224 16.20 754.00 17 17 1 0
03/23/23 03:00:08 224 27.50 754.00 16 15 1 0
03/23/23 04:00:17 224 30.40 754.00 19 18 1 0
03/23/23 05:00:59 224 94.80 754.00 27 24 3 2
from
https://github.com/eclaro/oracle_scripts/blob/master/awrsnaps.sql
col SNAP_00 for a6 just right head "00H "
col SNAP_01 for a6 just right head "01H "
col SNAP_02 for a6 just right head "02H "
col SNAP_03 for a6 just right head "03H "
col SNAP_04 for a6 just right head "04H "
col SNAP_05 for a6 just right head "05H "
col SNAP_06 for a6 just right head "06H "
col SNAP_07 for a6 just right head "07H "
col SNAP_08 for a6 just right head "08H "
col SNAP_09 for a6 just right head "09H "
col SNAP_10 for a6 just right head "10H "
col SNAP_11 for a6 just right head "11H "
col SNAP_12 for a6 just right head "12H "
col SNAP_13 for a6 just right head "13H "
col SNAP_14 for a6 just right head "14H "
col SNAP_15 for a6 just right head "15H "
col SNAP_16 for a6 just right head "16H "
col SNAP_17 for a6 just right head "17H "
col SNAP_18 for a6 just right head "18H "
col SNAP_19 for a6 just right head "19H "
col SNAP_20 for a6 just right head "20H "
col SNAP_21 for a6 just right head "21H "
col SNAP_22 for a6 just right head "22H "
col SNAP_23 for a6 just right head "23H "
col dt head "DATE"
col DATE for a10
col INST for 9999
WITH x as (
SELECT
trunc(end_interval_time) DT, INSTANCE_NUMBER,
min(case when extract(hour from end_interval_time) = 0 then snap_id else NULL end) SN00,
min(case when extract(hour from end_interval_time) = 1 then snap_id else NULL end) SN01,
min(case when extract(hour from end_interval_time) = 2 then snap_id else NULL end) SN02,
min(case when extract(hour from end_interval_time) = 3 then snap_id else NULL end) SN03,
min(case when extract(hour from end_interval_time) = 4 then snap_id else NULL end) SN04,
min(case when extract(hour from end_interval_time) = 5 then snap_id else NULL end) SN05,
min(case when extract(hour from end_interval_time) = 6 then snap_id else NULL end) SN06,
min(case when extract(hour from end_interval_time) = 7 then snap_id else NULL end) SN07,
min(case when extract(hour from end_interval_time) = 8 then snap_id else NULL end) SN08,
min(case when extract(hour from end_interval_time) = 9 then snap_id else NULL end) SN09,
min(case when extract(hour from end_interval_time) = 10 then snap_id else NULL end) SN10,
min(case when extract(hour from end_interval_time) = 11 then snap_id else NULL end) SN11,
min(case when extract(hour from end_interval_time) = 12 then snap_id else NULL end) SN12,
min(case when extract(hour from end_interval_time) = 13 then snap_id else NULL end) SN13,
min(case when extract(hour from end_interval_time) = 14 then snap_id else NULL end) SN14,
min(case when extract(hour from end_interval_time) = 15 then snap_id else NULL end) SN15,
min(case when extract(hour from end_interval_time) = 16 then snap_id else NULL end) SN16,
min(case when extract(hour from end_interval_time) = 17 then snap_id else NULL end) SN17,
min(case when extract(hour from end_interval_time) = 18 then snap_id else NULL end) SN18,
min(case when extract(hour from end_interval_time) = 19 then snap_id else NULL end) SN19,
min(case when extract(hour from end_interval_time) = 20 then snap_id else NULL end) SN20,
min(case when extract(hour from end_interval_time) = 21 then snap_id else NULL end) SN21,
min(case when extract(hour from end_interval_time) = 22 then snap_id else NULL end) SN22,
min(case when extract(hour from end_interval_time) = 23 then snap_id else NULL end) SN23,
max(case when extract(hour from end_interval_time) = 0 then startup_time else NULL end) ST00,
max(case when extract(hour from end_interval_time) = 1 then startup_time else NULL end) ST01,
max(case when extract(hour from end_interval_time) = 2 then startup_time else NULL end) ST02,
max(case when extract(hour from end_interval_time) = 3 then startup_time else NULL end) ST03,
max(case when extract(hour from end_interval_time) = 4 then startup_time else NULL end) ST04,
max(case when extract(hour from end_interval_time) = 5 then startup_time else NULL end) ST05,
max(case when extract(hour from end_interval_time) = 6 then startup_time else NULL end) ST06,
max(case when extract(hour from end_interval_time) = 7 then startup_time else NULL end) ST07,
max(case when extract(hour from end_interval_time) = 8 then startup_time else NULL end) ST08,
max(case when extract(hour from end_interval_time) = 9 then startup_time else NULL end) ST09,
max(case when extract(hour from end_interval_time) = 10 then startup_time else NULL end) ST10,
max(case when extract(hour from end_interval_time) = 11 then startup_time else NULL end) ST11,
max(case when extract(hour from end_interval_time) = 12 then startup_time else NULL end) ST12,
max(case when extract(hour from end_interval_time) = 13 then startup_time else NULL end) ST13,
max(case when extract(hour from end_interval_time) = 14 then startup_time else NULL end) ST14,
max(case when extract(hour from end_interval_time) = 15 then startup_time else NULL end) ST15,
max(case when extract(hour from end_interval_time) = 16 then startup_time else NULL end) ST16,
max(case when extract(hour from end_interval_time) = 17 then startup_time else NULL end) ST17,
max(case when extract(hour from end_interval_time) = 18 then startup_time else NULL end) ST18,
max(case when extract(hour from end_interval_time) = 19 then startup_time else NULL end) ST19,
max(case when extract(hour from end_interval_time) = 20 then startup_time else NULL end) ST20,
max(case when extract(hour from end_interval_time) = 21 then startup_time else NULL end) ST21,
max(case when extract(hour from end_interval_time) = 22 then startup_time else NULL end) ST22,
max(case when extract(hour from end_interval_time) = 23 then startup_time else NULL end) ST23
FROM dba_hist_snapshot
JOIN v$database using (DBID)
group by trunc(end_interval_time), INSTANCE_NUMBER
)
select
to_char(DT,'YYYY-MM-DD') DT, INSTANCE_NUMBER as INST,
lpad(SN00 || case when ST00 is NOT NULL and ST00 <> lag(ST23) over (ORDER BY DT) then '*' else ' ' end,6,' ') as SNAP_00,
lpad(SN01 || case when ST01 is NOT NULL and ST01 <> ST00 then '*' else ' ' end ,6,' ') as SNAP_01,
lpad(SN02 || case when ST02 is NOT NULL and ST02 <> ST01 then '*' else ' ' end ,6,' ') as SNAP_02,
lpad(SN03 || case when ST03 is NOT NULL and ST03 <> ST02 then '*' else ' ' end ,6,' ') as SNAP_03,
lpad(SN04 || case when ST04 is NOT NULL and ST04 <> ST03 then '*' else ' ' end ,6,' ') as SNAP_04,
lpad(SN05 || case when ST05 is NOT NULL and ST05 <> ST04 then '*' else ' ' end ,6,' ') as SNAP_05,
lpad(SN06 || case when ST06 is NOT NULL and ST06 <> ST05 then '*' else ' ' end ,6,' ') as SNAP_06,
lpad(SN07 || case when ST07 is NOT NULL and ST07 <> ST06 then '*' else ' ' end ,6,' ') as SNAP_07,
lpad(SN08 || case when ST08 is NOT NULL and ST08 <> ST07 then '*' else ' ' end ,6,' ') as SNAP_08,
lpad(SN09 || case when ST09 is NOT NULL and ST09 <> ST08 then '*' else ' ' end ,6,' ') as SNAP_09,
lpad(SN10 || case when ST10 is NOT NULL and ST10 <> ST09 then '*' else ' ' end ,6,' ') as SNAP_10,
lpad(SN11 || case when ST11 is NOT NULL and ST11 <> ST10 then '*' else ' ' end ,6,' ') as SNAP_11,
lpad(SN12 || case when ST12 is NOT NULL and ST12 <> ST11 then '*' else ' ' end ,6,' ') as SNAP_12,
lpad(SN13 || case when ST13 is NOT NULL and ST13 <> ST12 then '*' else ' ' end ,6,' ') as SNAP_13,
lpad(SN14 || case when ST14 is NOT NULL and ST14 <> ST13 then '*' else ' ' end ,6,' ') as SNAP_14,
lpad(SN15 || case when ST15 is NOT NULL and ST15 <> ST14 then '*' else ' ' end ,6,' ') as SNAP_15,
lpad(SN16 || case when ST16 is NOT NULL and ST16 <> ST15 then '*' else ' ' end ,6,' ') as SNAP_16,
lpad(SN17 || case when ST17 is NOT NULL and ST17 <> ST16 then '*' else ' ' end ,6,' ') as SNAP_17,
lpad(SN18 || case when ST18 is NOT NULL and ST18 <> ST17 then '*' else ' ' end ,6,' ') as SNAP_18,
lpad(SN19 || case when ST19 is NOT NULL and ST19 <> ST18 then '*' else ' ' end ,6,' ') as SNAP_19,
lpad(SN20 || case when ST20 is NOT NULL and ST20 <> ST19 then '*' else ' ' end ,6,' ') as SNAP_20,
lpad(SN21 || case when ST21 is NOT NULL and ST21 <> ST20 then '*' else ' ' end ,6,' ') as SNAP_21,
lpad(SN22 || case when ST22 is NOT NULL and ST22 <> ST21 then '*' else ' ' end ,6,' ') as SNAP_22,
lpad(SN23 || case when ST23 is NOT NULL and ST23 <> ST22 then '*' else ' ' end ,6,' ') as SNAP_23
from x
order by INSTANCE_NUMBER, DT
;
DATE INST 00H 01H 02H 03H 04H 05H 06H 07H 08H 09H 10H 11H 12H 13H 14H 15H 16H 17H 18H 19H 20H 21H 22H 23H
---------- ----- ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------
2020-10-24 1 105336 105339 105343 105347 105351 105355 105359 105363 105367 105371 105375 105379 105383 105387 105391 105395 105399 105403 105407 105411 105415 105419 105423 105427
2 comments:
set linesize 200 pagesize 200
col snaptime for a25
select dhdi.instance_name,
dhdi.db_name,
dhs.snap_id,
to_char(dhs.begin_interval_time,'MM/DD/YYYY:HH24:MI') begin_snap_time,
to_char(dhs.end_interval_time,'MM/DD/YYYY:HH24:MI') end_snap_time,
decode(dhs.startup_time,dhs.begin_interval_time,'**db restart**',null) db_bounce
from dba_hist_snapshot dhs, dba_hist_database_instance dhdi
where dhdi.dbid = dhs.dbid
and dhdi.instance_number = dhs.instance_number
and dhdi.startup_time = dhs.startup_time
and dhs.end_interval_time >= to_date(sysdate - &&num_days_back)
order by db_name, instance_name, snap_id;
set linesize 200 pagesize 200
col BEGIN_INTERVAL_TIME for a28
col END_INTERVAL_TIME for a28
select snap_id,begin_interval_time,end_interval_time from dba_hist_snapshot where to_char(begin_interval_time,'DD-MON-YYYY')='26-AUG-2017' ;
Post a Comment