Capacity Planning – Forecasts

La gestión de la capacidad (Capacity Management) es la disciplina de ITIL que tiene como objetivo asegurar que se utilizan los recursos adecuados en cada momento y, además, permite predicir, planificar, implementar y optimizar los recursos en el futuro.

La planificación de la capacidad (Capacity Planning) es la actividad del Capacity Management relacionada con el análisis de la utilización de los recursos actuales, el pronóstico y los procesos necesarios para garantizar que estarán disponibles en el futuro.

El recurso más recurrente en el día a día de un DBA es el almacenamiento. Una de las tareas más comunes, por poner un ejemplo, es ampliar el tamaño de un tablespace, ya sea incrementando el tamaño de un datafile o añadiendo nuevos ficheros. Es tan habitual, que sólo esta actividad puede suponer horas y horas de trabajo cada mes.

Hoy os traigo un par de consultas SQL para obtener una la predicción de crecimiento de tablespaces y filesystems el futuro. Os puede ser muy útil para estimar cuando espacio vas a necesitar durante los próximos 12 meses, y así ampliar el tamaño de los tbs/fs que os haga falta con cierta anticipación. Os podéis ahorrar bastante tiempo (y algún que otro susto en forma de incidencia crítica).

Este es un informe generado con estas consultas. Deben ser ejecutadas en el repositorio de Enterprise Manager Grid Control, en concreto para la versión 11g, pero facilmente serán compatible con Cloud Control 12c. Si no tenéis Grid Control, podéis reusar el código con unos pequeños cambios para usar el repositorio AWR de cada BD (vistas DBA_HIST_*). Os lo dejo como ejercicio 😉

Comentarios sobre la información obtenida con estas queries:

• Estas consultas utilizan el histórico almacenado en el repositorio de Grid Control (MGMT_METRIC_*) hasta un máximo de 60 días. Para BBDD recién creadas los valores estimados no serán fiables al 100%, lógicamente.
• La estimación se realiza con las funciones de regresión lineal (REGR_SLOPE y REGR_INTERCEPT). Es la misma teoría que utiliza Excel con la función Forecast.
• Sólo se muestran aquellos FS/TBS que vayan a superar el umbral de alerta (threshold) para dicho target durante los próximos 12 meses.
• La última columna (REQ 12M) muestra cuanto espacio tenéis que añadir para evitar una alerta de llenado durante los próximos 12 meses. El cálculo es el siguiente, si añadimos el espacio indicado al tablespace, al cabo de 12 meses este tendrá un porcentaje de ocupación de (THRESHOLD – 10)%.

Espero vuestros comentarios. Este es un pequeño ejercicio de Capacity Planning, y sólo estamos hablando de almacenamiento. Hay mucho terreno para debatir.

TABLESPACE FORECAST

-- File: tbs_forecast.sql
-- Description: OEM Tablespace Forecast
-- Author: Raul Ibanez (raul @ dbajunior.com)
-- Gracias a Facundo por retocar la query para evitar los grupos

