Oracle V$ACTIVE_SESSION_HISTORY

SQL>select SAMPLE_TIME from v$active_session_history;

SAMPLE_TIME

---------------------------------------------------------------------------

26-MAY-18 01.01.30.028 AM

26-MAY-18 01.01.29.028 AM

26-MAY-18 01.01.28.028 AM

26-MAY-18 01.01.27.028 AM

26-MAY-18 01.01.26.018 AM

26-MAY-18 01.01.25.018 AM

26-MAY-18 01.01.24.018 AM

26-MAY-18 01.01.23.018 AM

26-MAY-18 01.01.22.018 AM

26-MAY-18 01.01.21.018 AM

26-MAY-18 01.01.20.018 AM

 

可以看到V$ACTIVE_SESSION_HISTORY是动态每秒采样一次，通过SAMPLE_TIME可以将数据库出现问题的时间范围缩小来定位问题。

查询sql_id：（关于TX的等待事件）

set linesize 1000 pagesize 1000

col EVENT for a20

col SQL_ID for a30

col TIME for a40

select to_char(sample_time,'yyyymmdd hh24:mi') time ,EVENT,SQL_ID,count(*) from v$active_session_history

where  sample_time> to_date('20180526 03:00:00','yyyymmdd hh24:mi:ss') and sample_time<to_date('20180526 03:10:00','yyyymmdd hh24:mi:ss')

and EVENT like '%enq: TX - row lock contention%'  group by  to_char(sample_time,'yyyymmdd hh24:mi') ,EVENT,SQL_ID order by 1;

 

如果说 v$session_wait_history 是一小步，那么 ASH 则是 Oracle 迈出根本变革的一大步。从 Oracle 10g 开始， Oracle 引入了 ASH 新特性，也就是活动 Session 历史信息记录（ Active Session History， ASH）。 ASH 以 V$SESSION 为基础，每秒钟采样一次， 记录活动会话等待的事件。 因为记录所有会话的活动是非常昂贵的， 所以不活动的会话不会被采样， 这一点从 ASH的“ A” 上就可以看出。 采样工作由 Oracle 10g 新引入的一个后台进程 MMNL 来完成。是否启用 ASH 功能，受一个内部隐含参数控制： 

很多人可能更关心性能，如此频繁的采样是否会极大地影响数据库的性能呢？ 采样的性能影响无疑是存在的，但是因为 Oracle 的采样工具可以直接访问 Oracle 10g 内部结构，所以是极其高效的，对于性能的影响也非常小，这也正是 Oracle 提供优化或诊断工具的优势所在。ASH 信息被设计为在内存中滚动的， 在需要的时候早期的信息是会被覆盖的。 ASH 记录的信息可以通过 v$active_session_history 视图来访问，对于每个活动 SESSION，每次采样会在这个视图中记录一行信息。这部分内存在 SGA 中分配： 

SQL> select * from v$sgastat where name like '%ASH%';
POOL NAME BYTES
------------ ----------------------- ----------
shared pool ASH buffers 6291456
注意 ASH buffers 的最小值为 1MB，最大值不超过 30MB， 大小按照以下算法分配：
Max ( Min (cpu_count * 2MB, 5% * SHARED_POOL_SIZE, 30MB), 1MB)
在以上公式中，如果 SHARED_POOL_SIZE 未显示设置，则限制为 2%*SGA_TARGET。
这一算法在 Oracle 10g 的不同版本中，可能不同。 根据这个算法， 我的采样系统分配的 ASH Buffers 为 6MB。

 

下面是老外的一篇文章：

http://www.dba-oracle.com/oracle10g_tuning/t_v$active_session_history.htm

 

