Oracle ASH查询活动会话历史记录

什么是活动会话历史记录（ASH）

活动会话历史记录（ASH）是Oracle 10g中引入的。它每秒对每个活动数据库会话的活动进行采样。数据保存在数据库内存中的缓冲区中。设计目标是保持约一个小时。如果会话未处于活动状态，则不会进行采样。内存缓冲区通过称为v$active_session_history的视图公开。拍摄AWR快照时，会将ASH缓冲区中每10行中的1行向下复制到AWR存储库中。当缓冲区达到66％满时，也可以将其刷新到快照之间的磁盘上，因此不会丢失任何数据。数据存储在WRH$_ACTIVE_SESSION_HISTORY中，并通过dba_hist_active_sess_history公开。默认情况下处于启用状态，但在要使用它之前，请注意这是一个许可功能。
您可以通过设置oracle underscore参数来禁用ash

alter system set "_ash_enable"=False;

我要强调的是，如果会话未处于活动状态，则不会进行采样。您实际上可以设置参数 _ash_enable_all = TRUE，以强制对所有会话（包括空闲会话）进行采样

10个有用的查询活动会话历史记录（Oracle ASH）

（1）Oracle提供了一个名为DBMS_APPLICATION_INFO的软件包，它允许您设置两个属性；会话的模块和动作。然后，该值将出现在 v$session中，并且对于帮助您确定哪些数据库会话与应用程序的哪一部分相关非常有用。然后，这些值也会被ASH捕获。

（2）使用活动会话历史记录可以找到最近一小时内最活跃的会话

SELECT sql_id,COUNT(*),ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOAD

FROM gv$active_session_history

WHERE sample_time > SYSDATE - 1/24

AND session_type = 'BACKGROUND'

GROUP BY sql_id

ORDER BY COUNT(*) DESC;

SELECT sql_id,COUNT(*),ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOAD

FROM gv$active_session_history

WHERE sample_time > SYSDATE - 1/24

AND session_type = 'FOREGROUND'

GROUP BY sql_id

ORDER BY COUNT(*) DESC;

（3）找出此会话为其的oracle等待事件

SELECT sample_time, event, wait_time

FROM gv$active_session_history

WHERE session_id = &1

AND session_serial# = &2

（4） 最近一小时内最活跃的会话

SELECT sql_id,COUNT(*),ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOAD

FROM gv$active_session_history

WHERE sample_time > SYSDATE - 1/24

AND session_type = 'BACKGROUND'

GROUP BY sql_id

ORDER BY COUNT(*) DESC;SELECT sql_id,COUNT(*),ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOAD

FROM gv$active_session_history

WHERE sample_time > SYSDATE - 1/24

AND session_type = 'FOREGROUND'

GROUP BY sql_id

ORDER BY COUNT(*) DESC;

（5） 最近1小时内大多数I / O密集型sql

SELECT sql_id, COUNT(*)

FROM gv$active_session_history ash, gv$event_name evt

WHERE ash.sample_time > SYSDATE - 1/24

AND ash.session_state = 'WAITING'

AND ash.event_id = evt.event_id

AND evt.wait_class = 'User I/O'

GROUP BY sql_id

ORDER BY COUNT(*) DESC;

（6）最近1分钟内在Oracle中锁定信息

col event for a22

col block_type for a18

col objn for a18

col otype for a10

col fn for 99

col sid for 9999

col bsid for 9999

col lm for 99

col p3 for 99999

col blockn for 99999

select

to_char(sample_time,'HH:MI') st,

substr(event,0,20) event,

a.session_id sid,

mod(a.p1,16)  lm,

a.p2,

a.p3,

nvl(o.object_name,ash.current_obj#) objn,

substr(o.object_type,0,10) otype,

CURRENT_FILE# fn,

CURRENT_BLOCK# blockn,

a.SQL_ID,

BLOCKING_SESSION bsid

from v$active_session_history a,

all_objects o

where event like 'enq: TX%'

and o.object_id (+)= a.CURRENT_OBJ#

and sample_time > sysdate - 40/(60*24)

Order by sample_time

/

 

（7） 顶级sql在cpu / wait / io上花费更多

select

ash.SQL_ID ,

sum(decode(a.session_state,'ON CPU',1,0)) "CPU",

sum(decode(a.session_state,'WAITING',1,0)) -

sum(decode(a.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "WAIT" ,

sum(decode(a.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "IO" ,

sum(decode(a.session_state,'ON CPU',1,1)) "TOTAL"

from v$active_session_history a,v$event_name en

where SQL_ID is not NULL and en.event#=ash.event#

（8）特定会话的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 BETWEEN '&starttime' AND

'&endtime' AND

A.EVENT# = B.EVENT# AND

A.SESSION_ID= &sid AND

A.SQL_ID = C.SQL_ID

GROUP BY C.SQL_TEXT, B.NAME

（9）最近15分钟内在CPU上的最高会话

SELECT * FROM

(

SELECT s.username, s.module, s.sid, s.serial#, s.sql_id,count(*)

FROM v$active_session_history h, v$session s

WHERE h.session_id = s.sid

AND h.session_serial# = s.serial#

AND session_state= 'ON CPU' AND

sample_time > sysdate - interval '15' minute

GROUP BY s.username, s.module, s.sid, s.serial#,s.sql_id

ORDER BY count(*) desc

)

where rownum <= 10;

如何生成ASH报告

Oracle提供与AWR一样的标准脚本来分析ASM数据

我将解释如何从SQL * PLUS生成ASH（活动会话历史记录）报告。也可以使用企业管理器完成相同的操作。

脚本位置与其他标准脚本$ ORACLE_HOME / rdbms / admin /

SQL> @$ORACLE_HOME/rdbms/admin/ashrpt.sql

 

Current Instance

~~~~~~~~~~~~~~~~

 

DB Id DB Name Inst Num Instance

———– ———— ——– ————

848748484 TECHDB 1 TECHDB

 

Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

Enter ‘html’ for an HTML report, or ‘text’ for plain text

Defaults to ‘html’

Enter value for report_type: html

..

..

Defaults to -15 mins

Enter value for begin_time: 01/11/15 00:00:00

Report begin time specified: 01/11/15 00:00:00

 

Enter value for duration: 10

Report duration specified: 10

 

Using 11-Jan-15 00:00:00 as report begin time

Using 11-Jan-15 00:10:00 as report end time

 

Enter value for report_name: Example

 

Summary of All User Input

————————-

Format : HTML

DB Id : 848748484

Inst num : 1

Begin time : 11-Jan-15 00:00:00

End time : 11-Jan-15 00:10:00

Slot width : Default

Report targets : 0

Report name : Example

 

End of Report

</body></html>

Report written to Example

 

活动会话历史记录存档

活动会话历史记录每秒钟从数据库实例中收集有关活动会话的信息。根据数据库活动的不同，这将导致在ASH缓冲区内收集大量数据，但是由于ASH缓冲区是内存驻留结构，因此它只有有限的空间。另外，当实例发生故障时，实例的内存也随之消失。因此，Oracle数据库将信息从ASH缓冲区存档到数据库表中以使其持久化。在一个名为DBA_HIST_ACTIVE_SESS_HISTORY的视图中可以看到该已归档的表数据。如果在V$ACTIVE_SESSION_HISTORY视图中找不到数据，请在DBA_HIST_ACTIVE_SESS_HISTORY视图中进行检查