Database Growth Monitor

The following package monitors the growth for the database. It can be used for Capacity Planning.

1. Create the necessary tables

CREATE GLOBAL TEMPORARY TABLE SEGMENT_START_INFO AS
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME, BYTES START_SIZE, SYSDATE START_DATE
FROM DBA_SEGMENTS WHERE 1=2
/
CREATE GLOBAL TEMPORARY TABLE SEGMENT_CURRENT_INFO AS
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME, BYTES CURRENT_SIZE, SYSDATE CURRENT_DATE
FROM DBA_SEGMENTS WHERE 1=2
/
CREATE TABLE GROWTH_METRIC_LOG(STAGE VARCHAR2(255), RUN_DATE DATE) TABLESPACE USERS;
CREATE PUBLIC SYNONYM GROWTH_METRIC_LOG FOR GROWTH_METRIC_LOG;
GRANT SELECT ON GROWTH_METRIC_LOG TO PUBLIC;
CREATE TABLE SEGMENT_GROWTH_METRICS
(
OWNER VARCHAR2(30 BYTE),
SEGMENT_NAME VARCHAR2(81 BYTE),
PARTITION_NAME VARCHAR2(30 BYTE),
SEGMENT_TYPE VARCHAR2(18 BYTE),
BYTES NUMBER,
EXTENTS NUMBER,
ADD_DATE DATE,
DEL_DATE DATE
)
/
CREATE TABLE TABLESPACE_GROWTH_METRICS AS SELECT SYSDATE RUN_DATE, TABLESPACE_NAME, BYTES DAILY_AVG_GROWTH_MB
FROM DBA_SEGMENTS WHERE 1=2
/
CREATE PUBLIC SYNONYM TABLESPACE_GROWTH_METRICS FOR TABLESPACE_GROWTH_METRICS
/
GRANT SELECT ON TABLESPACE_GROWTH_METRICS TO PUBLIC
/

2. Create the Package which will monitor the growth


GRANT SELECT ON DBA_SEGMENTS TO OEM;
--@CREATE_TABS.SQL
CREATE OR REPLACE PACKAGE PKG_DATABASE_GROWTH_METRIC IS
PROCEDURE SP_DATABASE_GROWTH_METRIC;
PROCEDURE SP_CAPTURE_SEGMENT_GROWTH;
PROCEDURE SP_ANALYZE_SEGMENT_GROWTH;
END PKG_DATABASE_GROWTH_METRIC;
/

CREATE OR REPLACE PACKAGE BODY PKG_DATABASE_GROWTH_METRIC IS
/***********************************************************************
**CREATED BY: MAKARAND G
**CREATE DATE: 10/28/2004
**OVERVIEW: RUNS ALL OTHER PROCEDURES
**VERSION 01.01.00
***********************************************************************/
PROCEDURE SP_DATABASE_GROWTH_METRIC IS
BEGIN
INSERT INTO GROWTH_METRIC_LOG VALUES ('START SP_CAPTURE_SEGMENT_GROWTH', SYSDATE);
COMMIT;
SP_CAPTURE_SEGMENT_GROWTH;
INSERT INTO GROWTH_METRIC_LOG VALUES ('END SP_CAPTURE_SEGMENT_GROWTH', SYSDATE);
COMMIT;
END SP_DATABASE_GROWTH_METRIC;
PROCEDURE SP_ANALYZE_SEGMENT_GROWTH IS
BEGIN
--FIND OUT THE CURRENT SIZE FOR EACH OBJECT
INSERT INTO SEGMENT_CURRENT_INFO (OWNER, SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME, CURRENT_SIZE, CURRENT_DATE)
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME, SUM(BYTES) CURRENT_SIZE, MAX(ADD_DATE) CURRENT_DATE
FROM SEGMENT_GROWTH_METRICS A
WHERE DEL_DATE IS NULL
GROUP BY OWNER, SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME;
--FIND OUT THE BASE SIZE FOR EACH OBJECT
INSERT INTO SEGMENT_START_INFO (OWNER, SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME, START_SIZE, START_DATE)
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME, SUM(BYTES) START_SIZE, MIN(ADD_DATE) START_DATE
FROM SEGMENT_GROWTH_METRICS A
WHERE DEL_DATE IS NULL
AND ADD_DATE = (
SELECT MIN(ADD_DATE) FROM SEGMENT_GROWTH_METRICS B
WHERE DEL_DATE IS NULL
AND A.OWNER = B.OWNER
AND A.SEGMENT_NAME = B.SEGMENT_NAME
AND A.SEGMENT_TYPE = B.SEGMENT_TYPE
AND NVL(A.PARTITION_NAME, '**') = NVL(B.PARTITION_NAME, '**'))
GROUP BY OWNER, SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME;

