用zcolud分析历史性能

剩下的就是awr了。

你好 zcolud 是什么呀 麻烦介绍一下

ASH分析呢

oracle看awr。sqlserver看动态视图或者建一个扩展事件

–查询历史某个时间段的等待事件（推荐）
dba_hist_active_sess_history视图数据来自于VACTIVE_SESSION_HISTORY，记录了内存中活动会话的历史信息。通过dba_hist_active_sess_history视图关联vsqlarea和DBA_HIST_SNAPSHOT可以跟踪到某个时间段内的sql。当然能跟踪到的sql多少取决于vsqlarea，毕竟只有还保留在vsqlarea中的sql才能跟踪到。
10.1、查看故障时间段等待事件、问题sql id及会话访问次数
–alter session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss’;
select trunc(sample_time, ‘mi’) tm, sql_id, nvl(event,‘CPU’),count(distinct session_id) cnt
from dba_hist_active_sess_history
where sample_time between to_date(‘2020-04-01 14:00:00’) and
to_date(‘2020-04-01 14:30:00’)
group by trunc(sample_time, ‘mi’), sql_id,nvl(event,‘CPU’)
order by cnt desc;

10.2、查看故障时间段该sql相关的等待事件及对应的会话访问次数
select sql_id, nvl(event, ‘CPU’), count(distinct session_id) sz
from dba_hist_active_sess_history a, dba_hist_snapshot b
where sample_time between to_date(‘2019-08-22 14:00:00’) and
to_date(‘2019-08-22 15:00:00’)
and sql_id = ‘4ksvn2rgjnhcm’
and a.snap_id = b.snap_id
and a.instance_number = b.instance_number
group by sql_id, nvl(event, ‘CPU’)
order by sz desc;

10.3、查看历史会话等待事件对应的session信息

–1、查看awr时间段
select to_char(s.startup_time, ‘dd Mon “at” HH24:mi:ss’) instart_fmt,
di.instance_name inst_name,
di.db_name db_name,
s.snap_id snap_id,
to_char(s.end_interval_time, ‘dd Mon YYYY HH24:mi’) snapdat,
s.snap_level lvl
from dba_hist_snapshot s, dba_hist_database_instance di
where di.dbid = s.dbid
and di.instance_number = s.instance_number
and di.startup_time = s.startup_time
order by db_name, instance_name, snap_id;
–2、查看等待事件为行锁的session
select a.snap_id,
a.sql_id,
a.session_id,
a.session_serial#,
a.blocking_session,
a.blocking_session_serial#,
a.blocking_session_status
from DBA_HIST_ACTIVE_SESS_HISTORY a
where event like ‘%enq: TX - row lock contention%’
and snap_id between 58525 and 58569;
–3、查看阻塞回话，并统计阻塞次数
select a.blocking_session,
a.blocking_session_serial#,
count(a.blocking_session)
from DBA_HIST_ACTIVE_SESS_HISTORY a
where event like ‘%enq: TX - row lock contention%’
and snap_id between 58525 and 58569
group by a.blocking_session, a.blocking_session_serial#
order by 3 desc
–4、查看阻塞回话的sql_id和被阻塞的sql_id，条件为阻塞大于19次的
select distinct b.sql_id,c.blocked_sql_id,b.session_id,b.session_serial#,b.sql_opname,b.event,b.module,b.program,b.machine
from DBA_HIST_ACTIVE_SESS_HISTORY b,
(select a.sql_id as blocked_sql_id,
a.blocking_session,
a.blocking_session_serial#,
count(a.blocking_session)
from DBA_HIST_ACTIVE_SESS_HISTORY a
where event like ‘%enq: TX - row lock contention%’
and snap_id between 58525 and 58569
group by a.blocking_session, a.blocking_session_serial#,a.sql_id
having count(a.blocking_session) > 19
order by 3 desc) c
where b.session_id = c.blocking_session
and b.session_serial# = c.blocking_session_serial#
and b.snap_id between 58525 and 58569;

脚本的话可以参考这个