Как использовать методы наименьших квадратов для оценки ресурсов и мониторинга баз Oracle

В повседневной деятельности администраторам баз данных Oracle требуется отслеживать существенное количество метрик в части пропускной способности, нагрузки, роста СУБД, хостов СУБД, дисковых подсистем и т.д. Все эти данные бережно собираются и складируются системами мониторинга. Чтобы информация не лежала мертвым грузом, ее можно использовать для прогнозирования в части вопросов сайзинга и даже проактивного мониторинга. В этом посте мы продемонстрируем как это делать.

Возьмем Oracle Cloud Control. В схеме SYSMAN в представлении MGMT_METRICS_1DAY у нас хранится значение метрики «Filesystem Space Available (MB)» для некоего хоста с TARGET_GUID = '6B1E3AFA92B3EA29AD73BB87432C084C'.
 

SELECT TO_CHAR (CAST (ROLLUP_TIMESTAMP AS DATE), 'YYYY-MM-DD"T"HH24:MI:SS'),
         ROUND (VALUE_AVERAGE, 2) AVG_FREE_SPACE_INM
    FROM SYSMAN.MGMT_METRICS_1DAY
   WHERE 	
TARGET_GUID = '6B1E3AFA92B3EA29AD73BB87432C084C'
         AND METRIC_GUID = HEXTORAW ('E8838C71E687BF0A9E02FFACC0C9AC80')
         AND KEY_VALUE = '/u10'
ORDER BY ROLLUP_TIMESTAMP;

График ниже показывает, что за три месяца, с 14 августа 2018 года по 15 ноября 2018 года, свободное место в файловой системе /u01 уменьшилось  на 1.5TB — с 4,1 ТБ до 2,6 ТБ

Для определения времени, на которое нам хватит оставшихся 2,6 ТБ, обратимся к функциям линейной регрессии.

В Oracle для вычисления наклона линии регрессии используется функция REGR_SLOPE(x,y). Функция наклона линии регрессии REGR_SLOPE определяется отношением ковариации множеств x и y к дисперсии множества y:

REGR_SLOPE(x,y) = COVAR_POP(x,y) / VAR_POP(y)

Для вычисления перехвата оси Y используется функция REGR_INTERCEPT(x,y). Функция перехвата оси Y REGR_INTERCEPT определяется разницей среднего значения множества x и произведения наклона линии регрессии и среднего значения множества y:

REGR_INTERCEPT(x,y) = AVG(x) – REGR_SLOPE(x, y) * AVG(y)

Для вычисления R-квадрата или коэффициента детерминации используется функция REGR_R2(x,y), которая не определена при нулевой дисперсии y, равна единице при нулевой дисперсии x и ненулевой дисперсии y. А в случае положительной дисперсии x и ненулевой дисперсии y равна квадрату корреляции x и y:

NULL if VAR_POP(y)  = 0
 
1 if VAR_POP(x)  = 0 and
VAR_POP(y) != 0
 
POWER(CORR(expr1,expr),2) if VAR_POP(x)  > 0 and
VAR_POP(y)  != 0

Мы бы описали коэффициент детерминации как величину, характеризующую процент описанных вариаций моделью. Если коэффициент детерминации равен 1, то наша модель описывает 100% вариаций и наш прогноз будет максимально точным.

Предлагаем в качестве x взять разницу времени, в которое получена метрика и текущим временем —  ROLLUP_TIMESTAMP-SYSDATE. В качестве y возьмем среднее значение свободного оставшегося места на диске /u01 в МБ — «Filesystem Space Available (MB)». В таком случае, используя функцию REGR_INTERCEPT(ROLLUP_TIMESTAMP-SYSDATE, VALUE_AVERAGE) в качестве групповой, мы можем оценить, через сколько суток закончится место на диске /u01.

В развернутом виде значение функции наклона линии регрессии, перехвата оси и коэффициента детерминации получаем запросом:

