Monitor database size

From the time I was an APPS DBA on largest EBS project in Croatia, I learned that monitoring database has important role of any DBA task. This is not only because of storage planning but also to see if anything irregular is happening in database. Here is the true story and how I managed that unpleasant situation and help developers to find their bugs and calm down managers about capacity planning and budgeting!

All here mentioned (including data) are real values from one EBS instance so many experienced APPS DBA may compare their sizes with here shown and make some conclusions.

The story

At the beginning, for the non experienced EBS people let me tell that in EBS 11.5.10.2 there are more then 22.100 tables in database, so knowing them or monitoring (like it is on systems with several hundred tables) is more then not possible without good tool or some automated help.

Case nr. 1.

The problem arise when my EBS instance grow in two months for more then 50%, from 105 GB to 155GB. I have create SR and ask them what should I suspect on and get an answer: what has grow up from previous period? I was astonished that they have no clue for their lead product what could really grow so fast. The best of all was when a lady from SR said: "The best should be that you contact your application provider about details what is really growing!"... !??

The situation was outrageous when my database fall in severity 1, which was direct circumstance of uncontrolled grow of some tables (which should be purged and controlled in any mean)! Situation from that moment look like:

Top 5 objects by NUMBER of records row. Monitored periods 20070901-20070701
OWNER            SEGMENT_NAME                             SEGMENT_TYPE                 NR_RECORDS       REC_GROW                %
-------------------------------------------------------------------------------------------------------------------------------------
XXHY             XXHY_CC_PAY_PLAN_AMOUNTS_ALL             TABLE                        41.613.708       8.010.912              23,84
APPLSYS          WF_ITEM_ATTRIBUTE_VALUES                 TABLE                         7.709.606       7.591.294           6.416,33
APPS             XXHY_CC_PAYMENT_PLAN_DISCO_MV            TABLE                        39.684.371       6.288.759              18,83
GL               GL_JE_LINES                              TABLE                        11.314.140       4.550.960              67,29

Top 5 objects by SIZE grow. Monitored periods 20070901-20070701
OWNER            SEGMENT_NAME                             SEGMENT_TYPE                  SIZE [MB]    grow [MB]          %
----------------------------------------------------------------------------------------------------------------------------------
AR               AR_TAX_EXTR_SUB_COM_EXT                  TABLE                          3.804,75     3.804,63   3.043.700,00
AR               AR_TAX_EXTRACT_SUB_ITF                   TABLE                          3.202,13     3.202,00   2.561.600,00
XXHY             XXHY_HLK_POSTING_CARD_XML                TABLE                          2.435,13     2.434,88     973.950,00
GL               GL_JE_LINES                              TABLE                          2.510,63     1.330,00         112,65

After enabling some purging concurrents, as I was having 24x7 Oracle people on site, and they didn’t show any special interest about my complaints on database grow subject, I left this case beside for "some better time".

Case nr. 2.

This time came when 7 months later, database size raised from 85GB to 250 GB with average growing of 30-40 GB per month (with every month bigger grow!). Situation from that moment looks like:

Top 5 objects by NUMBER of records grow. Monitored periods 20080101-20070701
OWNER            SEGMENT_NAME                             SEGMENT_TYPE                 NR_RECORDS       REC_GROW                %
-------------------------------------------------------------------------------------------------------------------------------------
XXHY             XXHY_CC_PAY_PLAN_AMOUNTS_ALL             TABLE                        59.309.318      25.706.522              76,50
GL               GL_JE_LINES                              TABLE                        28.111.423      21.348.243             315,65
APPS             XXHY_CC_PAYMENT_PLAN_DISCO_MV            TABLE                        53.388.260      19.992.648              59,87
GL               GL_IMPORT_REFERENCES                     TABLE                        17.497.831      17.457.633          43.429,11
AR               AR_DISTRIBUTIONS_ALL                     TABLE                        16.409.478      11.893.345             263,35

Top 5 objects by SIZE grow. Monitored periods 20080101-20070701
OWNER            SEGMENT_NAME                             SEGMENT_TYPE                  SIZE [MB]    grow [MB]          %
----------------------------------------------------------------------------------------------------------------------------------
GL               GL_JE_LINES                              TABLE                          6.995,25     5.814,63         492,50
APPS             XXHY_CC_PAYMENT_PLAN_DISCO_MV            TABLE                         11.014,00     3.904,00          54,91
GL               GL_IMPORT_REFERENCES                     TABLE                          2.758,13     2.752,25      46.846,81
AR               AR_RECEIVABLE_APPLICATIONS_ALL           TABLE                          3.129,75     2.406,38         332,66
XXHY             XXHY_CC_PAY_PLAN_AMOUNTS_ALL             TABLE                          4.750,63     2.105,63          79,61

And GL.GL_JE_LINES history data looks like:

GL.GL_JE_LINES(TABLE)
         Period     SIZE MB   TREND MB        TREND %      CUMUL %          RECORDS      TREND REC        TREND %         CUMUL %
        --------  --------------------------------------------------    -----------------------------------------------------------
        20070701    1.180,63    1.180,63          0,00          0,00        6.763.180     6.763.180            0,00            0,00
        20070901    2.510,63    1.330,00        112,65        112,65       11.314.140     4.550.960           67,29           67,29
        20071101    5.174,38    2.663,75        106,10        338,27       21.853.222    10.539.082           93,15          223,12
        20080101    6.995,25    1.820,87         35,19        492,50       28.111.423     6.258.201           28,64          315,65
        20080301    8.678,25    1.683,00         24,06        635,05       33.910.408     5.798.985           20,63          401,40
        20080501   10.009,75    1.331,50         15,34        747,83       38.756.116     4.845.708           14,29          473,05
        20080701   10.841,00      831,25          8,30        818,24       41.629.108     2.872.992            7,41          515,53
        20080802   13.160,13    2.319,13         21,39      1.014,67       49.606.017     7.976.909           19,16          633,47
        20080901   13.677,50      517,37          3,93      1.058,49       51.380.285     1.774.268            3,58          659,71
        20081001   14.812,63    1.135,13          8,30      1.154,64       55.277.203     3.896.918            7,58          717,33
        ===========================================================================================================================

Now with DSS tool it is very easy to see that GL_JE_LINES is growing very rapidly (first in Top 5 for longer period!) without any known reason. In that time I was having some primitive SQLs for monitoring size and with this primitive tool I saw that GL_JE_LINES growing-I was not aware how fast and what is the trend of grow. Unfortunatelly I was not aware what else is growing and the impact of that to the performance.

When I say my findings to Oracle people, firstly they defend that our employees have entered manually all those entries. After some math calculation, because number or inserted records was really huge, it was obvious that this is not possible to do manually in such a short period. After that I contacted directly Oracle’s lead developer. He realize very soon that "Oracle feature" was in our environment reproduced as a bug.

The feature is that Oracle automatically tries book out unallocated items in the general ledger. This is done through one concurrent which runs every night (right now I cannot remember the name). This concurrent is programmed to work in a way that firstly inserts unallocated items and when finds out that there are irresolvable errors, it insert all inserted items again with contra sign, creating in this case two times of all unallocated items on every job. If I say that in our database we have huge number of such an unknown items, every night GL_JE_LINES was getting bigger and bigger without reason. And users were complaining. So I was the initiator of bug resolving without knowledge what is going on!

In that moment I was determent to start reacting regardless all! This is the time I wrote first version of Database Size Stat solution. All after is nice history….

The solution

Database Size Stat (DSS) solution is set of two tables and one package. Role of package (db_size_pkg) is to collect data from database and use as common storage for all exposed procedures. Collection is taken on some regular basis into one table (db_size). In mine case 2 months were enough for my needs…but you can make run it more often if you want.