Oracle10g introduces the v$active_session_history view that keeps a history for recent active sessions’ activity. Oracle takes snapshots of active database sessions every second without placing serious overhead on the system. A database session is considered active by Oracle when it is consuming CPU time or waiting for an event that does not belong to the idle wait class. This view contains a considerable amount of information that is available in the v$session view, but it also has the sample_time column that points to a time in the past when a session was doing some work or waiting for a resource.v$active_session_history view contains a single row for each session when sampling was performed.

 Oracle10g引入了v $ active_session_history 视图，该视图保留了最近活动会话活动的历史记录。Oracle每秒都会捕获活动数据库会话的快照，而不会给系统带来严重的开销。当Oracle消耗CPU时间或等待不属于空闲等待类的事件时，Oracle认为数据库会话是活动的。此视图包含v $ session视图中提供的大量信息，但它还具有sample_time列，该列指向会话执行某项工作或等待资源时的过去时间。 V $ ACTIVE_SESSION_HISTORY 执行采样时，视图包含每个会话的单行。

 

 

An interesting possibility becomes available with the introduction of the v$active_session_history view in Oracle10g. With this tool, Oracle DBAs are now able to trace sessions without the need to use the well known 10046 event to perform extended tracing. All tracing can be performed now using only SQL queries without the need to review raw trace files and format them using the TKPROF utility.

通过 在Oracle10g中引入v $ active_session_history视图，可以获得一种有趣的可能性。使用此工具，Oracle DBA现在能够跟踪会话，而无需使用众所周知的10046事件来执行扩展跟踪。现在可以仅使用SQL查询执行所有跟踪，而无需查看原始跟踪文件并使用TKPROF实用程序对其进行格式化。

 

Oracle keeps session history in the circular memory buffer in the SGA. This means that the greater the database activity is, the smaller the amount of time session history available in the ASH view is. In this instance, it might help that the AWR dba_hist_active_sess_history view stores the ASH history for a longer time; however, the dba_hist_active_sess_history view stores ASH data snapshots only for the times the AWR snapshots were taken.

Oracle将会话历史记录保存在SGA的循环内存缓冲区中。这意味着数据库活动越大，ASH视图中可用的会话历史记录的时间量就越小。在这种情况下，AWR dba_hist_active_sess_history视图可以帮助 更长时间地存储ASH历史记录。但是， dba_hist_active_sess_history视图仅在AWR快照拍摄时存储ASH数据快照。

 

 

How can the information available through the v$active_session_historyview be used?. If a session that is experiencing delays or hangs has been identified and the goal is to identify the SQL statement(s) the session is issuing, along with the wait events being experienced for a particular time period, a query similar to this one can be issued:

如何使用通过 v $ active_session_history视图获得的信息？如果已经识别出遇到延迟或挂起的会话，并且目标是识别会话正在发出的SQL语句，以及在特定时间段内遇到的等待事件，那么类似于此的查询：

SELECT C.SQL_TEXT,B.NAME,COUNT(*),SUM(TIME_WAITED) FROM v$ACTIVE_SESSION_HISTORY A,v$EVENT_NAME B,v$SQLAREA C

WHERE    A.sample_time> to_date('20180526 03:00:00','yyyymmdd hh24:mi:ss') and A.sample_time<to_date('20180526 03:10:00','yyyymmdd hh24:mi:ss')  AND A.EVENT# = B.EVENT# AND A.SESSION_ID=1 AND A.SQL_ID = C.SQL_ID

GROUP BY C.SQL_TEXT, B.NAME;

The current_obj# column can be joined with the dba_objects view to get name of the object, or it can be joined with the current_file# column using dba_data_files to see the name of datafile that was accessed. Even a particular block that caused a wait event can be identified using the current_block#column.

 

It is also possible to identify hot datafiles, objects, or even data blocks that are being accessed by sessions more frequently than others and thus could be candidates for additional investigations. The hot_files_ash.sql query shows hot datafiles that caused the most wait times during session access:

 

该CURRENT_OBJ＃列可以加入与DBA_OBJECTS视图来获取对象的名称，也可以使用DBA_DATA_FILES地看到，被访问的数据文件的名称current_file＃列结合。甚至可以使用 current_block＃列识别导致等待事件的特定块 。

 

还可以比其他人更频繁地识别会话访问的热数据文件，对象甚至数据块，因此可以作为其他调查的候选者。该hot_files_ash.sql 查询显示导致会话访问期间最等待时间热点数据文件：

 

 

            hot_files_ash.sql

 

