Monday, July 16, 2012

Tablespace growth and usage monitoring

We want to create a mechanism to monitor growth of our tablespaces and how much space is used in them. We will use DBA_TABLESPACE_USAGE_METRICS view and store its output in a table called HELPDESK.TABLESPACE_STATS.

If you use autoextendible datafiles, according to Oracle Note 455715.1:
Tablespace_size in DBA_TABLESPACE_USAGE_METRICS takes the maximum file size for autoextensible tablespace which corresponds to maxblocks in dba_data_files.
We create HELPDESK.TABLESPACE_STATS and its indexes:
CREATE TABLE HELPDESK.TABLESPACE_STATS
(
  TABLESPACE_NAME  VARCHAR2(30 BYTE),
  TABLESPACE_SIZE  NUMBER,
  USED_SPACE       NUMBER,
  USED_PERCENT     NUMBER,
  COLLECTION_DATE  DATE
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          1M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE INDEX HELPDESK.DATE_IDX ON HELPDESK.TABLESPACE_STATS
(COLLECTION_DATE)
LOGGING
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          1M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE INDEX HELPDESK.NAME_IDX ON HELPDESK.TABLESPACE_STATS
(TABLESPACE_NAME)
LOGGING
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          1M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

Now, schedule a job to run the following INSERT. I choose to run it once per week.
INSERT INTO HELPDESK.TABLESPACE_STATS (TABLESPACE_NAME,
                                       USED_SPACE,
                                       TABLESPACE_SIZE,
                                       USED_PERCENT,
                                       COLLECTION_DATE)
   SELECT A.TABLESPACE_NAME,
          A.USED_SPACE * B.BLOCK_SIZE,
          A.TABLESPACE_SIZE * B.BLOCK_SIZE,
          A.USED_PERCENT,
          SYSDATE
     FROM DBA_TABLESPACE_USAGE_METRICS A, DBA_TABLESPACES B
    WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME;

Let's see a scenario, where we have collected 3 weeks of data:
  SELECT TRUNC (COLLECTION_DATE) "DATE",
         TABLESPACE_NAME,
         ROUND (TABLESPACE_SIZE / 1024 / 1024, 2) "SIZE MB",
         ROUND (USED_SPACE / 1024 / 1024, 2) "USED SPACE MB",
         ROUND (USED_PERCENT, 2) "USED %"
    FROM HELPDESK.TABLESPACE_STATS
ORDER BY 1, 2;
   
DATETABLESPACE_NAMESIZE MBUSED SPACE MBUSED %
02/07/2012IDX302066.67
02/07/2012SYSAUX600418.5669.76
02/07/2012SYSTEM700362.2551.75
02/07/2012TBL402255
02/07/2012TEMP2000
02/07/2012UNDOTBS12453.251.33
02/07/2012USERS5480
09/07/2012IDX302583.33
09/07/2012SYSAUX600418.5669.76
09/07/2012SYSTEM700362.2551.75
09/07/2012TBL452657.78
09/07/2012TEMP2000
09/07/2012UNDOTBS12453.251.33
09/07/2012USERS54.896
16/07/2012IDX3528.882.29
16/07/2012SYSAUX600418.5669.76
16/07/2012SYSTEM700362.2551.75
16/07/2012TBL453577.78
16/07/2012TEMP2000
16/07/2012UNDOTBS12453.251.33
16/07/2012USERS75.578.57

If we want to see the differences between two dates:
  SELECT LAST.TABLESPACE_NAME,
         ROUND ( (LAST.TABLESPACE_SIZE - FIRST.TABLESPACE_SIZE) / 1024 / 1024,
                2)
            "SIZE GROWTH MB",
         ROUND ( (LAST.USED_SPACE - FIRST.USED_SPACE) / 1024 / 1024, 2)
            "USED SPACE GROWTH MB",
         ROUND (LAST.USED_PERCENT - FIRST.USED_PERCENT, 2) "USED % GROWTH"
    FROM (SELECT TABLESPACE_NAME,
                 TABLESPACE_SIZE,
                 USED_SPACE,
                 USED_PERCENT
            FROM HELPDESK.TABLESPACE_STATS
           WHERE TRUNC (COLLECTION_DATE) = '16-JUL-12') LAST,
         (SELECT TABLESPACE_NAME,
                 TABLESPACE_SIZE,
                 USED_SPACE,
                 USED_PERCENT
            FROM HELPDESK.TABLESPACE_STATS
           WHERE TRUNC (COLLECTION_DATE) = '02-JUL-2012') FIRST
   WHERE LAST.TABLESPACE_NAME = FIRST.TABLESPACE_NAME
ORDER BY 1;

TABLESPACE_NAMESIZE GROWTH MBUSED SPACE GROWTH MBUSED % GROWTH
IDX58.815.62
SYSAUX000
SYSTEM000
TBL51322.78
TEMP000
UNDOTBS1000
USERS21.5-1.43

So, for instance, in 2 weeks, for tablespace TBL we gave 5 MB extra space, but 13 MB were additionally used, thus increasing its used percent by 22,78%.

1 comment:

  1. Thanks for this. Enabled me to convince management that the very tiny, tiny overhead in running this is a good thing.

    ReplyDelete