【AWR】Oracle awr相關檢視及體系介紹
Oracle 資料庫中的awr及ash 為 dba提供了非常便利的條件,來分析資料庫相關效能問題,下面主要介紹awr相關資訊。
-
awr主要結構
2.awr相關檢視
相關檢視還有一個 dba_hist_snapshot ,來輔助查詢。相關檢視主要列介紹:
3.ASH
4.AWR 相關指令碼
5.相關sql語句舉例
--Wait events 5min set lines 200 pages 999 col event for a32 select * from ( select sql_id,inst_id, sum(decode(vash.session_state,'ON CPU',1,0)) as "ON CPU", sum(decode(vash.session_state,'WAITING',1,0)) as "WAITING ON CPU", event,count(distinct(session_id||session_serial#)) as "SESSION COUNT" from gv$active_session_history vash where sample_time > sysdate -10/(60*24) group by event,inst_id,sql_id order by 4 desc ) where rownum<=20; --query sql_id dbtime ,last 10 minutes select * from ( select nvl(sql_id,'null') as sql_id,sum(1) as "DBtime in Seconds" from v$active_session_history where sample_time > sysdate-10/(24*60) group by sql_id order by 2 desc ) where rownum<=11; -- cpu usage info ,last 10 minutes select * from ( select sql_id,inst_id, sum(decode(vash.session_state,'ON CPU',1,0)) as "Number on CPU", sum(decode(vash.session_state,'WAITING',1,0)) as "Number WAITING ON CPU" from gv$active_session_history vash where sample_time > sysdate -10/(60*24) group by sql_id,inst_id order by 3 desc ) where rownum<=20; --每天9點 dbtime對比 set linesize 200 set pagesize 20000 col DATE_TIME for a20 col STAT_NAME for a10 col dbtime_value1 for a10 col dbtime_value2 for a10 col dbtime_value3 for a10 WITH sysstat AS ( SELECT sn.begin_interval_time begin_interval_time, sn.end_interval_time end_interval_time, ss.VALUE e_value, ss.instance_number instance_number, lag(ss.VALUE, 1) over (ORDER BY ss.snap_id) b_value FROM DBA_HIST_SYS_TIME_MODEL ss, dba_hist_snapshot sn WHERE to_char(sn.end_interval_time,'hh24')='09' --每天9-10點 dbtime AND ss.snap_id = sn.snap_id AND ss.dbid = sn.dbid AND ss.instance_number = sn.instance_number AND ss.dbid = (SELECT dbid FROM v$database) AND ss.stat_name = 'DB time' ) select date_time,listagg(dbtime_value1) within group (order by date_time) dbtime_1, listagg(dbtime_value2) within group (order by date_time) dbtime_2, listagg(dbtime_value3) within group (order by date_time) dbtime_3 from ( select to_char(end_interval_time,'yyyy-mm-dd hh24:mi') date_time, CASE WHEN instance_number=1 then round((e_value - nvl(b_value, 0))/60/1000/1000 ,2) end as dbtime_value1, CASE WHEN instance_number=2 then round((e_value - nvl(b_value, 0))/60/1000/1000 ,2) end as dbtime_value2, CASE WHEN instance_number=3 then round((e_value - nvl(b_value, 0))/60/1000/1000 ,2) end as dbtime_value3 FROM sysstat WHERE (e_value - nvl(b_value, 0)) > 0 AND nvl (b_value, 0) > 0 order by 1 ) group by date_time;
參考:
Using Automatic Workload Repository for Database Tuning: Tips for Expert DBAs
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29487349/viewspace-2841583/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle AWR 配置檢視Oracle
- oracle awr相關知識Oracle
- Oracle 11g AWR 系列六:使用 AWR 檢視Oracle
- Oracle AWR 介紹及報告分析(2) finalOracle
- Oracle AWR 介紹及報告分析(1) finalOracle
- oracle 10g AWR介紹(ZT)Oracle 10g
- 【轉】ORACLE 10G AWR 相關Oracle 10g
- 轉載詳細的Oracle ASH/AWR介紹及報告分析Oracle
- 【AWR】DBA_HIST檢視檢視儲存在AWR中的歷史資料
- Oracle中自動工作負載資訊庫(AWR)介紹Oracle負載
- Oracle10g的AWR效能優化工具介紹Oracle優化
- AWR簡介
- ORACLE AWROracle
- Oracle AWR ---Oracle
- oracle awrOracle
- awr 中單個SQL 效能檢視SQL
- ORACLE 檢視介紹Oracle
- [轉]Oracle資料庫ASH和AWR的簡單介紹Oracle資料庫
- Oracle中自動工作負載資訊庫(AWR)介紹(2)Oracle負載
- Oracle10g 中AWR相關的兩個新後臺程式MMON及MMNLOracle
- oracle awr ashOracle
- Oracle AWR速查Oracle
- oracle,metric,awrOracle
- 【Oracle】AWR analyseOracle
- Awr Report Memory Advisory分析介紹
- 【AWR】Oracle資料庫建立awr基線Oracle資料庫
- 【AWR】Oracle批量生成awr報告指令碼Oracle指令碼
- oracle Metrics相關檢視Oracle
- 【Oracle】-【AWR/Stackpack】-AWR(Stackpack)執行許可權Oracle
- oracle工具 awr formatOracleORM
- 轉載oracle awrOracle
- EJBCA認證系統結構及相關介紹
- Oracle 10g,AWR,AWR,ADDM最佳實踐Oracle 10g
- Oracle 11g關閉開啟AWROracle
- 檢視oracle鎖相關資訊Oracle
- oracle goldengate 相關概念介紹OracleGo
- oracle資料庫dba_hist等檢視中的Delta相關欄位介紹Oracle資料庫
- Oracle 補丁體系 及 opatch 工具 介紹Oracle