获取数据库30天内各个指标的TOP语句

最新推荐文章于 2024-10-24 08:55:16 发布

原创最新推荐文章于 2024-10-24 08:55:16 发布 · 3.5k 阅读

2 ·

CC 4.0 BY-SA版权

Oracle 管理专栏收录该内容

188 篇文章

订阅专栏

本文聚焦于从DBA_HIST_SQLSTAT历史数据中提取SQL性能关键指标，包括平均执行时间、CPU时间、缓冲获取次数、解析率、物理读取量、磁盘读取次数、总执行次数和程序执行时间，旨在快速识别一周内性能瓶颈的SQL语句，为数据库优化提供依据。

要查询短时间内的可以从v$sql 或者是v$sqlarea 如果要查询一周或者一个月内那么有可能在V$SQLAREA里找不到!下面是通过历史DBA_HIST_SQLSTAT里获得, 这个是通过快照方式保留下来的.

--执行时间最长的


WITH  BASTABLE AS 
(  
  SELECT DBMS_LOB.SUBSTR(SQL_TEXT,4000, 1 ) AS SQL_FULL_TEXT,
         DHST.SQL_ID,
         ROUND (X.ELAPSED_TIME / 1000000 / X.EXECUTIONS_DELTA, 3) AVG_ELAPSED_TIME_SEC,
         ROUND (X.CPU_TIME / 1000000 / X.EXECUTIONS_DELTA, 3) AVG_CPU_TIME_SEC, 
         ROUND (X.BUFFER_GETS_DELTA / X.EXECUTIONS_DELTA, 3) AVG_BUFFER_GETS,  
         ROUND (X.PARSE_CALLS_DELTA/X.EXECUTIONS_DELTA*100, 3) EXEC_PARSE_RATE,
         ROUND (X.PHYSICAL_READ_BYTES_DELTA/1024/X.EXECUTIONS_DELTA, 3) AVG_PHYSICAL_READ_KB, 
         ROUND (X.DISK_READS_DELTA / X.EXECUTIONS_DELTA, 3) AVG_DISK_READS,      
         EXECUTIONS_DELTA AS EXEC_TOTAL_NUM,DHST.COMMAND_TYPE,N.COMMAND_NAME        
    FROM DBA_HIST_SQLTEXT DHST, DBA_HIST_SQLCOMMAND_NAME    N,  
         (  
          SELECT DHSS.SQL_ID SQL_ID,
                 SUM (DHSS.CPU_TIME_DELTA) CPU_TIME,   
                 SUM (DHSS.ELAPSED_TIME_DELTA) ELAPSED_TIME,   
                 CASE SUM (DHSS.EXECUTIONS_DELTA) WHEN 0 THEN 1 ELSE SUM (DHSS.EXECUTIONS_DELTA) END AS EXECUTIONS_DELTA,
                 CASE SUM (DHSS.SORTS_DELTA) WHEN 0 THEN 1 ELSE SUM (DHSS.SORTS_DELTA) END AS SORTS_DELTA,     
                 CASE SUM (DHSS.FETCHES_DELTA) WHEN 0 THEN 1 ELSE SUM (DHSS.FETCHES_DELTA) END AS FETCHES_DELTA,
                 CASE SUM (DHSS.PARSE_CALLS_DELTA) WHEN 0 THEN 1 ELSE SUM (DHSS.PARSE_CALLS_DELTA) END AS PARSE_CALLS_DELTA,
                 CASE SUM (DHSS.DISK_READS_DELTA) WHEN 0 THEN 1 ELSE SUM (DHSS.DISK_READS_DELTA) END AS DISK_READS_DELTA,
                 CASE SUM (DHSS.BUFFER_GETS_DELTA) WHEN 0 THEN 1 ELSE SUM (DHSS.BUFFER_GETS_DELTA) END AS BUFFER_GETS_DELTA,
                 CASE SUM (DHSS.IOWAIT_DELTA) WHEN 0 THEN 1 ELSE SUM (DHSS.IOWAIT_DELTA) END AS IOWAIT_DELTA,
                 CASE SUM (DHSS.PHYSICAL_READ_BYTES_DELTA) WHEN 0 THEN 1 ELSE SUM (DHSS.PHYSICAL_READ_BYTES_DELTA) END AS PHYSICAL_READ_BYTES_DELTA                                                                                                                                                                                         
            FROM DBA_HIST_SQLSTAT DHSS 
           WHERE DHSS.SNAP_ID IN   
                (SELECT SNAP_ID   
                   FROM DBA_HIST_SNAPSHOT   
                  WHERE BEGIN_INTERVAL_TIME >= TRUNC(SYSDATE)-30
                    AND END_INTERVAL_TIME <TRUNC(SYSDATE)-0  
                ) 
           AND DHSS.PARSING_SCHEMA_NAME =UPPER('SHARK')                      
          GROUP BY DHSS.SQL_ID
          ) X 
   WHERE X.SQL_ID = DHST.SQL_ID   
     AND DHST.COMMAND_TYPE = N.COMMAND_TYPE 
)
SELECT * FROM 
(
SELECT SQL_FULL_TEXT,SQL_ID,EXEC_TOTAL_NUM, AVG_DISK_READS AS VALUE_S, 'AVG_DISK_READS' AS VALUES_TYPE 
FROM BASTABLE WHERE COMMAND_TYPE<>47  AND SQL_FULL_TEXT NOT LIKE '/* SQL A%' ORDER BY AVG_DISK_READS DESC ) WHERE ROWNUM <=5
UNION ALL 
SELECT * FROM 
(
SELECT SQL_FULL_TEXT,SQL_ID,EXEC_TOTAL_NUM, AVG_ELAPSED_TIME_SEC AS VALUE_S, 'AVG_ELAPSED_TIME_SEC' AS VALUES_TYPE 
FROM BASTABLE WHERE COMMAND_TYPE<>47  AND SQL_FULL_TEXT NOT LIKE '/* SQL A%'  ORDER BY AVG_ELAPSED_TIME_SEC DESC ) WHERE ROWNUM <=5
UNION ALL 
SELECT * FROM 
(
SELECT SQL_FULL_TEXT,SQL_ID,EXEC_TOTAL_NUM, AVG_CPU_TIME_SEC AS VALUE_S, 'AVG_CPU_TIME_SEC' AS VALUES_TYPE 
FROM BASTABLE WHERE COMMAND_TYPE<>47  AND SQL_FULL_TEXT NOT LIKE '/* SQL A%'  ORDER BY AVG_CPU_TIME_SEC DESC ) WHERE ROWNUM <=5
UNION ALL 
SELECT * FROM 
(
SELECT SQL_FULL_TEXT,SQL_ID,EXEC_TOTAL_NUM, AVG_BUFFER_GETS AS VALUE_S, 'AVG_BUFFER_GETS' AS VALUES_TYPE 
FROM BASTABLE WHERE COMMAND_TYPE<>47  AND SQL_FULL_TEXT NOT LIKE '/* SQL A%'  ORDER BY AVG_BUFFER_GETS DESC ) WHERE ROWNUM <=5
UNION ALL 
SELECT * FROM 
(
SELECT SQL_FULL_TEXT,SQL_ID,EXEC_TOTAL_NUM, EXEC_PARSE_RATE AS VALUE_S, 'EXEC_PARSE_RATE' AS VALUES_TYPE 
FROM BASTABLE WHERE COMMAND_TYPE<>47  AND SQL_FULL_TEXT NOT LIKE '/* SQL A%'  ORDER BY EXEC_PARSE_RATE DESC ) WHERE ROWNUM <=5
UNION ALL 
SELECT * FROM 
(
SELECT SQL_FULL_TEXT,SQL_ID,EXEC_TOTAL_NUM, AVG_PHYSICAL_READ_KB AS VALUE_S, 'AVG_PHYSICAL_READ_KB' AS VALUES_TYPE 
FROM BASTABLE WHERE COMMAND_TYPE<>47 ORDER BY AVG_PHYSICAL_READ_KB DESC ) WHERE ROWNUM <=5
UNION ALL 
SELECT * FROM 
(
SELECT SQL_FULL_TEXT,SQL_ID,EXEC_TOTAL_NUM, EXEC_TOTAL_NUM AS VALUE_S, 'EXEC_TOTAL_NUM' AS VALUES_TYPE 
FROM BASTABLE WHERE COMMAND_TYPE<>47  AND SQL_FULL_TEXT NOT LIKE '/* SQL A%'  ORDER BY EXEC_TOTAL_NUM DESC ) WHERE ROWNUM <=5
UNION ALL 
SELECT * FROM 
(
SELECT SQL_FULL_TEXT,SQL_ID,EXEC_TOTAL_NUM, AVG_ELAPSED_TIME_SEC AS VALUE_S, 'PROCEDURES_EXEC_TIME' AS VALUES_TYPE 
FROM BASTABLE WHERE COMMAND_TYPE=47  AND SQL_FULL_TEXT NOT LIKE '/* SQL A%'  ORDER BY AVG_ELAPSED_TIME_SEC DESC ) WHERE ROWNUM <=5;