14 Developing a Database Growth Management Strategy

This chapter describes how to develop a database growth management strategy, including determining the optimal profile or size for the database; monitoring disk space usage, hardware resources, and database performance; understanding growth management challenges and testing strategies; and understanding space management.

This chapter includes the following sections:

For information about managing database growth with the purge scripts and component table partitioning, see Managing Database Growth.

For information about troubleshooting, see Parallel Purging and Table Partitioning Issues.

Note:

This chapter is intended for database administrators.

Introduction to Planning for Database Growth

An Oracle SOA Suite 12c installation presents several challenges for database administrators, including managing the growth of the Oracle SOA Suite database. Underestimating the importance of managing the database can lead to issues when the database is moved to a production environment. This chapter helps you determine an appropriate strategy and highlights the need for capacity planning, testing, and monitoring.

The recommended strategies are based on the profile or the predicted size of the Oracle SOA Suite installation. The tools and techniques that implement the strategies are straight forward, which helps to simplify the recommendations. However, this does restrict administrators to a set of tools that must be implemented as designed.

Testing and monitoring are required to determine the effectiveness and resource requirements of a growth management strategy. Thorough testing ensures that the tools, the database, and hardware resources all come together to meet the needs of current and future database growth estimates.

Identifying the Profile or Size of the Database

This section describes how to identify the profile or size of the Oracle SOA Suite database to determine an optimal growth management strategy. For more information, see Understanding Growth Management Challenges and Testing Strategies.

The calculations detailed in this section approximate disk space usage and are not a replacement for conducting a thorough space capacity plan. However, the estimates are sufficient to draw conclusions on the appropriate growth management strategy, while highlighting the need for disk space planning.

Note:

This section provides frequent references to Monitoring Space Usage_ Hardware Resources_ and Database Performance for details about space calculation instructions.

Table 14-1 provides a profile of small, medium, and large installations based on the composite space persisted daily and the minimum retention space. These two metrics are related in an and/or condition because the retention policy may not retain more than several days of data, but instance inflow may be high.

Table 14-1 Oracle SOA Suite Database Profiles

Database Profile Composite Space Persisted Daily Minimum Retention of Space

Small

< 10 GB

< 100 GB

Medium

10-30 GB

100-300 GB

Large

> 30 GB

> 300 GB

Identifying the Inflow of Data

The rate of composite inflow and space usage is best understood as an average derived after many days of load testing. This allows for allocated space (segment extents) to be better utilized. To understand inflow, the following data points are investigated:

Identifying the Number of Instances Produced Daily

Use the following formula to identify the number of instances produced daily:

Daily-inflow-composite = (Instance Total / Period)

For example, calculate the average number of instances produced daily based on 5 days of load testing with a total composite count of 100,000:

(20,000 = (100,000 / 5))

For more information, see Determining the Growth Trend of Components.

Identifying the Disk Space Used by Each Instance

Use the following formula to identify the disk space used by each instance:

Inflow-space-per-instance = (SOA Schema Size /Instance Total)

For example, calculate the average disk space used by each instance with a total of 100,000 composites and an Oracle SOA Suite schema size of around 200 GB:

(2MB = (200 GB / 100,000))

For more information, see Determining the Growth Trend of the Schema.

Identifying the Composite Space Persisted Daily

Use the following formula to identify the composite space persisted daily:

Daily-inflow-composite-space = (Daily-inflow-composite * Inflow-space-per-composite)

For example, calculate the average disk space used by composites daily with 20,000 composites daily of 2 MB each:

(40 GB = (20,000 * 2 MB))

Alternatively:

Daily-inflow-composite-space = (SOA Schema Size / Period)
Analyzing Space Distribution of Oracle SOA Suite Segments

Analyze the shape of the Oracle SOA Suite schema so that the distribution of segment space (tables, indexes, and large objects (LOBs)) is understood and segments that may be problematic are identified:

For more information, see Determining the Growth Trend of Components.

Developing a Retention Policy

You must consider how long composites are retained in the database. This affects the size of the Oracle SOA Suite schema and the performance of the purge scripts. The following factors drive the retention policy:

  • Legal requirements

  • Line of business requirements

  • Overall company policy on retention of data

The longer the retention policy, the greater the volume of data that must be stored and, correspondingly, the higher the disk capacity requirements.

Determining the Minimum Retained Disk Space

Use the following formula to identify the minimum retained disk space. For information about identifying Daily-inflow-composite-space, see Identifying the Composite Space Persisted Daily.

Min-space-retain = Daily-inflow-composite-space * Retention Period in days

For example, calculate the minimum retained disk space with 40 GB of composite space persisted daily and a retention period of 10 days:

(400 GB = (40 GB * 10))
Determining the Minimum Number of Retained Composites

Use the following formula to identify the minimum number of retained composites. For information about identifying Daily-inflow-composite, see Identifying the Number of Instances Produced Daily.

Min-composites-retain = Daily-inflow-composite * Retention Period in days

For example, calculate the minimum number of retained composites with 20,000 composites created daily and a retention period of 10 days:

(200,000 = (20,000 * 10))

From this, it can be stated that 200,000 composites retain on average 400 GB of data based on a 10 day retention policy.

Note:

The above calculation defines an approximation on the minimum disk space usage. The actual minimum disk space usage is more accurately determined through quality assurance testing. For more information, see Quality Assurance Testing.

Identifying the Outflow of Data

The outflow of composites is a measurement of the number of instances that are deleted. It is not a factor when determining the database profile, but is an important metric when evaluating the effectiveness of the growth management strategy.

The goal of measuring outflow is to ensure the following:

  • That the inflow can be deleted.

  • A steady state for space usage, hopefully just above the minimum retained disk space usage.

For more information, see Determining the Minimum Retained Disk Space.

There are two cycles that must first be defined:

  • The purge cycle: Refers to a period that may involve multiple executions of the purge scripts.

  • The maintenance cycle: Refers to the number of days required to perform all space management operations. For more information, see Introduction to the Components of a Data File.

The appropriate growth management strategy may include both purging and partitioning. Therefore, outflow should not be measured until eligible partitions have been dropped. The dropping of partitions is a maintenance operation. Therefore, it makes little sense to measure outflow metrics until the maintenance cycle has finished.

The following metrics can be captured before and after the completion of the maintenance cycle to understand the effectiveness of the strategy and to re-evaluate space distribution:

Note:

If a steady state has been reached, then the difference between the before and after metrics for components and schema growth should be near zero or negative. Otherwise, the purge strategy may not be coping or the inflow of data may have increased.

Identifying Long Running Composites and Table Partitioning

Long running composites are composites that remain open beyond the retention period. These composites do not have significant implications for the purge scripts, but their impact is felt when table partitioning is implemented. (Understanding Growth Management Challenges and Testing Strategies recommends that table partitioning be considered for medium and large installations.)

The partitioned tables are (should be) excluded from the purge scripts because their space is reclaimed through the database ALTER TABLE ... DROP PARTITION command. Long running composites remain open beyond the desired retention period, preventing the partition from being dropped and the space from being reclaimed. Therefore, when estimating the size of the partitioned tables, the retention period that is used should equal the longest running composite:

Retention period = Longest Running Composite

The space implications of long running transactions and the size of the partitioned tables are best understood through quality assurance testing, as described in Understanding Growth Management Challenges and Testing Strategies. However, use the calculations in the following sections to estimate the space used by each table that is partitioned.

Recommendations for Each Table to Partition

It is best to load the tables over a period of several days to produce reasonable averages. Table 14-2 provides details.

Table 14-2 Table Partitioning Recommendations

Action Command

Determine the average number of rows generated daily for each table.

(Total rows / period)

Estimate the number of rows based on the longest running composite.

((Total rows / period) * Longest running composite in
 days)

Estimate space usage for the table and index based on row calculations.

See Estimating Table Size and Estimating Index Size.

Note:

In Oracle SOA Suite Release 11g R1 (11.1.1.6), a set of scripts called row migration were provided. These scripts move long running composites from one partition to another, thereby facilitating the removal of the partition. However, it is a best practice to plan for the space requirements of the partition tables and not rely on the row migration scripts.

Monitoring Space Usage, Hardware Resources, and Database Performance

The following sections describe how to monitor space usage:

The following sections describe how to monitor hardware resources and database performance:

Monitoring Space Usage

Monitoring space is an essential task of database and system administration to avoid unplanned outages. This section provides advice to help with capacity planning and determine the effectiveness of the growth management strategy. It is not meant to be a comprehensive guide on all aspects of monitoring and capacity planning.

Determining the Growth Trend of Components

The component master tables record the creation date of each row used to determine growth trends over a given period:

  • SCA_FLOW_INSTANCE: CREATED_TIME

  • CUBE_INSTANCE: CREATION_DATE

However, querying the component master tables is limited as purging constantly removes data, making trend predictions difficult. To determine a trend over a given period requires data to be regularly collected with an associated time stamp. This can be achieved with a simple component history table that is populated each day. This table should be sufficient to determine the effectiveness of the growth management strategy and growth trends.

  • For performance reasons, it may be best to create a history table per component, as shown in the following example:

    SQL> CREATE TABLE SOA_FABRIC_HIST (SFH_DATE TIMESTAMP, SFH_COUNT NUMBER);
    
    SQL> INSERT INTO SOA_FABRIC_HIST SELECT SYSTIMESTAMP, COUNT(*) FROM 
    SCA_FLOW_INSTANCE GROUP BY SYSTIMESTAMP;
    
    SQL> CREATE TABLE SOA_BPEL_HIST (SBH_DATE TIMESTAMP, SBH_COUNT NUMBER);
    
    SQL> INSERT INTO SOA_BPEL_HIST SELECT SYSTIMESTAMP, COUNT(*) FROM CUBE_INSTANCE
     GROUP BY SYSTIMESTAMP;
    

    and so on.

  • An alternate to counting the rows in each component table is to query the NUM_ROWS column from USER_TABLES as long as the tables are analyzed.

Determining the Growth Trend of the Oracle SOA Suite Schema

The objects (table, index, and LOBs) created for the Oracle SOA Suite schema may be spread over many tablespaces, but all have the same owner (*soainfra) to group space usage. When trying to determine the space requirements of each composite, measuring the whole schema allows for better space metrics because indexes and LOB segments are taken into account.

To monitor the growth of the Oracle SOA Suite schema, a simple history table can be populated daily. This table should be sufficient to determine the effectiveness of space management. For more information, see Understanding Space Management.

  • Create a schema growth table based on object type, as shown in the following example:

    SQL> CREATE TABLE SOA_SIZE_SCHEMA_HIST (SSCH_DATE TIMESTAMP, SSCH_SUM_MB
     NUMBER);
    
    SQL> INSERT INTO SOA_SIZE_SCHEMA_HIST SELECT SYSTIMESTAMP, SUM(BYTES)/1024/1024
     FROM DBA_SEGMENTS WHERE OWNER='<SOA_OWNER>' GROUP BY SYSTIMESTAMP;
    
  • Optionally, a history table by object type (for example, table, index, and LOB) may be helpful when coupled with the largest segment to verify which tables may need partitioning or lack space management. The following example creates a history table:

    SQL> CREATE TABLE SOA_SIZE_TYPE_HIST (SSCH_DATE TIMESTAMP, SSCH_TYPE
     VARCHAR2(18), SSCH_SUM_MB NUMBER);
    
    SQL> INSERT INTO SOA_SIZE_TYPE_HIST SELECT SYSTIMESTAMP, SEGMENT_TYPE,
     SUM(BYTES)/1024/1024 FROM DBA_SEGMENTS WHERE OWNER='SOA_OWNER' GROUP BY
     SYSTIMESTAMP, SEGMENT_TYPE;
Determining the Largest Segments

Understanding which segments are the largest in the schema helps to determine the tables that may be better managed as range partitioned tables. In addition, monitoring the largest segments may uncover missing space management operations or inappropriate audit settings.

  • Identify the largest 20 segments for the Oracle SOA Suite schema, as shown in the following example:

    SQL> SELECT SEGMENT_NAME, SEGMENT_TYPE, (SUM(BYTES)/1024/1024) MB_SIZE FROM
     DBA_SEGMENTS WHERE OWNER='SOA_OWNER' AND ROWNUM < 20 GROUP BY SEGMENT_NAME,
     SEGMENT_TYPE ORDER BY 3 DESC;
    
  • The largest segments may be LOB segments, as shown in the following example:

    SQL> SELECT L.TABLE_NAME, S.SEGMENT_NAME, (SUM(BYTES)/1024/1024) MB_SIZE FROM
     DBA_LOBS L, DBA_SEGMENTS S WHERE S.OWNER='<SOA_OWNER>' AND S.SEGMENT_TYPE =
     'LOBSEGMENT' AND S.SEGMENT_NAME = L.SEGMENT_NAME AND S.OWNER='SOA_OWNER''
     GROUP BY L.TABLE_NAME, S.SEGMENT_NAME;
    

    or

    SQL> SELECT L.TABLE_NAME FROM DBA_LOBS L WHERE L.OWNER ='<SOA_OWNER>' AND
     L.SEGMENT_NAME = ‘SEGMENT_NAME';
    
  • To determine the size of an individual segment:

    SQL> SELECT SEGMENT_NAME, (SUM(BYTES)/1024/1024) MB_SIZE FROM DBA_SEGMENTS
    WHERE OWNER='SOA_OWNER' AND SEGMENT_NAME = ‘TABLE_NAME' GROUP BY SEGMENT_
    NAME;
Determining the Growth Trend of Tables and Indexes

Use the OBJECT_GROWTH_TREND function of the DBMS_SPACE package to show the trend in space growth for a table. For more information, see Oracle Database Administrator’s Guide.

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 totals from the AWR or computes current space and combines it with historic space changes retrieved from AWR.

The following example displays the growth in used and allocated space over time for the SCA_FLOW_INSTANCE table.

SQL> SELECT TIMEPOINT, SPACE_USAGE, SPACE_ALLOC, QUALITY FROM TABLE (DBMS_
SPACE.OBJECT_GROWTH_TREND ('DEV_SOAINFRA','SCA_FLOW_INSTANCE','TABLE'));

The following example shows the complete syntax for the dbms_space.object_growth_trend procedure:

dbms_space.object_growth_trend ( 
 object_owner in varchar2, 
 object_name in varchar2, 
 object_type in varchar2,
 partition_name in varchar2 default null, 
 start_time in timestamp default null, 
 end_time in timestamp default null, 
 interval in dsinterval_unconstrained default null, 
 skip_interpolated in varchar2 default 'false', 
 timeout_seconds in number default null, 
 single_datapoint_flag in varchar2 default 'true')

Table 14-3 describes the object_growth_trend function parameters.

Table 14-3 OBJECT_GROWTH_TREND Function Parameters

Parameter Description

object_owner

The schema containing the object.

object_name

The name of the object.

OBJECT_TYPE

The type of the object.

partition_name

The name of the table or index partition, if relevant. Otherwise, specify NULL.

start_time

A time stamp value indicating the beginning of the growth trend analysis.

end_time

A time stamp value indicating the end of the growth trend analysis. The default is NOW.

interval

The interval (yes) or not (no). This setting is useful when the result table is displayed as a table rather than a chart. This is because you can see more clearly how the actual recording interval relates to the requested reporting interval. The function returns a table, each 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.

skip_interpolated

Specify whether to skip interpolation of missing values (true or false).

timeout_seconds

The timeout value for the function in seconds.

single_data_point_flag

Specify whether in the absence of statistics to sample the segment.

Returned values are shown in the following example:

TYPE object_growth_trend_row IS RECORD( 
 timepoint timestamp,
space_usage number, 
space_alloc number, 
quality varchar(20));

Table 14-4 describes the object_growth_trend_row return values.

Table 14-4 OBJECT_GROWTH_TREND_ROW Return Values

Parameter Description

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.

  • 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. (The output returned by this function is an aggregation of values recorded across all instances in an Oracle Real Application Clusters 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.

  • 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 (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 are selected.

Estimating Table Size

The create_table_cost procedure of the dbms_space package allows the size of a table to be estimated using the predicted number of rows for an existing table or based on a table column definition. The size of tables can vary widely based on the tablespace storage attribute that is accounted for by this procedure. There are two overloads of this procedure:

  • The first procedure takes the column information of the table.

  • The second procedure takes the average row size of the table.

Because the Oracle SOA Suite tables already exist, it is the second variant that is most useful. The following example estimates the size of the CUBE_INSTANCE table with 10.000 rows, an average row length of 360, and a PCT FREE 0f 10.

  1. Execute the following command. The average row length and current PCT_FREE is derived from DBA_TABLES after ensuring that the table has been analyzed:

    SQL> SELECT AVG_ROW_LEN, PCT_FREE FROM DBA_TABLES WHERE TABLE_NAME = 
    'CUBE_INSTANCE' AND OWNER = 'DEV_SOAINFRA';
    
  2. Execute the dbms_space.create_table_cost procedure:

    set serverout on 
    declare
    
    v_used number(10); 
    v_alloc number(10);
    
    begindbms_space.create_table_cost 
    ('SOA1_SOAINFRA',360,10000,10, v_used,v_Alloc); 
    dbms_output.put_line('used bytes: ' || to_char(v_used)); 
    dbms_output.put_line('allocated bytes: ' || to_char(v_alloc));
    
    end; 
    /
    

The following example estimates the space usage of a table based on its column definition:

set serveroutput on 
declare

  v_used_bytes number(10); 
  v_allocated_bytes number(10); 
  v_type sys.create_table_cost_columns;

begin

v_type := sys.create_table_cost_columns 
( sys.create_table_cost_colinfo('number',9), 
 sys.create_table_cost_colinfo('varchar2',50), 
sys.create_table_cost_colinfo('varchar2',15), 
sys.create_table_cost_colinfo('date'f,null), 
sys.create_table_cost_colinfo('date'f,null) );
dbms_space.create_table_cost 
  ('users',v_type,10000,7,v_used_bytes,v_allocated_bytes); 
dbms_output.put_line('used bytes: ' || to_char(v_used_bytes)); 
dbms_output.put_line('allocated bytes: ' || to_char(v_allocated_bytes));
end; 
/

Complete syntax for the dbms_space.create_table_cost procedure is shown in the following examples:

  • First overload:

    dbms_space.create_table_cost (tablespace_name in varchar2, 
    colinfos in create_table_cost_columns, row_count in number, 
    pct_free in number, used_bytes out number, 
    alloc_bytes out number); create type 
    create_table_cost_colinfo is object (col_type varchar(200), 
    col_size number);
    
  • Second overload:

    dbms_space.create_table_cost (tablespace_name in varchar2, 
    avg_row_size in number, row_count in number, pct_free in number, 
    used_bytes out number, alloc_bytes out number);

Both variants require the input values shown in Table 14-5 and Table 14-6.

Table 14-5 CREATE_TABLE_COST Procedure Parameters

Parameter Description

tablespace_name

The tablespace in which the object is 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 data type of the column) and col_size (the number of characters or bytes in the column).

Table 14-6 CREATE_TABLE_COST Procedure Return Values

Parameter Description

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.

Estimating Index Size

The create_index_cost procedure of the dbms_space package enables you to estimate the space use cost of creating an index on an existing table. Use this to determine the cost of adding an index to the Oracle SOA Suite schema.

The following example estimates the size of a new index based on the data definition language (DDL):

set serveroutput on 
declare
v_used_bytes number(10); 
v_allocated_bytes number(10);
begin
dbms_space.create_index_cost( 
‘create index cube_index on cube_instance(cikey)' 
v_used_bytes, v_allocated_bytes); 
dbms_output.put_line(‘used bytes: ‘ || to_char(v_used_bytes)); 
dbms_output.put_line(‘allocated bytes: ‘|| to_char(v_allocated_bytes));end; /

The complete syntax for DBMS_SPACE.CREATE_INDEX_COST procedure is as follows:

dbms_space.create_index_cost (ddl in varchar2, used_bytes out number,
  alloc_bytes out number, plan_table in varchar2 default null);

The procedure requires the input values shown in Table 14-7.

Table 14-7 DBMS_SPACE.CREATE_INDEX_COST Values

Parameter Description

ddl

The CREATE INDEX statement that creates the index. The DDL statement must be against an existing table.

plan_table

(Optional]) 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.

Usage notes:

  • The table on which the index is created must already exist.

  • The computation of the index size depends on statistics gathered on the segment.

  • It is imperative that the table has been analyzed recently.

  • In the absence of correct statistics, the results may be inaccurate.

Monitoring Unused Indexes

The Oracle database provides a means of monitoring indexes to determine whether they are used. If an index is not used, it can be dropped, eliminating unnecessary statement overhead.

  1. Enter the following statement to start monitoring the usage of an index:

    SQL> ALTER INDEX INDEX_NAME MONITORING USAGE;
    
  2. Enter the following statement to stop monitoring:

    SQL> ALTER INDEX INDEX_NAME NOMONITORING USAGE;
    

The view V$OBJECT_USAGE can be queried for the index being monitored to see if the index has been used. The view contains a USED column whose value is YES or NO, depending upon if the index has been used within the time period being monitored. The view also contains the start and stop times of the monitoring period, and a MONITORING column (YES/NO) to indicate if usage monitoring is currently active. Each time that you specify MONITORING USAGE, the V$OBJECT_USAGE view is reset for the specified index. The previous usage information is cleared or reset, and a new start time is recorded. When you specify NOMONITORING USAGE, no further monitoring is performed, and the end time is recorded for the monitoring period. Until the next ALTER INDEX...MONITORING USAGE statement is issued, the view information is left unchanged.

Monitoring the Hardware Resources and Database

The hardware resource must support the tools selected to implement the growth strategy. It is important to monitor hardware resources during online transaction processing (OLTP) and maintenance periods and especially when these periods overlap.

Hardware - OSWatcher Black Box

To monitor CPU, disk, memory, and network O/S resources, Oracle provides OSWatcher Black Box, which must be installed on all tiers in the Oracle SOA Suite installation.

OSWatcher Black Box is a collection of UNIX shell scripts intended to collect and archive operating system and network metrics to aid Oracle Support Services in diagnosing performance issues. OSWatcher Black Box operates as a set of background processes on the server and gathers OS data on a regular basis, invoking such UNIX utilities as vmstat, netstat, and iostat.

The OSWatcher Black Box User Guide and product can be downloaded through Metalink Support note 301137.1.

https://support.oracle.com
Database – AWR / ADDM

Oracle provides AWR and the Automatic Database Diagnostic Monitor (ADDM) to gather and analyze database performance statistics.

AWR can be configured to automatically collect performance statistical snapshots at regular intervals, or it can be manually triggered. The ADDM utility can then analyze the statistics between two snapshot intervals to produce a performance summary report that includes the following:

  • CPU bottlenecks

  • Undersized memory structures

  • I/O capacity issues

  • High load SQL statements

  • High load PL/SQL execution and compilation issues, and high-load Java usage

  • Oracle Real Application Clusters specific issues

  • Suboptimal use of the Oracle database by the application

  • Database configuration issues

  • Concurrency issues

  • Hot objects

The AWR and ADDM utilities can be executed manually or through Oracle Enterprise Manager Database Control. For more information, see Oracle Database 2 Day + Performance Tuning Guide.

Disk I/O - Oracle Orion

A complete Oracle SOA Suite I/O design plan is beyond the scope of this chapter. However, good advice can be found in the Oracle Database Performance Tuning Guide. A well designed I/O plan considers capacity, availability, and performance.

The AWR reports identify segments, data files, and tablespaces that are accessed frequently. OSWatcher identifies lengthy I/O queues. The combination of these tools identifies the disk devices and files of concern. This may identify a need to redistribute files to balance I/O or to modify the redundant arrays of independent disks (RAID) levels on slow volumes.

The I/O calibration feature (Oracle Orion) of the Oracle database issues random I/O using the Oracle data files to access the storage media. This produces results that closely match the actual performance of the database. Oracle Orion is expressly designed for simulating Oracle database I/O workloads using the same I/O software stack as Oracle. Oracle Orion can also simulate the effect of striping performed by Oracle Automatic Storage Management.

Understanding Growth Management Challenges and Testing Strategies

This section provides recommendations on the appropriate tools to manage the growth of the Oracle SOA Suite database. The provided advice is for new installations or existing installations whose current strategy may lack procedures such as monitoring and space management. For additional advice, see Parallel Purging and Table Partitioning Issues.

It is advisable to read Monitoring Space Usage, Hardware Resources, and Database Performance to become familiar with the Oracle SOA Suite purging and partitioning tools described in Managing Database Growth.

Database Growth Management Challenges

The challenges described in this section provide background by describing situations that have contributed to the recommended strategies.

Excessive Growth of Oracle SOA Suite Tables Due to an Ineffective Growth Management Strategy

When an ineffective purging strategy has been implemented, the Oracle SOA Suite tables may grow very large. This leads to an urgent need to reclaim space. The larger the tables, the harder it is to delete rows and reclaim space.

The performance of the parallel purge script relies on factors such as CPU resources and the speed of disk I/O. However, very large tables have proven to be challenging. The challenge is due to the amount of data that must be parsed to determine composites that can be deleted. This parsing can monopolize the elapsed time of the entire purge script. For more information, see Using Parallel Query Slaves.

This situation highlights the need to constantly monitor the effectiveness of the growth management strategy and to take corrective actions as soon as possible before the tables become very large.

Tuned Parallel Purge Script Cannot Handle the Inflow

To help the performance of the parallel purge script, tables with long elapsed times can be range partitioned and excluded from the script. However, this raises concerns about downtime, if table partitioning is necessary in a live production environment. While tables can be partitioned online through the redefinition packages, this operation may not complete in a reasonable time when presented with a very large table.Starting in Oracle SOA Suite Release 11g R1 (11.1.1.6), the granularity of tables that can be partitioned improved. It is possible to partition high volume tables with only their master table. This should make partitioning more attractive and feasible.

Table Partitions Cannot Be Dropped Due to Long Running Composites

Long running composites are composites that remain open beyond the retention period. This has implications for Oracle SOA Suite table partitions because they cannot be dropped until all composites they contain are completed. A single flow instance can prevent a partition from being dropped and space reclaimed. This situation gave rise to the row migration scripts that were introduced with Oracle SOA Suite Release 11g R1 (11.1.1.6).

Plan for sufficient disk space to accommodate partitioned tables that include the retention period of the longest running composites. The row migration scripts are recommended for corrective actions when there is an urgent need to reclaim space.

The movement of composite data by the row migration script raises the following concerns:

  • The scripts were written with an assumption that around 5% of the data in a partition be moved to avoid performance concerns with the script.

    • To maintain the requirements of equipartitioning requires the movement of 5% of the rows across many tables. For more information about equipartitioning, see Referential Integrity and Equipartitioning.

    • The arbitrary figure of 5% does not convey the size of the partition, which depends on inflow rate and partition period (daily, weekly, and monthly).

  • Repeated migration of long running composites to either the latest partition or to a partition specifically created to pool active composites may lead to an accumulation.

    • A given partition may become very large and require purging that has other implications. For more information, see Partition Pruning.

Quality Assurance Testing

Testing the growth management strategies is essential to ensure that tools and techniques are practiced and understood.The hardware resources available for quality assurance testing are unlikely to be identical to a production environment. Therefore, administrators must make conservative predictions when projecting results onto the production host.

The following topics are described:

Reviewing Metalink Support Note 1384379.1

Review support note Doc ID 358.1 Maintenance and Administration Advisor: Oracle Fusion Middleware (FMW) SOA 11g Infrastructure Database.

https://support.oracle.com
Configuring the Production Audit Level Setting

Ensure that audit settings are appropriate for a production environment. For more information, see Reducing Audit Levels.

Creating an Oracle SOA Suite Schema - Test Environment

Though difficult, it is important to create a test environment that is comparable to a production environment. The performance of the purge scripts are adversely affected by the amount of data that requires parsing. Therefore, a well-sized test environment is important to understand purge performance. For more information, see Using Parallel Query Slaves.

The workload mix that populates the schema must resemble a production environment in payload size and number of composite invocations. The data must simulate many days of loading, so that space averages and size estimates can be better determined.

Measure Inflow and Space Estimations

During the loading of data, take time to determine the space used per table and respective space distribution. For more information, see Identifying the Inflow of Data for calculations on inflow.

Collect metrics with partitioning in mind:

Base Point Backup of Test Environment

Perform a backup of the test environment.

Executing the Parallel or Single Threaded Script and Reclaiming Space

There are two cycles that must first be understood:

  • The purge cycle: Refers to a period that may involve multiple executions of the purge scripts.

  • The maintenance cycle: Refers to the number of days required to perform all space management operations. For more information, see Introduction to the Components of a Data File.

The goal is to execute the purge scripts and maintenance operations to determine the optimal cycle for each, ensuring that the purge has room to grow. The purge script may need to be executed multiple times per day and maintenance operations may span multiple days.

The following sections describe how to tune, monitor, and reclaim space:

Reviewing the Testing Results

The goals should be as follows:

Partitioning the Tables Causing a Bottleneck

Large installations should consider partitioning the tables identified as difficult to purge. These tables may also be the largest tables. Table partitioning is a proven method of removing bulk data. The Oracle SOA Suite schema is instrumented with a partition key to facilitate range partitioning.

Starting in Oracle SOA Suite Release 11g R1 (11.1.1.6), the granularity of tables that can be partitioned improved. You can partition high volume tables with only their master table. This should make partitioning more attractive and feasible:

Base Point Backup with Partitioned Table

Perform a backup that includes the partitioned table.

Repeating Purge Testing and Review and Excluding the Partitioned Table

Repeat the purge testing and test results review described in Executing the Parallel or Single Threaded Script and Reclaiming Space and Reviewing the Testing Results. Exclude the partitioned table.

Recommended Growth Management Strategies

The following recommended growth management strategies are based on the Oracle SOA Suite database profile:

The strategies require testing to determine their effectiveness. The testing must ensure the following:

  • Purge scripts have room to grow.

  • Partitioned tables have sufficient disk space.

  • Growth is monitored.

  • Maintenance is performed with minimal impact to OLTP performance.

Recommendations for Large Database Profiles

Installations with large database profiles must seriously consider table partitioning as part of their strategy. Table range partitioning is a proven method for managing large tables:

  1. Range interval partitioning is an extension of range partitioning in which the database automatically allocates partitions. For more information, see Range Interval Partitioning.
  2. Execute the parallel purge excluding the tables that are partitioned. For more information, see Looped Purge in Parallel Script with dbms_scheduler.
  3. Partition the tables that result in bottlenecks when running the purge script. For more information, see Partitioning Component Tables.
  4. Reclaim space as part of the maintenance cycle. For more information, see Introduction to the Components of a Data File.
  5. Drop eligible partitions as part of the maintenance cycle. For more information, see Running the Verification Script.
  6. Monitor the inflow and outflow. For more information, see Developing a Retention Policy and Identifying the Outflow of Data.
  7. Monitor database and hardware resources. For more information, see Reclaiming Segment and Data File Space.
Recommendations for Small Database Profiles

Small installations with limited CPU, memory, and disk space should start with the single-threaded purge and then move to the parallel purge:

  1. Execute the single threaded purge. For more information, see Looped Purge Script.

  2. Execute the parallel purge. For more information, see Looped Purge in Parallel Script with dbms_scheduler.

    1. If the single threaded purge is not performing, then the parallel purge should be tested.

  3. Monitor the inflow and outflow. For more information, see Developing a Retention Policy and Identifying the Outflow of Data.

  4. Monitor database and hardware resources. For more information, see Reclaiming Segment and Data File Space.

Understanding Space Management

Much of the information in this section can be found in the various Oracle Database administration guides. The space management concepts and commands summarized in this section address a common misconception that the Oracle SOA Suite purge scripts reclaim space. To reclaim space, database maintenance operations must be executed.

By default, the Oracle SOA Suite schema is created on locally managed tablespaces with automatic segment space management (ASSM). Therefore, all advice is limited by this situation. This section is not meant as a comprehensive guide to all database space management features.

Introduction to the Components of a Data File

A data file scan can be divided into the following components:

  • Segment: Contains a specific type of database object. That is, a table is stored in a table segment, and an index is stored in an index segment.

  • Extent: A contiguous set of data blocks within a segment. The Oracle database allocates space for segments in units of one extent.

  • Data block: Also called a database block, this is the smallest unit of I/O-to-database storage. An extent consists of several contiguous data blocks.

  • Tablespace: Consists of one or more physical data files. A locally managed tablespace maintains a bitmap in the data file header to track free and used space in the data file body. Each bit corresponds to a group of blocks. When space is allocated or freed, the Oracle database changes the bitmap values to reflect the new status of the blocks.

Segments, extents, and data blocks are all logical structures.

Figure 14-1 shows the relationship between logical and physical storage.

Figure 14-1 Logical and Physical Storage

Description of Figure 14-1 follows
Description of "Figure 14-1 Logical and Physical Storage"
Segment High Water Mark

To manage space, the Oracle database tracks the state of blocks in the segment. The high water mark (HWM) is the point in a segment beyond which data blocks are unformatted and have never been used. When the blocks between the HWM and low HWM are full, the HWM advances to the right and the low HWM advances to the location of the old HWM. Figure 14-2 provides details. As the database inserts data over time, the HWM continues to advance to the right, with the low HWM always trailing behind it. Unless you manually rebuild, truncate, or shrink/deallocate the object, the HWM never retreats.

Reclaiming Segment and Data File Space

The following sections provide instructions on common techniques used to reclaim segment and data file space.

Performing an Online Segment Shrink

The purge scripts delete rows from database segments (tables and indexes) and release space within the data blocks for reuse, but may also cause fragmentation with some space too small for reuse. The space can be defragmented and the extents reclaimed by performing an online segment shrink. The shrink operation consolidates free space below the HWM and compacts the segment. After this, it then moves the HWM and deallocates the space above the HWM.

Data manipulation language (DML) can still be issued during the data movement phase of segment shrink. However, DML operations are blocked for a short time at the end of the shrink operation when the space is deallocated. Indexes are maintained during the shrink operation and remain usable.

The Segment Advisor can identify segments that benefit from online segment shrink. However, after constant purging, most Oracle SOA Suite segments should be candidates for online segment shrink operations. For more information on the Segment Advisor, see Oracle Database Administrator’s Guide.

The general online segment shrink steps are as follows:

  • Before executing the shrink command, row movement must be enabled:

    SQL> ALTER TABLE TABLE_NAME ENABLE ROW MOVEMENT;
    
  • To shrink a segment:

    SQL> ALTER TABLE TABLE_NAME SHRINK SPACE;
    
  • The COMPACT clause lets you divide the shrink segment operation into two phases. When you specify COMPACT, the segment space is defragmented and rows are compacted, but it postpones the resetting of the HWM and the deallocation of space. Dividing the operations into two phases is useful for large tables and reduces the impact on the blocking of DML during the deallocation phase.

    You can reissue the shrink space without the COMPACT clause during off-peak hours to complete the second phase:

    SQL> ALTER TABLE TABLE_NAME SHRINK SPACE COMPACT;
    
  • The CASCADE clause extends the segment shrink operation to all dependent segments of the object. For example, if you specify CASCADE when shrinking a table segment, all indexes of the table are also shrunk:

    SQL> ALTER TABLE TABLE_NAME SHRINK SPACE COMPACT CASCADE;
    
  • For very large tables, it is advisable to perform the shrink in two phases and not to use the CASCADE clause. Perform the COMPACT operation first, perhaps even compact the basic LOBs before that, then execute the normal shrink command to reclaim the unused space.

  • All segment types are eligible for online segment shrink, except the following:

    • Index-organized table (IOT) mapping tables

    • Tables with row ID-based materialized views

    • Tables with function-based indexes

    • Secure file LOBs

    • Compressed tables

Table 14-8 provides online segment shrinking examples.

Table 14-8 Online Segment Shrink Examples

Scenario Example

Shrink a large table in two phases.

SQL> ALTER TABLE TABLE_NAME ENABLE ROW MOVEMENT;
SQL> ALTER TABLE TABLE_NAME SHRINK SPACE COMPACT;
SQL> ALTER TABLE TABLE_NAME SHRINK SPACE;

Shrink a table and all its dependent segments (including BASICFILE LOB segments).

SQL> ALTER TABLE TABLE_NAME SHRINK SPACE CASCADE;

Shrink a BASICFILE LOB segment only.

SQL> ALTER TABLE TABLE_NAME MODIFY LOB (LOB_NAME)
 (SHRINK SPACE);

Shrink a single partition of a partitioned table.

SQL> ALTER TABLE TABLE_NAME MODIFY PARTITION P1 SHRINK SPACE;
Deallocating Unused Space

The DEALLOCATE UNUSED command can be used to manually deallocate unused space. This command frees unused space above the HWM. The online segment shrink also releases space above HWM.

SQL> ALTER TABLE TABLE_NAME DEALLOCATE;

Use the optional KEEP clause to specify the amount of space retained in the segment of table, index, or cluster:

SQL> ALTER TABLE TABLE_NAME DEALLOCATE UNUSED KEEP INTEGER;
SQL> ALTER INDEX INDEX_NAME DEALLOCATE UNUSED KEEP INTEGER;

Note:

The UNUSED_SPACE procedure of the DBMS_SPACE package returns information about the position of the HWM and the amount of unused space in a segment. For segments in locally managed tablespaces with ASSM, use the SPACE_USAGE procedure for more accurate information on unused space. (Use the DBA_FREE_SPACE view to verify the deallocated space.)

Coalescing or Rebuilding indexes

Whether to rebuild BTREE indexes is a point of contention for database administrators. Unfortunately, over time the Oracle SOA Suite purge scripts fragment most of the Oracle SOA Suite BTREE indexes in a manner that requires them to be rebuilt to maintain SQL performance.

The purge scripts delete only closed composites, leaving the open ones in each index data block. Because many of the Oracle SOA Suite index keys are monotonically increasing, the free space in the data block is not reused.

SQL> ALTER INDEX INDEX_NAME REBUILD OR COALESCE

Table 14-9 describes the costs and benefits of coalescing or rebuilding indexes.

Table 14-9 Costs and Benefits of Coalescing or Rebuilding Indexes

Rebuild Index Coalesce Index

Quickly moves index to another tablespace.

Cannot move index to another tablespace.

Higher costs: Requires more disk space.

Lower costs: does not require more disk space.

Creates new tree and shrinks height, if applicable.

Coalesces leaf blocks within the same branch of the tree.

Enables you to quickly change storage and tablespace parameters without having to drop the original index.

Quickly frees up index leaf blocks for use.

One method to combat the need for index rebuilding is to convert them to global hash indexes. Hashing monotonically increasing keys distributes them randomly across data blocks, thereby improving space reuse. There are other improvements that include a reduction in buffer busy waits for hot index blocks. However, not all Oracle SOA Suite indexes are good candidates for conversion. See the following documentation:

http://www.oracle.com/technetwork/database/options/clustering/overview/index-086583.html
Dropping Table Partitions

Table partitions can be dropped to remove table data in bulk and reclaim space. Within Oracle SOA Suite, do not drop the partitions unless identified as eligible. For more information, see Running the Verification Script.

Dropping a Partition

SQL> ALTER TABLE TABLE_NAME DROP PARTITION P1;

Although the DROP PARTITION operation takes longer, it is worth specifying the UPDATE INDEXES clause to avoid the need to rebuild indexes.

Many table maintenance operations on partitioned tables invalidate (mark UNUSABLE) the corresponding indexes or index partitions. You must then rebuild the entire index or, for a global index, each of its partitions. The database lets you override this default behavior if you specify UPDATE INDEXES in your ALTER TABLE statement for the maintenance operation. Specifying this clause tells the database to update the indexes at the time it executes the maintenance operation DDL statement. This provides the benefits described in this section.

The following operations support the UPDATE INDEXES clause:

  • ADD PARTITION

  • COALESCE PARTITION

  • DROP PARTITION

  • EXCHANGE PARTITION

  • MERGE PARTITION

  • MOVE PARTITION

  • SPLIT PARTITION

  • TRUNCATE PARTITION

Under certain circumstances, the partition operations shown in Table 14-10 may be required to assist with issues.

Table 14-10 Partition Operations

Partition Operation Description

Shrink table partition

SQL> ALTER TABLE TABLE_NAME MODIFY PARTITION P1 SHRINK
 SPACE;

Truncate table partition

SQL> ALTER TABLE ... TRUNCATE PARTITION

Compress table partition

SQL> ALTER TABLE TABLE_NAME MOVE PARTITION PART_NAME
 TABLESPACE TABLESPACE_NAME NOLOGGING COMPRESS FOR
 OLTP;

Notable restrictions:

  • Online segment shrinking is not supported for compressed tables.

  • Secure file LOBs have their own compression methods.

  • Compression technology increases CPU resource usage.

  • Altering a partition to enable compression applies only to new data. To compress existing data, you must move the partition. Moving table partitions drops the old partition segment and creates a new segment, even if you do not specify a new tablespace.