SELECT   COVAR_POP (ROLLUP_TIMESTAMP - SYSDATE, NULLIF (ROUND (VALUE_AVERAGE, 2), 0)) / VAR_POP (NULLIF (ROUND (VALUE_AVERAGE, 2), 0)) REGR_SLOPE,
         AVG (ROLLUP_TIMESTAMP)- (COVAR_POP (SYSDATE - ROLLUP_TIMESTAMP, NULLIF (ROUND (VALUE_AVERAGE, 2), 0))/VAR_POP(NULLIF(ROUND(VALUE_AVERAGE, 2), 0)))*AVG (NULLIF (ROUND(VALUE_AVERAGE, 2), 0))
            REGR_INTERCEPT,
         AVG (SYSDATE - ROLLUP_TIMESTAMP)-(COVAR_POP(SYSDATE - ROLLUP_TIMESTAMP,NULLIF(ROUND(VALUE_AVERAGE,2),0))/VAR_POP(NULLIF(ROUND(VALUE_AVERAGE,2),0)))*AVG(NULLIF(ROUND(VALUE_AVERAGE,2),0)))      REGR_INTERCEPT_ABS,
         CASE
            WHEN VAR_POP (NULLIF (ROUND (VALUE_AVERAGE, 2), 0)) = 0
            THEN
               NULL
            WHEN     VAR_POP (SYSDATE - ROLLUP_TIMESTAMP) = 0
                 AND VAR_POP (NULLIF (ROUND (VALUE_AVERAGE, 2), 0)) != 0
            THEN
               1
            WHEN     VAR_POP (SYSDATE - ROLLUP_TIMESTAMP) > 0
                 AND VAR_POP (NULLIF (ROUND (VALUE_AVERAGE, 2), 0)) != 0
            THEN
               POWER (CORR (SYSDATE - ROLLUP_TIMESTAMP, NULLIF (ROUND (VALUE_AVERAGE, 2), 0)),2)
         END
            REGR_R2
    FROM MGMT_METRICS_1DAY
   WHERE 	
TARGET_GUID = '6B1E3AFA92B3EA29AD73BB87432C084C'
         AND METRIC_GUID = HEXTORAW ('E8838C71E687BF0A9E02FFACC0C9AC80')
         AND KEY_VALUE = '/u10'
ORDER BY ROLLUP_TIMESTAMP;

Или уже с использованием функций REGR_SLOPE, REGR_INTERCEPT и REGR_R2:

SELECT REGR_SLOPE(SYSDATE-ROLLUP_TIMESTAMP, NULLIF(ROUND(VALUE_AVERAGE,2),0)) RSLP,
       REGR_INTERCEPT(SYSDATE-ROLLUP_TIMESTAMP, NULLIF(ROUND(VALUE_AVERAGE,2),0)) RINSP,  
       REGR_R2(SYSDATE-ROLLUP_TIMESTAMP, NULLIF(ROUND(VALUE_AVERAGE,2),0)) RSQR
       FROM MGMT_METRICS_1DAY
       WHERE TARGET_GUID='6B1E3AFA92B3EA29AD73BB87432C084C'
       AND METRIC_GUID=HEXTORAW('E8838C71E687BF0A9E02FFACC0C9AC80')
 	
  AND KEY_VALUE='/u10'
       ORDER BY ROLLUP_TIMESTAMP;

REGR_SLOPE = -0.00005
REGR_INTERCEPT = 149.46
REGR_R2 = 0.97

В нашем случае коэффициент детерминации близок к 1, и мы видим, что примерно через 149 cуток место на файловой системе /u10 закончится.

Мы можем использовать данный метод для оценки времени, на которое нам хватит имеющегося места на дисках для хостов продуктивных баз. В нашем случае хосты продуктивных баз включены в группу с METRIC_GUID='E8838C71E687BF0A9E02FFACC0C9AC80'.

Создаем User Definded Type T_TYPE c полями, которые нам нужны для выводимых данных: имя хоста, имя файловой системы, наклон линии регрессии, число дней, через которое закончится место на файловой системе и коэффициент детерминации.

CREATE OR REPLACE TYPE T_TYPE AS OBJECT(TARGET_NAME VARCHAR2(256), KEY_VALUE VARCHAR2(256), RSLP NUMBER,RINSP NUMBER,RSQR NUMBER);

Создаем Nested Table Type R_TYPE на базе T_TYPE:

CREATE OR REPLACE TYPE R_TYPE AS TABLE OF T_TYPE;

Создаем пакет, включающий функцию GET_VALUES для извлечения данных из MGMT_METRICS_1DAY  и процедуру отправки полученных данных по почте SEND_VALUES.

CREATE OR REPLACE PACKAGE EST_FS_EXHAUST IS

Для функции GET_VALUES входными параметрами будут переменная V_GN с именем группы хостов и значением коэффициента детерминации V_RSQ.

FUNCTION GET_VALUES(V_GN VARCHAR2, V_RSQ NUMBER) RETURN R_TYPE;

Для процедуры SEND_VALUES  входными параметрами будут переменная V_GN  и V_RSQ, аналогичные переменным функции GET_VALUES, плюс email, на который мы планируем отправить наш мини-отчет.

PROCEDURE SEND_VALUES(V_GN VARCHAR2, V_RSQ NUMBER, V_MAIL VARCHAR2);
END EST_FS_EXHAUST;

В теле пакета определяем функцию GET_VALUES и процедуру SEND_VALUES
     

CREATE OR REPLACE PACKAGE BODY EST_FS_EXHAUST IS
  
FUNCTION GET_VALUES(V_GN VARCHAR2, V_RSQ NUMBER) RETURN R_TYPE AS
   	V_REC R_TYPE;
BEGIN
SELECT
T_TYPE(
M.TARGET_NAME,
	D.KEY_VALUE,
ROUND(REGR_SLOPE(D.ROLLUP_TIMESTAMP-SYSDATE, NULLIF(ROUND(D.VALUE_AVERAGE,2),0)),0),
ROUND((ABS(REGR_INTERCEPT(D.ROLLUP_TIMESTAMP-SYSDATE, NULLIF(ROUND(D.VALUE_AVERAGE,2),0)))),0),  
ROUND(REGR_R2(D.ROLLUP_TIMESTAMP-SYSDATE, NULLIF(ROUND(D.VALUE_AVERAGE,2),0)),0))
BULK COLLECT INTO V_REC
FROM MGMT_METRICS_1DAY D, MGMT_TARGETS M, MGMT_TARGET_MEMBERSHIPS G
WHERE M.TARGET_GUID=G.MEMBER_TARGET_GUID
AND M.TARGET_GUID=D.TARGET_GUID
AND G.COMPOSITE_TARGET_NAME=V_GN
AND METRIC_GUID=HEXTORAW('E8838C71E687BF0A9E02FFACC0C9AC80')
GROUP BY M.TARGET_NAME, D.KEY_VALUE
HAVING REGR_R2(D.ROLLUP_TIMESTAMP-SYSDATE, NULLIF(ROUND(D.VALUE_AVERAGE,2),0)) > V_RSQ
       ;
RETURN V_REC;
END GET_VALUES;
 PROCEDURE SEND_VALUES(V_GN VARCHAR2, V_RSQ NUMBER, V_MAIL VARCHAR2) IS
V_REC R_TYPE;
	MSG VARCHAR2(2048):='';
BEGIN
	V_REC:= GET_VALUES(V_GN,V_RSQ);
FOR I IN V_REC.FIRST..V_REC.LAST
LOOP
	MSG:=CHR(10)||MSG||' Host '||V_REC(I).TARGET_NAME||' filesystem '||V_REC(I).KEY_VALUE||' will be exhausted in '||V_REC(I).RINSP||' days'|| CHR(9)||CHR(10);
END LOOP;
EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''mail_server''';
UTL_MAIL.SEND(SENDER => 'monitoring@yourmail.com',
        	RECIPIENTS => V_MAIL,
           	SUBJECT => 'Test Mail',
           	MESSAGE => MSG,
         	MIME_TYPE => 'text; charset=us-ascii');
END;
 
END EST_FS_EXHAUST;
/

Допустим, мы хотим получить время, через которое закончится место в файловых системах группы продуктивных хостов ‘prod_hosts’ при коэффициенте детерминации больше 0,5:

begin
EST_FS_EXHAUST.SEND_VALUES('prod_hosts',0.5,'operator@yourdomain.com');
end;
/
PL/SQL procedure successfully completed.

Как итог, в почту приходит сообщение:

Host host1 filesystem /u51 will be exhausted in 342 days          
Host host2 filesystem /u40 will be exhausted in 236 days          
Host host3 filesystem /u20/redo01 will be exhausted in 1100310 days             
Host host4 filesystem /u10 will be exhausted in 150 days          
Host host4 filesystem /u01/integration will be exhausted in 75080 days     
Host host4 filesystem /u01/app will be exhausted in 135 days   
Host host5  filesystem /u30/redo01 will be exhausted in 62252596 days          
Host host6 filesystem /u01 will be exhausted in 260 days          
Host host7  filesystem /u99 will be exhausted in 1038 days

Обратите внимание на файловые системы /u20/redo01 и /u30/redo01 — здесь расположены REDO LOGS и место не расходуется. Наша модель спрогнозировала, что место в /u20/redo01 на хосте host3 закончится через 2990 лет, а в /u30/redo01 на хосте host5 — через 169164 года. В обоих случаях наклон линии регрессии меньше –1.   

Использование этого метода можно расширить — например, оценивать время до исчерпания места в FAST RECOVERY AREA, в табличных пространствах базы и других областях.

Автор: VTB

Источник