V$ACTIVE_SESSION_HISTORY视图的使用

V$ACTIVE_SESSION_HISTORY 显示数据库中的采样会话活动。ASH每秒从v$session中取快照，存在V$ACTIVE_SESSION_HISTORY中，并收集所有活动会话的等待信息。若ASH数据被刷新到磁盘，则需要从DBA_HIS_ACTIVE_SESS_HISTORY视图中查询相关信息。

该视图是ASH的核心，用以记录活动SESSION的历史等待信息，每秒采样一次，这部分内容记录在内存中，期望值是记录一个小时的内容。

列名数据类型说明SAMPLE_IDNUMBER样本的IDSAMPLE_TIMETIMESTAMP(3)取样本的时间SESSION_IDNUMBER会话标识符; 映射到 V$SESSION.SIDSESSION_SERIAL#NUMBER会话序列号 (用于唯一标识一个会话的对象); 映射到 V$SESSION.SERIAL#USER_IDNUMBEROracle用户标识符; 映射到 V$SESSION.USER#SQL_IDVARCHAR2(13)

会话在取样时执行的 SQL 语句的 SQL 标识符

SQL_CHILD_NUMBERNUMBERChild number of the SQL statement that the session was executing at the time of samplingSQL_PLAN_HASH_VALUENUMBER

sql游标计划的数值表示形式。这所有会话样本的信息可能不可用。v$session不包含此信息。

FORCE_MATCHING_SIGNATURENUMBERThe signature used when the CURSOR_SHARING parameter is set to FORCESQL_OPCODENUMBERIndicates what phase of operation the SQL statement was in; maps to V$SESSION.COMMAND。 “V$SESSION” for information on interpreting this columnSERVICE_HASHNUMBERHash that identifies the Service; maps to V$ACTIVE_SERVICES.NAME_HASHSESSION_TYPEVARCHAR2(10)会话类型:

• FOREGROUND
• BACKGROUND

SESSION_STATEVARCHAR2(7)会话状态:

• WAITING
• ON CPU

QC_SESSION_IDNUMBER查询协调器的会话ID。This information is only available if the sampled session is a parallel query slave. For all other sessions, the value is 0.QC_INSTANCE_IDNUMBER查询协调器实例的ID。 This information is only available if the sampled session is a parallel query slave. For all other sessions, the value is 0.BLOCKING_SESSIONNUMBER阻塞会话的会话标识符。Populated only when the session was waiting for enqueues or a “buffer busy” wait. Maps toV$SESSION.BLOCKING_SESSION.BLOCKING_SESSION_STATUSVARCHAR2(11)阻塞会话的状态:

• VALID
• NO HOLDER
• GLOBAL
• NOT IN WAIT
• UNKNOWN

BLOCKING_SESSION_SERIAL#NUMBER阻塞会话的序列号EVENTVARCHAR2(64)If SESSION_STATE = WAITING, then the event for which the session was waiting for at the time of sampling.If SESSION_STATE = ON CPU, then this column will be NULL.See Also: “Oracle Wait Events”EVENT_IDNUMBERIdentifier of the resource or event for which the session is waiting or for which the session last waited. Interpretation is similar to that of theEVENT column.EVENT#NUMBERNumber of the resource or event for which the session is waiting or for which the session last waited. Interpretation is similar to that of theEVENT column.SEQ#NUMBER序列号唯一标识等待（增加每个等待）P1TEXTVARCHAR2(64)第一个附加参数的文本P1NUMBER第一个附加参数P2TEXTVARCHAR2(64)第二个参数的文本P2NUMBER第二个附加参数P3TEXTVARCHAR2(64)第三个附加参数的文本P3NUMBER第三个附加参数WAIT_CLASSVARCHAR2(64)Wait class name of the event for which the session was waiting at the time of sampling. Interpretation is similar to that of the EVENT column. Maps to V$SESSION.WAIT_CLASS.WAIT_CLASS_IDNUMBER等待的会话在等待的时间采样的事件的类标识符。Interpretation is similar to that of the EVENTcolumn. Maps to V$SESSION.WAIT_CLASS_ID.WAIT_TIMENUMBER0 if the session was waiting at the time of samplingTotal wait time for the event for which the session last waited if the session was on the CPU when sampledWhether or not WAIT_TIME = 0 is what is useful to find the SESSION_STATE at the time of sampling, rather than the actual value of WAIT_TIMEitself. Maps to V$SESSION.WAIT_TIME.TIME_WAITEDNUMBERIf SESSION_STATE = WAITING, then the time that the session actually spent waiting for that EVENT. This column is set for waits that were in progress at the time the sample was taken.If a wait event lasted for more than a second and was caught waiting in more than one session sample row, then the actual time spent waiting for that wait event will be populated in the last of those session sample rows. At any given time, this information will not be available for the latest session sample.XIDRAW(8)Transaction ID that the session was working on at the time of sampling. V$SESSION does not contain this information.CURRENT_OBJ#NUMBER对象ID的会话被引用的对象。此信息仅供如果会话在等待申请，集群，并发和用户I / O等待事件。映射到 V$SESSION.ROW_WAIT_OBJ#.CURRENT_FILE#NUMBERFile number of the file containing the block that the session is referencing. This information is only available if the session was waiting for Cluster, Concurrency, and User I/O wait events. Maps to V$SESSION.ROW_WAIT_FILE#.CURRENT_BLOCK#NUMBERID of the block that the session is referencing. This information is only available if the session was waiting for Cluster, Concurrency, and User I/O wait events. Maps to V$SESSION.ROW_WAIT_BLOCK#.PROGRAMVARCHAR2(48)操作系统程序的名称MODULEVARCHAR2(48)Name of the executing module when sampled, as set by the DBMS_APPLICATION_INFO.SET_MODULE procedureACTIONVARCHAR2(32)Name of the executing module when sampled, as set by the DBMS_APPLICATION_INFO.SET_ACTION procedureCLIENT_IDVARCHAR2(64)Client identifier of the session; maps to V$SESSION.CLIENT_IDENTIFIER

用法举例：查找最近一分钟内，最消耗CPU的sql语句

SELECT sql_id, count(*), round(count(*) / sum(count(*)) over(), 2) pctload
 FROM V$ACTIVE_SESSION_HISTORY
WHERE sample_time > sysdate – 1 / (24 * 60)
   AND session_type <> 'BACKGROUND’
   AND session_state = 'ON CPU’
GROUP BY sql_id
 ORDER BY count(*) desc;

用法举例：查找最近一分钟内，最消耗I/O的sql语句

SELECT ash.sql_id,count(*)
 FROM V$ACTIVE_SESSION_HISTORY ASH,V$EVENT_NAME EVT
WHERE ash.sample_time > sysdate -1/(24*60)
   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;

用法举例：查找最近一分钟内，最消耗CPU的session

SELECT session_id,count(*)
 FROM V$ACTIVE_SESSION_HISTORY
WHERE session_state = 'ON CPU’
   AND sample_time > sysdate -1/(24*60)
GROUP BY session_id
ORDER BY count(*) desc;

用法举例：查找最近一分钟内，最消耗资源的sql语句

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*60)
GROUP BY ash.sql_id
 ORDER BY sum(decode(ash.session_state,'ON CPU’,1,1)) desc;

用法举例：查找最近一分钟内，最消耗资源的session

SELECT ash.session_id,ash.session_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 en.event# = ash.event# and ash.sample_time > sysdate -1/(24*60)
GROUP BY ash.session_id,ash.user_id,ash.session_serial#,ash.program
 ORDER BY sum(decode(ash.session_state,'ON CPU’,1,1))