List the sql statements for oracle dbtime
You can check the busy degree of the database according to dbtime, get the awr report of a snapshot and analyze it.
-
--
-- 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 999999.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 2791.85
80 81 01-MAY-18 09.00.01.121 PM 1 2749.62
66 67 01-MAY-18 07.00.08.007 AM 1 2733.33
67 68 01-MAY-18 08.00.21.814 AM 1 2732.78
57 58 30-APR-18 10.00.06.732 PM 1 2728.00
56 57 30-APR-18 09.00.35.565 PM 1 2727.69
65 66 01-MAY-18 06.00.00.538 AM 1 2726.59
72 73 01-MAY-18 01.00.30.321 PM 1 2723.55
100 101 02-MAY-18 05.00.27.047 PM 1 1764.40
55 56 30-APR-18 08.00.13.544 PM 1 1706.66
59 60 01-MAY-18 12.00.17.902 AM 1 1704.93
BEGIN_SNAP END_SNAP BEGIN_TIMESTAMP INST DBTIME
-
64 65 01-MAY-18 05.00.19.310 AM 1 1703.53
83 84 02-MAY-18 12.00.44.286 AM 1 1702.55
85 86 02-MAY-18 02.00.11.423 AM 1 1701.95
69 70 01-MAY-18 10.00.21.811 AM 1 1675.91
77 78 01-MAY-18 06.00.37.335 PM 1 1675.91
71 72 01-MAY-18 12.00.16.844 PM 1 1675.83
52 53 30-APR-18 05.00.02.455 PM 2 1109.93
101 102 02-MAY-18 06.00.39.504 PM 2 1088.41
86 87 02-MAY-18 03.00.24.853 AM 2 1049.19
53 54 30-APR-18 06.00.12.352 PM 2 1036.89
44 45 30-APR-18 09.00.00.954 AM 2 1026.10
BEGIN_SNAP END_SNAP BEGIN_TIMESTAMP INST DBTIME
-
51 52 30-APR-18 04.00.25.353 PM 2 1017.33
50 51 30-APR-18 03.00.11.207 PM 2 1003.77
45 46 30-APR-18 10.00.12.054 AM 2 1003.51
47 48 30-APR-18 12.00.02.027 PM 2 1002.93
46 47 30-APR-18 11.00.29.502 AM 2 1002.87
48 49 30-APR-18 01.00.12.856 PM 2 1002.62
49 50 30-APR-18 02.00.10.611 PM 2 1002.43
43 44 30-APR-18 08.00.29.126 AM 2 880.34
