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 ;

如何查詢以往的session歷史資訊

相關文章