Oracle AWR中常用到的幾個SQL語句
原文來自：http://blog.itpub.net/29320885/viewspace-2129349/
分享幾個AWR指令碼中查詢資源佔有的SQL，更改想要的snapid就可以查相應時間段的資料庫效能資訊了
按執行時間查sql
分享幾個AWR指令碼中查詢資源佔有的SQL，更改想要的snapid就可以查相應時間段的資料庫效能資訊了
按執行時間查sql
select s.sql_id
, elapsed_time/1000000 elapsed_time
, cpu_time/1000000 cpu_time
, iowait_time/1000000 iowait_time
, gets
, reads
, rws
, clwait_time/1000000 clwait_time
, execs
, st.sql_text sqt
, elapsed_time/1000000 /decode(execs,0,null,execs) elpe
from
(select * from
( select sql_id
, sum(executions_delta) execs
, sum(buffer_gets_delta) gets
, sum(disk_reads_delta) reads
, sum(rows_processed_delta) rws
, sum(cpu_time_delta) cpu_time
, sum(elapsed_time_delta) elapsed_time
, sum(clwait_delta) clwait_time
, sum(iowait_delta) iowait_time
from dba_hist_sqlstat
where snap_id > 52370
and snap_id <= 52373
group by sql_id
order by sum(elapsed_time_delta) desc)
where rownum <= 20 ) s
, dba_hist_sqltext st
where st.sql_id = s.sql_id
order by elapsed_time desc, sql_id;
按CPU
select s.sql_id
, cpu_time/1000000 cpu_time
, elapsed_time/1000000 elapsed_time
, iowait_time/1000000 iowait_time
, gets
, reads
, rws
, clwait_time/1000000 clwait_time
, execs
, substr(regexp_replace(st.sql_text,'(\s)+',' '),1,50) sqt
, ' ' nl
, cpu_time/1000000/decode(execs,0,null,execs) cppe
, elapsed_time/1000000/decode(execs,0,null,execs) elpe
, iowait_time/1000000/decode(execs,0,null,execs) iope
, gets/decode(execs,0,null,execs) bpe
, reads/decode(execs,0,null,execs) rpe
, rws/decode(execs,0,null,execs) rwpe
, clwait_time/1000000/decode(execs,0,null,execs) clpe
, ' ' ep
, st.sql_text sqtn
from
(select * from
( select sql_id
, sum(executions_delta) execs
, sum(buffer_gets_delta) gets
, sum(disk_reads_delta) reads
, sum(rows_processed_delta) rws
, sum(cpu_time_delta) cpu_time
, sum(elapsed_time_delta) elapsed_time
, sum(iowait_delta) iowait_time
, sum(clwait_delta) clwait_time
from dba_hist_sqlstat
where snap_id > 52370
and snap_id <= 52373
group by sql_id
order by sum(cpu_time_delta) desc)
where rownum <= 10 ) s
, dba_hist_sqltext st
where st.sql_id = s.sql_id
order by cpu_time desc, sql_id;
按I/O排序
select s.sql_id
, iowait_time/1000000 iowait_time
, elapsed_time/1000000 elapsed_time
, cpu_time/1000000 cpu_time
, gets
, reads
, rws
, clwait_time/1000000 clwait_time
, execs
, substr(regexp_replace(st.sql_text,'(\s)+',' '),1,50) sqt
, ' ' nl
, iowait_time/1000000/decode(execs,0,null,execs) iope
, elapsed_time/1000000/decode(execs,0,null,execs) elpe
, cpu_time/1000000/decode(execs,0,null,execs) cppe
, gets/decode(execs,0,null,execs) bpe
, reads/decode(execs,0,null,execs) rpe
, rws/decode(execs,0,null,execs) rwpe
, clwait_time/1000000/decode(execs,0,null,execs) clpe
, ' ' ep
, substr(regexp_replace(st.sql_text,'(\s)+',' '),51,50) sqtn
from
(select * from
( select sql_id
, sum(executions_delta) execs
, sum(buffer_gets_delta) gets
, sum(disk_reads_delta) reads
, sum(rows_processed_delta) rws
, sum(cpu_time_delta) cpu_time
, sum(elapsed_time_delta) elapsed_time
, sum(iowait_delta) iowait_time
, sum(clwait_delta) clwait_time
from dba_hist_sqlstat
where snap_id > 52370
and snap_id <= 52373
group by sql_id
order by sum(iowait_delta) desc)
where rownum <= 20 ) s
, dba_hist_sqltext st
where st.sql_id = s.sql_id
order by iowait_time desc, reads desc, sql_id;
按gets
select s.sql_id
, gets
, reads
, elapsed_time/1000000 elapsed_time
, cpu_time/1000000 cpu_time
, iowait_time/1000000 iowait_time
, rws
, clwait_time/1000000 clwait_time
, execs
, substr(regexp_replace(st.sql_text,'(\s)+',' '),1,50) sqt
, ' ' nl
, gets/decode(execs,0,null,execs) bpe
, reads/decode(execs,0,null,execs) rpe
, elapsed_time/1000000/decode(execs,0,null,execs) elpe
, cpu_time/1000000/decode(execs,0,null,execs) cppe
, iowait_time/1000000/decode(execs,0,null,execs) iope
, rws/decode(execs,0,null,execs) rwpe
, clwait_time/1000000/decode(execs,0,null,execs) clpe
, ' ' ep
, substr(regexp_replace(st.sql_text,'(\s)+',' '),51,50) sqtn
from
(select * from
( select sql_id
, sum(executions_delta) execs
, sum(buffer_gets_delta) gets
, sum(disk_reads_delta) reads
, sum(rows_processed_delta) rws
, sum(cpu_time_delta) cpu_time
, sum(elapsed_time_delta) elapsed_time
, sum(iowait_delta) iowait_time
, sum(clwait_delta) clwait_time
from dba_hist_sqlstat
where snap_id > 52370
and snap_id <= 52373
group by sql_id
order by sum(buffer_gets_delta) desc)
where rownum <= 20 ) s
, dba_hist_sqltext st
where st.sql_id = s.sql_id
order by gets desc, cpu_time desc, sql_id;
按執行次數
select s.sql_id
, execs
, elapsed_time/1000000 elapsed_time
, cpu_time/1000000 cpu_time
, iowait_time/1000000 iowait_time
, gets
, reads
, rws
, clwait_time/1000000 clwait_time
, substr(regexp_replace(st.sql_text,'(\s)+',' '),1,50) sqt
, ' ' nl
, ' ' ep
, elapsed_time/1000000/decode(execs,0,null,execs) elpe
, cpu_time/1000000/decode(execs,0,null,execs) cppe
, iowait_time/1000000/decode(execs,0,null,execs) iope
, gets/decode(execs,0,null,execs) bpe
, reads/decode(execs,0,null,execs) rpe
, rws/decode(execs,0,null,execs) rwpe
, clwait_time/1000000/decode(execs,0,null,execs) clpe
, substr(regexp_replace(st.sql_text,'(\s)+',' '),51,50) sqtn
from
(select * from
( select sql_id
, sum(executions_delta) execs
, sum(buffer_gets_delta) gets
, sum(disk_reads_delta) reads
, sum(rows_processed_delta) rws
, sum(cpu_time_delta) cpu_time
, sum(elapsed_time_delta) elapsed_time
, sum(iowait_delta) iowait_time
, sum(clwait_delta) clwait_time
from dba_hist_sqlstat
where snap_id > 52370
and snap_id <= 52373
group by sql_id
order by sum(executions_delta) desc)
where rownum <= 20 ) s
, dba_hist_sqltext st
where st.sql_id = s.sql_id
order by execs desc, sql_id;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28211342/viewspace-2130235/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- awr報表中用到的幾個SQLSQL
- SQL中常見語句SQL
- MySQL中常用的SQL語句MySql
- sql server中常用語句SQLServer
- DB2 用到的基本SQL語句DB2SQL
- MySQL中常用SQL語句的編寫MySql
- 幾個定位、查詢session的sql語句SessionSQL
- oracle幾個比較有用的語句Oracle
- oracle的sql語句OracleSQL
- oracle sql語句OracleSQL
- 學用ORACLE AWR和ASH特性(7)-AWR的幾個幫Oracle
- ThinkPHP 列印 sql 語句的幾種方式PHPSQL
- Oracle基本SQL語句OracleSQL
- oracle常用SQL語句OracleSQL
- ORACLE很重要的sql語句OracleSQL
- 【AWR】通過AWR報告中記錄的 SQL Id獲得SQL語句的執行計劃SQL
- MongoDB中常用語句MongoDB
- Oracle一個SQL語句的處理過程（轉）OracleSQL
- SQL查詢語句 (Oracle)SQLOracle
- Oracle SQL語句分類OracleSQL
- oracle update語句的幾點寫法Oracle
- Oracle SQL精妙SQL語句講解OracleSQL
- Oracle 行轉列的sql語句OracleSQL
- Oracle sql with 語句語法與例子OracleSQL
- Oracle 查詢某個session正在執行的sql語句OracleSessionSQL
- Oracle釋出一個SQL語句的處理過程OracleSQL
- 如何使用awr_set_report_thresholds控制AWR報告裡的sql語句數量SQL
- 一個SQL語句的優化SQL優化
- SQL語句的4個階段SQL
- 工作中，我們經常用到哪些SQL語句呢？SQL
- 如何定位SQL語句在共享池裡用到了哪些chunksSQL
- 單條SQL語句實現複雜邏輯的幾個例子(1)SQL
- 單條SQL語句實現複雜邏輯的幾個例子(2)SQL
- 單條SQL語句實現複雜邏輯的幾個例子(3)SQL
- 單條SQL語句實現複雜邏輯的幾個例子(4)SQL
- 單條SQL語句實現複雜邏輯的幾個例子(5)SQL
- Oracle SQL精妙SQL語句講解（轉）OracleSQL
- Oracle之sql語句優化OracleSQL優化