INSERT INTO TABLESPACE_GROWTH_METRICS
--FIND OUT TABLESPACE DAILY AVG GROWTH, WHICH WOULD COME FROM THE INDIVIDUAL SEGMENTS
SELECT SYSDATE, TABLESPACE_NAME, ROUND(SUM(DAILY_AVG_GROWTH)/1024/1024) DAILY_AVG_GROWTH_MB FROM (
SELECT A.OWNER, A.SEGMENT_NAME, A.SEGMENT_TYPE, A.PARTITION_NAME, C.TABLESPACE_NAME, (B.CURRENT_SIZE - A.START_SIZE)/ROUND(B.CURRENT_DATE-A.START_DATE) DAILY_AVG_GROWTH
FROM SEGMENT_START_INFO A, SEGMENT_CURRENT_INFO B, TEMP_DBA_SEGMENTS C
WHERE A.OWNER = B.OWNER
AND A.SEGMENT_NAME = B.SEGMENT_NAME
AND A.SEGMENT_TYPE = B.SEGMENT_TYPE
AND NVL(A.PARTITION_NAME, '**') = NVL(B.PARTITION_NAME, '**')
AND A.OWNER = C.OWNER
AND A.SEGMENT_NAME = C.SEGMENT_NAME
AND A.SEGMENT_TYPE = C.SEGMENT_TYPE
AND NVL(A.PARTITION_NAME, '**') = NVL(C.PARTITION_NAME, '**')
AND ROUND(B.CURRENT_DATE-A.START_DATE) > 0)
GROUP BY TABLESPACE_NAME
HAVING ROUND(SUM(DAILY_AVG_GROWTH)/1024/1024) > 0
ORDER BY TABLESPACE_NAME;
COMMIT;
END SP_ANALYZE_SEGMENT_GROWTH;
PROCEDURE SP_CAPTURE_SEGMENT_GROWTH IS
CURSOR C1 IS
SELECT A.OWNER, A.SEGMENT_NAME,
A.PARTITION_NAME,
A. SEGMENT_TYPE
FROM TEMP_DBA_SEGMENTS A, (
SELECT OWNER,
SEGMENT_NAME,
PARTITION_NAME,
SEGMENT_TYPE,
SUM(BYTES) PREV_SEG_BYTES,
SUM(EXTENTS) PREV_SEG_EXTS
FROM SEGMENT_GROWTH_METRICS
WHERE DEL_DATE IS NULL
GROUP BY OWNER,
SEGMENT_NAME,
PARTITION_NAME,
SEGMENT_TYPE) B
WHERE A.OWNER = B.OWNER
AND A.SEGMENT_NAME = B.SEGMENT_NAME
AND A.SEGMENT_TYPE = B.SEGMENT_TYPE
AND NVL(A.PARTITION_NAME, '**') = NVL(B.PARTITION_NAME, '**')
AND (A.EXTENTS < B.PREV_SEG_EXTS OR A.BYTES < B.PREV_SEG_BYTES);
CURSOR C2 IS
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME FROM SEGMENT_GROWTH_METRICS WHERE DEL_DATE IS NULL
MINUS
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME FROM TEMP_DBA_SEGMENTS ;


