DBA_HIST_ACTIVE_SESS_HISTORY應用
--檢視event事件
select event,count(*) cnt from DBA_HIST_ACTIVE_SESS_HISTORY where wait_class<>'Idle' and sample_time<sysdate-1
group by event order by cnt ;
--檢視某時間段內的sql
SET PAUSE ON SET PAUSE 'Press Return To Continue' SET HEADING ON SET LINESIZE 300 SET PAGESIZE 60 COLUMN Sample_Time FOR A12 COLUMN username FOR A20 COLUMN sql_text FOR A40 COLUMN program FOR A40 COLUMN module FOR A40 SELECT sample_time, u.username, h.program, h.module, s.sql_text FROM DBA_HIST_ACTIVE_SESS_HISTORY h, DBA_USERS u, DBA_HIST_SQLTEXT s WHERE sample_time BETWEEN '&s_time' and '&e_time' --AND INSTANCE_NUMBER=&inst_no AND h.user_id=u.user_id AND h.sql_id = s.sql_iD ORDER BY 1
--top sql
col type for a10 col "CPU" for 999999.9 col "IO" for 999999.9 select * from ( select ash.SQL_ID , ash.SQL_PLAN_HASH_VALUE Plan_hash, aud.name type, 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(wait_class, 'User I/O',1,0),0)) "WAIT" , sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) "IO" , sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL" from dba_hist_active_sess_history ash, audit_actions aud where SQL_ID is not NULL and ash.sql_opcode=aud.action and ash.sample_time > sysdate - &minutes /( 60*24) group by sql_id, SQL_PLAN_HASH_VALUE , aud.name order by sum(decode(session_state,'ON CPU',1,1)) desc ) where rownum < 10 /
--io size
col event for a25
select event,round(min(p3)) mn,
round(avg(p3)) av,
round(max(p3)) mx,
count(*) cnt
from dba_hist_active_sess_history
--from v$active_session_history
where (event like 'db file%' or event like 'direct %')
and event not like '%parallel%'
group by event
order by event
/
--查詢2011-12-28日對dj_sz表的update語句。
select to_char(b.begin_interval_time,'yyyy-mm-dd hh24:mi:ss') update_time,c.sql_text sql_text
from dba_hist_active_sess_history a,DBA_HIST_SNAPSHOT b,v$sqlarea c
where a.snap_id=b.snap_id
and a.sql_id=c.sql_id
and b.begin_interval_time>=to_date('2011-12-28','yyyy-mm-dd') and b.begin_interval_time < to_date('2011-12-29','yyyy-mm-dd')
and lower(c.sql_text) like '%update%dj_sz%'
order by update_time
select * from DBA_HIST_ACTIVE_SESS_HISTORY where sample_time between to_date('18-APR-2012 10:40:00','DD-MON-YYYY HH24:MI:SS') and to_date('18-APR-2012 10:40:10','DD-MON-YYYY HH24:MI:SS') and instance_number in (1,12) order by sample_time,instance_number,SESSION_ID;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29337971/viewspace-1816635/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 查詢DBA_HIST_ACTIVE_SESS_HISTORY緩慢
- 加快dba_hist_active_sess_history的查詢速度
- 巧用dba_hist_active_sess_history跟蹤某個時間段內SQLSQL
- 從dba_hist_active_sess_history裡尋找session間的等待關係Session
- 微信小程式、流應用、原生應用app、輕應用微信小程式APP
- 如何透過dba_hist_active_sess_history分析歷史資料庫效能問題資料庫
- 應用
- 使用汽車應用庫構建應用
- 【DBA】如何通過dba_hist_active_sess_history分析資料庫歷史效能問題資料庫
- 【ASH】如何匯出檢視DBA_HIST_ACTIVE_SESS_HISTORY的查詢結果資料
- 一文搞懂SaaS應用架構:應用服務、應用結構、應用互動設計應用架構
- [應用案例]OT應用案例之dasdig
- SAP BTP MTA 應用的應用場景
- Android應用及應用管理簡介Android
- 《反應式應用開發》之“什麼是反應式應用”
- DOCKER應用Docker
- redis應用Redis
- Canvas應用Canvas
- iptables應用
- 應用安全
- sed 應用
- goFlag應用Go
- 應用元件元件
- Web應用Web
- ArryList應用
- erlang應用
- 用 Docker 建立 serverless 應用DockerServer
- 2.3 應用程式容器中的應用程式概述
- 單頁面應用和多頁面應用
- Websphere安裝、企業部署應用 【應用案例】Web
- 應用程式無響應?如何在Mac上強制退出應用Mac
- win10應用商店應用無法安裝怎麼解決_win10應用商店應用安裝沒反應處理方法Win10
- 應用試客打造611“應用狂歡節”助力應用登頂Top1
- golang RPC 應用(1) :net/rpc的應用GolangRPC
- DataGuard之Apply Services(redo應用和SQL應用)APPSQL
- Windbg在應用層除錯漏洞時的應用除錯
- 關於騰訊應用管理中心,認領應用
- 基於應用程度的系統應用優化優化