Anuj Singh Oracle DBA: Oracle AWR snap id

Snap id
snapshot id
awr snapshot information
awr snapshot info

awr_snapid.sql

Check the current AWR interval time and retention period

select snap_id,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot where BEGIN_INTERVAL_TIME > systimestamp -1 order by BEGIN_INTERVAL_TIME desc;


EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;



NOTE : Retention and interval both use value in minutes .

8 Days = 8*24*60 = 11520 minutes
7 DAYS = 7*24*60= 10080 minutes
6 DAYS = 6*24*60 = 8640 minutes



set linesize 300
 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
)
 

SELECT extract(day from snap_interval) *24*60+extract(hour from snap_interval) *60+extract(minute from snap_interval) snapshot_Interval_minute,
extract(day from retention) *24*60+extract(hour from retention) *60+extract(minute from retention) retention_Interval
,(extract(day from retention) *24*60+extract(hour from retention) *60+extract(minute from retention))/24/60 days
FROM dba_hist_wr_control;  




SQL> execute  dbms_workload_repository.modify_snapshot_settings (topnsql => 'MAXIMUM');

PL/SQL procedure successfully completed.
                                                                            15 min          30 days
SQL> execute dbms_workload_repository.modify_snapshot_settings( interval => 15, retention =>43200);

PL/SQL procedure successfully completed.

SQL> 

SELECT extract(day from snap_interval) *24*60+extract(hour from snap_interval) *60+extract(minute from snap_interval) snapshot_Interval_minute,
extract(day from retention) *24*60+extract(hour from retention) *60+extract(minute from retention) retention_Interval
,(extract(day from retention) *24*60+extract(hour from retention) *60+extract(minute from retention))/24/60 days
FROM dba_hist_wr_control;  




SNAPSHOT_INTERVAL_MINUTE RETENTION_INTERVAL       DAYS
------------------------ ------------------ ----------
                      15              43200         30






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:

Anuj Singh15 March 2017 at 12:53
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;

Anuj Singh26 August 2017 at 16:36
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' ;

Monday, 10 October 2011

Oracle AWR snap id

2 comments: