Capacity Planning for Database Objects #capacity-planner


Capacity Planning for Database Objects

Oracle Database provides two ways to plan capacity for database objects:

With Enterprise Manager

With the DBMS_SPACE PL/SQL package

This section discusses the PL/SQL method. Refer to Enterprise Manager online help and Oracle Database 2 Day DBA for details on capacity planning with Enterprise Manager.

Three procedures in the DBMS_SPACE package enable you to predict the size of new objects and monitor the size of existing database objects. This section discusses those procedures and contains the following sections:

Estimating the Space Use of a Table

The size of a database table can vary greatly depending on tablespace storage attributes, tablespace block size, and many other factors. The CREATE_TABLE_COST procedure of the DBMS_SPACE package lets you estimate the space use cost of creating a table. Please refer to Oracle Database PL/SQL Packages and Types Reference for details on the parameters of this procedure.

The procedure has two variants. The first variant uses average row size to estimate size. The second variant uses column information to estimate table size. Both variants require as input the following values:

TABLESPACE_NAME. The tablespace in which the object will be created. The default is the SYSTEM tablespace.

ROW_COUNT. The anticipated number of rows in the table.

PCT_FREE. The percentage of free space you want to reserve in each block for future expansion of existing rows due to updates.

In addition, the first variant also requires as input a value for AVG_ROW_SIZE. which is the anticipated average row size in bytes.

The second variant also requires for each anticipated column values for COLINFOS. which is an object type comprising the attributes COL_TYPE (the datatype of the column) and COL_SIZE (the number of characters or bytes in the column).

The procedure returns two values:

USED_BYTES. The actual bytes used by the data, including overhead for block metadata, PCT_FREE space, and so forth.

ALLOC_BYTES. The amount of space anticipated to be allocated for the object taking into account the tablespace extent characteristics.

Beginning with release of Oracle Database, the default size of the first extent of any new segment for a partitioned table is 8 MB instead of 64 KB. This helps improve performance of inserts and queries on partitioned tables. Although partitioned tables will start with a larger initial size, once sufficient data is inserted, the space consumption will be the same as in previous releases. You can override this default by setting the INITIAL size in the storage clause for the table. This new default only applies to table partitions and LOB partitions.

Estimating the Space Use of an Index

The CREATE_INDEX_COST procedure of the DBMS_SPACE package lets you estimate the space use cost of creating an index on an existing table.

The procedure requires as input the following values:

DDL. The CREATE INDEX statement that would create the index. The table specified in this DDL statement must be an existing table.

[Optional] PLAN_TABLE. The name of the plan table to use. The default is NULL .

The results returned by this procedure depend on statistics gathered on the segment. Therefore, be sure to obtain statistics shortly before executing this procedure. In the absence of recent statistics, the procedure does not issue an error, but it may return inappropriate results. The procedure returns the following values:

USED_BYTES. The number of bytes representing the actual index data.

ALLOC_BYTES. The amount of space allocated for the index in the tablespace.

Obtaining Object Growth Trends

The OBJECT_GROWTH_TREND function of the DBMS_SPACE package produces a table of one or more rows, where each row describes the space use of the object at a specific time. The function retrieves the space use totals from the Automatic Workload Repository or computes current space use and combines it with historic space use changes retrieved from Automatic Workload Repository. Please refer to Oracle Database PL/SQL Packages and Types Reference for detailed information on the parameters of this function.

The function requires as input the following values:

OBJECT_OWNER. The owner of the object.

OBJECT_NAME. The name of the object.

PARTITION_NAME. The name of the table or index partition, is relevant. Specify NULL otherwise.

OBJECT_TYPE. The type of the object.

START_TIME. A TIMESTAMP value indicating the beginning of the growth trend analysis.

END_TIME. A TIMESTAMP value indicating the end of the growth trend analysis. The default is ” NOW “.

INTERVAL. The length in minutes of the reporting interval during which the function should retrieve space use information.

SKIP_INTERPOLATED. Determines whether the function should omit values based on recorded statistics before and after the INTERVAL (‘ YES ‘) or not (‘ NO ‘). This setting is useful when the result table will be displayed as a table rather than a chart, because you can see more clearly how the actual recording interval relates to the requested reporting interval.

The function returns a table, each of row of which provides space use information on the object for one interval. If the return table is very large, the results are pipelined so that another application can consume the information as it is being produced. The output table has the following columns:

TIMEPOINT. A TIMESTAMP value indicating the time of the reporting interval.

Records are not produced for values of TIME that precede the oldest recorded statistics for the object.

SPACE_USAGE. The number of bytes actually being used by the object data.

SPACE_ALLOC. The number of bytes allocated to the object in the tablespace at that time.

QUALITY. A value indicating how well the requested reporting interval matches the actual recording of statistics. This information is useful because there is no guaranteed reporting interval for object size use statistics, and the actual reporting interval varies over time and from object to object.

The values of the QUALITY column are:

GOOD. The value whenever the value of TIME is based on recorded statistics with a recorded timestamp within 10% of the INTERVAL specified in the input parameters.

INTERPOLATED. The value did not meet the criteria for GOOD. but was based on recorded statistics before and after the value of TIME. Current in-memory statistics can be collected across all instances in a cluster and treated as the “recorded” value for the present time.

PROJECTION. The value of TIME is in the future as of the time the table was produced. In an Oracle Real Application Clusters environment, the rules for recording statistics allow each instance to choose independently which objects will be selected.

The output returned by this function is an aggregation of values recorded across all instances in an Oracle RAC environment. Each value can be computed from a combination of GOOD and INTERPOLATED values. The aggregate value returned is marked GOOD if at least 80% of that value was derived from GOOD instance values.

Capacity Planner #capacity-planner


Date posted 23 June 2017

A leading food manufacturing organisation based in the Merseyside area are looking for a Capacity Planner to join their team for an excellent opportunity. As Capacity Planner you will be responsible for the medium and long term planning processes, providing visibility on risks and opportunities to the factory and Central Business Units and supporting the Integrated Business Process Manager with the 5+ year capacity investment planning with the Supply Points.

  • Ensure that all functions contribute and own their part of the process (shift levels and overtime requirements for each production area; planning parameters for medium term planning and scheduling; new product launches and changes in min/ max settings)
  • Generate a compelling risks opportunities outlook to help drive decisions and to monitor the evolution.
  • Lead the min / max inventory calculation, including alignment with CBUs and MDC
  • Lead Monthly Supply Point Capacity Review and provide data for WWBU Supply Review
  • Track and highlight performance of latest capacity plan as compared to last RF, Strat Plan, SCP
  • Supporting sourcing decisions in the long-term to solve capacity constraints and exploit capacity opportunities
  • Ensure the consistency and development in capacity planning methodology and tools.
  • Chair Supply Capability Review meeting with manufacturing, testing and logistics for identifying risks and lead mitigation plans
  • Propose and coordinate the OU calculation needed to meet the capacity needs

About the person:

  • Significant experience in a Capacity Planning role
  • SAP APO Excel/Access skills
  • Excellent stakeholder management skills to bring together inputs from multiple operational departments
  • Ability to influence others, and potential to lead a small team in the future
  • Ability to crystallize complex planning situations into clear messages
  • Ability to present data clearly and to generate actions and decisions from data-driven discussions

Get in touch