oracle数据库 时间段,oracle实用sql(11)--收集一周各时段的load profile

工作中需要对近一周每小时的db time,db cpu,logical read,physical  read,transactions等信息做成EXCEL图表的形式，如果这些数据从awr报告中一个一个产生来收集就有些浪费时间了.

可以通过以下查询，直接获取数据库各实例中按默认快照间隔的load profile,然后将查询结果粘贴到EXCEL中来形成图表。

select f.instance_number,

f.stat_name,

to_char(f.END_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi:ss') b_time,

to_char(g.END_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi:ss') e_time,

(g.value - f.value) / 1000000 value_sum,

EXTRACT(DAY FROM g.end_interval_time - f.END_INTERVAL_TIME) * 86400 +

EXTRACT(HOUR FROM g.end_interval_time - f.END_INTERVAL_TIME) * 3600 +

EXTRACT(MINUTE FROM g.end_interval_time - f.END_INTERVAL_TIME) * 60 +

EXTRACT(SECOND FROM g.end_interval_time - f.END_INTERVAL_TIME) elapsed_second,

(g.value - f.value) / 1000000 /

(EXTRACT(DAY FROM g.end_interval_time - f.END_INTERVAL_TIME) * 86400 +

EXTRACT(HOUR FROM g.end_interval_time - f.END_INTERVAL_TIME) * 3600 +

EXTRACT(MINUTE FROM g.end_interval_time - f.END_INTERVAL_TIME) * 60 +

EXTRACT(SECOND FROM g.end_interval_time - f.END_INTERVAL_TIME)) value_per_sec

from (select a.instance_number,

a.snap_id b_snap_id,

lead(a.snap_id, 1, 0) over(partition by a.instance_number, stat_name order by a.snap_id) e_snap_id,

a.stat_name,

a.value,

b.end_interval_time

from DBA_HIST_SYS_TIME_MODEL a, dba_hist_snapshot b

where a.snap_id = b.snap_id

and a.instance_number = b.instance_number

and a.stat_name in ('DB time', 'DB CPU')

and b.begin_interval_time >= trunc(sysdate - 7)

and b.begin_interval_time <= trunc(sysdate)) f,

(select a.instance_number,

lag(a.snap_id, 1, 0) over(partition by a.instance_number, stat_name order by a.snap_id) b_snap_id,

a.snap_id e_snap_id,

a.stat_name,

a.value,

b.end_interval_time

from DBA_HIST_SYS_TIME_MODEL a, dba_hist_snapshot b

where a.snap_id = b.snap_id

and a.instance_number = b.instance_number

and a.stat_name in ('DB time', 'DB CPU')

and b.begin_interval_time >= trunc(sysdate - 7)

and b.begin_interval_time <= trunc(sysdate)) g

where g.e_snap_id != 0

and f.b_snap_id != 0

and f.instance_number = g.instance_number

and f.stat_name = g.stat_name

and f.b_snap_id = g.b_snap_id

and f.e_snap_id = g.e_snap_id

union all

select f.instance_number,

f.stat_name,

to_char(f.END_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi:ss') b_time,

to_char(g.END_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi:ss') e_time,

(g.value - f.value)  value_sum,

EXTRACT(DAY FROM g.end_interval_time - f.END_INTERVAL_TIME) * 86400 +

EXTRACT(HOUR FROM g.end_interval_time - f.END_INTERVAL_TIME) * 3600 +

EXTRACT(MINUTE FROM g.end_interval_time - f.END_INTERVAL_TIME) * 60 +

EXTRACT(SECOND FROM g.end_interval_time - f.END_INTERVAL_TIME) elapsed_second,

(g.value - f.value) /

(EXTRACT(DAY FROM g.end_interval_time - f.END_INTERVAL_TIME) * 86400 +

EXTRACT(HOUR FROM g.end_interval_time - f.END_INTERVAL_TIME) * 3600 +

EXTRACT(MINUTE FROM g.end_interval_time - f.END_INTERVAL_TIME) * 60 +

EXTRACT(SECOND FROM g.end_interval_time - f.END_INTERVAL_TIME)) value_per_sec

from (select a.instance_number,

a.snap_id b_snap_id,

lead(a.snap_id, 1, 0) over(partition by a.instance_number, stat_name order by a.snap_id) e_snap_id,

a.stat_name,

a.value,

b.end_interval_time

from DBA_HIST_SYSSTAT a, dba_hist_snapshot b

where a.snap_id = b.snap_id

and a.instance_number = b.instance_number

and a.stat_name in ('redo size',

'session logical reads',

'db block changes',

'physical reads',

'physical writes',

'user calls',

'parse count (total)',

'parse count (hard)',

'sorts (memory)',

'logons cumulative',

'execute count',

'user commits',

'user rollbacks')

and b.begin_interval_time >= trunc(sysdate - 7)

and b.begin_interval_time <= trunc(sysdate)) f,

(select a.instance_number,

lag(a.snap_id, 1, 0) over(partition by a.instance_number, stat_name order by a.snap_id) b_snap_id,

a.snap_id e_snap_id,

a.stat_name,

a.value,

b.end_interval_time

from DBA_HIST_SYSSTAT a, dba_hist_snapshot b

where a.snap_id = b.snap_id

and a.instance_number = b.instance_number

and a.stat_name in ('redo size',

'session logical reads',

'db block changes',

'physical reads',

'physical writes',

'user calls',

'parse count (total)',

'parse count (hard)',

'sorts (memory)',

'logons cumulative',

'execute count',

'user commits',

'user rollbacks')

and b.begin_interval_time >= trunc(sysdate - 7)

and b.begin_interval_time <= trunc(sysdate)) g

where g.e_snap_id != 0

and f.b_snap_id != 0

and f.instance_number = g.instance_number

and f.stat_name = g.stat_name

and f.b_snap_id = g.b_snap_id

and f.e_snap_id = g.e_snap_id

order by 1, 2, 3;