v$active_session_history檢視[轉]
http://asmboy001.blog.51cto.com/340398/111240
指令碼摘自《教你如何成為oracle 10g OCP》
查詢最近一分鐘內,最消耗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))
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))
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/24570973/viewspace-753012/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- v$active_session_history檢視Session
- 轉載--V$ACTIVE_SESSION_HISTORY檢視的使用Session
- V$ACTIVE_SESSION_HISTORY檢視的使用Session
- V$ACTIVE_SESSION_HISTORY 檢視中包含的資訊Session
- [20211214]檢視檢視V$ACTIVE_SESSION_HISTORY遇到奇怪問題.txtSession
- 11G 中的V$ACTIVE_SESSION_HISTORY 檢視沒有資料Session
- 自己總結的V$檢視(轉)
- v$sql檢視和v$sqlarea檢視的構建SQL
- v$session 檢視Session
- [20130125]利用v$active_session_history檢視解決資料庫問題.txtSession資料庫
- 幾個檢視 v$mystat v$systata v$sessionSession
- (轉)Oracle動態效能檢視學習之v$processOracle
- Oracle動態效能檢視學習之 V$ROLLSTAT -- 轉Oracle
- 【會話】V$SESSION檢視會話Session
- oracle v$sysstat效能檢視Oracle
- Oracle常用的V$檢視Oracle
- Oracle 等待事件V$檢視Oracle事件
- v$resource_limit檢視MIT
- v檢視的授權
- v$sesstat,v$mystat,v$statname和v$sysstat檢視簡介
- 11g v$active_session_history的新增列Session
- 【Oracle九大效能檢視】之6.v$process檢視Oracle
- 使用V$SQL_PLAN檢視SQL
- V$SQLAREA 檢視TOP_SQLSQL
- Oracle中效能檢視V$SYSSTATOracle
- v$sql_plan 檢視解析SQL
- oracle V$PGASTAT檢視記錄OracleAST
- V$PGASTAT動態效能檢視AST
- 程式檢視(轉)
- 授權某使用者,檢視動態效能檢視的許可權(如v$latch,v$lock,v$sqlarea,v$sql,v$sysstat)SQL
- v$sqlarea,v$sql,v$sqltext三個檢視的區別SQL
- 【轉載】使用檢視V$RESERVED_WORDS得到Oracle的保留字Oracle
- Oracle的V$檢視和DBA_檢視的參考提示Oracle
- Oracle的v$動態檢視 收藏Oracle
- V$ASM_DISK 檢視含義ASM
- V$session 檢視的小運用Session
- 效能檢視 V$Session_LONGOPSSessionGo
- (轉)Oracle動態效能檢視學習之V$DB_OBJECT_CACHEOracleObject