When data are collected (for at least two different dates), then with many other procedures from db_size_pkg package anyone it is easy to see anything about database size: size, grow, trend, history comparison, new/dropped objects and all other possible statistic in database which I ound interesting.

DSS is tested against 10/11g databases and is not compatible with lower versions of Oracle because analytic function calls that I use in package.

The implementation

Implementation is really easy and takes only 4 steps.

1. Create db_size.tbl index organized table 
2. Create db_size_gtt.tbl global temporary table
3. Install db_size_pkg.pks package source
4. Install db_size_pkb.plb wrapped package body (10g and onwards)
5. If you want you to automate the process of taking snapshots (I recommend that) you may do that through ordinary Oracle job. This example is based on one month window:
   
   DECLARE
     X NUMBER;
   BEGIN
     SYS.DBMS_JOB.SUBMIT
     ( job       => X
      ,what      => 'db_size_pkg.create_snapshot;'
      ,next_date => to_date('01.09.2008 00:01:00','dd/mm/yyyy hh24:mi:ss')
      ,interval  => 'TRUNC(LAST_DAY(SYSDATE)) + 1+1/1440'
      ,no_parse  => FALSE
     );
     SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
   COMMIT;
   END;

All those objects should be placed in schema with enough privileges to compile package. There is no need to accent that SYS schema may be gracefully avoided as much as possible for purposes like this. Reason is more then known...

The common part

Several things are conditio sine qua non to do before running any other procedure.

"create_snapshot" procedure

The whole snapshot is done through single procedure - create_snapshot. Create snapshot iterate through dba_segments view and collect statistic on all segment_names. Runing the procedure is very easy:

PROCEDURE create_snapshot (p_period in varchar2 DEFAULT 'TODAY');

However, running snapshots should be taken with sysdba role user or user with enough privileges to run select queries on many dba_ views.

Because snapshot is connected with period in all following text I’ll referenced snapshot as term of period.
If no p_period is not set then sysdate is taken as default.

Period in all examples and varchar2 type of data and always in format YYYYMMDD regardless NLS settings of database/instance/session you run from. In this way you'll always get the correct sort order and less formatting functions in result outputs.

In mine case I was initially restoring database from 1st July 2007 to 1st November 2008 in two moths periods, taking snapshot one by one. That was very annoying work, but beside checking backups and RMAN catalogs (you have done that of course!?), it was essential to get initial data to start using DSS with full lungs.

handle "create_snapshot" errors

If you run "create_snapshot" with non sysdba role user, some of objects might not be accessible. In previous version the whole snapshot action was roll backed. From version 1.4 in such a cases, analyzed object get a number of records "-1" and size "0" and procedure continue to run. This allows DBA for later select of such an objects and grant appropriate right to user that run snapshot.

Beside that, if you run "create_snapshot" procedure from sqlplus like tool, in a case of any error you get a complete description of error. You can achieve this in a way:

SQL> set serveroutput on size 123456;
SQL>  exec db_size_pkg.create_snapshot;
----------------------------------------------------
ORA-01031: insufficient privileges
SELECT count(*) FROM
"SYS"."LINK$"

ORA-06512: at "TOOLS.DB_SIZE_PKG", line 994

----- PL/SQL Call
Stack -----
  object      line  object
  handle    number
name
0x800000017e95ded0      1000  package body
TOOLS.DB_SIZE_PKG
0x800000017e2abd70         1  anonymous block

----------------------------------------------------
----------------------------------------------------
ORA-01031: insufficient privileges
SELECT count(*) FROM
"SYS"."USER_HISTORY$"

ORA-06512: at "TOOLS.DB_SIZE_PKG", line 994

----- PL/SQL
Call Stack -----
  object      line  object
  handle    number
name
0x800000017e95ded0      1000  package body
TOOLS.DB_SIZE_PKG
0x800000017e2abd70         1  anonymous block

----------------------------------------------------
Snapshot added 12648 new records in db_size table

*************************************************************
                  Database Size Stat 1.4
        (c) 1992-2010 Damir Vadas, All Rights Reserved.

             Non commercial/production use only!
          All others use only with author permission.
*************************************************************

PL/SQL procedure successfully completed.

SQL>

If you run snapshot from job, you will get no error,but later you may check result with query that search for "nr_records=-1":

SQL> select * from db_size where nr_records=-1;

PERIOD   OWNER  SEGMENT_NAME     SEGMENT_TYPE     TABLESPACE_NAME PART_NAME   NR_BYTES NR_BLOCKS NR_EXTENTS NR_RECORDS
-------- ------ ---------------- ---------------- --------------- ----------- -------- --------- ---------- ----------
20100517 SYS    LINK$             TABLE           SYSTEM          NP              65536        8          1         -1
20100517 SYS    USER_HISTORY$     TABLE           SYSTEM          NP              65536        8          1         -1

SQL>

Granting select privilege on such an objects, you may fix this problem.

"nls_num" number format variable

Number formatting is presented through nls_num variable declared in public part of db_size_pkg. Initial value is based on Croatian NLS number format…but you can change to USA format easy in a way:

nls_num VARCHAR2(30) := 'NLS_NUMERIC_CHARACTERS = '',.'''; 

Example:
  SQL> begin
    2    db_size_pkg.nls_num :=  'NLS_NUMERIC_CHARACTERS = '',.''';
    3    db_size_pkg.dropped_objects();
    4    db_size_pkg.nls_num :=  'NLS_NUMERIC_CHARACTERS = ''.,''';
    5    db_size_pkg.dropped_objects();
    6  end;
    7  /

  ***************************************************************************************************
          0 TOTAL dropped                                             0,000 MB            0 records
  ***************************************************************************************************

  ***************************************************************************************************
          0 TOTAL dropped                                             0.000 MB            0 records
  ***************************************************************************************************

The core procedures

First an main procedure is get_db_size. The main purpose is to show in variety of outputs which describes size of database through periods.

PROCEDURE get_db_size (p_period        IN VARCHAR2 DEFAULT null, 
                       p_list_tblspace IN VARCHAR2 DEFAULT 'N');

