3
自己常用的ash脚本，包含root cause、top session、top sql等
5墨值下载
1、sample_time 分组活动会话趋势
分钟级别的活动会话分布
set linesize 260 pagesize 1000
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select trunc(sample_time,'mi'),count(*)
from gv$active_session_history
where sample_time between
to_date('&date1', 'yyyy-mm-dd hh24:mi:ss') and
to_date('&date2', 'yyyy-mm-dd hh24:mi:ss')
and inst_id in (select instance_number from v$instance)
group by trunc(sample_time,'mi')
order by trunc(sample_time,'mi');
秒级别的活动会话分布
set linesize 260 pagesize 1000
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select sample_time,sample_id,count(*)
from gv$active_session_history
where sample_time between
to_date('&date1', 'yyyy-mm-dd hh24:mi:ss') and
to_date('&date2', 'yyyy-mm-dd hh24:mi:ss')
and inst_id in (select instance_number from v$instance)
group by sample_time,sample_id
order by sample_time,sample_id;
2、获取活动会话最多的 sample_time
v$active_session_history 视图去挖掘信息
set linesize 260 pagesize 1000
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select * from (
select inst_id,sample_time,sample_id,count(*)
from gv$active_session_history
where sample_time between
to_date('&date1', 'yyyy-mm-dd hh24:mi:ss') and
to_date('&date2', 'yyyy-mm-dd hh24:mi:ss')
and inst_id in (select instance_number from v$instance)
group by inst_id,sample_time,sample_id
order by sample_time)
dba_hist_active_sess_history 视图去挖掘信息
set linesize 260 pagesize 1000
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select * from (
select INSTANCE_NUMBER,sample_time,sample_id,count(*)
from dba_hist_active_sess_history
where sample_time between
to_date('&date1', 'yyyy-mm-dd hh24:mi:ss') and
to_date('&date2', 'yyyy-mm-dd hh24:mi:ss')
and instance_number in (select instance_number from v$instance)
group by INSTANCE_NUMBER ,sample_time,sample_id
order by sample_time)
3.采样某瞬时 SAMPLE_TIME 或者某时间段内 SAMPLE_TIME
3.1.采样某瞬时 SAMPLE_TIME 的 SQL_ID||SQL_PLAN_HASH_VALUE、EVENT 的活动会话分组
某个 sample_time 时间点查看该 sample_time 时间点的等待事件和相关 SQL_ID、p1、p2 等参数
set linesize 260 pagesize 10000
select sql_id, nvl(event,'on cpu') event,count(*),p1||'',p2||''
from v$active_session_history a
where sample_id=&sample_id
group by sql_id, nvl(event,'on cpu'),p1||'',p2||''
order by count(*) desc;
某个 sample_time 时间点查看该 sample_time 时间点的等待事件和 SQL_PLAN_HASH_VALUE、p1、p2
等参数
set linesize 260 pagesize 10000
select SQL_PLAN_HASH_VALUE, nvl(event,'on cpu') event, count(*),p1||'',p2||''
from v$active_session_history a
where sample_id=&sample_id
group by SQL_PLAN_HASH_VALUE, nvl(event,'on cpu'),p1||'',p2||''
order by count(*) desc;
3.2.采样某时间段内 SAMPLE_TIME 的 SQL_ID||SQL_PLAN_HASH_VALUE、EVENT 的活动会话的分组
某时间段内等待事件和相关 SQL_ID、p1、p2 等参数
set linesize 260 pagesize 10000
col nvl(event,'ON CPU') for a40
select *
from (select sql_id, nvl(event,'ON CPU'), count(*)
from gv$active_session_history
where sample_time between
to_date('&date1', 'yyyy-mm-dd hh24:mi:ss') and
to_date('&date2', 'yyyy-mm-dd hh24:mi:ss')
and inst_id in (select instance_number from v$instance)
group by sql_id, event
order by count(*) desc)
where rownum <=10;
某时间段内的等待事件和相关 SQL_PLAN_HASH_VALUE、p1、p2 等参数
set linesize 260 pagesize 10000
col nvl(event,'ON CPU') for a40
select *
from (select SQL_PLAN_HASH_VALUE, nvl(event,'ON CPU'), count(*)
from gv$active_session_history
where sample_time between
to_date('&date1', 'yyyy-mm-dd hh24:mi:ss') and
to_date('&date2', 'yyyy-mm-dd hh24:mi:ss')
and inst_id in (select instance_number from v$instance)
group by SQL_PLAN_HASH_VALUE, event
order by count(*) desc)
where rownum <=10;
4、获取某时间段内的 top session_id+session_serial#和堵塞其他会话的 top
session_id+session_serial#
获取某时间段内的 top session_id+session_serial#
set linesize 260 pagesize 10000
col nvl(event,'ON CPU') for a40
select *
from (select session_id,session_serial#,sql_id,nvl(event,'ON CPU'),count(*)
from gv$active_session_history
where sample_time between
of 10
5墨值下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论