Skip to Content

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

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. Fidel Vales

    Hi,

    Interesting approach, I like the query for the quick overview. In this case lots of very small tables take the space.

    But, why don’t you use DB02?

    DB02 => Segments => Overview

    Then you can choose between

    1) Top Sizes

    2) Top Growth (per day/week/month)

    also you can see the history of a specific object using

    DB02 => Segments => Detail analysis

    Note that the data is not in real time but a job gather the information

    (0) 
    1. Tom Cenens

      Hi Fidel

      Good point.

      I would like to add more to this even, why not use Data Volume Management in Solution Manager?

      You can get much more useful information out of DVM.

      Best regards

      Tom

      (0) 
    2. Bhavesh Patel Post author

      Thanks Fidel, Tom.

      I haved used DB02 also.

      But, as i experianced it, DB02 was not giving me a way to compare growth of an object month-on-month. eg. if my table BSIS is grown from 80GB to 140GB in last 10 month, what was its growth pattern month-on-month ? was that object increased gradually over 10 months, or it was not gworing high in first 8 month & grown suddenly in last 2 month only.

      And, secondly, in DB02, it gives data as on last refreshed (with report RSORACOLR), it doesn’t give values as-on-that-moment.

      Feel free to use my code and give feedback.

      Thanks again,

      Bhavesh Patel

      TCS, Ahmedabad

      (0) 
  2. Venkatramani Hariharan

    Hi Mr.Patel,

    Good info!

    As mentioned by Fidel , We can use existing SAP transaction codes itself.

    We had used to DB02OLD , detailed analysis   where you can restrict the selections based on size . If you restrict size based on 1 GB , it displays all the segments size greater than 1 GB . If you execute periodically and present in excel ( Management people like excel and graphs you know 🙂 ) it will be nice, We used to present in that way and it works well with less effort.Even guys with less DBA expertise can work with that and create report.

    However, thanks for sharing the idea and nicely presented on the percentage about less space utilized segments.

    I haven’t explored DVM in solman yet. could be better option.

    Thanks

    Venkat

    (0) 
    1. Bhavesh Patel Post author

      Hi Venkat,

      Thanks for giving feedback.

      I agree with yout inputs.

      But, in using DB02, i have to execute it (after giving different selection criteria, eg. >1 GB), then i have to export the result to XL. And, next time, i have to do the same again. Next tine, if there is need to fetche objects >2GB, i can not compare this out-out with the previous one (coz, previous o/p has more result with >1GB input).

      A agree with your second point that, DB02 can be operated by less experianced DBAs also.

      I have created this query tool, with following benifits in mind:

      • If the admin person changes often, the exported XL can not be tracked well & the replacement BASIS / Admin will not have past data. In my approach, data will remain stored in the database even after 10 years. It will not occupy any large space, coz, we are storing >5GB objects only.
      • Month-over-Month or year-over-year comparision can be done once i open spool file in XLs.

      Feel free to use my code & continue to give feedback.

      Thanks again,

      Bhavesh Patel

      TCS, Ahmedabad

      (0) 

Leave a Reply