Search This Blog

Total Pageviews

Monday, 10 October 2011

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 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
   
Posted by at

2 comments:

Anuj Singh said...

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;


15 March 2017 at 12:53
Anuj Singh said...

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' ;

26 August 2017 at 16:36

Post a Comment

Subscribe to: Post Comments (Atom)

Oracle DBA

My photo
Essex, United Kingdom

anuj blog Archive