不通过收集AWR报告，采用SQL方式对数据库进行一个基本的性能评估
通过SQL可以获取DBTIME DBCPU LOADPROFILE等信息
各时间数据库繁忙程度
set linesize 160
Select
To_Char(Dhs.Startup_Time,'mm-dd hh24:mi') As Startup_Time,Dhstm.Snap_Id, To_Char(Dhs.Begin_Interval_Time,'mm-dd hh24:mi') As Begin_Interval_Time,
Ceil( (To_Date(To_Char(End_Interval_Time,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')-To_Date(To_Char(Begin_Interval_Time,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss'))*24*60 ) As Snapshot_Delta_Minutes,
Round( (Dhstm.Value - Lag( Dhstm.Value ) Over( Partition By Dhs.Startup_Time, Dhstm.Dbid, Dhstm.Instance_Number,Dhstm.Stat_Id Order By Dhstm.Snap_Id ))/1000000/60,2) As Db_Time_Minutes,
Round( Round( (Dhstm.Value - Lag( Dhstm.Value ) Over( Partition By Dhs.Startup_Time, Dhstm.Dbid, Dhstm.Instance_Number,Dhstm.Stat_Id Order By Dhstm.Snap_Id ))/1000000/60,2)/
Ceil( (To_Date(To_Char(End_Interval_Time,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')-To_Date(To_Char(Begin_Interval_Time,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss'))*24*60 ),2 ) As Db_Busy_Rate
From Sys.Dba_Hist_Sys_Time_Model Dhstm
Join Sys.Dba_Hist_Snapshot Dhs On Dhstm.Dbid=Dhs.Dbid And Dhstm.Instance_Number=Dhs.Instance_Number And Dhstm.Snap_Id=Dhs.Snap_Id
Where Stat_Name Like 'DB time'
Order By Snap_Id , Stat_Name;
获取DBTIME和DBCPU
select
s.dbid,
s.snap_id,
min(s.end_interval_time) begin_interval_time,
sum(decode(b.stat_name,'DB time',(e.value - b.value)/1000000/60,0)) dbtime_by_Min,
sum(decode(b.stat_name,'DB CPU', (e.value - b.value)/1000000/60,0)) cpu__by_Min
from DBA_HIST_SYS_TIME_MODEL e,
DBA_HIST_SYS_TIME_MODEL b,
DBA_HIST_SNAPSHOT s
where b.dbid = s.dbid
and b.INSTANCE_NUMBER = s.INSTANCE_NUMBER
and b.snap_id = s.snap_id
and e.dbid = e.dbid
and e.INSTANCE_NUMBER = s.INSTANCE_NUMBER and s.INSTANCE_NUMBER=1
and e.snap_id = s.snap_id + 1
and b.stat_name in ( 'DB time', 'DB CPU')
and e.stat_name = b.stat_name and to_char(s.begin_interval_time, 'YYYY-MM-DD') = '2023-06-16'
group by s.dbid, s.snap_id
order by 1,2;
获取数据库load profile
select instance_number,
btime,
SUM(DECODE(metric_name, 'User Transaction Per Sec', average, 0)) "Trx_P_S(times)",
SUM(DECODE(metric_name, 'Logons Per Sec', average, 0)) "Logon_P_S(times)",
SUM(DECODE(metric_name,
'Physical Read IO Requests Per Sec',
average,
0)) "Phy_R_IO_P_S(times)",
SUM(DECODE(metric_name,
'Physical Write IO Requests Per Sec',
average,
0)) "Phy_W_IO_P_S",
round(SUM(DECODE(metric_name, 'Redo Generated Per Sec', average, 0)) / 1024 / 1024,
2) "Redo_P_S(M)",
SUM(DECODE(metric_name, 'DB Block Changes Per Sec', average, 0)) "DB_Block_Changes_P_S",
round(SUM(DECODE(metric_name,
'Physical Read Bytes Per Sec',
average,
0)) / 1024 / 1024,
2) "Phy_R_P_S(M)",
round(SUM(DECODE(metric_name,
'Physical Write Bytes Per Sec',
average,
0)) / 1024 / 1024,
2) "Phy_W_P_S(M)"
from (select t.instance_number,
to_Char(t.BEGIN_TIME, 'yyyy-mm-dd hh24:mi') btime,
t.metric_name,
round(avg(t.average), 2) average
from DBA_HIST_SYSMETRIC_SUMMARY t
where t.metric_name in
('Physical Read IO Requests Per Sec',
'Physical Write IO Requests Per Sec',
'Redo Generated Per Sec',
'Physical Read Bytes Per Sec',
'Physical Write Bytes Per Sec',
'DB Block Changes Per Sec',
'Logons Per Sec',
'User Transaction Per Sec')
and to_char(t.BEGIN_TIME, 'yyyy-mm-dd') = '2023-06-16'
group by instance_number, metric_name, t.BEGIN_TIME
order by instance_number)
group by instance_number, btime;
TOP EVENT
select to_char(y.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD hh24:mi') datetime,
x.instance_number,
x.event,
rn,
x.waits Waits,
x.wait_times Total_Wait_Time_sec,
case
when x.event = 'DB CPU' then
null
else
round(x.wait_times * 1000 / x.waits, 2)
end avg_ms_per_time,
x.PCT,
wait_class
from (SELECT snap_id,
instance_number,
event,
waits,
round(wait_times, 1) wait_times,
round(RATIO_TO_REPORT(wait_times)
OVER(PARTITION BY instance_number, snap_id) * 100,
1) PCT,
wait_class,
rn
FROM (SELECT snap_id,
instance_number,
event,
round(Waits) Waits,
wait_times,
rank() over(partition by snap_id, instance_number order by wait_times desc) rn,
wait_class
FROM (SELECT stat_name EVENT,
snap_id,
instance_number,
case
when value / 1000000 - LAG(value)
OVER(PARTITION BY instance_number,
stat_name ORDER BY instance_number,
snap_id) / 1000000 < 0 then
value / 100000
else
value / 1000000 - LAG(value)
OVER(PARTITION BY instance_number,
stat_name ORDER BY instance_number,
snap_id) / 1000000
end Waits,
case
when value / 1000000 - LAG(value)
OVER(PARTITION BY instance_number,
stat_name ORDER BY instance_number,
snap_id) / 1000000 < 0 then
value / 100000
else
value / 1000000 - LAG(value)
OVER(PARTITION BY instance_number,
stat_name ORDER BY instance_number,
snap_id) / 1000000
end wait_times,
null wait_class
FROM DBA_HIST_SYS_TIME_MODEL
WHERE stat_name = 'DB CPU'
UNION ALL
SELECT event_name,
snap_id,
instance_number,
case
when total_waits - LAG(total_waits)
OVER(PARTITION BY instance_number,
event_name ORDER BY instance_number,
snap_id) < 0 then
total_waits
else
(total_waits - LAG(total_waits)
OVER(PARTITION BY instance_number,
event_name ORDER BY instance_number,
snap_id))
end Waits,
case
when (time_waited_micro / 1e6 -
LAG(time_waited_micro)
OVER(PARTITION BY instance_number,
event_name ORDER BY instance_number,
snap_id) / 1e6) < 0 then
time_waited_micro / 1e6
else
time_waited_micro / 1e6 -
LAG(time_waited_micro)
OVER(PARTITION BY instance_number,
event_name ORDER BY instance_number,
snap_id) / 1e6
end wait_times,
wait_class
FROM DBA_HIST_SYSTEM_EVENT
WHERE wait_class not in
('Idle', 'Administrative', 'System I/O'))
WHERE wait_times > 0
and waits > 0)
WHERE rn <= 10) x,
dba_hist_snapshot y
where x.snap_id = y.SNAP_ID
and x.INSTANCE_NUMBER = y.INSTANCE_NUMBER
and to_char(y.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD') = '2023-06-16'
ORDER BY y.BEGIN_INTERVAL_TIME, y.INSTANCE_NUMBER, rn;
