Monitor large and growing objects in Oracle Database - A practical approach

[bhavesh_patel3]

Monitor large and growing objects in Oracle Database - A practical approach

Growing Objects in the Database is of the up most concerns for the DBAs/BASIS/Infra Team.If we do not keep close watch on the growing database objects, at a later time, it may create performance problems and unnecessary large database space (i.e. storage space) requirement. We may need to give right justification to upper management about what objects/tables are growing ( i.e. tables are growing due to business transactions or they are system/BASIS related tables).

There are SAP Note (706478) which tells which all tables are Basis tables.

In this blog, i take an example of ORACLE Database (Version 8i/9i/10g/11g).

Total size of the Database and Free space can be viewed in transaction code DB02.

In this example:

Total Database size: 1532.49 GBFree Space: 390.72 GBUsed Space: 1141.77 GB

Above Used Space can be verified by following SQL SELECT query:

select  (sum(blocks)*8192)/(1024*1024*1024) USED_GB from dba_segments ;

Total count of objects in the Database: 160382.

Above total objects are of various segment types - like: TABLE, INDEX, LOBINDEX, TABLE PARTITION, LOBSEGMENT, CLUSTER, INDEX PARTITION etc.

These all objects are occupying some space in the database. They occupy space in the chunks of blocks.

Size-wise distribution of the above objects can be obtained by following SQL SELECT query (run as SYSTEM account):

COMPUTE SUM OF SEG_COUNT ON REPORT

BREAK ON REPORT

SELECT CASE

    WHEN (BLOCKS * 8192/(1024*1024     ))  <= 10    THEN '0      0 to 10  MB'

    WHEN (BLOCKS * 8192/(1024*1024     ))  <= 100   THEN '1  10 MB to 100 MB'

    WHEN (BLOCKS * 8192/(1024*1024*1024))  <= 0.5   THEN '2 100 MB to 500 MB'

    WHEN (BLOCKS * 8192/(1024*1024*1024))  <= 1     THEN '3 500 MB to 1   GB'

    WHEN (BLOCKS * 8192/(1024*1024*1024))  <= 5     THEN '4      1 to 5   GB'

    WHEN (BLOCKS * 8192/(1024*1024*1024))  <= 10    THEN '5      5 to 10  GB'

    WHEN (BLOCKS * 8192/(1024*1024*1024))  <= 50    THEN '6     10 to 50  GB'

    WHEN (BLOCKS * 8192/(1024*1024*1024))  <= 100   THEN '7     50 to 100 GB'

    WHEN (BLOCKS * 8192/(1024*1024*1024))  <= 250   THEN '8    100 to 250 GB'

    WHEN (BLOCKS * 8192/(1024*1024*1024))  <= 500   THEN '9    250 to 500 GB'

    ELSE '10          500+ GB'

  END AS SIZE_RANGE,

  COUNT(*) AS SEG_COUNT,

  to_char( ratio_to_report( count(*) ) over ()*100, '999.999') ||' %' AS "       PCT"

FROM dba_segments

  GROUP BY CASE

    WHEN (BLOCKS * 8192/(1024*1024     ))  <= 10    THEN '0      0 to 10  MB'

    WHEN (BLOCKS * 8192/(1024*1024     ))  <= 100   THEN '1  10 MB to 100 MB'

    WHEN (BLOCKS * 8192/(1024*1024*1024))  <= 0.5   THEN '2 100 MB to 500 MB'

    WHEN (BLOCKS * 8192/(1024*1024*1024))  <= 1     THEN '3 500 MB to 1   GB'

    WHEN (BLOCKS * 8192/(1024*1024*1024))  <= 5     THEN '4      1 to 5   GB'

    WHEN (BLOCKS * 8192/(1024*1024*1024))  <= 10    THEN '5      5 to 10  GB'

    WHEN (BLOCKS * 8192/(1024*1024*1024))  <= 50    THEN '6     10 to 50  GB'

    WHEN (BLOCKS * 8192/(1024*1024*1024))  <= 100   THEN '7     50 to 100 GB'

    WHEN (BLOCKS * 8192/(1024*1024*1024))  <= 250   THEN '8    100 to 250 GB'

    WHEN (BLOCKS * 8192/(1024*1024*1024))  <= 500   THEN '9    250 to 500 GB'

    ELSE '10          500+ GB'

END

order by SIZE_RANGE

;

Output of above query:

SIZE_RANGE           SEG_COUNT         PCT

------------------- ----------  ----------

0      0 to 10  MB      148769    92.759 %

1  10 MB to 100 MB        9107     5.678 %

2 100 MB to 500 MB        1442     0.899 %

3 500 MB to 1   GB         887     0.553 %

4      1 to 5   GB         149     0.093 %

5      5 to 10  GB          21     0.013 %

6     10 to 50  GB           6     0.013 %

7     50 to 100 GB           1     0.001 %

                    ----------

sum                     160382

Thus, out of total 160382 objects, only 177 objects ( 0.120% of total objects)  are having > 1 GB size.

So, instead of working on all 160382 objects, lets concentrate on those few, large objects only.

I propose following at ORACLE/SQL level to achieve this:

Create a small table in database, create an index on it, and populate it once a month - every month.Below two steps guides on the same:

1)  Create  Table and index:

CREATE TABLE ZSIZES_OF_OBJECTS

AS

SELECT SYSDATE SIZE_ON_DATE, a.SEGMENT_NAME, a.BLOCKS, a.OWNER, a.PARTITION_NAME, a.SEGMENT_TYPE, a.TABLESPACE_NAME

FROM DBA_SEGMENTS A WHERE (BLOCKS * 8192/(1024*1024*1024)) <= 1;

CREATE INDEX zsizes_of_objects_i on zsizes_of_objects ( SIZE_ON_DATE ,SEGMENT_NAME );

2) Populate above table every month: (on a pre-defined date, say 1st of every month):

INSERT INTO ZSIZES_OF_OBJECTS

SELECT SYSDATE, a.SEGMENT_NAME, a.BLOCKS, a.OWNER, a.PARTITION_NAME, a.SEGMENT_TYPE, a.TABLESPACE_NAME

FROM DBA_SEGMENTS A WHERE (BLOCKS * 8192/(1024*1024*1024)) <= 1;

After few month, above table (named TABLE ZSIZES_OF_OBJECTS) will hold GOOD, DOABLE, COMPACT, USEFUL, ANALYZABLE practice/information about top-growing tables/objects in ORACLE Database in the SAP system.

Hope this helps!

Please feel free to revert back  your with concerns/clarifications/Appreciation.

Best Regards,

Bhavesh Patel

TATA CONSULTANCT SERVICES