一.背景

一般当数据库出现性能问题的时候，我总是习惯于去先通过v$session查看等待事件，然后根据异常等待事件查看sql_id、userrname、program等信息。
当然了，要收集更详细的信息，system state dump和hang analyze会更好一点。
但是很多时候都是出现性能问题的时候我们不在旁边，不能及时收集到信息，这时候我们可以通过DBA_HIST_ACTIVE_SESS_HISTORY视图结合awr报告来看。

二.DBA_HIST_ACTIVE_SESS_HISTORY

2.1.官方文档位置

书：Oracle Database Reference

https://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_3201.htm#REFRN23400

2.2.DBA_HIST_ACTIVE_SESS_HISTORY介绍

DBA_HIST_ACTIVE_SESS_HISTORY显示内存中活动会话或者最近的系统活动的历史内容。包含V$$ACTIVE_SESSION_HISTORY的快照内容。
DBA_HIST_ACTIVE_SESS_HISTORY比V$$ACTIVE_SESSION_HISTORY多SNAP_ID、DBID、INSTANCE_NUMBER。

2.3.v$session流程

--1.找出异常等待事件
select event#,event,count(*) from v$session where status='ACTIVE' and event not like '%message%' group by event#, event order by 3;              
SQL> select event#,event,count(*) from v$session where status='ACTIVE' and event not like '%message%' group by event#, event order by 3;              

    EVENT# EVENT                                             COUNT(*)
---------- ---------------------------------------------------------
3 VKTM Logical Idle Wait                                                1
259 Space Manager: slave idle wait                                      1
379 Streams AQ: qmn coordinator idle wait                               1
380 Streams AQ: qmn slave idle wait                                     1
1 pmon timer                                                            1
257 smon timer                                                          1
374 Streams AQ: waiting for time management or cleanup tasks            1
74 DIAG idle wait                                                       2
8 rows selected
--2.根据event#查看会话信息
select sid,serial#,username,program,sql_id from v$session where event#='74' order by sql_id;
SQL> select sid,serial#,username,program,sql_id from v$session where event#='74' order by sql_id;

       SID    SERIAL# USERNAME            PROGRAM              SQL_ID
---------- ---------- ------------------------------ ---------------------
       126          1                                oracle@single (DIAG)                             
         2          1                                oracle@single (DIA0)                             

SQL> 
--根据sql_id获取sqltext
select sql_text from v$sqltext where sql_id='';

2.4.DBA_HIST_ACTIVE_SESS_HISTORY流程

--1.找出异常等待事件
select sample_time,event,count(1) from dba_hist_active_sess_history where sample_time < (sysdate -1) group by sample_time,event ;

select sample_time,event,count(1) 
from dba_hist_active_sess_history 
where sample_time between to_timestamp('2022-05-08 21:10:00','yyyy-mm-dd hh24:mi:ss') and to_timestamp('2022-05-09 21:30:00','yyyy-mm-dd hh24:mi:ss') 
group by sample_time,event ;

--2.查看相关等待事件的blocking_session
select SNAP_ID,SESSION_ID,SESSION_SERIAL#,EVENT,BLOCKING_SESSION_STATUS,BLOCKING_SESSION,BLOCKING_SESSION_SERIAL# from dba_hist_active_sess_history where event='log file sync';
SQL> select SNAP_ID,SESSION_ID,SESSION_SERIAL#,EVENT,BLOCKING_SESSION_STATUS,BLOCKING_SESSION,BLOCKING_SESSION_SERIAL# from dba_hist_active_sess_history where event='log file sync';

   SNAP_ID SESSION_ID SESSION_SERIAL# EVENT                                                            BLOCKING_SESSION_STATUS BLOCKING_SESSION BLOCKING_SESSION_SERIAL#
---------- ---------- --------------- ---------------------------------------------------------------- ----------------------- ---------------- ------------------------
        18        192              93 log file sync                                                    VALID                                189                        1
         8         69             779 log file sync                                                    VALID                                189                        1
         8         69             813 log file sync                                                    VALID                                189                        1
         2         10              31 log file sync                                                    VALID                                189                        1
         2         10              37 log file sync                                                    VALID                                189                        1
         2         10              41 log file sync                                                    VALID                                189                        1

6 rows selected
--3.查看blocking session对应的信息
select session_id,sql_id,blocking_session,event,machine 
from dba_hist_active_sess_history
where sample_time > to_date('2022-05-08 21:10:00','yyyy-mm-dd hh24:mi:ss') 
and sample_time < to_date('2022-05-09 21:30:00','yyyy-mm-dd hh24:mi:ss')
and session_id=189;
SQL> select session_id,sql_id,blocking_session,event,machine
  2  from dba_hist_active_sess_history
  3  where sample_time > to_date('2022-05-08 21:10:00','yyyy-mm-dd hh24:mi:ss')
  4  and sample_time < to_date('2022-05-09 21:30:00','yyyy-mm-dd hh24:mi:ss')
  5  and session_id=189;

SESSION_ID SQL_ID        BLOCKING_SESSION EVENT                                                            MACHINE
---------- ------------- ---------------- ---------------------------------------------------------------- ----------------------------------------------------------------
       189                                log file parallel write                                          single

SQL>