最小二乗法を使用してリソースを評価し、Oracleデータベースを監視する方法

日常の操作では、Oracleデータベース管理者は、スループット、負荷、DBMSの成長、DBMSホスト、ディスクサブシステムなどに関して、多数のメトリックを追跡する必要があります。 これらのデータはすべて、監視システムによって慎重に収集および保存されます。 情報が自重ではないように、サイジングの問題に関する予測や事前の監視に使用できます。 この投稿では、その方法を示します。







Oracle Cloud Controlを使用してください。 MGMT_METRICS_1DAY



のSYSMANスキームでは、 TARGET_GUID = '6B1E3AFA92B3EA29AD73BB87432C084C'



ホストの「使用可能なファイルシステム領域（MB）」メトリックの値を格納します。

 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;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

以下のグラフは、2018年8月14日から2018年11月15日までの3か月間、/ u01ファイルシステムの空き領域が1.5 TB減少したことを示しています-4.1 TBから2.6 TB







残りの2.6 TBで十分な時間を判断するために、線形回帰関数を使用します。



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 2乗または決定係数を計算するために、関数REGR_R2（x、y）が使用されます。これは、ゼロ分散yに対して定義されておらず、ゼロ分散xおよび非ゼロ分散yに対して1に等しくなります。 そして、xの正の分散とyの非ゼロ分散の場合、xとyの相関の2乗に等しくなります。

 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％を表し、予測は可能な限り正確になります。



メトリックが受信される時間差と現在の時間（ROLLUP_TIMESTAMP-SYSDATE）をxとして採用することを提案します。 yとして、空きディスク領域の平均値/ u01（MB）-「使用可能なファイルシステム領域（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日後に/ u10ファイルシステム上の場所が終了することがわかります。



この方法を使用して、本番データベースのホストに十分なディスク容量がある時間を見積もることができます。 私たちの場合、本稼働データベースのホストは、 METRIC_GUID='E8838C71E687BF0A9E02FFACC0C9AC80'



グループに含まれています。



出力に必要なフィールドを持つUser Definded Type T_TYPE



を作成しUser Definded Type T_TYPE



：ホスト名、ファイルシステム名、回帰直線の傾き、ファイルシステム上のスペースが終了するまでの日数、および決定係数。

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

T_TYPEに基づいてネストされたテーブルタイプR_TYPEを作成します。

 CREATE OR REPLACE TYPE R_TYPE AS TABLE OF T_TYPE;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

MGMT_METRICS_1DAY



からデータを抽出するGET_VALUES



関数と、受信したデータをメールSEND_VALUES



で送信する手順を含むパッケージを作成します。

 CREATE OR REPLACE PACKAGE EST_FS_EXHAUST IS
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

GET_VALUES



関数のGET_VALUES



入力パラメーターはホストグループの名前と決定係数V_RSQ



値を持つV_GN



変数になります。

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

プロシージャーSEND_VALUES



入力パラメーターは、 GET_VALUES



関数の変数と同様の変数V_GN



およびV_RSQ



に加えて、ミニレポートの送信先の電子メールになります。

 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.
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

その結果、メッセージがメールで届きます。



ホストhost1ファイルシステム/ u51は342日で使い果たされます

ホストhost2ファイルシステム/ u40は236日で使い果たされます

ホストhost3ファイルシステム/ u20 / redo01は1100310日で使い果たされます

ホストhost4ファイルシステム/ u10は150日で使い果たされます

ホストhost4ファイルシステム/ u01 /統合は75080日で使い果たされる

ホストhost4ファイルシステム/ u01 /アプリは135日で使い果たされます

ホストhost5ファイルシステム/ u30 / redo01は62252596日で使い果たされます

ホストhost6ファイルシステム/ u01は260日で使い果たされます

ホストhost7ファイルシステム/ u99は1038日で使い果たされます



ファイルシステム/ u20 / redo01および/ u30 / redo01に注意してください-ここにREDOログがあり、場所は無駄になりません。 私たちのモデルは、host3の/ u20 / redo01の場所は2990年で終わり、host5の/ u30 / redo01の場所は169164年で終わると予測しました。 どちらの場合も、回帰直線の傾きは-1未満です。



この方法の使用は拡張することができます-例えば、FAST RECOVERY AREA、データベースの表スペース、およびその他の領域のスペースがなくなるまでの時間を見積もることができます。