Oracle AWR snap
[code]
define BEGIN = "2022-05-12 01:00:00"
define END = "2022-05-12 03:00:00"
set linesize 300 pagesize 300
col BEGIN_INTERVAL_TIME for a27
col END_INTERVAL_TIME for a27
select
snap_id,
instance_number,
begin_interval_time,
end_interval_time
from dba_hist_snapshot
where dbid = (select dbid from v$database)
--and begin_interval_time >= to_timestamp('&BEGIN','YYYY-MM-DD HH24:MI:SS') and end_interval_time <= (to_timestamp('&END','YYYY-MM-DD HH24:MI:SS') + 5/24/60)
--and begin_interval_time >= to_timestamp('&BEGIN','YYYY-MM-DD HH24:MI:SS') and end_interval_time <= to_timestamp('&END','YYYY-MM-DD HH24:MI:SS')
and begin_interval_time >sysdate -1
order by snap_id, instance_number;
SNAP_ID INSTANCE_NUMBER BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- --------------- --------------------------- ---------------------------
41738 1 12-MAY-22 01.00.03.510 AM 12-MAY-22 02.00.11.856 AM
41738 2 12-MAY-22 01.00.03.582 AM 12-MAY-22 02.00.11.905 AM
41739 1 12-MAY-22 02.00.11.856 AM 12-MAY-22 03.00.20.062 AM
41739 2 12-MAY-22 02.00.11.905 AM 12-MAY-22 03.00.20.100 AM
SQL>
====
SET LINESIZE 145
SET PAGESIZE 9999
SET TRIMSPOOL ON
SET VERIFY off
COLUMN instance_name_print FORMAT a13 HEADING 'Instance Name'
COLUMN snap_id FORMAT 9999999 HEADING 'Snap ID'
COLUMN startup_time FORMAT a21 HEADING 'Instance Startup Time'
COLUMN begin_interval_time FORMAT a20 HEADING 'Begin Interval Time'
COLUMN end_interval_time FORMAT a20 HEADING 'End Interval Time'
COLUMN elapsed_time FORMAT 999,999,999,999.99 HEADING 'Elapsed Time (min)'
COLUMN db_time FORMAT 999,999,999,999.99 HEADING 'DB Time (min)'
COLUMN pct_db_time FORMAT 999999999 HEADING '% DB Time'
COLUMN cpu_time FORMAT 999,999,999.99 HEADING 'CPU Time (min)'
BREAK ON instance_name_print ON startup_time
SPOOL awr_snap.txt
SELECT
i.instance_name instance_name_print
, s.snap_id snap_id
, TO_CHAR(s.startup_time, 'dd/mm/yyyy HH24:MI:SS') startup_time
, TO_CHAR(s.begin_interval_time, 'dd/mm/yyyy HH24:MI:SS') begin_interval_time
, TO_CHAR(s.end_interval_time, 'dd/mm/yyyy HH24:MI:SS') end_interval_time
, ROUND(EXTRACT(DAY FROM s.end_interval_time - s.begin_interval_time) * 1440 +
EXTRACT(HOUR FROM s.end_interval_time - s.begin_interval_time) * 60 +
EXTRACT(MINUTE FROM s.end_interval_time - s.begin_interval_time) +
EXTRACT(SECOND FROM s.end_interval_time - s.begin_interval_time) / 60, 2) elapsed_time
, ROUND((e.value - b.value)/1000000/60, 2) db_time
, ROUND(((((e.value - b.value)/1000000/60) / (EXTRACT(DAY FROM s.end_interval_time - s.begin_interval_time) * 1440 +
EXTRACT(HOUR FROM s.end_interval_time - s.begin_interval_time) * 60 +
EXTRACT(MINUTE FROM s.end_interval_time - s.begin_interval_time) +
EXTRACT(SECOND FROM s.end_interval_time - s.begin_interval_time) / 60) ) * 100), 2) pct_db_time
FROM
dba_hist_snapshot s
, gv$instance i
, dba_hist_sys_time_model e
, dba_hist_sys_time_model b
WHERE
i.instance_number = s.instance_number
AND e.snap_id = s.snap_id
AND b.snap_id = s.snap_id - 1
AND e.stat_id = b.stat_id
AND e.instance_number = b.instance_number
AND e.instance_number = s.instance_number
AND e.stat_name = 'DB time'
ORDER BY
i.instance_name
, s.snap_id;
SPOOL OFF
Instance Name Snap ID Instance Startup Time Begin Interval Time End Interval Time Elapsed Time (min) DB Time (min) % DB Time
------------- -------- --------------------- -------------------- -------------------- ------------------- ------------------- ----------
orcl 5697 04/11/2011 10:27:02 20/11/2011 01:00:49 20/11/2011 02:00:51 60.03 .03 0
5698 20/11/2011 02:00:51 20/11/2011 03:00:52 60.03 .03 0
5699 20/11/2011 03:00:52 20/11/2011 04:00:54 60.03 .02 0
5700 20/11/2011 04:00:54 20/11/2011 05:00:56 60.03 .02 0
5701 20/11/2011 05:00:56 20/11/2011 06:00:59 60.06 2.47 4
5702 20/11/2011 06:00:59 20/11/2011 07:00:02 59.05 4.15 7
5703 20/11/2011 07:00:02 20/11/2011 08:00:04 60.04 .58 1
5704 20/11/2011 08:00:04 20/11/2011 09:00:06 60.03 .03 0
5718 20/11/2011 22:00:30 20/11/2011 23:00:32 60.03 1.09 2
5719 20/11/2011 23:00:32 21/11/2011 00:00:34 60.03 .03 0
5720 21/11/2011 00:00:34 21/11/2011 01:00:36 60.04 .05 0
5721 21/11/2011 01:00:36 21/11/2011 02:00:38 60.03 .04 0
5722 21/11/2011 02:00:38 21/11/2011 03:00:40 60.03 .04 0
5723 21/11/2011 03:00:40 21/11/2011 04:00:41 60.03 .03 0
5724 21/11/2011 04:00:41 21/11/2011 05:00:43 60.03 .03 0
5725 21/11/2011 05:00:43 21/11/2011 06:00:45 60.03 .03 0
5726 21/11/2011 06:00:45 21/11/2011 07:00:46 60.03 .04 0
5727 21/11/2011 07:00:46 21/11/2011 08:00:50 60.06 .06 0
5728 21/11/2011 08:00:50 21/11/2011 09:00:52 60.03 .04 0
5729 21/11/2011 09:00:52 21/11/2011 10:00:55 60.04 .05 0
5730 21/11/2011 10:00:55 21/11/2011 11:00:57 60.04 .38 1
5731 21/11/2011 11:00:57 21/11/2011 12:00:05 59.13 .34 1
5732 21/11/2011 12:00:05 21/11/2011 13:00:10 60.09 .35 1
5733 21/11/2011 13:00:10 21/11/2011 14:00:15 60.08 .10 0
5734 21/11/2011 14:00:15 21/11/2011 15:00:18 60.05 .11 0
5735 21/11/2011 15:00:18 21/11/2011 16:00:21 60.05 .07 0
5736 21/11/2011 16:00:21 21/11/2011 17:00:23 60.04 .07 0
5737 21/11/2011 17:00:23 21/11/2011 18:00:25 60.03 .03 0
5738 21/11/2011 18:00:25 21/11/2011 19:00:27 60.03 .03 0
5739 21/11/2011 19:00:27 21/11/2011 20:00:28 60.03 .13 0
5740 21/11/2011 20:00:28 21/11/2011 21:00:30 60.03 .03 0
5741 21/11/2011 21:00:30 21/11/2011 22:00:32 60.03 1.75 3
5742 21/11/2011 22:00:32 21/11/2011 23:00:41 60.15 40.19 67
5743 21/11/2011 23:00:41 22/11/2011 00:00:43 60.03 .04 0
5744 22/11/2011 00:00:43 22/11/2011 01:00:45 60.04 .05 0
5745 22/11/2011 01:00:45 22/11/2011 02:00:47 60.03 .05 0
5746 22/11/2011 02:00:47 22/11/2011 03:00:49 60.03 .03 0
5747 22/11/2011 03:00:49 22/11/2011 04:00:50 60.03 .03 0
5748 22/11/2011 04:00:50 22/11/2011 05:00:52 60.03 .03 0
5749 22/11/2011 05:00:52 22/11/2011 06:00:54 60.03 .03 0
5750 22/11/2011 06:00:54 22/11/2011 07:00:55 60.03 .03 0
5751 22/11/2011 07:00:55 22/11/2011 08:00:58 60.05 .07 0
5752 22/11/2011 08:00:58 22/11/2011 09:00:05 59.12 .57 1
5753 22/11/2011 09:00:05 22/11/2011 10:00:10 60.08 .37 1
5754 22/11/2011 10:00:10 22/11/2011 11:00:16 60.09 .86 1
5755 22/11/2011 11:00:16 22/11/2011 12:00:20 60.06 .07 0
5756 22/11/2011 12:00:20 22/11/2011 13:00:24 60.08 .18 0
5757 22/11/2011 13:00:24 22/11/2011 14:00:31 60.11 .16 0
5758 22/11/2011 14:00:31 22/11/2011 15:00:37 60.11 1.00 2
5759 22/11/2011 15:00:37 22/11/2011 16:00:42 60.08 .58 1
5760 22/11/2011 16:00:42 22/11/2011 17:00:47 60.09 .20 0
5761 22/11/2011 17:00:47 22/11/2011 18:00:50 60.06 .04 0
5762 22/11/2011 18:00:50 22/11/2011 19:00:54 60.06 .03 0
5763 22/11/2011 19:00:54 22/11/2011 20:00:57 60.06 .03 0
5781 23/11/2011 13:00:05 23/11/2011 14:00:09 60.07 .04 0
5782 23/11/2011 14:00:09 23/11/2011 15:00:14 60.08 .40 1
5783 23/11/2011 15:00:14 23/11/2011 16:00:20 60.09 .16 0
5784 23/11/2011 16:00:20 23/11/2011 17:00:28 60.14 .33 1
5785 23/11/2011 17:00:28 23/11/2011 18:00:32 60.06 .04 0
5786 23/11/2011 18:00:32 23/11/2011 19:00:36 60.07 .04 0
5787 23/11/2011 19:00:36 23/11/2011 20:00:39 60.06 .03 0
5788 23/11/2011 20:00:39 23/11/2011 21:00:42 60.06 .03 0
5789 23/11/2011 21:00:42 23/11/2011 22:00:53 60.17 2.91 5
5790 23/11/2011 22:00:53 23/11/2011 23:00:58 60.10 56.04 93
5791 23/11/2011 23:00:58 24/11/2011 00:00:02 59.07 .04 0
5792 24/11/2011 00:00:02 24/11/2011 01:00:07 60.08 .05 0
5793 24/11/2011 01:00:07 24/11/2011 02:00:10 60.06 .05 0
5794 24/11/2011 02:00:10 24/11/2011 03:00:14 60.06 .04 0
5795 24/11/2011 03:00:14 24/11/2011 04:00:17 60.05 .03 0
5796 24/11/2011 04:00:17 24/11/2011 05:00:21 60.06 .03 0
5797 24/11/2011 05:00:21 24/11/2011 06:00:24 60.06 .03 0
5798 24/11/2011 06:00:24 24/11/2011 07:00:28 60.06 .03 0
5900 28/11/2011 12:00:45 28/11/2011 13:00:48 60.06 .10 0
5901 28/11/2011 13:00:48 28/11/2011 14:00:52 60.06 .09 0
5902 28/11/2011 14:00:52 28/11/2011 15:00:55 60.06 .08 0
5903 28/11/2011 15:00:55 28/11/2011 16:00:59 60.07 .06 0
207 rows selected.
No comments:
Post a Comment