【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 AWROracle
- 【AWR】DBA_HIST檢視檢視儲存在AWR中的歷史資料
- 【AWR】Oracle資料庫建立awr基線Oracle資料庫
- 【AWR】Oracle批量生成awr報告指令碼Oracle指令碼
- [轉]Oracle資料庫ASH和AWR的簡單介紹Oracle資料庫
- oracle工具 awr formatOracleORM
- Oracle 11g關閉開啟AWROracle
- oracle之 AWR固定基線Oracle
- [20230220][20230110]生成相關備庫的awr報表
- oracle 10G特性之awrOracle 10g
- Oracle 客戶端生成AWR方法Oracle客戶端
- Oracle生成awr報告操作步驟Oracle
- Oracle相關資料字典檢視Oracle
- 19 Oracle Data Guard 相關檢視Oracle
- oracle awr快照點不記錄問題Oracle
- Oracle AWR無法生成快照(ORA-32701)Oracle
- 講講AWR
- [20230303]生成相關備庫的awr報表(補充說明).txt
- 【手摸手玩轉 OceanBase 173】清理備份相關檢視介紹
- Oracle 12.2 physical standby備庫收集AWR報告Oracle
- 詳解Oracle AWR執行日誌分析工具Oracle
- 【最佳化】AWR
- 【手摸手玩轉 OceanBase 160】日誌歸檔相關檢視介紹
- 【手摸手玩轉 OceanBase 167】資料備份相關檢視介紹
- ArrayList相關方法介紹及原始碼分析原始碼
- Oracle 11.2.0.4 awr過期快照無法自動清理Oracle
- 9. Oracle常用分析診斷工具——9.1. AWROracle
- 【效能調優】Oracle AWR報告指標全解析Oracle指標
- Nginx 相關介紹Nginx
- 基線,移動視窗,AWR學習心得
- AWR TOP SQL實現SQL
- MySQL檢視介紹MySql
- 檢視:dba_hist_wr_control查詢到兩套庫的awr保留策略
- 以太坊:Dapp及相關開發工具介紹APP
- 【BUG】Oracle12c tablespace io statistics missing from awr reportOracle
- awr-----一份經典的負載很高的awr報告負載
- linux使用者及組相關檔案介紹Linux
- ORACLE AWR效能報告和ASH效能報告的解讀Oracle