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

刘文钊1

已于 2022-02-09 08:35:02 修改

阅读量447

点赞数

分类专栏： # 常用sql和技巧文章标签： sql 数据库 dba

于 2022-02-07 17:20:46 首次发布

原文链接：http://blog.itpub.net/29320885/viewspace-2129349/

版权

常用sql和技巧专栏收录该内容

26 篇文章 0 订阅

订阅专栏

分享几个AWR脚本中查询资源占有的SQL，更改想要的snapid就可以查相应时间段的数据库性能信息了

按执行时间查sql

SELECT s.Sql_Id
      ,Elapsed_Time / 1000000 Elapsed_Time --执行时间查sql        
      ,Cpu_Time / 1000000 Cpu_Time --cpu时间       
      ,Iowait_Time / 1000000 Iowait_Time --I/O时间       
      ,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--cpu时间  
                      ,SUM(Elapsed_Time_Delta) Elapsed_Time--执行时间查sql  
                      ,SUM(Clwait_Delta) Clwait_Time
                      ,SUM(Iowait_Delta) Iowait_Time--I/O时间   
                  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;

一天内的执行情况查询

SELECT s.Sql_Id
      ,Elapsed_Time / 1000000 Elapsed_Time --执行时间查sql        
      ,Cpu_Time / 1000000 Cpu_Time --cpu时间       
      ,Iowait_Time / 1000000 Iowait_Time --I/O时间       
      ,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 Dhs.Sql_Id
                      ,SUM(Dhs.Executions_Delta) Execs --执行次数
                      ,SUM(Dhs.Buffer_Gets_Delta) Gets --逻辑读
                      ,SUM(Dhs.Disk_Reads_Delta) Reads
                      ,SUM(Dhs.Rows_Processed_Delta) Rws
                      ,SUM(Dhs.Cpu_Time_Delta) Cpu_Time --cpu时间  
                      ,SUM(Dhs.Elapsed_Time_Delta) Elapsed_Time --执行时间查sql  
                      ,SUM(Dhs.Clwait_Delta) Clwait_Time
                      ,SUM(Dhs.Iowait_Delta) Iowait_Time --I/O时间   
                  FROM Dba_Hist_Sqlstat  Dhs
                      ,Dba_Hist_Snapshot Dhh
                 WHERE 1 = 1
                   AND Dhs.Snap_Id = Dhh.Snap_Id
                   --AND Extract(Hour FROM p.End_Interval_Time) BETWEEN 8 AND 16 --每天8点到16点
                   AND Dhh.End_Interval_Time BETWEEN SYSDATE - 1 AND SYSDATE
                /*and Snap_Id > 52370                      
                AND Snap_Id <= 52373*/
                 GROUP BY Sql_Id
                 ORDER BY SUM(Dhs.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;

按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;

刘文钊1

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Oracle AWR中常用到的几个SQL语句

分享几个AWR脚本中查询资源占有的SQL，更改想要的snapid就可以查相应时间段的数据库性能信息了按执行时间查sqlselect 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_
复制链接

扫一扫