Uno de los estudiantes de «Mentor Dojo» recibió esta solicitud de su jefe.
Querían estimar el crecimiento de todos los objetos de un cierto tablespace, o esquema, en un mes.
Mi primera respuesta fue «bueno, supongamos el crecimiento del último mes», pero no conservaban esta información. Además, es posible que se necesite ejecutar algo como esto para crear un informe de auditoría de una base de datos y proporcionar esta información a sus clientes sobre los sistemas.
Entonces comenzamos a construir un procedimiento para generar una simulación de crecimiento de segmentos en una base de datos Oracle particular usando el paquete de PL/SQL llamado DBMS_SPACE.
La idea es ejecutar el procedimiento segment_growth_trend para todos los segmentos en un esquema específico y almacenarlo en una tabla, de modo que podamos consultar los segmentos principales con el mayor crecimiento estimado y también poder usar esta información en el futuro. Como todo esto se basa en la información de AWR, podemos ver la evolución del crecimiento de esos tablespaces por cada segmento.
Entonces, lo primero que debe hacer es probar el paquete DBMS_SPACE para verificar que tengamos todos los privilegios necesarios y que el procedimiento OBJECT_GROWTH_TREND funciona como se espera:
set serveroutput on
begin
for x in (select *
from table(dbms_space.OBJECT_GROWTH_TREND
('&USER','&TABLE','TABLE')))
loop
dbms_output.put_line (x.TIMEPOINT||x.SPACE_USAGE||
x.SPACE_ALLOC||x.QUALITY);
end loop;
end;
/
El resultado, como puede ver en el procedimiento, es una tabla completa de valores de tamaño para la tabla que ingresó. También puede tener una primera línea con un error como este, pero es algo que se puede ignorar según la nota de soporte de Oracle «SELECT Statement Using DBMS_SPACE.OBJECT_GROWTH_TREND Fails with ORA-14551» (Doc ID 1094627.1), es un error, sin resolver, es puramente cosmético y puede ser ignorado con seguridad.
La salida será algo como esto:
EXCEPTION in chrow processing - code: -1031 msg: ORA-01031: privilegios insuficientes 21/12/22 15:37:20,24400018352983145728INTERPOLATED 22/12/22 15:37:20,24400018352983145728INTERPOLATED 23/12/22 15:37:20,24400018352983145728INTERPOLATED 24/12/22 15:37:20,24400018352983145728INTERPOLATED 25/12/22 15:37:20,24400018352983145728INTERPOLATED 26/12/22 15:37:20,24400018352983145728INTERPOLATED 27/12/22 15:37:20,24400018352983145728PROJECTED 28/12/22 15:37:20,24400018352983145728PROJECTED 29/12/22 15:37:20,24400018352983145728PROJECTED 30/12/22 15:37:20,24400018352983145728PROJECTED 31/12/22 15:37:20,24400018352983145728PROJECTED
PL/SQL procedure successfully completed.
Entonces, el día que escribí esta publicación es el 26 de diciembre de 2022, por lo que los valores hasta hoy son «INTERPOLADOS» y los valores futuros son «PROYECTADOS». También se puede ver el espacio utilizado y el espacio asignado para el segmento.
Ahora sería el momento de ejecutar el procedimiento que obtendrá los valores iniciales, actuales y futuros para este segmento y almacenar los resultados en una tabla.
Vamos, entonces, al primer paso: la creación de la tabla CAPACITY_SEGMENTS
CREATE TABLE CAPACITY_SEGMENTS ( OWNER VARCHAR2(30), SEGMENT_NAME VARCHAR2(30), SEGMENT_TYPE VARCHAR2(30), PARTITION_NAME VARCHAR2(30), TABLESPACE_NAME VARCHAR2(30), SEGMENT_SIZE NUMBER, CAPACITY_LAST_MONTH NUMBER, CURRENT_CAPACITY NUMBER, FUTURE_CAPACITY NUMBER, SEGMENT_PCT_OCCUPIED NUMBER, INSTANT_MEASURE DATE) SEGMENT CREATION IMMEDIATE;
Aparte de los atributos de los objetos como OWNER, NAME, TYPE, TABLESPACE y SIZE, en esta tabla también almacenaremos valores interesantes para lanzar nuestras estimaciones en el futuro, como CURRENT_CAPACITY emparejado con INSTANT_MEASURE, y también es interesante poder comprobar si también encaja con los valores de FUTURE_CAPACITY a lo largo del tiempo. .
Hay que tener en cuenta que esta proyección es de 5 días desde el tiempo de ejecución, por lo que se pueden extrapolar esas tendencias a meses o años como se desee (multiplicando x6 para obtener un mes, por ejemplo).
Luego, simplemente ejecutamos el procedimiento CHECK_SEGMENT_GROWTH proporcionando el propietario del esquema del que se desea estimar el crecimiento de los objetos. El procedimiento almacenará los resultados en la tabla CAPACITY_SEGMENTS recientemente creada.
Todos esos scripts están en Café Database GitHub aquí:
https://github.com/CafeDatabase/segment-growth-simulation
Aquí hay tres scripts:
test_dbms_space_growth_trend.sql
–> Sirve para testear la ejecución de DBMS_SPACE.OBJECT_GROWTH_TREND
create_segment_growth_table.sql
–> Crea la tabla CAPACITY_SEGMENTS para almacenar las tendencias de crecimiento de los objetos
proc_segment_growth_simulation.sql
–> Y aquí el procedure que calcula esta estimación de crecimiento y almacena los resultados en la tabla.
Una última cosa, si se producen errores al ejecutar el paquete DBMS_SPACE dentro del bloque PL/SQL, verifica los privilegios o intenta ejecutarlo bajo un esquema que tenga todos los privilegios sobre los objetos que va a analizar.
¡A disfrutar!