1.查询以往的session历史信息
select * from v$active_session_history where session_id=12 and session_serial#=703;
2.查询ash使用的空间:
select * from v$sgastat where name like 'ASH%' ;
3.确认目前ash相关设置,如ash是否启用,间隔多久采样数据等
select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from x$ksppi a, x$ksppcv b
where a.indx = b.indx
and a.ksppinm like '_ash%' ;
4.查询确认目前ash最旧的采样时间:
select to_char(min(sample_time),'yyyy/mm/dd hh24:mi:ss') "Sample Time" from v$active_session_history ;
5.查询最近10分钟内最耗费cpu的sql语句:
select a.sql_id, a.sess_count, a.cpu_load, b.sql_text
from (select sql_id,
count(*) sess_count,
round(count(*) / sum(count(*)) over(), 2) cpu_load
from v$active_session_history
where sample_time > sysdate - 1 / (24 * 6)
and session_type <> 'BACKGROUND'
and session_state = 'ON CPU'
group by sql_id
order by count(*) desc) a,
v$sqlarea b
where a.sql_id = b.sql_id ;
6.查询最近10分钟内最耗费io的sql语句:
select a.sql_id, a.sess_count, a.sess_count, b.sql_text
from (select ash.sql_id, count(*) sess_count
from v$active_session_history ash, v$event_name evt
where ash.sample_time > sysdate - 1 / (24 * 6)
and ash.session_state = 'WAITING'
and ash.event_id = evt.event_id
and evt.wait_class = 'User I/O'
group by ash.sql_id
order by count(*) desc) a,
v$sqlarea b
where a.sql_id = b.sql_id;
7.查询最近10分钟内最耗费系统资源的sql语句:
select a.sql_id, a.cpu, a.wait, a.io, a.total, b.sql_text
from (select ash.sql_id,
sum(decode(ash.session_state, 'ON CPU', 1, 0)) "CPU",
sum(decode(ash.session_state, 'WAITING', 1, 0)) -
sum(decode(ash.session_state,'WAITING',decode(en.wait_class, 'USER I/O', 1, 0), 0)) "WAIT",
sum(decode(ash.session_state,'WAITING',decode(en.wait_class, 'USER I/O', 1, 0), 0)) "IO",
sum(decode(ash.session_state, 'ON CPU', 1, 1)) "TOTAL"
from v$active_session_history ash, v$event_name en
where sql_id is not null
and en.event# = ash.event#
and ash.sample_time > sysdate - 1 / (24 * 6)
group by ash.sql_id
order by sum(decode(ash.session_state, 'ON CPU', 1, 1)) desc) a,
v$sqlarea b
where a.sql_id = b.sql_id;
8.查询最近10分钟内最耗费系统资源的数据库会话:
select a.sid,a.serial#,b.username,a.user_id,a.program,a.cpu,a.waiting,a.io,a.total
from (select ash.session_id sid,
ash.session_serial# serial#,
ash.user_id,
ash.program,
sum(decode(ash.session_state, 'ON CPU', 1, 0)) "CPU",
sum(decode(ash.session_state, 'WAITING', 1, 0)) -
sum(decode(ash.session_state,'WAITING',decode(en.wait_class, 'USER I/O', 1, 0), 0)) "WAITING",
sum(decode(ash.session_state,'WAITING',decode(en.wait_class, 'USER I/O', 1, 0), 0)) "IO",
sum(decode(ash.session_state, 'ON CPU', 1, 1)) "TOTAL"
from v$active_session_history ash, v$event_name en
where sql_id is not null
and en.event# = ash.event#
and ash.sample_time > sysdate - 1 / (24 * 6)
group by ash.session_id,ash.user_id,ash.session_serial#,ash.program
order by sum(decode(ash.session_state, 'ON CPU', 1, 1)) desc) a,
v$session b
where a.sid = b.sid;
9.查询当前用户会话是在等待哪一个会话:
select distinct a.session_id,
a.session_serial#,
a.sql_id,
a.session_state,
a.session_type,
a.blocking_session,
a.blocking_session_serial#,
a.blocking_session_status,
a.event,
a.sql_id
from v$active_session_history a, v$session s
where a.blocking_session is not null
and a.blocking_session = s.sid
and a.blocking_session_serial# = s.serial#
and s.sql_id is not null;
10.
Oracle11g停用自动分析的方式:
exec dbms_auto_task_admin.disable(client_name=>'auto optimizer stats collection',operation=>null,window_name=>null) ;
Oracle11g启用自动分析的方式:
exec dbms_auto_task_admin.enable(client_name=>'auto optimizer stats collection',operation=>null,window_name=>null) ;
查询旧的统计数据保留的天数:
select dbms_stats.get_stats_history_retention from dual ;
修改统计数据保留的天数:
exec dbms_stats.alter_stats_history_retention(60) ;
查询目前最早的统计数据时间:
select dbms_stats.get_stats_history_availability from dual ;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28878983/viewspace-2136494/,如需转载,请注明出处,否则将追究法律责任。