Sunday, April 24, 2022

Estimating Size of Oracle Database Objects --seeing history of object/table size


Resource and performance capacity of the servers is one side of the puzzle. Equally important is to size/estimate the database for storage and the data growth. This would mean the database, the database objects, and the underlying storage subsystem would also have to be sized for today and tomorrow.

Oracle provides few packages and procedures that help determine the size of objects and indexes based on the   estimated growth size. Even further, using the DBMS_SPACE.OBJECT_GROWTH_TREND function, a growth pattern for existing tables can be obtained.

The following query will list the object growth trend for an object; the data for the trend listed is gathered from Automatic Workload Repository (AWR). The growth trends for two of the tables are listed following.

The OBJECT_GROWTH_TREND function returns four values:
TIMEPOINT—Is a time stamp value indicating the time of the recording/reporting.
SPACE_USAGE—Lists the amount of space used by the object at the given point in time.
SPACE_ALLOCATED—Lists the amount of space allocated to the object in the table space at the
given point in time.
QUALITY—Indicates the quality of data reported; there are three possible values:
INTERPOLATED—The value did not meet the criteria of GOOD. As noted in the outputs
following, the used and allocated are same. Basically, the values do not reflect any usage.
GOOD—The value whenever the value of TIME is based on recorded statistics. Value is
marked good if at least 80% of the value is derived from GOOD instance values.
PROJECTED—The value of time is in the future as of the time the table was produced.


In a RAC environment, the output reflects the aggregation of values recorded across all instances in the cluster.

SELECT *
FROM TABLE(dbms_space.object_growth_trend(object_owner => 'ABDUL',
 object_name => 'HISTORY', object_type => 'TABLE'));


TIMEPOINT SPACE_USAGE SPACE_ALLOC QUALITY
------------------------------- ----------- ----------- --------------------
28-MAY-14 11.12.43.052162 AM 111082969 111082969 INTERPOLATED
29-MAY-14 11.12.43.052162 AM 111082969 111082969 INTERPOLATED
30-MAY-14 11.12.43.052162 AM 111082969 111082969 INTERPOLATED
31-MAY-14 11.12.43.052162 AM 111082969 111082969 INTERPOLATED
01-JUN-14 11.12.43.052162 AM 111082969 111082969 INTERPOLATED
02-JUN-14 11.12.43.052162 AM 111082969 111082969 INTERPOLATED
03-JUN-14 11.12.43.052162 AM 111082969 111082969 INTERPOLATED
04-JUN-14 11.12.43.052162 AM 131877793 134217728 GOOD
05-JUN-14 11.12.43.052162 AM 132003569 134369941 PROJECTED
06-JUN-14 11.12.43.052162 AM 132129344 134522153 PROJECTED
07-JUN-14 11.12.43.052162 AM 132255119 134674366 PROJECTED
08-JUN-14 11.12.43.052162 AM 132380894 134826579 PROJECTED
09-JUN-14 11.12.43.052162 AM 132506669 134978791 PROJECTED

No comments:

Post a Comment