Oracle awr sqlid,Oracle AWR中常用到的几个SQL语句

原文来自：http://blog.itpub.net/29320885/viewspace-2129349/

分享几个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;