check Oracle database slow SQL Response

SET LINESIZE 200 PAGESIZE 50000

COL INST_ID FORMAT 999

COL "Response_Time (msecs)" FORMAT 999,999,999,999.99

COL BEGIN_TIME FORMAT A17
COL END_TIME FORMAT A17

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;

BEGIN_TIME        END_TIME          INST_ID Response_Time (msecs)                                                                                                                                       
----------------- ----------------- ------- ---------------------                                                                                                                                       

15-JUL-2023 01:05 15-JUL-2023 01:06       1                   .44
15-JUL-2023 01:04 15-JUL-2023 01:05       1                   .49
15-JUL-2023 01:03 15-JUL-2023 01:04       1                   .59
15-JUL-2023 01:02 15-JUL-2023 01:03       1                   .52
15-JUL-2023 01:01 15-JUL-2023 01:02       1                   .45
15-JUL-2023 01:00 15-JUL-2023 01:01       1                  1.07
15-JUL-2023 00:59 15-JUL-2023 01:00       1                   .58

You can also query for the minimum, average and maximum response time for the last minute:

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;

BEGIN_TIME        END_TIME          INST_ID Min Response Time (msecs) Avg Response Time (msecs) Max Response Time (msecs)
----------------- ----------------- ------- ------------------------- ------------------------- -------------------------
30-JUL-2023 20:12 30-JUL-2023 21:13       1                       .00                     17.65                     40.38

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;

BEGIN_TIME        END_TIME          INST_ID Response Time (msecs)                                                                                                                                       
----------------- ----------------- ------- --------------------- 
15-JUL-2023 01:03 15-JUL-2023 01:04       1                   .59
15-JUL-2023 01:02 15-JUL-2023 01:03       1                   .52
15-JUL-2023 01:01 15-JUL-2023 01:02       1                   .45
15-JUL-2023 01:00 15-JUL-2023 01:01       1                  1.07
15-JUL-2023 00:59 15-JUL-2023 01:00       1                   .58

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;