BEGIN
/* CREATE A SNAPSHOT OF DBA_SEGMENTS, WHICH WILL IMPROVE THE PROCESSING TIME, ALSO IT CAN BE USED FOR BACKTRACKING TILL NEXT RUN
*/
EXECUTE IMMEDIATE 'DROP TABLE TEMP_DBA_SEGMENTS';
EXECUTE IMMEDIATE 'CREATE TABLE TEMP_DBA_SEGMENTS PARALLEL AS SELECT * FROM DBA_SEGMENTS';
EXECUTE IMMEDIATE 'ANALYZE TABLE TEMP_DBA_SEGMENTS ESTIMATE STATISTICS';
EXECUTE IMMEDIATE 'ANALYZE TABLE SEGMENT_GROWTH_METRICS ESTIMATE STATISTICS';
/* FIRST FOR ALL THE OBJECTS WHICH ARE SHRUNK DUE THE REBUILD OR MOVE OR ANY OTHER ACTION SOFT DELETE THEM
** THESE OBJECTS WILL AGAIN BE INSERTED AT A LATER STAGE
*/
FOR X IN C1
LOOP
UPDATE SEGMENT_GROWTH_METRICS A
SET DEL_DATE = SYSDATE
WHERE A.OWNER = X.OWNER
AND A.SEGMENT_NAME = X.SEGMENT_NAME
AND NVL(A.PARTITION_NAME,'**') = NVL(X.PARTITION_NAME, '**')
AND A.SEGMENT_TYPE = X.SEGMENT_TYPE
AND DEL_DATE IS NULL;
END LOOP;
INSERT INTO GROWTH_METRIC_LOG VALUES ('STEP1:TOTAL4', SYSDATE);
COMMIT;
/* INSERT OBJECTS WHICH ARE NEW/WHO ARE NOT MARKED FOR SOFT DELETE.
** THESE WERE NEVER FOUND IN GROWTH METRIC SO FAR OR SOFT DELETED EARLIER
*/
INSERT INTO SEGMENT_GROWTH_METRICS
SELECT OWNER ,
SEGMENT_NAME,
PARTITION_NAME,
SEGMENT_TYPE,
BYTES,
EXTENTS,
SYSDATE ADD_DATE,
NULL
FROM TEMP_DBA_SEGMENTS A
WHERE NOT EXISTS (SELECT 1 FROM SEGMENT_GROWTH_METRICS B
WHERE A.OWNER = B.OWNER
AND A.SEGMENT_NAME = B.SEGMENT_NAME
AND A.SEGMENT_TYPE = B.SEGMENT_TYPE
AND NVL(A.PARTITION_NAME, '**') = NVL(B.PARTITION_NAME, '**')
AND B.DEL_DATE IS NULL)
AND SEGMENT_TYPE NOT LIKE '%UNDO%'
AND SEGMENT_TYPE != 'TEMPORARY';
INSERT INTO GROWTH_METRIC_LOG VALUES ('STEP2:TOTAL4', SYSDATE);
COMMIT;
/* INSERT THE EXISTING OBJECTS GROWTH. WE ONLY CONSIDER THE # OF EXTENTS AND BYTES ADDED SINCE THE LAST TIME AND CONSIDERS IT AS A GROWTH
*/
INSERT INTO SEGMENT_GROWTH_METRICS
SELECT A.OWNER,
A.SEGMENT_NAME,
A.PARTITION_NAME,
A.SEGMENT_TYPE,
A.BYTES - B.PREV_SEG_BYTES BYTES_ADDED,
A.EXTENTS - B.PREV_SEG_EXTS EXTENTS_ADDED,
SYSDATE ADD_DATE,
NULL DEL_DATE
FROM TEMP_DBA_SEGMENTS A, (
SELECT OWNER,
SEGMENT_NAME,
PARTITION_NAME,
SEGMENT_TYPE,
SUM(BYTES) PREV_SEG_BYTES,
SUM(EXTENTS) PREV_SEG_EXTS
FROM SEGMENT_GROWTH_METRICS
WHERE DEL_DATE IS NULL
GROUP BY OWNER,
SEGMENT_NAME,
PARTITION_NAME,
SEGMENT_TYPE) B
WHERE A.OWNER = B.OWNER
AND A.SEGMENT_NAME = B.SEGMENT_NAME
AND A.SEGMENT_TYPE = B.SEGMENT_TYPE
AND NVL(A.PARTITION_NAME, '**') = NVL(B.PARTITION_NAME, '**')
AND (A.EXTENTS > B.PREV_SEG_EXTS OR A.BYTES > B.PREV_SEG_BYTES);
INSERT INTO GROWTH_METRIC_LOG VALUES ('STEP3:TOTAL4', SYSDATE);
COMMIT;
/* THESE ARE THE OBJECTS WHICH WERE PRESENT LAST TIME BUT DROPPED NOW, SO NOT PART OF GROWTH ANYMORE
*/
/*
UPDATE SEGMENT_GROWTH_METRICS A
SET DEL_DATE = SYSDATE
WHERE NOT EXISTS (SELECT 1 FROM TEMP_DBA_SEGMENTS B
WHERE A.OWNER = B.OWNER
AND A.SEGMENT_NAME = B.SEGMENT_NAME
AND A.SEGMENT_TYPE = B.SEGMENT_TYPE
AND NVL(A.PARTITION_NAME, '**') = NVL(B.PARTITION_NAME, '**'))
AND DEL_DATE IS NULL;
*/
FOR B IN C2
LOOP
UPDATE SEGMENT_GROWTH_METRICS A
SET DEL_DATE = SYSDATE
WHERE A.OWNER = B.OWNER
AND A.SEGMENT_NAME = B.SEGMENT_NAME
AND A.SEGMENT_TYPE = B.SEGMENT_TYPE
AND NVL(A.PARTITION_NAME, '**') = NVL(B.PARTITION_NAME, '**')
AND DEL_DATE IS NULL;
END LOOP;
INSERT INTO GROWTH_METRIC_LOG VALUES ('STEP4:TOTAL4', SYSDATE);
COMMIT;
END SP_CAPTURE_SEGMENT_GROWTH;

