14 Developing a Database Growth Management Strategy
This chapter includes the following sections:
-
Monitoring Space Usage, Hardware Resources, and Database Performance
-
Understanding Growth Management Challenges and Testing Strategies
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:
-
Determine the growth trend of the components. For more information, see Determining the Growth Trend of Components.
-
Collect component statistics after each day of load testing so that growth averages can be determined.
-
-
Determine the growth trend of the Oracle SOA Suite schema. For more information, see Determining the Growth Trend of the Schema.
-
Collect schema sizing statistics after each day of load testing so that growth averages can be determined.
-
-
Determine the largest segments. For more information, see Determining the Largest Segments.
-
Determine the growth trend of tables and indexes. For more information, see Determining the Growth Trend of Tables and Indexes.
-
The growth statistics for table and indexes are automatically gathered through the Automatic Workload Repository (AWR).
-
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:
-
Determine the growth trend of the components. For more information, see Determining the Growth Trend of Components.
-
Determine the growth trend of the Oracle SOA Suite schema. For more information, see Determining the Growth Trend of the Schema.
-
Determine the largest segments. For more information, see Determining the Largest Segments.
-
Determine the growth trend of tables and indexes. For more information, see Determining the Growth Trend of Tables and Indexes.
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. |
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 fromUSER_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 |
---|---|
|
The schema containing the object. |
|
The name of the object. |
|
The type of the object. |
|
The name of the table or index partition, if relevant. Otherwise, specify |
|
A time stamp value indicating the beginning of the growth trend analysis. |
|
A time stamp value indicating the end of the growth trend analysis. The default is |
|
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. |
|
Specify whether to skip interpolation of missing values ( |
|
The timeout value for the function in seconds. |
|
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 |
---|---|
|
A timestamp value indicating the time of the reporting interval. Records are not produced for values of |
|
The number of bytes actually being used by the object data. |
|
The number of bytes allocated to the object in the tablespace at that time. |
|
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.
|
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.
-
Execute the following command. The average row length and current
PCT_FREE
is derived fromDBA_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';
-
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 |
---|---|
|
The tablespace in which the object is created. The default is the |
|
The anticipated number of rows in the table. |
|
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 The second variant also requires for each anticipated column values for |
Table 14-6 CREATE_TABLE_COST Procedure Return Values
Parameter | Description |
---|---|
|
The actual bytes used by the data, including overhead for block metadata, |
|
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 |
---|---|
|
The |
|
(Optional]) The name of the plan table to use. The default is
|
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.
-
Enter the following statement to start monitoring the usage of an index:
SQL> ALTER INDEX INDEX_NAME MONITORING USAGE;
-
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.
-
Determine the growth trend of the components. For more information, see Determining the Growth Trend of Components.
-
Determine the growth trend of the Oracle SOA Suite schema. For more information, see Determining the Growth Trend of the Schema.
Collect metrics with partitioning in mind:
-
Determine the largest segments. For more information, see Determining the Largest Segments.
-
Determine the growth trend of tables and indexes. For more information, see Determining the Growth Trend of Tables and Indexes.
-
Estimate the table size. For more information, see Estimating Table Size.
-
Estimate the index size. For more information, see Estimating Index Size.
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:
-
Parallel purge. For more information, see Executing the Parallel Purge Script.
-
Hardware and database monitoring. For more information, see Monitoring the Hardware Resources and Database.
-
Space management operations. For more information, see Introduction to the Components of a Data File.
Reviewing the Testing Results
The goals should be as follows:
-
Determine if the purge can delete and reclaim space, either equal to or above the inflow. For more information, see Developing a Retention Policy and Identifying the Outflow of Data.
-
Determine how many times the purge must be executed and the total elapsed time of the purge cycle to meet purging requirements. This purge cycle must complete within 80% of the time allocated for its execution.
-
Determine the bottleneck tables. For more information, see Debugging and Tracing Purging Operations.
-
Review database and O/S reports for resource contention and waits. For more information, see Monitoring the Hardware Resources and Database.
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:
-
Partial partitioning.
-
Long running composites. For more information, see Identifying Long Running Composites and Table Partitioning.
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:
- Range interval partitioning is an extension of range partitioning in which the database automatically allocates partitions. For more information, see Range Interval Partitioning.
- Execute the parallel purge excluding the tables that are partitioned. For more information, see Looped Purge in Parallel Script with dbms_scheduler.
- Partition the tables that result in bottlenecks when running the purge script. For more information, see Partitioning Component Tables.
- Reclaim space as part of the maintenance cycle. For more information, see Introduction to the Components of a Data File.
- Drop eligible partitions as part of the maintenance cycle. For more information, see Running the Verification Script.
- Monitor the inflow and outflow. For more information, see Developing a Retention Policy and Identifying the Outflow of Data.
- 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:
-
Execute the single threaded purge. For more information, see Looped Purge Script.
-
Execute the parallel purge. For more information, see Looped Purge in Parallel Script with dbms_scheduler.
-
If the single threaded purge is not performing, then the parallel purge should be tested.
-
-
Monitor the inflow and outflow. For more information, see Developing a Retention Policy and Identifying the Outflow of Data.
-
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.
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 specifyCOMPACT
, 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 specifyCASCADE
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 theCOMPACT
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 |
SQL> ALTER TABLE TABLE_NAME SHRINK SPACE CASCADE; |
Shrink a |
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:
|
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.
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.
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
.
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