该SQL脚本用于从Oracle的AWR（Automatic Workload Repository）报告中查找最繁忙的时间段。通过分析DBtime，可以了解数据库在特定时间段内的繁忙程度。脚本允许用户指定实例号、开始快照ID和结束快照ID来限制分析范围，以帮助优化数据库性能。
列出oracle dbtime得sql语句
发布时间：2020-08-11 21:45:11
来源：ITPUB博客
阅读：94
作者：abin1703
可以根据dbtime 查看数据库得繁忙程度，获取某个快照得awr报告，进行分析
----------------------------------------------------------------------------------------
--
-- File name: dbtime.sql
-- Purpose: Find busiest time periods in AWR.
-
-- Author: Kerry Osborne
--
-- Usage: This scripts prompts for three values, all of which can be left blank.
--
-- instance_number: set to limit to a single instance in RAC environment
--
-- begin_snap_id: set it you want to limit to a specific range, defaults to 0
--
-- end_snap_id: set it you want to limit to a specific range, defaults to 99999999
--
--
---------------------------------------------------------------------------------------
set lines 155
col dbtime for 999,999.99
col begin_timestamp for a40
select * from (
select begin_snap, end_snap, timestamp begin_timestamp, inst, a/1000000/60 DBtime from
(
select
e.snap_id end_snap,
lag(e.snap_id) over (order by e.snap_id) begin_snap,
lag(s.end_interval_time) over (order by e.snap_id) timestamp,
s.instance_number inst,
e.value,
nvl(value-lag(value) over (order by e.snap_id),0) a
from dba_hist_sys_time_model e, DBA_HIST_SNAPSHOT s
where s.snap_id = e.snap_id
and e.instance_number = s.instance_number
and to_char(e.instance_number) like nvl('&instance_number',to_char(e.instance_number))
and stat_name = 'DB time'
)
where begin_snap between nvl('&begin_snap_id',0) and nvl('&end_snap_id',99999999)
and begin_snap=end_snap-1
order by dbtime desc
)
where rownum < 31
/
BEGIN_SNAP END_SNAP BEGIN_TIMESTAMP INST DBTIME
---------- ---------- ---------------------------------------- ---------- -----------
99 100 02-MAY-18 04.00.11.163 PM 1 2,791.85
80 81 01-MAY-18 09.00.01.121 PM 1 2,749.62
66 67 01-MAY-18 07.00.08.007 AM 1 2,733.33
67 68 01-MAY-18 08.00.21.814 AM 1 2,732.78
57 58 30-APR-18 10.00.06.732 PM 1 2,728.00
56 57 30-APR-18 09.00.35.565 PM 1 2,727.69
65 66 01-MAY-18 06.00.00.538 AM 1 2,726.59
72 73 01-MAY-18 01.00.30.321 PM 1 2,723.55
100 101 02-MAY-18 05.00.27.047 PM 1 1,764.40
55 56 30-APR-18 08.00.13.544 PM 1 1,706.66
59 60 01-MAY-18 12.00.17.902 AM 1 1,704.93
BEGIN_SNAP END_SNAP BEGIN_TIMESTAMP INST DBTIME
---------- ---------- ---------------------------------------- ---------- -----------
64 65 01-MAY-18 05.00.19.310 AM 1 1,703.53
83 84 02-MAY-18 12.00.44.286 AM 1 1,702.55
85 86 02-MAY-18 02.00.11.423 AM 1 1,701.95
69 70 01-MAY-18 10.00.21.811 AM 1 1,675.91
77 78 01-MAY-18 06.00.37.335 PM 1 1,675.91
71 72 01-MAY-18 12.00.16.844 PM 1 1,675.83
52 53 30-APR-18 05.00.02.455 PM 2 1,109.93
101 102 02-MAY-18 06.00.39.504 PM 2 1,088.41
86 87 02-MAY-18 03.00.24.853 AM 2 1,049.19
53 54 30-APR-18 06.00.12.352 PM 2 1,036.89
44 45 30-APR-18 09.00.00.954 AM 2 1,026.10
BEGIN_SNAP END_SNAP BEGIN_TIMESTAMP INST DBTIME
---------- ---------- ---------------------------------------- ---------- -----------
51 52 30-APR-18 04.00.25.353 PM 2 1,017.33
50 51 30-APR-18 03.00.11.207 PM 2 1,003.77
45 46 30-APR-18 10.00.12.054 AM 2 1,003.51
47 48 30-APR-18 12.00.02.027 PM 2 1,002.93
46 47 30-APR-18 11.00.29.502 AM 2 1,002.87
48 49 30-APR-18 01.00.12.856 PM 2 1,002.62
49 50 30-APR-18 02.00.10.611 PM 2 1,002.43
43 44 30-APR-18 08.00.29.126 AM 2 880.34
503
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