END PKG_DATABASE_GROWTH_METRIC;
/
GRANT EXECUTE ON PKG_DATABASE_GROWTH_METRIC TO PUBLIC
/
CREATE PUBLIC SYNONYM PKG_DATABASE_GROWTH_METRIC FOR PKG_DATABASE_GROWTH_METRIC
/

3. Schedule a growth monitoring script by calling

exec PKG_DATABASE_GROWTH_METRIC.SP_DATABASE_GROWTH_METRIC;

4. Analyze the growth on-demand

exec PKG_DATABASE_GROWTH_METRIC.SP_ANALYZE_SEGMENT_GROWTH;

5. Interpret the result


SELECT A.TABLESPACE_NAME, ROUND(A.TOTAL_SPACE) TOTAL_SPACE_MB, ROUND(SUM(B.BYTES)/1024/1024) FREE_SPACE_MB, ROUND((SUM(B.BYTES)/(1024*1024)/A.TOTAL_SPACE)*100,2) PERCENT_FREE, C.DAILY_AVG_GROWTH_MB, ROUND(SUM(B.BYTES)/1024/1024/C.DAILY_AVG_GROWTH_MB) "#OF DAYS TO FAIL"
FROM (
SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 TOTAL_SPACE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A,
DBA_FREE_SPACE B, TABLESPACE_GROWTH_METRICS C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
AND A.TABLESPACE_NAME = C.TABLESPACE_NAME
AND C.RUN_DATE = (SELECT MAX(RUN_DATE) FROM TABLESPACE_GROWTH_METRICS)
GROUP BY A.TABLESPACE_NAME, A.TOTAL_SPACE, C.DAILY_AVG_GROWTH_MB
ORDER BY 6
/

6. Additional queries to troubleshoot


SELECT EXECUTIONS FROM V$SQLAREA WHERE SQL_TEXT LIKE 'UPDATE SEGMENT_GROWTH_METRICS%'
SELECT COUNT(1) FROM (
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME FROM SEGMENT_GROWTH_METRICS WHERE DEL_DATE IS NULL
MINUS
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME FROM TEMP_DBA_SEGMENTS)


SELECT COUNT(1),SUM(BYTES)/1024/1024 DB_SIZE FROM SEGMENT_GROWTH_METRICS WHERE DEL_DATE IS NULL;

SELECT COUNT(1),SUM(BYTES)/1024/1024 DB_SIZE FROM TEMP_DBA_SEGMENTS WHERE SEGMENT_TYPE NOT LIKE '%UNDO%' AND SEGMENT_TYPE != 'TEMPORARY' ;

--PROBLEM SEGMENTS
SELECT SEGMENT_NAME, SUM(BYTES) FROM SEGMENT_GROWTH_METRICS WHERE DEL_DATE IS NULL GROUP BY SEGMENT_NAME
MINUS
SELECT SEGMENT_NAME, SUM(BYTES) FROM TEMP_DBA_SEGMENTS WHERE SEGMENT_TYPE NOT LIKE '%UNDO%' GROUP BY SEGMENT_NAME
/