ITH rl AS (
 SELECT
 --G.COMPOSITE_TARGET_NAME,
 --CASE G.COMPOSITE_TARGET_NAME
 -- WHEN 'prod_group' THEN '80'
 -- WHEN 'nonprod_group' THEN '90'
 -- ELSE '90'
 --END 
 '85' THRESHOLD,
 T.TARGET_NAME,
 M.KEY_VALUE TABLESPACE,
 REGR_SLOPE(M.VALUE_AVERAGE, ((M.ROLLUP_TIMESTAMP - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400)) SLOPE,
 REGR_INTERCEPT(M.VALUE_AVERAGE, ((M.ROLLUP_TIMESTAMP - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400)) YINTERCEPT,
 M.METRIC_GUID,
 M.TARGET_GUID,
 C.VALUE CURRENT_PERC
 FROM
 MGMT_METRICS_1DAY M,
 MGMT_TARGETS T,
 --MGMT$GROUP_FLAT_MEMBERSHIPS G,
 MGMT_CURRENT_METRICS C
 WHERE
 -- Metric D34E49F7B030C38C5DBEB6C62D4CAFA8 => TBS SPACE USED (MB) / Collected every week
 M.METRIC_GUID = HEXTORAW('D34E49F7B030C38C5DBEB6C62D4CAFA8') AND
 -- Metric 3E6F70DB22758B7B9756EF342180E7BB => TBS SPACE USED (%)
 C.METRIC_GUID = HEXTORAW('3E6F70DB22758B7B9756EF342180E7BB') AND
 M.KEY_VALUE = C.KEY_VALUE AND
 M.ROLLUP_TIMESTAMP >= SYSDATE-60 AND
 M.TARGET_GUID = T.TARGET_GUID AND
 T.TARGET_GUID = C.TARGET_GUID AND
 --G.MEMBER_TARGET_GUID = M.TARGET_GUID AND
 --G.COMPOSITE_TARGET_NAME = 'prod_group' AND -- Filter OEM Group
 --T.TARGET_NAME NOT LIKE '%dr%' AND -- Filter Target Name
 --G.COMPOSITE_TARGET_TYPE = 'composite' AND
 M.KEY_VALUE NOT LIKE 'UNDOTBS%' AND -- Filter Tablespace Name
 M.KEY_VALUE NOT LIKE '%TEMP%'
 GROUP BY
 T.TARGET_NAME, M.KEY_VALUE, M.METRIC_GUID, M.TARGET_GUID, C.VALUE 
)
SELECT
 TARGET_NAME,
 TABLESPACE,
 THRESHOLD "THRESHOLD %",
 -- SLOPE,
 -- YINTERCEPT,
 ROUND(CURRENT_PERC,2) "CURR%",
 ROUND(((((((SYSDATE+31) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT)* CURRENT_PERC) /
 ((((SYSDATE) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT)),2) "NEXT1M%",
 ROUND(((((((SYSDATE+138) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT)* CURRENT_PERC) /
 ((((SYSDATE) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT)),2) "NEXT6M%",
 ROUND(((((((SYSDATE+365) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT)* CURRENT_PERC) /
 ((((SYSDATE) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT)),2) "NEXT12M%",
 ROUND((((SYSDATE) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT) "CURR(MB)",
 ROUND((((SYSDATE+31) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT) "NEXT1M(MB)",
 ROUND((((SYSDATE+138) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT) "NEXT6M(MB)",
 ROUND((((SYSDATE+365) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT) "NEXT12M(MB)",
 '+' || TO_CHAR(ROUND(((100*((((SYSDATE+365) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT)/(THRESHOLD-10))-
 (100*((((SYSDATE) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT)/CURRENT_PERC))/1024))||'GB' "REQ 12M"
FROM rl
WHERE
 SLOPE > 0 AND
 ROUND(((((((SYSDATE+365) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT)* CURRENT_PERC) /
 ((((SYSDATE) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT)),2) > THRESHOLD
ORDER BY
 ROUND(((100*((((SYSDATE+365) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT)/(THRESHOLD-10))-
 (100*((((SYSDATE) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT)/CURRENT_PERC))/1024) DESC;

FILESYSTEM FORECAST

-- File: fs_forecast.sql
-- Description: OEM Filesystem Forecast
-- Author: Raul Ibanez (raul @ dbajunior.com)

WITH rl AS (
  SELECT
--     G.COMPOSITE_TARGET_NAME,
--	 CASE G.COMPOSITE_TARGET_NAME
--	    WHEN 'prod_group' THEN '80'
--      WHEN 'nonprod_group' THEN '90'
--		ELSE '90'
--	 END THRESHOLD,
     T.TARGET_NAME,
     M1.KEY_VALUE,
     REGR_SLOPE(M1.VALUE_AVERAGE - M2.VALUE_AVERAGE,
       ((M1.ROLLUP_TIMESTAMP - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400)) SLOPE,
     REGR_INTERCEPT(M1.VALUE_AVERAGE - M2.VALUE_AVERAGE,
       ((M1.ROLLUP_TIMESTAMP - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400)) YINTERCEPT,
	 M1.METRIC_GUID,
	 M1.TARGET_GUID,
	 (100-C.VALUE) CURRENT_PERC
  FROM
    MGMT_METRICS_1DAY M1,
    MGMT_METRICS_1DAY M2,
    MGMT_TARGETS T,
--    MGMT$GROUP_FLAT_MEMBERSHIPS G,
    MGMT_CURRENT_METRICS C
  WHERE
    -- Metric 162045AD9191652427CAC47D8BA40671 => Filesystem Size (MB) / Collected every week
    -- Metric E8838C71E687BF0A9E02FFACC0C9AC80 => Available (MB) / Collected every week
    M1.METRIC_GUID = HEXTORAW('162045AD9191652427CAC47D8BA40671') AND
    M2.METRIC_GUID = HEXTORAW('E8838C71E687BF0A9E02FFACC0C9AC80') AND
    -- Metric 6E65075DA52ACA744B4B8C3FCB018289 => Filesystem Space Available (%)
    C.METRIC_GUID = HEXTORAW('6E65075DA52ACA744B4B8C3FCB018289') AND
    M1.ROLLUP_TIMESTAMP = M2.ROLLUP_TIMESTAMP AND
    M1.KEY_VALUE = C.KEY_VALUE AND
    M2.KEY_VALUE = C.KEY_VALUE AND
    M1.ROLLUP_TIMESTAMP >= SYSDATE-60 AND
    M2.ROLLUP_TIMESTAMP >= SYSDATE-60 AND
    M1.TARGET_GUID = T.TARGET_GUID AND
    M2.TARGET_GUID = T.TARGET_GUID AND
    T.TARGET_GUID = C.TARGET_GUID AND
--    G.MEMBER_TARGET_GUID = M1.TARGET_GUID AND
--    G.MEMBER_TARGET_GUID = M2.TARGET_GUID AND
--    G.COMPOSITE_TARGET_NAME = 'prod_group' AND -- Filter any OEM Group
--    G.COMPOSITE_TARGET_TYPE = 'composite'
  GROUP BY
--    G.COMPOSITE_TARGET_NAME,
    T.TARGET_NAME, M1.KEY_VALUE, M1.METRIC_GUID, M1.TARGET_GUID, C.VALUE
)
SELECT
  TARGET_NAME,
  KEY_VALUE,
  THRESHOLD "THRESHOLD %",
  -- SLOPE,
  -- YINTERCEPT,
  ROUND(CURRENT_PERC,2) "CURR%",
  ROUND(((((((SYSDATE+31) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT)* CURRENT_PERC) /
   ((((SYSDATE) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT)),2) "NEXT1M%",
  ROUND(((((((SYSDATE+138) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT)* CURRENT_PERC) /
   ((((SYSDATE) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT)),2) "NEXT6M%",
  ROUND(((((((SYSDATE+365) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT)* CURRENT_PERC) /
   ((((SYSDATE) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT)),2) "NEXT12M%",
  ROUND((((SYSDATE) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT) "CURR(MB)",
  ROUND((((SYSDATE+31) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT) "NEXT1M(MB)",
  ROUND((((SYSDATE+138) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT) "NEXT6M(MB)",
  ROUND((((SYSDATE+365) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT) "NEXT12M(MB)",
  '+' || TO_CHAR(ROUND(((100*((((SYSDATE+365) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT)/(THRESHOLD-10))-
  (100*((((SYSDATE) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT)/CURRENT_PERC))/1024))||'GB' "REQ 12M"
FROM rl
WHERE
  SLOPE > 0 AND
  ROUND(((((((SYSDATE+365) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT)* CURRENT_PERC) /
   ((((SYSDATE) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT)),2) > THRESHOLD
ORDER BY
  ROUND(((100*((((SYSDATE+365) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT)/(THRESHOLD-10))-
  (100*((((SYSDATE) - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400) * SLOPE + YINTERCEPT)/CURRENT_PERC))/1024) DESC;