如何快速判断Oracle数据库是否运行缓慢

查看过去一分钟数据库的响应时间

SET LINESIZE 200 PAGESIZE 50000
COL BEGIN_TIME FORMAT A17
COL END_TIME FORMAT A17
COL INST_ID FORMAT 999
COL "Response Time (msecs)" FORMAT 999,999,999,999.99

  SELECT TO_CHAR (BEGIN_TIME, 'DD-MON-YYYY HH24:MI') BEGIN_TIME,
         TO_CHAR (END_TIME, 'DD-MON-YYYY HH24:MI') END_TIME,
         INST_ID,
         ROUND (VALUE * 10, 2) "Response Time (msecs)"
    FROM GV$SYSMETRIC
   WHERE     1 = 1
         AND METRIC_NAME = 'SQL Service Response Time'
ORDER BY INST_ID;

查看过去一分钟数据库的最小、最大、平均的响应时间

SET LINESIZE 200 PAGESIZE 50000
COL BEGIN_TIME FORMAT A17
COL END_TIME FORMAT A17
COL INST_ID FORMAT 999
COL "Min Response Time (msecs)" FORMAT 999,999,999,999.99
COL "Avg Response Time (msecs)" FORMAT 999,999,999,999.99
COL "Max Response Time (msecs)" FORMAT 999,999,999,999.99

  SELECT TO_CHAR (BEGIN_TIME, 'DD-MON-YYYY HH24:MI') BEGIN_TIME,
         TO_CHAR (END_TIME, 'DD-MON-YYYY HH24:MI') END_TIME,
         INST_ID,
         ROUND (MINVAL * 10, 2) "Min Response Time (msecs)",
         ROUND (AVERAGE * 10, 2) "Avg Response Time (msecs)",
         ROUND (MAXVAL * 10, 2) "Max Response Time (msecs)"
    FROM GV$SYSMETRIC_SUMMARY
   WHERE     1 = 1
         AND METRIC_NAME = 'SQL Service Response Time'
ORDER BY INST_ID;

现在就有了数据库当前的响应时间。接下来需要做的就是和已经建立的阈值或其它窗口的响应时间做比较。可能需要查看多个时间窗口，来确认是否有响应问题。

可以检查以下视图： dba_hist_sysmetric_history 和 dba_hist_sysmetric_summary。

v$sysmetric_history每小时刷新一次dba_hist_sysmetric_history上的数据，v$sysmetric_summary也每小时刷新一次dba_hist_sysmetric_summary上的数据，因此，如果你没有关于响应时间的基线，应该将当前的响应时间与存储在上述视图中的旧窗口进行对比，如下所示（出于实用目的，我将只显示输出的一个片段，而不是修改谓词）：

SET LINESIZE 200 PAGESIZE 50000
COL BEGIN_TIME FORMAT A17
COL END_TIME FORMAT A17
COL INST_ID FORMAT 999
COL "Response Time (msecs)" FORMAT 999,999,999,999.99

  SELECT TO_CHAR (BEGIN_TIME, 'DD-MON-YYYY HH24:MI') BEGIN_TIME,
         TO_CHAR (END_TIME, 'DD-MON-YYYY HH24:MI') END_TIME,
         INSTANCE_NUMBER INST_ID,
         ROUND (VALUE * 10, 2) "Response Time (msecs)"
    FROM DBA_HIST_SYSMETRIC_HISTORY
   WHERE 1 = 1 AND METRIC_NAME = 'SQL Service Response Time'
ORDER BY BEGIN_TIME DESC, INSTANCE_NUMBER;


SET LINESIZE 200 PAGESIZE 50000
COL BEGIN_TIME FORMAT A17
COL END_TIME FORMAT A17
COL INST_ID FORMAT 999
COL "Min Response Time (msecs)" FORMAT 999,999,999,999.99
COL "Avg Response Time (msecs)" FORMAT 999,999,999,999.99
COL "Max Response Time (msecs)" FORMAT 999,999,999,999.99

  SELECT TO_CHAR (BEGIN_TIME, 'DD-MON-YYYY HH24:MI') BEGIN_TIME,
         TO_CHAR (END_TIME, 'DD-MON-YYYY HH24:MI') END_TIME,
         INSTANCE_NUMBER INST_ID,
         ROUND (MINVAL * 10, 2) "Min Response Time (msecs)",
         ROUND (AVERAGE * 10, 2) "Avg Response Time (msecs)",
         ROUND (MAXVAL * 10, 2) "Max Response Time (msecs)"
    FROM DBA_HIST_SYSMETRIC_SUMMARY
   WHERE 1 = 1 AND METRIC_NAME = 'SQL Service Response Time'
ORDER BY BEGIN_TIME DESC, INSTANCE_NUMBER;

这样就有了一个快速了解数据库是否运行缓慢的方法。在这种情况下，我们的生产数据库在预期的响应时间内运行。显然，如果遇到数据库响应时间缓慢的问题，应该调查根本原因，并根据具体情况采取纠正措施。
请记住，每个应用和每种实现都会有不同的响应时间要求；你的工作是根据每种情况的具体细节来了解响应时间是否令人满意。