V$ACTIVE_SESSION_HISTORY视图相关了解
发表于:2025-02-13 作者:千家信息网编辑
千家信息网最后更新 2025年02月13日,转载 V$ACTIVE_SESSION_HISTORY视图相关了解---http://blog.csdn.net/haibusuanyun/article/details/17959973V$ACTI
千家信息网最后更新 2025年02月13日V$ACTIVE_SESSION_HISTORY视图相关了解
--查找最近一分钟内,最消耗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));
转载 V$ACTIVE_SESSION_HISTORY视图相关了解---http://blog.csdn.net/haibusuanyun/article/details/17959973
V$ACTIVE_SESSION_HISTORY 显示数据库中的采样会话活动。ASH每秒从v$session中取快照,存在V$ACTIVE_SESSION_HISTORY中,并收集所有活动会话的等待信息。若ASH数据被刷新到磁盘,则需要从DBA_HIS_ACTIVE_SESS_HISTORY视图中查询相关信息。
该视图是ASH的核心,用以记录活动SESSION的历史等待信息,每秒采样一次,这部分内容记录在内存中,期望值是记录一个小时的内容。
列名 | 数据类型 | 说明 |
---|---|---|
SAMPLE_ID | NUMBER | 样本的ID |
SAMPLE_TIME | TIMESTAMP(3) | 取样本的时间 |
SESSION_ID | NUMBER | 会话标识符; 映射到 V$SESSION.SID |
SESSION_SERIAL# | NUMBER | 会话序列号 (用于唯一标识一个会话的对象); 映射到 V$SESSION.SERIAL# |
USER_ID | NUMBER | Oracle用户标识符; 映射到 V$SESSION.USER# |
SQL_ID | VARCHAR2(13) | 会话在取样时执行的 SQL 语句的 SQL 标识符 |
SQL_CHILD_NUMBER | NUMBER | Child number of the SQL statement that the session was executing at the time of sampling |
SQL_PLAN_HASH_VALUE | NUMBER | sql游标计划的数值表示形式。这所有会话样本的信息可能不可用。v$session不包含此信息。 |
FORCE_MATCHING_SIGNATURE | NUMBER | The signature used when the CURSOR_SHARING parameter is set to FORCE |
SQL_OPCODE | NUMBER | Indicates what phase of operation the SQL statement was in; maps to V$SESSION.COMMAND。 "V$SESSION" for information on interpreting this column |
SERVICE_HASH | NUMBER | Hash that identifies the Service; maps to V$ACTIVE_SERVICES.NAME_HASH |
SESSION_TYPE | VARCHAR2(10) | 会话类型:
|
SESSION_STATE | VARCHAR2(7) | 会话状态:
|
QC_SESSION_ID | NUMBER | 查询协调器的会话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_ID | NUMBER | 查询协调器实例的ID。 This information is only available if the sampled session is a parallel query slave. For all other sessions, the value is 0. |
BLOCKING_SESSION | NUMBER | 阻塞会话的会话标识符。Populated only when the session was waiting for enqueues or a "buffer busy" wait. Maps toV$SESSION.BLOCKING_SESSION. |
BLOCKING_SESSION_STATUS | VARCHAR2(11) | 阻塞会话的状态:
|
BLOCKING_SESSION_SERIAL# | NUMBER | 阻塞会话的序列号 |
EVENT | VARCHAR2(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_ID | NUMBER | Identifier 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# | NUMBER | Number 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 | 序列号唯一标识等待(增加每个等待) |
P1TEXT | VARCHAR2(64) | 第一个附加参数的文本 |
P1 | NUMBER | 第一个附加参数 |
P2TEXT | VARCHAR2(64) | 第二个参数的文本 |
P2 | NUMBER | 第二个附加参数 |
P3TEXT | VARCHAR2(64) | 第三个附加参数的文本 |
P3 | NUMBER | 第三个附加参数 |
WAIT_CLASS | VARCHAR2(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_ID | NUMBER | 等待的会话在等待的时间采样的事件的类标识符。Interpretation is similar to that of the EVENTcolumn. Maps to V$SESSION.WAIT_CLASS_ID. |
WAIT_TIME | NUMBER | 0 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_WAITED | NUMBER | If 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. |
XID | RAW(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# | NUMBER | File 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# | NUMBER | ID 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#. |
PROGRAM | VARCHAR2(48) | 操作系统程序的名称 |
MODULE | VARCHAR2(48) | Name of the executing module when sampled, as set by the DBMS_APPLICATION_INFO.SET_MODULE procedure |
ACTION | VARCHAR2(32) | Name of the executing module when sampled, as set by the DBMS_APPLICATION_INFO.SET_ACTION procedure |
CLIENT_ID | VARCHAR2(64) | Client identifier of the session; maps to V$SESSION.CLIENT_IDENTIFIER |
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));
标识
参数
标识符
消耗
附加
信息
语句
视图
对象
序列
序列号
数据
文本
查询
活动
阻塞
三个
事件
内容
时间
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
对网络安全课程的思考
提高网络安全意识的视频
数据库原理及应用看不懂
净网2019网络安全宣传周
网络安全的体系构建包括什么
网络安全的小
浙江互联网软件开发需要多少钱
局域网网络安全与维护报告
老骆驼信息网络安全
宜兴进口软件开发口碑推荐
怎么才能连接到一个服务器上
软件开发职称
河津市启动网络安全法集中宣传周
历史数据库恢复
软件开发需要配多少钱的电脑
四川教育软件开发
数据库管理系统产品
数据库dmp
360安全卫士安装服务器
连入斐讯共享共享网络安全吗
有关网络安全的稿子
创建数据库校对规则
广州施工项目管控软件开发平台
erp软件开发软件哪家好
数据库中课程表下教室的简称
奉贤区通信网络安全防护咨询
华为三层设置WDS服务器
网络安全分层保护制度
网络安全法律法规宣贯方案
网络安全危害防范防方法