SELECT f.file_name "Data File",COUNT(*) "Wait Number",SUM(h.time_waited) "Total Time Waited"

FROM v$active_session_history h,dba_data_files f

WHERE h.current_file# = f.file_id

GROUP BY f.file_name ORDER BY 3 DESC; 

The sample output looks like:

 

Data File                              Wait Number Total Time Waited

-------------------------------------- ----------- -----------------

D:\ORACLE\ORADATA\DBDABR\SYSAUX01.DBF         5514         994398837

D:\ORACLE\ORADATA\DBDABR\SYSTEM01.DBF         2579         930483678

D:\ORACLE\ORADATA\DBDABR\UNDOTBS01.DBF         245           7727218

D:\ORACLE\ORADATA\DBDABR\USERS01.DBF           141           1548274

 

To be fair to the 10046 trace, the v$active_session_history does not catch session activity that is extremely fast, but it should catch activity that causes the most waits and resource consumption and will, therefore, be useful to the DBA. Statistically, the v$active_session_history does catch extremely fast operations if they occur sufficiently often to contribute to user time.

 

The following text includes several helpful queries that run against the v$active_session_history view. The first query, events_waits_hr_ask.sql, reports a list of resources that were in high demand in the last hour. This query does not reflect Idle wait events.

 为了公平对待10046跟踪， v $ active_session_history不会捕获非常快的会话活动，但它应该捕获导致最多等待和资源消耗的活动，因此对DBA有用。从统计上来说，v $ active_session_history 确实可以捕获非常快速的操作，如果它们足够频繁地发生以促进用户时间。

 

以下文本包含针对v $ active_session_history 视图运行的几个有用的查询。 events_waits_hr_ask.sql的第一个查询 报告了过去一小时内需求量很大的资源列表。此查询不反映空闲等待事件。

 

 

            events_waits_hr_ash.sql

 

SELECT h.event "Wait Event",SUM(h.wait_time + h.time_waited) "Total Wait Time" FROM v$active_session_history h,v$event_name e

WHERE

   h.sample_time BETWEEN sysdate - 1/24 AND sysdate

  AND h.event_id = e.event_id

  AND e.wait_class <> 'Idle'

GROUP BY h.event

ORDER BY 2 DESC

 

The output looks like the following:

 

Wait Event                      Total Wait Time

------------------------------- ---------------

Queue Monitor Task Wait              10,256,950

class slave wait                     10,242,904

log file switch completion            5,142,555

control file parallel write           4,813,121

db file sequential read                 334,871

process startup                         232,137

log file sync                           203,087

latch free                               36,934

log buffer space                         25,090

latch: redo allocation                   22,444

db file parallel write                      714

db file scattered read                      470

log file parallel write                     182

direct path read temp                       169

control file sequential read                160

direct path write temp                      112

SEE CODE DEPOT FOR FULL SCRIPTS

with 
   sql_exec2child as
 (select inst_id, sql_exec_start, sql_id, sql_exec_id 
 from (select inst_id, 
 sql_exec_start, 
 sql_opname, 
 sql_id, 
 sql_exec_id, 
 program, 
 count(distinct sql_child_number) 
 from gv$active_session_history 
 where sql_exec_id is not null 
 group by inst_id, 
 sql_exec_start, 
 sql_opname, 
 sql_id, 
 sql_exec_id, 
 program 
 having count(distinct sql_child_number) > 1 
 order by count(distinct sql_child_number) desc) 
 where rownum <= 1) 
 select distinct ash.sql_id, 
 program, 
 sql_child_number, 
 sql_opname, 
 sql_plan_operation || ' ' || sql_plan_options as PLAN_OPERATION 
 from gv$active_session_history ash, sql_exec2child 
 where ash.sql_id = sql_exec2child.sql_id 
 and ash.sql_exec_id = sql_exec2child.sql_exec_id 
 and ash.sql_exec_start = sql_exec2child.sql_exec_start 
 order by 1, 2 31 /