News

Stay up to date on the latest news from Rubicon Red. News, articles, customer success and press releases.

Monitoring DB Growth for FMW

June 09, 2015

While working on the customer site, I am sure most of us would have encountered the following questions(or more) from the clients (especially the DBA team):

  • How to track the table space growth for fusion middleware products and SOA-INFRA in particular?
  • When a new business process is deployed in to an environment, how to determine the  amount of table space its instances will require over time?
  • What will be the impact of increasing the SOA audit level on the table space growth ?

Its very difficult to answer the above questions upfront, but if we implement a monitoring solution on the table space growth, we could potentially be able to answer them. This blog aims at providing a solution that monitors the database growth both at the table and tablespace level.

This solution can be scheduled at a  weekly/Monthly frequency on production to monitor how the table space is growing (or) can be used to capture the snapshot before & after a load test to understand how much growth the table space has undergone. This could act as a vital statistic for increasing the table space whenever a new business process is rolled out to production.

The solution contains two tables to hold the db growth statistics :

  • SCH_TBL_SIZE_STATS_HDR - Captures the table space level growth statistics
  • SCH_TBL_SIZE_STATS_DTL - Captures the table level growth statistics

The below script creates the above mentioned tables and ideally requires to be created under a schema that has the DBA privileges to monitor any required tablespace:

  1. /**
  2. #####################################################################
  3. Table Spec - SCH_TBL_SIZE_STATS_HDR & SCH_TBL_SIZE_STATS_DTL
  4. #####################################################################
  5. @schema_table_size_stats_tbl_script.sql
  6. Tables to contain the statistics regarding the tablespace size growth by schema.
  7. Copyright Rubicon Red Pty Ltd
  8. Author - gkrishna
  9. **/
  10. DROP TABLE SCH_TBL_SIZE_STATS_DTL
  11. /
  12. DROP TABLE SCH_TBL_SIZE_STATS_HDR
  13. /
  14. DROP SEQUENCE SCH_TBL_SIZE_STATS_DTL_SEQ
  15. /
  16. DROP SEQUENCE SCH_TBL_SIZE_STATS_HDR_SEQ
  17. /
  18. CREATE SEQUENCE SCH_TBL_SIZE_STATS_HDR_SEQ
  19.   START WITH 1 INCREMENT BY 1 NOCACHE;
  20. /
  21. CREATE SEQUENCE SCH_TBL_SIZE_STATS_DTL_SEQ START WITH 1 INCREMENT BY 1 NOCACHE;
  22. /
  23. CREATE
  24.     TABLE SCH_TBL_SIZE_STATS_HDR
  25.     (
  26.       SCH_TBL_SIZE_STATS_HDR_ID NUMBER(18) PRIMARY KEY,
  27.       OWNER_SCHEMA              VARCHAR2(30) NOT NULL,
  28.       RUN_DATE                  DATE NOT NULL,
  29.       MB_ALLOCATED              NUMBER NOT NULL,
  30.       MB_FREE                   NUMBER NOT NULL,
  31.       MB_USED                   NUMBER NOT NULL,
  32.       PCT_FREE                  NUMBER NOT NULL,
  33.       PCT_USED                  NUMBER NOT NULL
  34.     );
  35.   /
  36. CREATE
  37.     TABLE SCH_TBL_SIZE_STATS_DTL
  38.     (
  39.       SCH_TBL_SIZE_STATS_DTL_ID NUMBER(18) PRIMARY KEY,
  40.       SCH_TBL_SIZE_STATS_HDR_ID NUMBER(18) NOT NULL,
  41.       TABLE_NAME                VARCHAR2(30) NOT NULL,
  42.       NO_OF_ROWS                NUMBER(15) NOT NULL,
  43.       TABLE_SIZE_IN_MB          NUMBER,
  44.       CONSTRAINT SCH_TBL_SIZE_STATS_HDR_FK FOREIGN KEY(
  45.       SCH_TBL_SIZE_STATS_HDR_ID) REFERENCES SCH_TBL_SIZE_STATS_HDR(
  46.       SCH_TBL_SIZE_STATS_HDR_ID)
  47.     );
  48.   /