Configuring Secure File LOBs

Secure files are a LOB storage architecture that provides performance benefits that many factor as faster and better than traditional LOB access. Secure files are a complete rewrite of the original LOB storage architecture, now called basic files.

Secure files support the following advanced features:

  • Deduplication: Stores only one copy of identical secure file data.

  • Compression: Reduces storage, I/O, redo log, and encryption overhead. The online segment shrink command is not supported for secure file LOBs due to compression.

  • Encryption

Secure files were a feature introduced in the Oracle 11g database. It is recommended that the highest available database patch set be applied to avoid known problems.

Secure File Requirements

The following settings are required to use secure files.

  1. Set the COMPATIBLE initialization parameter higher than 11.0.0.0.0.
    SQL> show parameter COMPATIBLE;
    
  2. The DB_SECUREFILE initialization parameter controls the default action of the database with regards to LOB storage (default if PERMITTED):
    SQL> ALTER SYSTEM SET DB_SECUREFILE = 'ALWAYS';
    SQL> SHOW PARAMETER DB_SECUREFILE
    Parameter Description

    ALWAYS

    All LOBs in ASSM tablespaces are created as secure file LOBs. LOBs in non-ASSM tablespaces are created as basic file LOBs unless explicitly specified as secure files. Basic file storage options are ignored, and secure file default storage options are used for any unspecified options.

    FORCE

    All LOBs are created as secure file LOBs. If the LOB is being created in a non-ASSM tablespace, an error is thrown. Basic file storage options are ignored, and secure file default storage options are used for any unspecified options.

    PERMITTED

    The default setting that enables secure file LOB storage when the SECUREFILE keyword is used. The default storage method is BASICFILE.

    NEVER

    Secure file LOBs are not permitted.

    IGNORE:

    Prevents creation of secure file LOBs, and ignores any errors associated with secure file storage options.

  3. Configure the tablespace to support ASSM:
    SQL> SELECT SEGMENT_SPACE_MANAGEMENT FROM DBA_TABLESPACES WHERE TABLESPACE_NAME
     = 'TABLESPACE_NAME';
Converting Secure Files

The Repository Creation Utility (RCU) that creates the Oracle SOA Suite tables by default also creates basic files. However, secure file LOBs can also be created at the time of SOA schema creation. The process below is only for the time of creation. Otherwise, see Migrating Secure Files.

  1. Ensure the requirements are met and the DB_SECUREFILE database initialization parameter is set to ALWAYS or FORCE. For more information about requirements, see Secure File Requirements.
  2. Run the appropriate Oracle SOA Suite RCU utility to create the schema. Although the LOBs are defined as basic, they are created as secure files. The basic file LOB storage parameters are ignored.

    The advanced features of compression, deduplication, and encryption are not enabled by default.

  3. Enter the following command to determine if the advanced features are enabled for the secure file LOB column:
    SQL> SELECT TABLE_NAME, COLUMN_NAME, SECUREFILE, RETENTION, ENCRYPT,
     COMPRESSION, DEDUPLICATION FROM DBA_LOBS;
    

    The ALTER command to enable the advanced features must be performed immediately after using RCU:

    SQL> ALTER TABLE LOB_TABLE MODIFY LOB(LOB_COLUMN) (COMPRESS);
    SQL> ALTER TABLE LOB_TABLE MODIFY LOB(LOB_COLUMN) (DEDUPLICATE);

    Note:

    Oracle recommends that you enable compression, deduplication, or encryption through the CREATE TABLE statement and/or online redefinition. For existing data, if you enable these features through the ALTER TABLE statement, all secure file LOB data in the table is read, modified, and written. This causes the database to lock the table during a potentially lengthy operation. Therefore, the ALTER table command is not recommended for converting populated tables, and causes locking.

Migrating Secure Files

The online redefinition is the recommended online method for converting to secure file LOBs. However, there are offline methods:

  • Create Table as Select (CTAS) script

  • Insert Table as Select (ITAS)

  • Export/import

Advantages of online redefinition:

  • No requirement to take the table or partition offline.

  • Can be performed in parallel.

Disadvantages of online redefinition:

  • Additional storage equal to the entire table or partition and all LOB segments must be available.

  • Global indexes must be rebuilt.

For best practices about executing the online REDFINITON package, including preventing redo generation and parallel execution, see Migrating Columns from BasicFiles LOBs to SecureFiles LOBs in Oracle Database SecureFiles and Large Objects Developer's Guide.

Additional Database Management Methods

This section describes additional methods available for managing database growth.

TRUNCATE Statement

The truncate statement removes all rows from a table. Therefore, this is unlikely to be performed or required in a Oracle SOA Suite production environment. For more information, see Removing Records from the Runtime Tables Without Dropping the Tables.

Resizing Data Files

To first avoid application errors and the need for manual intervention when a tablespace runs out of space, set the data files to AUTOEXTEND.

  1. To determine whether a data file is auto-extensible, query the DBA_DATA_FILES view column AUTOEXTENSIBLE:
    SQL> SELECT AUTOEXTENSIBLE FROM DBA_DATA_FILES
    
  2. Specify automatic file extension by specifying an AUTOEXTEND ON clause when the data file is created or altered:
    SQL> ALTER TABLESPACE TABLESPACE_NAME ADD DATAFILE '/U01/DATAFILE.DBF' SIZE 10M
     AUTOEXTEND ON;
    
    SQL> ALTER DATABASE DATAFILE '/U01/DATAFILE.DBF' AUTOEXTEND ON;
    

If the initial allocation of the data files was excessive or segments were allowed to grow excessively, the data file can be resized.

Resize the data file. However, this is assuming that the space is not used by segments.

SQL> ALTER DATABASE DATAFILE '/U01/DATAFILE.DBF'' RESIZE 50M;

It is not always possible to decrease the size of a file to a specific value. In which case, the database returns the following error.

ORA-03297: file contains used data beyond requested RESIZE value