Examples:    exec db_size_pkg.get_db_size;
                same as
                  exec db_size_pkg.get_db_size(null,'N');   
             exec db_size_pkg.get_db_size(null,'T');
             exec db_size_pkg.get_db_size(null,'H');
             exec db_size_pkg.get_db_size('20080501'); 
                 same as
                   exec db_size_pkg.get_db_size('20080501,'T');
                 same as
                   exec db_size_pkg.get_db_size('20080501','N'); 
             exec db_size_pkg.get_db_size('20080501,'H');

If no parameter is taken then this is the result:

SQL> exec db_size_pkg.get_db_size;
PERIOD         SIZE MB        TREND MB           RECORDS        TREND REC         REC/1 MB     TR REC/1 MB
==========================================================================================================
20070701      75.241,79           0,00         294.112.834               0        3.908,90            0,00
20070901     103.479,93      28.238,14         346.960.155      52.847.321        3.352,92        1.871,49
20071101     155.292,23      51.812,30         420.892.538      73.932.383        2.710,33        1.426,93
20080101     139.995,03     -15.297,20         438.604.712      17.712.174        3.133,00       -1.157,87
20080301     157.205,12      17.210,09         498.120.194      59.515.482        3.168,60        3.458,17
20080501     168.775,83      11.570,71         562.508.069      64.387.875        3.332,87        5.564,73
20080701     181.967,17      13.191,34         592.960.740      30.452.671        3.258,61        2.308,53
20080802     209.103,60      27.136,43         669.988.712      77.027.972        3.204,10        2.838,54
20080901     216.381,21       7.277,61         683.941.298      13.952.586        3.160,82        1.917,19
20081001     229.736,98      13.355,77         729.811.222      45.869.924        3.176,73        3.434,47
20091201     338.285,95     108.548,98       1.052.294.418     322.483.196        3.110,67        2.970,85
==========================================================================================================

Ass you can see, with such an output all important data on the global level-database. Size grow, grow trend, number of records, records trend and very interesting number of records for 1 MB grow and grow trend. As you can see this procedure gives you very easy way to predict database grow in the future. In our case the main point was contract. So if you connect number of contracts in certain date it is really easy to predict how many MB or records will be if number of contracts grow for 10%.

Another way is to run get_db_size with some p_period parameter value. According parameter is taken some additional output data are shown:

SQL> exec db_size_pkg.get_db_size('20080501');
PERIOD
===========================

===========================
20080501
===========================
         Tablespace                           %       SIZE MB          RECORDS
        -----------------------------------------------------------------------
        APPS_TS_ARCHIVE                     0,463         781          364.090
        APPS_TS_CONVERSION                  0,925       1.561        3.806.325
        APPS_TS_INTERFACE                   1,175       1.983          926.861
        APPS_TS_MEDIA                       1,813       3.059           62.158
        APPS_TS_MVIEWS                      8,003      13.508       66.472.066
        APPS_TS_NOLOGGING                   0,610       1.029        1.268.554
        APPS_TS_QUEUES                      0,892       1.506          446.396
        APPS_TS_SEED                        1,335       2.253        6.690.545
        APPS_TS_SUMMARY                     0,645       1.089        2.070.682
        APPS_TS_TX_DATA                    38,543      65.052      382.452.012
        APPS_TS_TX_IDX                     30,288      51.118            4.974
        CTXD                                0,082         139          404.347
        DISCO                               0,025          42           15.827
        DWH                                 8,947      15.100       43.085.706
        FOGLIGHT_TS                         0,001           3            8.159
        ODM                                 0,006          10           31.774
        PORTAL                              0,000           0                1
        SYSAUX                              0,984       1.661        6.025.698
        SYSTEM                              5,118       8.638       46.732.984
        TOOLS                               0,136         230        1.626.886
        XX_COUNTER                          0,009          15           12.024
        -----------------------------------------------------------------------
                                          100,000     168.776      562.508.069
        -----------------------------------------------------------------------

Such an output gives very nice view on tablespace statistic in chosen period.

If you put "H" parameter with period in front then you get the whole history of all tablespaces in that period:

SQL> exec db_size_pkg.get_db_size('20080501','H');
PERIOD
===========================
===========================
20080501
===========================
APPS_TS_ARCHIVE
         Period   DATABS %    SIZE MB     TREND MB      TREND %       CUMUL %          RECORDS      TREND REC        TREND %         CUMUL %
        --------  --------------------------------------------------------------    --------------------------------------------------------
        20070701    9,218         793         793          0,00          0,00        1.090.993     1.090.993            0,00            0,00
        20070901    8,031         691        -102        -12,88        -12,88          524.483      -566.510          -51,93          -51,93
        20071101    8,335         717          26          3,78         -9,58          693.288       168.805           32,19          -36,45
        20080101    8,785         756          39          5,40         -4,70          865.615       172.327           24,86          -20,66
        20080301    9,071         781          25          3,26         -1,59          193.206      -672.409          -77,68          -82,29
        20080501    9,073         781           0          0,02         -1,58          364.090       170.884           88,45          -66,63
        20080701    9,074         781           0          0,02         -1,56          150.694      -213.396          -58,61          -86,19
        20080802    9,074         781           0          0,00         -1,56          457.235       306.541          203,42          -58,09
        20080901    9,074         781           0          0,00         -1,56          565.322       108.087           23,64          -48,18
        20081001    9,076         781           0          0,02         -1,54          650.492        85.170           15,07          -40,38
        20091201   11,189         963         182         23,29         21,39        1.960.769     1.310.277          201,43           79,72
        ====================================================================================================================================

...
... intentionally shorten output ...
...

XX_COUNTER
         Period   DATABS %    SIZE MB     TREND MB      TREND %       CUMUL %          RECORDS      TREND REC        TREND %         CUMUL %
        --------  --------------------------------------------------------------    --------------------------------------------------------
        20070701    7,619          13          13          0,00          0,00            1.713         1.713            0,00            0,00
        20070901    7,821          13           0          2,65          2,65            3.703         1.990          116,17          116,17
        20071101    8,032          14           0          2,70          5,42            5.759         2.056           55,52          236,19
        20080101    8,358          14           1          4,06          9,70            8.253         2.494           43,31          381,79
        20080301    8,895          15           1          6,42         16,75            9.833         1.580           19,14          474,02
        20080501    9,010          15           0          1,29         18,25           12.024         2.191           22,28          601,93
        20080701    9,478          16           1          5,20         24,40           14.133         2.109           17,54          725,04
        20080802    9,561          16           0          0,87         25,48           15.088           955            6,76          780,79
        20080901    9,726          17           0          1,73         27,65           15.837           749            4,96          824,52
        20081001    9,900          17           0          1,79         29,94           16.922         1.085            6,85          887,86
        20091201   11,599          20           3         17,15         52,22           29.240        12.318           72,79        1.606,95
        ====================================================================================================================================

Here output was shorten because full output will have no sense.
With all other combination you can wide your analyze and the whole examples are in this txt file

"periods" procedure

To see the list of periods (list of taken snapshots) use periods procedure.

PROCEDURE periods;

SQL> exec db_size_pkg.periods;
Periods
--------
20070701
20070901
20071101
20080101
20080301
20080501
20080701
20080802
20080901
20081001
20091201
========

"get_last_period" function

Another procedure that deals with periods is get_last_period. This which returns last period in db_size table (last snapshot). If you use p_period parameter then function returns penultimate period.

FUNCTION get_last_period (p_period IN VARCHAR DEFAULT NULL
                         ) RETURN VARCHAR2;

Example:
  20091201          db_size_pkg.get_last_period;
  20080301          db_size_pkg.get_last_period('20080501');

null as parameter for period

In all procedures where period is parameter, if you leave it as null then last two periods are taken. That was very convenient to me because mostly queries was run against last two snapshots/periods.
Another limitation is that period1 must be greater then period2...but this is checked in each procedure internally and result with appropriate error message in any wrong usage.

New/dropped objects procedures

Monitoring of new and dropped objects are performed through two procedures with the similar names. Third one both_objects is union of first two. Showing it's output will explain the output result of all of them:

PROCEDURE new_objects (p_period1 IN VARCHAR2 DEFAULT null,
                       p_period2 IN VARCHAR2 DEFAULT null,
                       p_segment_type IN VARCHAR2 DEFAULT null) ;

PROCEDURE dropped_objects (p_period1 IN VARCHAR2 DEFAULT null,
                           p_period2 IN VARCHAR2 DEFAULT null,
                           p_segment_type IN VARCHAR2 DEFAULT null) ;

PROCEDURE both_objects (p_period1 IN VARCHAR2 DEFAULT null,
                        p_period2 IN VARCHAR2 DEFAULT null,
                        p_segment_type IN VARCHAR2 DEFAULT null) ;

Examples:
  exec db_size_pkg.both_objects;
  exec db_size_pkg.both_objects('20080501','20080301');
  exec db_size_pkg.both_objects(null,null,'TABLE PARTITON');
  exec db_size_pkg.both_objects('20091201','20070701','LOBSEGMENT');

SQL> exec db_size_pkg.both_objects('20080501','20080301');

List of dropped INDEX in period: 20080501-20080301
------------------------------------------------------------------------------
DAMIRV.AUDIT_DDL_LOG_PK(INDEX)
         Period     SIZE MB   TREND MB        TREND %      CUMUL %
        --------  --------------------------------------------------
        20080101        1,42        1,42          0,00          0,00
        20080301        0,10       -1,32        -92,96        -92,96
        ============================================================

XXHY.XXHY_DEBUG_LOG_N1(INDEX)
         Period     SIZE MB   TREND MB        TREND %      CUMUL %
        --------  --------------------------------------------------
        20070701    1.548,38    1.548,38          0,00          0,00
        20070901    1.985,38      437,00         28,22         28,22
        20071101      102,58   -1.882,80        -94,83        -93,38
        20080101    1.246,98    1.144,40      1.115,62        -19,47
        20080301    1.255,21        8,23          0,66        -18,93
        20091201    2.791,75    1.536,54        122,41         80,30
        ============================================================
------------------------------------------------------------------------------
        2 INDEX dropped                                          -125,531 MB
******************************************************************************

List of dropped TABLE in period: 20080501-20080301
------------------------------------------------------------------------------
DAMIRV.AUDIT_LOGON_LOG(TABLE)
         Period     SIZE MB   TREND MB        TREND %      CUMUL %          RECORDS      TREND REC        TREND %         CUMUL %
        --------  --------------------------------------------------    -----------------------------------------------------------
        20080101      100,04      100,04          0,00          0,00                2             2            0,00            0,00
        20080301      100,04        0,00          0,00          0,00               43            41        2.050,00        2.050,00
        ===========================================================================================================================

DAMIRV.AUDIT_LOGON_LOG_ARCH(TABLE)
         Period     SIZE MB   TREND MB        TREND %      CUMUL %          RECORDS      TREND REC        TREND %         CUMUL %
        --------  --------------------------------------------------    -----------------------------------------------------------
        20080101      100,04      100,04          0,00          0,00                0             0            0,00            0,00
        20080301      100,04        0,00          0,00          0,00                0             0            0,00            0,00
        ===========================================================================================================================
---------------------------------------------------------------------------------------------------
        2 TABLE dropped                                           -20,008 MB          -43 records
***************************************************************************************************

***************************************************************************************************
        4 TOTAL dropped                                          -145,539 MB          -43 records
***************************************************************************************************

List of new INDEX in period: 20080501-20080301
------------------------------------------------------------------------------
        APPS.XXHY_HLK_HANFA_CONTRACTS_I1                            0,188 MB
        APPS.XXHY_HLK_HANFA_OS_I1                                   0,038 MB
        APPS.XXHY_HLK_HANFA_RM_I1                                   0,075 MB
        APPS.XXHY_HLK_PARTNER_MAPPING_I1                            0,013 MB
        IGNITE_C.SYS_C00304984                                      0,010 MB
        XXHY.XXHY_AR_RECEIVABLE_APPL_U1                            36,088 MB
        XXHY.XXHY_CASH_RECEIPTS_N3                                  0,025 MB
        XXHY.XXHY_MTL_CATEGORIES_B_N2                               0,138 MB
        XXHY.XXHY_REC_UNPAID_INVOICE_N1                             8,625 MB
        XXHY.XXHY_REC_UNPAID_INVOICE_N2                             8,888 MB
        XXHY.XXHY_REC_UNPAID_INVOICE_N3                             6,788 MB
------------------------------------------------------------------------------
        11 INDEX new                                               60,873 MB
******************************************************************************

List of new LOBINDEX in period: 20080501-20080301
------------------------------------------------------------------------------
        APPS.SYS_IL0000815672C00036$$                               0,013 MB
------------------------------------------------------------------------------
        1 LOBINDEX new                                              0,013 MB
******************************************************************************

List of new LOBSEGMENT in period: 20080501-20080301
------------------------------------------------------------------------------
        APPS.SYS_LOB0000815672C00036$$                              0,013 MB
------------------------------------------------------------------------------
        1 LOBSEGMENT new                                            0,013 MB
******************************************************************************

List of new TABLE in period: 20080501-20080301
------------------------------------------------------------------------------
        APPS.RA_CUSTOMER_TRX_ALL_080304                             0,013 MB            2 records
        APPS.RA_CUSTOMER_TRX_ALL_080312                             0,013 MB            1 records
        APPS.RA_CUSTOMER_TRX_ALL_080402                             0,013 MB            1 records
        APPS.SR6696689994_040408                                    0,013 MB           78 records
        APPS.SR6696689994_160408                                    0,013 MB           66 records
        APPS.SR6835236_040422                                       0,013 MB           72 records
        APPS.XXHY_FX_CALCULATION_ALL210208                          0,013 MB            6 records
        APPS.XXHY_FX_CALCULATION_ALL_080304                         0,013 MB            4 records
        APPS.XXHY_FX_CALCULATION_ALL_080312                         0,013 MB            1 records
        APPS.XXHY_FX_CALCULATION_ALL_080402                         0,013 MB            3 records
        APPS.XXHY_FX_CALCULATION_ALL_170308                         0,013 MB            2 records
        APPS.XXHY_FX_CALCULATION_ALL_180208                         0,013 MB            2 records
        APPS.XXHY_HLK_HANFA_CONTRACTS                               0,938 MB       84.663 records
        APPS.XXHY_HLK_HANFA_RM                                      0,125 MB       27.234 records
        IGNITE_C.CONREPOS                                           0,010 MB            1 records
        XXHY.XXHY_HLK_PARTNER_MAPPING                               0,013 MB          245 records
        XXHY.XXHY_REC_UNPAID_INVOICE_ALL                           64,813 MB    2.707.789 records
---------------------------------------------------------------------------------------------------
        17 TABLE new                                               66,048 MB    2.820.170 records
***************************************************************************************************

***************************************************************************************************
        30 TOTAL new                                              126,945 MB    2.820.170 records
***************************************************************************************************

***************************************************************************************************
Total new/dropped in period 20080501-20080301                     -18,594 MB    2.820.127 records
***************************************************************************************************!

If you compare this output with grow size from get_db_size procedure's output, you’ll see that these numbers will allmost never be the same because many objects that were not dropped or are new, are growing or lessen, so this is where difference comes from. These procedures were used for quick analyze if some indexes are missing or some tables dropped.
All three wider examples can be downloaded from:

• new_objects example
• dropped_objects example
• both_objects example

"get_segment_type_size" procedure

What is get_db_size on database level, this procedure is for segments. When I say segments I mean on segment_type values from dba_segments view.

Procedure has only one parameter-segment_type. Here is the output for

   PROCEDURE get_segment_type_size (p_segment_type IN VARCHAR2);

SQL> exec db_size_pkg.get_segment_type_size('TABLE');

Segemnt type: TABLE

PERIOD     DB %      SIZE MB        TREND MB           RECORDS        TREND REC          REC/MB      TR REC/MB
==============================================================================================================
20070701   4,04      45.079,32           0,00       282.668.350               0        6.270,47           0,00
20070901   5,91      65.994,95      20.915,63       346.427.320      63.758.970        5.249,30       3.048,39
20071101   9,64     107.608,90      41.613,95       419.978.531      73.551.211        3.902,82       1.767,47
20080101   7,69      85.784,48     -21.824,42       437.905.606      17.927.075        5.104,72        -821,42
20080301   8,68      96.828,87      11.044,39       497.165.036      59.259.430        5.134,47       5.365,57
20080501   9,16     102.203,18       5.374,31       561.157.134      63.992.098        5.490,60      11.907,03
20080701   8,29      92.450,63      -9.752,56       548.065.355     -13.091.779        5.928,20       1.342,39
20080802   9,63     107.404,54      14.953,91       615.772.383      67.707.028        5.733,21       4.527,71
20080901   9,98     111.393,84       3.989,31       629.305.426      13.533.043        5.649,37       3.392,33
20081001  10,54     117.614,01       6.220,16       671.326.805      42.021.379        5.707,88       6.755,67
20091201  16,44     183.426,42      65.812,41       959.912.847     288.586.042        5.233,23       4.384,98
==============================================================================================================

What is also practical to this kind of analyze is that you can easy calculate database grow based on number of records of such an segment type (REC/MB and TR REC/MB columns).

List of possible segments can be easily retrieved as:

select distinct segment_type from db_size where period=&p_period

Example can be downloaded from txt file

The tablespace part

Three procedures deals with tablespace statistic. As mentioned before, global look is always the best point to start investigation. So after get_db_size this is second set of procedures that DBA should analyze in searching for problems.

"grow_hist_one_tblspc" procedure

Main procedure is grow_hist_one_tablespace which is the core procedure for other two.

PROCEDURE grow_hist_one_tblspc (p_tablespace_name IN VARCHAR2); 

Example:

SQL> exec db_size_pkg.grow_hist_one_tblspc('APPS_TS_INTERFACE');
APPS_TS_INTERFACE
         Period   DATABS %    SIZE MB     TREND MB      TREND %       CUMUL %          RECORDS      TREND REC        TREND %         CUMUL %
        --------  --------------------------------------------------------------    --------------------------------------------------------
        20070701    0,752         902         902          0,00          0,00          636.072       636.072            0,00            0,00
        20070901    8,904      10.674       9.773      1.084,05      1.084,05       17.023.234    16.387.162        2.576,31        2.576,31
        20071101   40,240      48.242      37.568        351,95      5.251,29       83.099.807    66.076.573          388,16       12.964,53
        20080101    1,275       1.528     -46.714        -96,83         69,52          839.979   -82.259.828          -98,99           32,06
        20080301    1,334       1.599          71          4,62         77,36          844.414         4.435            0,53           32,75
        20080501    1,654       1.983         384         23,99        119,91          926.861        82.447            9,76           45,72
        20080701    0,992       1.190        -793        -40,00         31,95          964.430        37.569            4,05           51,62
        20080802    3,963       4.751       3.562        299,43        427,04        5.898.281     4.933.851          511,58          827,30
        20080901    4,106       4.923         171          3,60        446,03        6.288.246       389.965            6,61          888,61
        20081001    4,326       5.186         263          5,35        475,24        6.298.268        10.022            0,16          890,18
        20091201   32,455      38.908      33.722        650,28      4.215,90       35.296.815    28.998.547          460,42        5.449,19
        ====================================================================================================================================

Idea of this output is to isolate output to one particular tablespace and see grow history.
Example can be downloaded from txt file

"n" procedures

For all produres that have "n" in its name, "n" represent number of output rows/elemnts to show. In all cases can be avoided and use default value for particular procedure. Only in these two examples n=100000 means "all tablesapces" to facilitate user command interface (this was convinient to me).

It is nice to see that column beside periods shows tablespace percentage in database size. TEMP and UNDO tablespaces are not excluded in DSS tool in any output..

"top_n_size_grow_hist_tblspc" procedure

This procedure shows tablespaces ordered by its grow (TREND MB colum).

PROCEDURE top_n_size_grow_hist_tblspc (p_period1 IN VARCHAR2 DEFAULT null, 
                                       p_period2 IN VARCHAR2 DEFAULT null, 
                                       n         IN pls_integer  DEFAULT 100000); 
Examples:
   exec db_size_pkg.top_n_size_grow_hist_tblspc ;
   exec db_size_pkg.top_n_size_grow_hist_tblspc ('20080501','20080301');
   exec db_size_pkg.top_n_size_grow_hist_tblspc ('20080501','20080301',5);

SQL> exec db_size_pkg.top_n_size_grow_hist_tblspc(null,null,3);
Tablespaces by SIZE grow. Monitored periods 20091201-20081001

APPS_TS_TX_DATA
         Period   DATABS %    SIZE MB     TREND MB      TREND %       CUMUL %          RECORDS      TREND REC        TREND %         CUMUL %
        --------  --------------------------------------------------------------    --------------------------------------------------------
        20070701    3,451      24.952      24.952          0,00             0      157.766.811   157.766.811            0,00            0,00
        20070901    5,167      37.357      12.404         49,71            50      215.247.823    57.481.012           36,43           36,43
        20071101    5,454      39.430       2.074          5,55            58      222.711.817     7.463.994            3,47           41,17
        20080101    6,814      49.264       9.834         24,94            97      288.586.226    65.874.409           29,58           82,92
        20080301    7,846      56.726       7.462         15,15           127      333.761.926    45.175.700           15,65          111,55
        20080501    8,997      65.052       8.326         14,68           161      382.452.012    48.690.086           14,59          142,42
        20080701    9,360      67.673       2.621          4,03           171      394.916.616    12.464.604            3,26          150,32
        20080802   10,696      77.335       9.662         14,28           210      443.468.997    48.552.381           12,29          181,09
        20080901   11,174      80.792       3.456          4,47           224      467.668.935    24.199.938            5,46          196,43
        20081001   11,978      86.601       5.809          7,19           247      497.345.922    29.676.987            6,35          215,24
        20091201   19,064     137.835      51.234         59,16           452      823.561.368   326.215.446           65,59          422,01
        ====================================================================================================================================

APPS_TS_INTERFACE
         Period   DATABS %    SIZE MB     TREND MB      TREND %       CUMUL %          RECORDS      TREND REC        TREND %         CUMUL %
        --------  --------------------------------------------------------------    --------------------------------------------------------
        20070701    0,752         902         902          0,00             0          636.072       636.072            0,00            0,00
        20070901    8,904      10.674       9.773      1.084,05         1.084       17.023.234    16.387.162        2.576,31        2.576,31
        20071101   40,240      48.242      37.568        351,95         5.251       83.099.807    66.076.573          388,16       12.964,53
        20080101    1,275       1.528     -46.714        -96,83            70          839.979   -82.259.828          -98,99           32,06
        20080301    1,334       1.599          71          4,62            77          844.414         4.435            0,53           32,75
        20080501    1,654       1.983         384         23,99           120          926.861        82.447            9,76           45,72
        20080701    0,992       1.190        -793        -40,00            32          964.430        37.569            4,05           51,62
        20080802    3,963       4.751       3.562        299,43           427        5.898.281     4.933.851          511,58          827,30
        20080901    4,106       4.923         171          3,60           446        6.288.246       389.965            6,61          888,61
        20081001    4,326       5.186         263          5,35           475        6.298.268        10.022            0,16          890,18
        20091201   32,455      38.908      33.722        650,28         4.216       35.296.815    28.998.547          460,42        5.449,19
        ====================================================================================================================================

APPS_TS_TX_IDX
         Period   DATABS %    SIZE MB     TREND MB      TREND %       CUMUL %          RECORDS      TREND REC        TREND %         CUMUL %
        --------  --------------------------------------------------------------    --------------------------------------------------------
        20070701    3,466      17.913      17.913          0,00             0            4.972         4.972            0,00            0,00
        20070901    4,686      24.219       6.306         35,20            35            4.972             0            0,00            0,00
        20071101    5,581      28.846       4.627         19,10            61            4.974             2            0,04            0,04
        20080101    7,531      38.927      10.082         34,95           117            4.974             0            0,00            0,04
        20080301    8,669      44.809       5.882         15,11           150            4.974             0            0,00            0,04
        20080501    9,890      51.118       6.309         14,08           185            4.974             0            0,00            0,04
        20080701    9,989      51.631         513          1,00           188            4.974             0            0,00            0,04
        20080802   11,187      57.823       6.192         11,99           223            4.974             0            0,00            0,04
        20080901   11,679      60.364       2.542          4,40           237            4.974             0            0,00            0,04
        20081001   12,535      64.791       4.426          7,33           262            4.974             0            0,00            0,04
        20091201   14,788      76.438      11.647         17,98           327            4.974             0            0,00            0,04
        ====================================================================================================================================

Example can be downloaded from txt file

"top_n_size_perc_hist_tblspc" procedure

This procedure shows tablespaces ordered by its percentage grow (TREND % colum). This one is interesting becase some small tablespaces may explode and you’ll never easy know that this happening without this kind of output. Percentage grow order may very differ from size grow order!

PROCEDURE top_n_size_perc_hist_tblspc (p_period1 IN VARCHAR2 DEFAULT null, 
                                       p_period2 IN VARCHAR2 DEFAULT null, 
                                       n IN pls_integer DEFAULT 100000); 

Examples:
  exec db_size_pkg.top_n_size_perc_hist_tblspc;
  exec db_size_pkg.top_n_size_perc_hist_tblspc('20080501','20080301');
  exec db_size_pkg.top_n_size_perc_hist_tblspc('20080501','20080301',5);


SQL> exec db_size_pkg.top_n_size_perc_hist_tblspc (null,null,3);
Tablespaces by PERCENTAGE grow. Monitored periods 20091201-20081001

DWH09
         Period   DATABS %    SIZE MB     TREND MB      TREND %       CUMUL %          RECORDS      TREND REC        TREND %         CUMUL %
        --------  --------------------------------------------------------------    --------------------------------------------------------
        20080701    1,942         200         200          0,00             0          266.512       266.512            0,00            0,00
        20080802    1,942         200           0          0,00             0       18.065.692    17.799.180        6.678,57        6.678,57
        20080901    1,942         200           0          0,00             0          313.393   -17.752.299          -98,27           17,59
        20081001    1,942         200           0          0,00             0          316.021         2.628            0,84           18,58
        20091201   92,233       9.500       9.300      4.650,00         4.650       24.787.383    24.471.362        7.743,59        9.200,66
        ====================================================================================================================================

CTXD
         Period   DATABS %    SIZE MB     TREND MB      TREND %       CUMUL %          RECORDS      TREND REC        TREND %         CUMUL %
        --------  --------------------------------------------------------------    --------------------------------------------------------
        20070701    1,694          16          16          0,00             0          114.176       114.176            0,00            0,00
        20070901    5,281          50          34        211,77           212        1.136.081     1.021.905          895,03          895,03
        20071101    7,248          69          19         37,25           328        1.922.159       786.078           69,19        1.583,51
        20080101   10,199          97          28         40,72           502        2.945.038     1.022.879           53,22        2.479,38
        20080301   14,625         139          42         43,40           763        3.990.317     1.045.279           35,49        3.394,88
        20080501   14,625         139           0          0,00           763          404.347    -3.585.970          -89,87          254,14
        20080701    1,468          14        -125        -89,96           -13           26.355      -377.992          -93,48          -76,92
        20080802    1,993          19           5         35,77            18          260.894       234.539          889,92          128,50
        20080901    3,044          29          10         52,70            80          624.025       363.131          139,19          446,55
        20081001    4,094          39          10         34,51           142          973.403       349.378           55,99          752,55
        20091201   35,730         340         301        772,73         2.010        5.386.868     4.413.465          453,41        4.618,04
        ====================================================================================================================================

Example can be downloaded from txt file

The "top_n" segment procedures

Four procedures of these type are third level of global scope when analyze database grow. They show trend in plain table output with minimal data. Idea is to clear up the main thing: grow in size, grow in percent, grow in records and records percentage grow on segment any level.

"top_n_size_grow" procedure

Order of segments are based on "grow [MB]" column.

PROCEDURE top_n_size_grow (p_period1 IN VARCHAR2 DEFAULT null, 
                           p_period2 IN VARCHAR2 DEFAULT null, 
                           n IN pls_integer DEFAULT 100); 
                              

Examples:
  exec db_size_pkg.top_n_size_grow;
  exec db_size_pkg.top_n_size_grow ('20080501','20080301');
  exec db_size_pkg.top_n_size_grow ('20080501','20080301',10);

SQL> exec db_size_pkg.top_n_size_grow ('20080501','20080301',10);
Top 10 objects by SIZE grow. Monitored periods 20080501-20080301

OWNER            SEGMENT_NAME                             SEGMENT_TYPE                  SIZE [MB]    grow [MB]          %
----------------------------------------------------------------------------------------------------------------------------------
GL               GL_JE_LINES                              TABLE                         10.009,75     1.331,50          15,34
XXHY             XXHY_GL_CC_DRILL_DOWN_V_TT               TABLE                         14.300,00     1.300,00          10,00
APPS             XXHY_CC_PAYMENT_PLAN_DISCO_MV            TABLE                         13.507,56     1.285,56          10,52
XXHY             XXHY_CC_PAY_PLAN_AMOUNTS_ALL             TABLE                          6.310,50       903,88          16,72
AR               RA_CUSTOMER_TRX_LINES_ALL                TABLE                          3.204,13       602,63          23,16
AR               RA_CUST_TRX_LINE_GL_DIST_ALL             TABLE                          2.854,25       599,63          26,60
GL               GL_IMPORT_REFERENCES                     TABLE                          4.206,38       595,25          16,48
XXHY             XXHY_CC_PAY_PLAN_AMOUNTS_U1              INDEX                          2.766,38       360,25          14,97
APPLSYS          DR$FND_LOBS_CTX$I                        TABLE                            473,25       323,50         216,03
AR               RA_CUSTOMER_TRX_ALL                      TABLE                          1.437,50       298,38          26,19

Example can be downloaded from txt file

"top_n_size_perc" procedure

Order of segments are based on "%" column. Important part is that only 10MB < segment sizes are involved.

Partition are placed in db_size as several records but in output all partition are treated as one segment!

PROCEDURE top_n_size_perc (p_period1 IN VARCHAR2 DEFAULT null,
                           p_period2 IN VARCHAR2 DEFAULT null,
                           n IN pls_integer DEFAULT 100);

Examples:
  exec db_size_pkg.top_n_size_perc;
  exec db_size_pkg.top_n_size_perc ('20080501','20080301');
  exec db_size_pkg.top_n_size_perc ('20080501','20080301',10);

SQL> exec db_size_pkg.top_n_size_perc ('20080501','20080301',10);
Top 10 objects by PERCENTAGE grow. Monitored periods 20080501-20080301
Only objects WITH size > 10MB are included!

OWNER            SEGMENT_NAME                             SEGMENT_TYPE                  SIZE [MB]    grow [MB]          %
----------------------------------------------------------------------------------------------------------------------------------
APPLSYS          DR$FND_LOBS_CTX$X                        INDEX                            175,38       131,88         303,16
APPLSYS          DR$FND_LOBS_CTX$I                        TABLE                            473,25       323,50         216,03
GL               GL_INTERFACE_N2                          INDEX                            122,75        75,13         157,74
GL               GL_INTERFACE_N1                          INDEX                             60,50        36,75         154,74
GL               GL_INTERFACE_N4                          INDEX                             64,75        37,13         134,39
GL               GL_INTERFACE                             TABLE                            395,63       224,00         130,52
JTF              SYS_LOB0000200028C00018$$                LOBSEGMENT                        22,63        10,63          88,54
APPLSYS          WF_NOTIFICATION_OUT_N1                   INDEX                             19,63         8,88          82,56
APPLSYS          WF_NOTIFICATION_OUT                      TABLE                            407,63       181,38          80,17
XXHY             XXHY_RM_CALCULATIONS_A_N3                INDEX                             56,13        24,50          77,47

It is really important to understand that some small segments (lower then 10 MB in EBS is peace of cake so they are not counted in output!) may explode very silently and this is really nice view to catch them! In this example it is obviously that grow of "DR$" indexes is probably connected with physical grow of parent table or in case when large records are dropped without proper rebuilding of indexes.
Example can be downloaded from txt file

"top_n_rec_grow" procedure

Order of segments are based on "REC_GROW" column. "REC_GROW" column tells how much records has been grown in monitored period.

PROCEDURE top_n_rec_grow (p_period1 IN VARCHAR2 DEFAULT null,
                          p_period2 IN VARCHAR2 DEFAULT null,
                          n IN pls_integer DEFAULT 100);

Examples:
  exec db_size_pkg.top_n_rec_grow;
  exec db_size_pkg.top_n_rec_grow ('20080501','20080301');
  exec db_size_pkg.top_n_rec_grow ('20080501','20080301',10);


SQL> exec db_size_pkg.top_n_rec_grow ('20080501','20080301',10);

Top 10 objects by NUMBER of records row. Monitored periods 20080501-20080301

OWNER            SEGMENT_NAME                             SEGMENT_TYPE                  NR_RECORDS   REC_GROW              %
-------------------------------------------------------------------------------------------------------------------------------------
XXHY             XXHY_CC_PAY_PLAN_AMOUNTS_ALL             TABLE                        78.422.940      10.990.267              16,30
APPS             XXHY_CC_PAYMENT_PLAN_DISCO_MV            TABLE                        66.472.066       6.750.522              11,30
XXHY             XXHY_GL_CC_DRILL_DOWN_V_TT               TABLE                        38.711.757       4.870.904              14,39
GL               GL_JE_LINES                              TABLE                        38.756.116       4.845.708              14,29
AR               RA_CUST_TRX_LINE_GL_DIST_ALL             TABLE                        19.620.633       4.184.139              27,11
GL               GL_IMPORT_REFERENCES                     TABLE                        26.560.744       3.775.827              16,57
SYS              WRI$_OPTSTAT_HISTHEAD_HISTORY            TABLE                         3.601.021       3.587.396          26.329,51
APPLSYS          DR$FND_LOBS_CTX$I                        TABLE                         3.819.293       2.758.699             260,11
AR               RA_CUSTOMER_TRX_LINES_ALL                TABLE                        10.691.695       2.131.972              24,91
AR               AR_DISTRIBUTIONS_ALL                     TABLE                        20.299.280       1.287.305               6,77

Example can be downloaded from txt file

"top_n_rec_perc" procedure

Order of segments are based on "%" column which represent percentage grow based on records in monitoring periods.
Only segments with nr_records > 10.000 records are included in this statistic!

PROCEDURE top_n_rec_perc (p_period1 IN VARCHAR2 DEFAULT null,
                          p_period2 IN VARCHAR2 DEFAULT null,
                          n IN pls_integer DEFAULT 100);

Examples:
  exec db_size_pkg.top_n_rec_perc;
  exec db_size_pkg.top_n_rec_perc ('20080501','20080301');
  exec db_size_pkg.top_n_rec_perc ('20080501','20080301',10);

SQL> exec db_size_pkg.top_n_rec_perc ('20080501','20080301',10);

Top 10 objects by PERCENTAGE records grow. Monitored periods 20080501-20080301
Only tables WITH nr_records > 10.000 records are included!

OWNER            SEGMENT_NAME                             SEGMENT_TYPE                  NR_RECORDS   REC_GROW              %
-------------------------------------------------------------------------------------------------------------------------------------
SYS              WRI$_OPTSTAT_HISTHEAD_HISTORY            TABLE                         3.601.021       3.587.396          26.329,51
SYS              WRI$_OPTSTAT_HISTGRM_HISTORY             TABLE                           893.265         849.158           1.925,22
SYS              WRH$_ACTIVE_SESSION_HISTORY              TABLE PARTITION                 235.092         212.023             919,08
SYS              WRH$_LATCH_MISSES_SUMMARY                TABLE PARTITION                  76.343          59.534             354,18
APPLSYS          DR$FND_LOBS_CTX$I                        TABLE                         3.819.293       2.758.699             260,11
APPLSYS          FND_STATS_HIST                           TABLE                           266.821         181.515             212,78
XXHY             XXHY_RP_UNPAID_INVOICE_LMT_ALL           TABLE                            68.333          45.169             195,00
SYS              SQLA$SQLTEXT                             TABLE                            58.105          35.498             157,02
CN               CN_SRP_PER_QUOTA_RC_ALL                  TABLE                            20.448           9.303              83,47
CN               CN_SRP_PERIOD_QUOTAS_EXT_ALL             TABLE                            27.328          12.428              83,41

Only segments with nr_records > 10.000 records are included in this statistic!
Example can be downloaded from txt file

The segment hist procedures

These are the lowest level of statistic with most detail of each segment-the whole history of it’s presence in database. They all are based on one core procedure (like in tblspc part)-grow_hist_one_segment.

"grow_hist_one_segment" procedure

Similar as in tablespaces, this is core procedure for all "hist" based procedures that deal with segments. It shows all needed data to monitor trend and cumulative numbers in total and percentage for size and records as well. Idea is to have most detailed viws on one segment level for all the periods.

PROCEDURE grow_hist_one_segment (p_owner IN VARCHAR2,
                                 p_segment_name IN VARCHAR2,
                                 p_segment_type IN VARCHAR2 DEFAULT 'TABLE');

Examples:
SQL> exec db_size_pkg.grow_hist_one_segment ('APPLSYS','WF_LOCAL_ROLES','TABLE PARTITION');

APPLSYS.WF_LOCAL_ROLES(TABLE PARTITION)
         Period     SIZE MB   TREND MB        TREND %      CUMUL %          RECORDS      TREND REC        TREND %         CUMUL %
        --------  --------------------------------------------------    -----------------------------------------------------------
        20070701       19,75       19,75          0,00          0,00        1.192.422     1.192.422            0,00            0,00
        20070901       20,63        0,88          4,46          4,46           90.451    -1.101.971          -92,41          -92,41
        20071101       21,63        1,00          4,85          9,52           94.483         4.032            4,46          -92,08
        20080101       22,50        0,87          4,02         13,92           98.711         4.228            4,47          -91,72
        20080301       23,38        0,88          3,91         18,38          102.880         4.169            4,22          -91,37
        20080501       24,38        1,00          4,28         23,44          107.357         4.477            4,35          -91,00
        20080701       24,50        0,12          0,49         24,05          111.376         4.019            3,74          -90,66
        20080802       25,75        1,25          5,10         30,38          113.368         1.992            1,79          -90,49
        20080901       26,00        0,25          0,97         31,65          114.858         1.490            1,31          -90,37
        20081001       25,75       -0,25         -0,96         30,38          116.496         1.638            1,43          -90,23
        20091201       28,75        3,00         11,65         45,57          127.384        10.888            9,35          -89,32
        ===========================================================================================================================

Example can be downloaded from txt file
Like in previous section there are four procedures that do the rest of jobs.

"top_n_size_grow_hist" procedure

Similar as top_n size_grow but here every segment is present instead of one row with full history.

PROCEDURE top_n_size_grow_hist (p_period1 IN VARCHAR2 DEFAULT null,
                                p_period2 IN VARCHAR2 DEFAULT null,
                                n IN pls_integer DEFAULT 10);

Examples:
  exec db_size_pkg.top_n_size_grow_hist;
  exec db_size_pkg.top_n_size_grow_hist ('20080501','20080301');
  exec db_size_pkg.top_n_size_grow_hist ('20080501','20080301',3);

SQL>  exec db_size_pkg.top_n_size_grow_hist ('20080501','20080301',3);

Top 3 objects by SIZE grow. Monitored periods 20080501-20080301

GL.GL_JE_LINES(TABLE)
         Period     SIZE MB   TREND MB        TREND %      CUMUL %          RECORDS      TREND REC        TREND %         CUMUL %
        --------  --------------------------------------------------    -----------------------------------------------------------
        20070701    1.180,63    1.180,63          0,00          0,00        6.763.180     6.763.180            0,00            0,00
        20070901    2.510,63    1.330,00        112,65        112,65       11.314.140     4.550.960           67,29           67,29
        20071101    5.174,38    2.663,75        106,10        338,27       21.853.222    10.539.082           93,15          223,12
        20080101    6.995,25    1.820,87         35,19        492,50       28.111.423     6.258.201           28,64          315,65
        20080301    8.678,25    1.683,00         24,06        635,05       33.910.408     5.798.985           20,63          401,40
        20080501   10.009,75    1.331,50         15,34        747,83       38.756.116     4.845.708           14,29          473,05
        20080701   10.841,00      831,25          8,30        818,24       41.629.108     2.872.992            7,41          515,53
        20080802   13.160,13    2.319,13         21,39      1.014,67       49.606.017     7.976.909           19,16          633,47
        20080901   13.677,50      517,37          3,93      1.058,49       51.380.285     1.774.268            3,58          659,71
        20081001   14.812,63    1.135,13          8,30      1.154,64       55.277.203     3.896.918            7,58          717,33
        20091201   24.228,25    9.415,62         63,56      1.952,15       88.233.655    32.956.452           59,62        1.204,62
        ===========================================================================================================================

XXHY.XXHY_GL_CC_DRILL_DOWN_V_TT(TABLE)
         Period     SIZE MB   TREND MB        TREND %      CUMUL %          RECORDS      TREND REC        TREND %         CUMUL %
        --------  --------------------------------------------------    -----------------------------------------------------------
        20071101    8.200,00    8.200,00          0,00          0,00                0             0            0,00            0,00
        20080101   11.300,00    3.100,00         37,80         37,80       28.096.387    28.096.387            0,00            0,00
        20080301   13.000,00    1.700,00         15,04         58,54       33.840.853     5.744.466           20,45           20,45
        20080501   14.300,00    1.300,00         10,00         74,39       38.711.757     4.870.904           14,39           37,78
        ===========================================================================================================================

APPS.XXHY_CC_PAYMENT_PLAN_DISCO_MV(TABLE)
         Period     SIZE MB   TREND MB        TREND %      CUMUL %          RECORDS      TREND REC        TREND %         CUMUL %
        --------  --------------------------------------------------    -----------------------------------------------------------
        20070701    7.110,00    7.110,00          0,00          0,00       33.395.612    33.395.612            0,00            0,00
        20070901    8.326,00    1.216,00         17,10         17,10       39.684.371     6.288.759           18,83           18,83
        20071101    9.542,00    1.216,00         14,60         34,21       45.989.776     6.305.405           15,89           37,71
        20080101   11.014,00    1.472,00         15,43         54,91       53.388.260     7.398.484           16,09           59,87
        20080301   12.222,00    1.208,00         10,97         71,90       59.721.544     6.333.284           11,86           78,83
        20080501   13.507,56    1.285,56         10,52         89,98       66.472.066     6.750.522           11,30           99,04
        20080701   14.740,00    1.232,44          9,12        107,31       72.912.475     6.440.409            9,69          118,33
        20080802   15.316,00      576,00          3,91        115,41       76.097.530     3.185.055            4,37          127,87
        20080901   15.700,00      384,00          2,51        120,82       78.322.225     2.224.695            2,92          134,53
        20081001   16.212,00      512,00          3,26        128,02       80.885.613     2.563.388            3,27          142,20
        20091201        0,06  -16.211,94       -100,00       -100,00                0   -80.885.613         -100,00            0,00
        ===========================================================================================================================

Here is important to notice that numbers that counts are in monitored periods (colored in red) so order of segments is based on their values (regardless they have now or before)!

All others numbers shows only it’s history in database until recent snapshot. All mentioned here will be share the same logic in other three "hist" procedures.
Example can be downloaded from txt file

"top_n_size_perc_hist" procedure

Similar as top_n size_perc but every segment is present instead of one row with full table history.

PROCEDURE top_n_size_perc_hist (p_period1 IN VARCHAR2 DEFAULT null,
                                p_period2 IN VARCHAR2 DEFAULT null,
                                n IN pls_integer DEFAULT 10);
                                                                   

Examples:
  exec db_size_pkg.top_n_size_perc_hist;
  exec db_size_pkg.top_n_size_perc_hist ('20080501','20080301');
  exec db_size_pkg.top_n_size_perc_hist ('20080501','20080301',3);

Because showing real example will take to much space, full example can be downloaded from txt file

"top_n_rec_grow_hist" procedure

Similar as top_n_rec_grow but every segment is present instead of one row with full table history.

PROCEDURE top_n_rec_grow_hist (p_period1 IN VARCHAR2 DEFAULT null,
                               p_period2 IN VARCHAR2 DEFAULT null,
                               n IN pls_integer DEFAULT 10);

Examples:
  exec db_size_pkg.top_n_rec_grow_hist;
  exec db_size_pkg.top_n_rec_grow_hist ('20080501','20080301');
  exec db_size_pkg.top_n_rec_grow_hist ('20080501','20080301',3);

Because showing real example will take to much space full example can be downloaded from txt file

"top_n_rec_perc_hist" procedure

Similar as top_n_rec_perc but every segment is present instead of one row with full table history.

PROCEDURE top_n_rec_perc_hist (p_period1 IN VARCHAR2 DEFAULT null,
                               p_period2 IN VARCHAR2 DEFAULT null,
                               n IN pls_integer DEFAULT 10);

Examples:
  exec db_size_pkg.top_n_rec_perc_hist;
  exec db_size_pkg.top_n_rec_perc_hist ('20080501','20080301');
  exec db_size_pkg.top_n_rec_perc_hist ('20080501','20080301',3);

Again, because showing real example will also take to much space, full example can be downloaded from txt file

The "top_n_tables" ever/now procedures

Last four procedures that deals with size or number of records are based on "biggest ever" or "biggest now". "Biggest ever" means biggest in all periods and "biggest now" mean biggest in last taken snapshot. All is done for size and records with history type of output.

All these procedures deal only with table segments because (beside partitions which had by me less important meanings) these segments would really be most interesting to keep highly under control.
Idea is to monitor kings and aces in database …

"top_n_tables_by_size_ever" procedure

PROCEDURE top_n_tables_by_size_ever (n in pls_integer default 10);

Because showing real example can be downloaded from txt file

"top_n_tables_by_records_ever" procedure

PROCEDURE top_n_tables_by_rec_ever (n in pls_integer default 10);

Example will take to much space example can be downloaded from txt file

"top_n_tables_by_size_now" procedure

PROCEDURE top_n_tables_by_size_now (n in pls_integer default 10);

Example will also take to much space so full example can be downloaded from txt file

"top_n_tables_by_records_now" procedure

PROCEDURE top_n_tables_by_rec_now (n in pls_integer default 10);

Example will take to much space example can be downloaded from txt file

The end

I hope that you had enough patience to come to this point and see all benefits of DSS tool. Even thought this tool is free for use, if you find it interesting please let me know…especially if you have made some improvements or found some bugs.

All implementation files (mentioned before) are packed together in one RAR file

If you want to play with procedures and have no data in db_size table, in rar file are the same data as in all examples, exported with Oracle expdp utility. Used PARFILE exp_db_size.dat is also in rar, so you can very easy import data in any schema you like.

If you want to analyze all examples locally (or compare with your EBS instance!) all data are also packed in one RAR file

Cheers!