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博客

如何查询以往的session历史信息

最新文章