How to quickly tell if your Oracle Database is running slow or not
If you are an experienced production database administrator, I am pretty sure that from time to time you have to hear “The problem is the database, it is running slow”.
If you are a junior DBA, trust me when I tell you that you will hear that more times and more frequently than you would wish to on your daily job.
My own experience tells me that the problem usually is not in the database itself; although almost the symptoms manifest inside it. Even so, you must always check because, who knows?
Try to think for a moment about the performance of the database in the same way as end users and developers usually think about the “end response time” (even though this is a huge simplification).
It does not matter how experienced a DBA you are. If you do not have metrics, baselines and suitable application-response-time thresholds, it will be hard to decide if the database is running slow or not (regardless whether the server or the database are experiencing a resource congestion).
So, we are going to start querying our global last-minute database response time:
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;
BEGIN_TIME END_TIME INST_ID Response Time (msecs)
----------------- ----------------- ------- ---------------------
19-MAY-2016 14:12 19-MAY-2016 14:13 1 .98
19-MAY-2016 14:12 19-MAY-2016 14:13 2 .93
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)
----------------- ----------------- ------- ------------------------- ------------------------- -------------------------
19-MAY-2016 13:12 19-MAY-2016 14:13 1 .00 1.02 1.55
19-MAY-2016 13:12 19-MAY-2016 14:13 2 .00 1.02 1.29
Now, you have your current database response time, and you need to compare it with the established thresholds or contrast it with the response time of other analysis windows. You will need to query a wide number of windows to understand if you are experiencing a deviation.
You can check the following views: DBA_HIST_SYSMETRIC_HISTORY and DBA_HIST_SYSMETRIC_SUMMARY.
V$SYSMETRIC_HISTORY flushes data over DBA_HIST_SYSMETRIC_HISTORY every hour, and V$SYSMETRIC_SUMMARY also flushes data over DBA_HIST_SYSMETRIC_SUMMARY hourly so, if you do not have baselines about your response time, you should contrast your current response time with old windows stored in the aforementioned views as follows (for practical purposes I will show only a fragment of the output instead of modifying the predicate):
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)
----------------- ----------------- ------- ---------------------
19-MAY-2016 13:58 19-MAY-2016 13:59 1 .99
19-MAY-2016 13:58 19-MAY-2016 13:59 2 1.08
19-MAY-2016 13:57 19-MAY-2016 13:58 1 .98
19-MAY-2016 13:57 19-MAY-2016 13:58 2 1.05
19-MAY-2016 13:56 19-MAY-2016 13:57 1 .87
19-MAY-2016 13:56 19-MAY-2016 13:57 2 1.04
19-MAY-2016 13:55 19-MAY-2016 13:56 1 .89
19-MAY-2016 13:55 19-MAY-2016 13:56 2 .89
19-MAY-2016 13:54 19-MAY-2016 13:55 1 .93
19-MAY-2016 13:54 19-MAY-2016 13:55 2 .97
19-MAY-2016 13:53 19-MAY-2016 13:54 1 1.23
19-MAY-2016 13:53 19-MAY-2016 13:54 2 1.29
19-MAY-2016 13:52 19-MAY-2016 13:53 1 .93
19-MAY-2016 13:52 19-MAY-2016 13:53 2 1.02
19-MAY-2016 13:51 19-MAY-2016 13:52 1 .98
19-MAY-2016 13:51 19-MAY-2016 13:52 2 .88
19-MAY-2016 13:50 19-MAY-2016 13:51 1 .89
19-MAY-2016 13:50 19-MAY-2016 13:51 2 .91
19-MAY-2016 13:49 19-MAY-2016 13:50 1 1.11
19-MAY-2016 13:49 19-MAY-2016 13:50 2 1.06
...
...
...
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;
BEGIN_TIME END_TIME INST_ID Min Response Time (msecs) Avg Response Time (msecs) Max Response Time (msecs)
----------------- ----------------- ------- ------------------------- ------------------------- -------------------------
19-MAY-2016 12:59 19-MAY-2016 13:59 1 .00 1.02 1.55
19-MAY-2016 12:59 19-MAY-2016 13:59 2 .00 1.01 1.29
19-MAY-2016 11:59 19-MAY-2016 12:59 1 .00 .84 1.26
19-MAY-2016 11:59 19-MAY-2016 12:59 2 .00 .88 1.18
19-MAY-2016 10:59 19-MAY-2016 11:59 1 .00 .78 1.05
19-MAY-2016 10:59 19-MAY-2016 11:59 2 .00 .87 1.19
19-MAY-2016 09:59 19-MAY-2016 10:59 1 .00 .84 1.08
19-MAY-2016 09:59 19-MAY-2016 10:59 2 .00 .92 1.27
19-MAY-2016 08:59 19-MAY-2016 09:59 1 .00 .77 1.01
19-MAY-2016 08:59 19-MAY-2016 09:59 2 .00 .85 1.17
19-MAY-2016 08:00 19-MAY-2016 08:59 1 .00 .81 1.87
19-MAY-2016 08:00 19-MAY-2016 08:59 2 .00 .84 1.11
19-MAY-2016 07:00 19-MAY-2016 08:00 1 .00 .66 .96
19-MAY-2016 07:00 19-MAY-2016 08:00 2 .00 .76 1.05
19-MAY-2016 06:00 19-MAY-2016 07:00 1 .00 .60 1.10
19-MAY-2016 06:00 19-MAY-2016 07:00 2 .00 .76 2.34
19-MAY-2016 05:00 19-MAY-2016 06:00 1 .00 .58 1.32
19-MAY-2016 05:00 19-MAY-2016 06:00 2 .00 .90 8.36
19-MAY-2016 04:00 19-MAY-2016 05:00 1 .00 .47 .70
19-MAY-2016 04:00 19-MAY-2016 05:00 2 .00 .55 .80
...
...
...
So, here you have a quick way to understand if your database is running slow or not. In this case, our production database is running within the expected response time. Obviously, if you are experiencing slowness in your database response time you should investigate the root reason and take the corrective actions depending on the case. And remember, each application over each implementation will have different response time requirements; your job will be to understand if the response time is satisfactory or not according to the particulars of each situation.
I hope you find this useful.
Credit- https://workwiththebest.intraway.com/blog-post/how-to-quickly-tell-if-your-oracle-database-is-running-slow-or-not/
No comments:
Post a Comment