The below package contains the procedure 'GATHER_SCHEMA_TABLE_SIZE' to gather the table space growth statistics and it needs to be created/compiled on the same schema as the above tables:

Package Specification

  1. --Package Specification
  2. CREATE OR REPLACE PACKAGE SCH_TBL_SIZE_STATS_PKG
  3. AS
  4.   --Type to hold the list of schema for which the statistics needs to be calculated.
  5.   TYPE SCHEMA_LIST IS TABLE OF VARCHAR2(30);
  6.   -- Procedure to gather the schema statistics
  7.   PROCEDURE GATHER_SCHEMA_TABLE_SIZE(
  8.       P_SCHEMA_LIST IN SCH_TBL_SIZE_STATS_PKG.SCHEMA_LIST);
  9.   -- Procedure to clean up the stats before re-runs for the same day
  10.   PROCEDURE CLEANUP_STATS(
  11.       P_SCHEMA_NAME IN VARCHAR2 ,
  12.       P_RUN_DATE    IN DATE);
  13. END SCH_TBL_SIZE_STATS_PKG;
  14. /

Package Body

  1. --Package Body
  2. CREATE OR REPLACE PACKAGE BODY SCH_TBL_SIZE_STATS_PKG
  3. AS
  4. -- Procedure to gather the schema statistics
  5. PROCEDURE GATHER_SCHEMA_TABLE_SIZE(
  6.     P_SCHEMA_LIST IN SCH_TBL_SIZE_STATS_PKG.SCHEMA_LIST)
  7. IS
  8.   CURSOR LIST_SCHEMA_TABLES_CUR(P_OWNER VARCHAR2)
  9.   IS
  10.     SELECT
  11.       OBJECT_ID,
  12.       OBJECT_NAME
  13.     FROM
  14.       DBA_OBJECTS
  15.     WHERE
  16.       OBJECT_TYPE = 'TABLE'
  17.     AND OWNER     = P_OWNER
  18.     AND STATUS    = 'VALID'
  19.     AND GENERATED = 'N'
  20.     AND OBJECT_NAME NOT LIKE '%$%'; --System tables.
  21.   l_index  NUMBER;
  22.   l_hdr_id       NUMBER(18);
  23.   l_dtl_id       NUMBER(18);
  24.   l_schema_found VARCHAR2(1);
  25. BEGIN
  26.   FOR l_index IN P_SCHEMA_LIST.FIRST .. P_SCHEMA_LIST.LAST
  27.   LOOP
  28.     -- check to make sure the schema exists otherwise just continue with the
  29.     -- rest of the schemas in the list.
  30.     BEGIN
  31.       SELECT
  32.         'Y'
  33.       INTO
  34.         l_schema_found
  35.       FROM
  36.         DBA_USERS
  37.       WHERE
  38.         USERNAME = P_SCHEMA_LIST(l_index);
  39.     EXCEPTION
  40.     WHEN NO_DATA_FOUND THEN
  41.       dbms_output.put_line('Invalid Schema'||P_SCHEMA_LIST(l_index));
  42.       CONTINUE;
  43.     END;
  44.     --clean up the statistics if it already exists for the day.
  45.     CLEANUP_STATS(P_SCHEMA_LIST(l_index),SYSDATE);
  46.     -- getting the primary key value for the header table.
  47.     SELECT
  48.       SCH_TBL_SIZE_STATS_HDR_SEQ.NEXTVAL
  49.     INTO
  50.       l_hdr_id
  51.     FROM
  52.       DUAL;
  53.     -- populating the header table with schema level details
  54.     INSERT
  55.     INTO
  56.       SCH_TBL_SIZE_STATS_HDR
  57.       (
  58.         SCH_TBL_SIZE_STATS_HDR_ID,
  59.         OWNER_SCHEMA,
  60.         RUN_DATE,
  61.         MB_ALLOCATED,
  62.         MB_FREE,
  63.         MB_USED,
  64.         PCT_FREE,
  65.         PCT_USED
  66.       )
  67.     SELECT
  68.       *
  69.     FROM
  70.       (
  71.         SELECT
  72.           l_hdr_id,
  73.           a.tablespace_name OWNER_SCHEMA,
  74.           SYSDATE RUN_DATE,
  75.           ROUND(a.bytes /1048576,2) MB_ALLOCATED,
  76.           ROUND(b.bytes /1048576,2) MB_FREE ,
  77.           ROUND((a.bytes-b.bytes)/1048576,2) MB_USED,
  78.           ROUND(b.bytes /a.bytes * 100,2) PCT_FREE,
  79.           ROUND((a.bytes-b.bytes)/a.bytes,2) * 100 PCT_USED
  80.         FROM
  81.           (
  82.             SELECT
  83.               tablespace_name,
  84.               SUM(a.bytes) bytes
  85.             FROM
  86.               DBA_DATA_FILES a
  87.             GROUP BY
  88.               tablespace_name
  89.           )
  90.           a,
  91.           (
  92.             SELECT
  93.               a.tablespace_name,
  94.               NVL(SUM(b.bytes),0) bytes
  95.             FROM
  96.              DBA_DATA_FILES a,
  97.              DBA_FREE_SPACE b
  98.             WHERE
  99.               a.tablespace_name = b.tablespace_name (+)
  100.             AND a.file_id       = b.file_id (+)
  101.             GROUP BY
  102.               a.tablespace_name
  103.           )
  104.           b,
  105.           DBA_TABLESPACES c
  106.         WHERE
  107.           a.tablespace_name   = b.tablespace_name(+)
  108.         AND a.tablespace_name = c.tablespace_name
  109.         AND a.tablespace_name = P_SCHEMA_LIST(l_index)
  110.         ORDER BY
  111.           a.tablespace_name
  112.       );
  113.     -- Now find all the non-system tables in the schema and then populate the
  114.     -- statistics
  115.     -- to the detail table
  116.     FOR tab IN LIST_SCHEMA_TABLES_CUR(P_SCHEMA_LIST(l_index))
  117.     LOOP
  118.       -- make sure we compute the statistics first before calculating the table
  119.       -- size.
  120.       EXECUTE immediate 'ANALYZE TABLE '||P_SCHEMA_LIST(l_index)||'.'||tab.OBJECT_NAME||
  121.       ' COMPUTE STATISTICS';
  122.       -- getting the primary key value for the detail table.
  123.       SELECT
  124.         SCH_TBL_SIZE_STATS_DTL_SEQ.NEXTVAL
  125.       INTO
  126.         l_dtl_id
  127.       FROM
  128.         DUAL;
  129.       -- populating the statistics for each table.
  130.       INSERT
  131.       INTO
  132.         SCH_TBL_SIZE_STATS_DTL
  133.         (
  134.           SCH_TBL_SIZE_STATS_DTL_ID,
  135.           SCH_TBL_SIZE_STATS_HDR_ID,
  136.           TABLE_NAME,
  137.           NO_OF_ROWS,
  138.           TABLE_SIZE_IN_MB
  139.         )
  140.       SELECT
  141.         l_dtl_id,
  142.         l_hdr_id,
  143.         table_name,
  144.         NVL(num_rows,0) ,
  145.         (
  146.           SELECT
  147.             SUM(bytes_in_mb) AS total_size_in_mb
  148.           FROM
  149.             (
  150.               SELECT
  151.                 dbs.bytes/(1024)/(1024) AS bytes_in_mb
  152.               FROM
  153.                 dba_segments dbs,
  154.                 dba_lobs dbl
  155.               WHERE
  156.                 dbl.table_name    =tab.OBJECT_NAME
  157.               AND dbs.segment_name=dbl.segment_name
  158.               UNION
  159.               SELECT
  160.                 dbs.bytes/(1024)/(1024) AS bytes_in_mb
  161.               FROM
  162.                 dba_segments dbs,
  163.                 dba_indexes dbi
  164.               WHERE
  165.                 dbi.table_name    =tab.OBJECT_NAME
  166.               AND dbs.segment_name=dbi.index_name
  167.               UNION
  168.               SELECT
  169.                 dbs.bytes/(1024)/(1024) AS bytes_in_mb
  170.               FROM
  171.                 dba_segments dbs,
  172.                 dba_tables dbt
  173.               WHERE
  174.                 dbt.table_name    =tab.OBJECT_NAME
  175.               AND dbs.segment_name=dbt.table_name
  176.             )
  177.             tbl_size
  178.         ) AS total_size_in_mb
  179.       FROM
  180.         dba_tables tbl
  181.       WHERE
  182.         tbl.table_name=tab.OBJECT_NAME
  183.       AND tbl.owner   =P_SCHEMA_LIST(l_index);
  184.       -- May be we need a better strategy here for commit.. for now
  185.       -- this should be ok
  186.       COMMIT;
  187.     END LOOP;
  188.   END LOOP;
  189. END GATHER_SCHEMA_TABLE_SIZE;
  190. -- Procedure to clean up the stats before re-runs for the same day
  191. PROCEDURE CLEANUP_STATS(
  192.     P_SCHEMA_NAME IN VARCHAR2 ,
  193.     P_RUN_DATE    IN DATE)
  194. IS
  195. BEGIN
  196.   --deleting the detail table statistics for the given schema
  197.   DELETE
  198.   FROM
  199.     SCH_TBL_SIZE_STATS_DTL
  200.   WHERE
  201.     SCH_TBL_SIZE_STATS_HDR_ID IN
  202.     (
  203.       SELECT
  204.         SCH_TBL_SIZE_STATS_HDR_ID
  205.       FROM
  206.         SCH_TBL_SIZE_STATS_HDR
  207.       WHERE
  208.         TRUNC(RUN_DATE) = TRUNC(P_RUN_DATE)
  209.       AND OWNER_SCHEMA  = P_SCHEMA_NAME
  210.     );
  211.   --deleting the header table statistics for the given schema
  212.   DELETE
  213.   FROM
  214.     SCH_TBL_SIZE_STATS_HDR
  215.   WHERE
  216.         TRUNC(RUN_DATE) = TRUNC(P_RUN_DATE)
  217.     AND OWNER_SCHEMA  = P_SCHEMA_NAME;
  218.     COMMIT;
  219. END CLEANUP_STATS;
  220. END SCH_TBL_SIZE_STATS_PKG;
  221. /

Now lets execute the procedure to analyze the statistics for SOA-INFRA schema:

  1. DECLARE
  2. l_schema_list SCH_TBL_SIZE_STATS_PKG.SCHEMA_LIST;
  3. BEGIN
  4.   l_schema_list := SCH_TBL_SIZE_STATS_PKG.SCHEMA_LIST('DEV_SOAINFRA');
  5.   SCH_TBL_SIZE_STATS_PKG.GATHER_SCHEMA_TABLE_SIZE(l_schema_list);
  6. END;
  7. /

Once the script finishes, lets check the generated data:

  1. SELECT * from SCH_TBL_SIZE_STATS_HDR

FMW DB Growth statistics tablespace level

The above result set shows the statistics for 25th & 26th September 2014 and it can be seen that there is a growth of 20 MB over all in the schema. Querying the detail table should give us the statistics at the table level:

  1. SELECT * from SCH_TBL_SIZE_STATS_DTL WHERE SCH_TBL_SIZE_STATS_HDR_ID = 9 ORDER BY TABLE_SIZE_IN_MB DESC

FMW DB Growth statistics table level

Above is the snapshot of table-level growth for 26th September and simple queries can be written to calculate the difference based on the previous run dates to identify how much the table has grown in rows/size.