Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.