8 Managing Optimizer Statistics

This chapter contains best practices for gathering optimizer statistics for the Oracle Communications Order and Service Management (OSM) product. Using the best practices in this chapter will result in better and more stable execution plans for SQL objects in the OSM database.

About Optimizer Statistics

Optimizer statistics are a collection of data that describe the database and the objects in the database. These statistics are used by the optimizer to choose the best execution plan for each SQL statement. Statistics are stored in the data dictionary and can be accessed using data dictionary views such as USER_TAB_STATISTICS.

Oracle Database internally prioritizes the database objects that require statistics, so that those objects that most need updated statistics are processed first. For more information about optimizer statistics, see the Oracle Database documentation.

Knowledge article 1369591.1, Master Note: Optimizer Statistics, has links to many other useful sources of information about optimizer statistics. It is available on the Oracle support website at: 

https://support.oracle.com

Gathering Optimizer Statistics

This section discusses methods of gathering optimizer statistics for OSM.

Gathering Statistics Online

The procedures provided by the om_db_stats_pkg package and native Oracle database statistics procedures can all be run online. However, when gathering statistics during peak hours you should temporarily lower the value of the DEGREE preference as described in "Using Parallel Collection for Gathering Statistics."

Automated Optimizer Statistics Collection

The automatic database optimizer statistics collection maintenance task is enabled by default. This typically triggers launch of an automatic database optimizer statistics collection job during a predefined maintenance window.

Partition statistics can be locked, and Oracle recommends that you:

  • Leave the automatic statistics collection enabled.

  • Some maintenance operations (such as purging partitions and deploying, undeploying, and purging cartridges) render the statistics stale. Schedule these maintenance operations to complete before automatic optimizer statistics collection starts.

For a list of steps and procedures that can be used to bootstrap and maintain the OSM Database Optimizer Statistics Management process with OSM releases that support locking of partition statistics, see knowledge article 1925539.1, New OSM Database Optimizer Statistics Management, on the Oracle support website for additional information: 

https://support.oracle.com

You can determine if the automatic optimizer statistics collection maintenance task is enabled by running the following commands as a SYSDBA user:

set serveroutput on
SELECT client_name, status FROM dba_autotask_operation;

You can disable the automatic optimizer statistics collection maintenance task by running the following commands as a SYSDBA user:

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'auto optimizer stats collection',
    operation => NULL,
    window_name => NULL);
END;
/

You can enable the automatic optimizer statistics collection maintenance task by running the following commands as a SYSDBA user:

BEGIN
  DBMS_AUTO_TASK_ADMIN.ENSABLE(
    client_name => 'auto optimizer stats collection',
    operation => NULL,
    window_name => NULL);
END;
/

Gathering Fixed Object Statistics

When fixed object statistics are missing, the database optimizer uses predefined default values that may not be adequate for your system, and this could lead to less than optimal execution plans. For example, RMAN, Data Guard, Streams, and Grid Control make heavy use of fixed tables through DBA and V$ views and they often experience performance issues when fixed object statistics have not been collected. Another common symptom is extreme TEMP space usage driven by poor plans against fixed tables.

The automatic database optimizer statistics collection job does not gather fixed object statistics.

Oracle recommends that you gather fixed object statistics:

  • When bootstrapping the OSM database optimizer statistics process

  • After an OSM upgrade

  • After deploying or undeploying cartridges

  • Following a significant increase in order volume

  • Following a change in partition size

You can gather fixed object statistics when there is a representative load on the system (ideally at peak utilization) by running the following commands as a SYSDBA user:

execute DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

Gathering System Statistics

System statistics enable the optimizer to more accurately determine the cost of each operation in an execution plan by using information about the actual system hardware that is executing the statement, such as CPU speed and I/O performance.

The automatic database optimizer statistics collection job does not gather system statistics.

Oracle recommends that you gather system statistics:

  • When bootstrapping the OSM database optimizer statistics process.

  • Following a significant increase in order volume.

  • Following changes in your database CPU speed or IO subsystem.

You can gather system statistics when there is a representative load on the system (ideally at peak utilization) by running the following commands as a SYSDBA user:

execute DBMS_STATS.GATHER_SYSTEM_STATS;

Gathering Cartridge Metamodel Statistics

OSM stores cartridge metamodel information in database tables and statistics on these tables should be kept up-to-date to ensure optimal order processing performance. Gather cartridge metamodel statistics:

  • When bootstrapping the OSM database optimizer statistics process.

  • After deploying or undeploying cartridges.

  • After an OSM upgrade.

You can cartridge metamodel statistics by running the following commands as an OSM order management user:

set serveroutput on
execute om_db_stats_pkg.gather_cartridge_stats;

Gathering Order Statistics

The procedure to follow to gather statistics on OSM order tables varies depending on table volatility. The contents of a volatile order table are primarily impacted by the current order volume. For these tables, once an order has been processed, the associated data is automatically deleted.

High Volatility Order Tables

The following order tables are always highly volatile because their contents are very short-lived. These tables may have thousands of rows at peak workload, but have little or no data when there is reduced order activity in the system.

  • OM_JMS_EVENT

  • OM_JMS_THREAD

  • OM_ORDER_STATE_PENDING

  • OM_ORDER_STATE_EVENT_PENDING

  • OM_COORD_NODE_INSTANCE

You should not enable incremental statistics on highly volatile tables. However, Oracle recommends that you lock statistics for these tables, using the following command:

execute om_db_stats_pkg.lock_volatile_order_stats;
Low Volatility Order Tables

The majority of order tables retain data until that data is purged. These tables are configured with a low level of volatility. Oracle recommends that you enable incremental statistics for low volatility tables.

Medium Volatility Order Tables

Some order tables have both partitions that retain order data and volatile partitions from which data is deleted after it is processed. For historical reasons, these tables are configured with a medium level of volatility. However, due to process improvements that have been identified over time, all medium volatility tables should be reconfigured either as high volatility or low volatility tables based on the characteristics of your solution.

By default, the following tables are configured with a medium level of volatility:

  • OM_ORDER_FLOW

  • OM_AUTOMATION_CTX

  • OM_AUTOMATION_CORRELATION

  • OM_ORDER_POS_INPUT

  • OM_UNDO_BRANCH_ROOT

  • OM_ORCH_DEPENDENCY_PENDING

If most of your orders complete in less than 1 hour, it is recommended that you manage these tables in the same manner as high volatility tables. Otherwise, it is recommended that you manage them in the same manner as low volatility tables.

In other words, if most of your orders complete in less than 1 hour, when bootstrapping the OSM database optimizer statistics process, run the following as the OSM order management user:

execute om_db_stats_pkg.unlock_volatile_order_stats;
execute om_db_stats_pkg.set_table_volatility('OM_ORDER_FLOW',
    om_const_pkg.v_volatility_high);
execute om_db_stats_pkg.set_table_volatility('OM_AUTOMATION_CTX',
    om_const_pkg.v_volatility_high);
execute om_db_stats_pkg.set_table_volatility('OM_AUTOMATION_CORRELATION',
    om_const_pkg.v_volatility_high);
execute om_db_stats_pkg.set_table_volatility('OM_ORDER_POS_INPUT',
    om_const_pkg.v_volatility_high);
execute om_db_stats_pkg.set_table_volatility('OM_UNDO_BRANCH_ROOT',
    om_const_pkg.v_volatility_high);
execute om_db_stats_pkg.set_table_volatility('OM_ORCH_DEPENDENCY_PENDING',
    om_const_pkg.v_volatility_high);
execute om_db_stats_pkg.lock_volatile_order_stats;

However, if most of your orders complete in more than 1 hour, run the following instead:

execute om_db_stats_pkg.unlock_volatile_order_stats;
execute om_db_stats_pkg.set_table_volatility('OM_ORDER_FLOW',
    om_const_pkg.v_volatility_low);
execute om_db_stats_pkg.set_table_volatility('OM_AUTOMATION_CTX',
    om_const_pkg.v_volatility_low);
execute om_db_stats_pkg.set_table_volatility('OM_AUTOMATION_CORRELATION',
    om_const_pkg.v_volatility_low);
execute om_db_stats_pkg.set_table_volatility('OM_ORDER_POS_INPUT',
    om_const_pkg.v_volatility_low);
execute om_db_stats_pkg.set_table_volatility('OM_UNDO_BRANCH_ROOT',
    om_const_pkg.v_volatility_low);
execute om_db_stats_pkg.set_table_volatility('OM_ORCH_DEPENDENCY_PENDING',
    om_const_pkg.v_volatility_low);
execute om_db_stats_pkg.lock_volatile_order_stats;
Enabling Incremental Statistics

Once table volatility has been properly reconfigured, and during the bootstrapping the OSM database optimizer statistics process, it is recommended that, as the OSM order management user, you enable incremental statistics for low volatility tables.

execute om_db_stats_pkg.set_table_prefs_incremental(a_incremental => true,
    a_volatility => om_const_pkg.v_volatility_low);

You should then confirm that INCREMENTAL_STALENESS is configured, using the following command:

SELECT dbms_stats.get_prefs(pname=>'INCREMENTAL_STALENESS', tabname=>'OM_ORDER_INSTANCE') FROM dual;

If the query above generates an error or doesn't return USE_STALE_PERCENT, USE_LOCKED_STATS, use the following commands to set the value:

set serveroutput on
BEGIN
  FOR T IN
  (SELECT IT.TABLE_NAME
    FROM OM_$INSTALL$TABLE IT, USER_TABLES UT
    WHERE IT.VOLATILITY = om_const_pkg.v_volatility_low AND UT.TABLE_NAME = IT.TABLE_NAME AND UT.PARTITIONED = 'YES'
  )
  LOOP
    dbms_stats.set_table_prefs(user, T.TABLE_NAME, 'INCREMENTAL_STALENESS', 'USE_STALE_PERCENT, USE_LOCKED_STATS');
  END LOOP;
END;
/

If this generates an error, review the list of database patches installed on your system. Otherwise, you can confirm that INCREMENTAL_STALENESS is now configured correctly by re-running the confirmation command you ran earlier, that is:

SELECT dbms_stats.get_prefs(pname=>'INCREMENTAL_STALENESS', tabname=>'OM_ORDER_INSTANCE') FROM dual;
Gathering High-Volatility-Table Statistics

Gather high-volatility-table statistics:

  • When bootstrapping the OSM database optimizer statistics process

  • After deploying or undeploying cartridges

  • After an OSM upgrade

  • Following a significant increase in order volume

  • After changing partition size

  • When a new partition becomes active

As the OSM order management user, gather high-volatility-table statistics when the workload is representative, preferably as near to the peak as possible, given that the database must have spare resources:

set serveroutput on
execute DBMS_STATS.SET_SCHEMA_PREFS(user, 'DEGREE', 2); 
execute om_db_stats_pkg.gather_volatile_order_stats(a_force_volatile => true)
execute DBMS_STATS.SET_SCHEMA_PREFS(user, 'DEGREE', 'DBMS_STATS.AUTO_DEGREE');
Gathering Low-Volatility-Table Statistics

Gather low-volatility-table statistics:

  • When bootstrapping the OSM database optimizer statistics process

  • "On a regular basis (for example, once a week) if the automatic database optimizer statistics collection maintenance task is disabled

As the OSM order management user, gather low-volatility-table statistics during a period of low system activity:

set serveroutput on
exec om_db_stats_pkg.gather_order_stats(a_volatility => om_const_pkg.v_volatility_low, a_force => false);

Preparing a New Partition

Before using a new (blank) partition, you should pre-populate the partition with optimizer statistics and then lock the statistics.

Populating New Partition Statistics

Pre-populating statistics on new partitions is of critical importance for avoiding the issues that will otherwise arise when the partition becomes active. For more information about this issue, see the following Oracle Blog entry:

https://blogs.oracle.com/optimizer/maintaining-statistics-on-large-partitioned-tables
Using Statistics from Another Partition

You can manually copy statistics to a new partition using the om_db_stats_pkg.copy_order_ptn_stats procedure. This procedure allows you to specify the partition from which to obtain statistics, as well as the partition to which to copy statistics.

Copy statistics from a mature partition to empty partitions:

  • When bootstrapping the OSM database optimizer statistics process

  • When creating new partitions

To avoid copying partial statistics, make sure that statistics are not being collected when copying statistics from another partition. You can check whether an automatic optimizer statistics collection job is in progress by running the following commands as a SYSDBA user:

set serveroutput on
declare
v_gathering_status integer;
begin
  begin
    select 1
      into v_gathering_status
      from dba_autotask_client_job
      where client_name = 'auto optimizer stats collection'
        and job_scheduler_status = 'RUNNING'
        and rownum = 1;
      dbms_output.put_line('Auto optimizer stats collection is running');
      return;
  exception
  when no_data_found then
    null;
  end;
  dbms_output.put_line('Auto optimizer stats collection is not running');
end;
/

If statistics collection is running, wait until it completes.

Then you can copy statistics to a new partition by running the following commands as the OSM order management user:

declare
    v_copied boolean;
begin
    om_db_stats_pkg.copy_order_ptn_stats(v_copied,
        a_src_partition_name => 'P_000000000000400001',
        a_dst_partition_name => 'P_000000000000700001');
end;

If you have changed the partition size and want to copy stats from an older partition of different size, you can use scale factor to scale the stats. Use the following commands:

Example of scaling up stats into destination partition:

declare
		      v_copied boolean;
begin
   om_db_stats_pkg.copy_order_ptn_stats(v_copied,
       a_src_partition_name => 'P_000000000000400001',
       a_dst_partition_name => 'P_000000000000700001',
       scale_factor => '2');
end;

Example of scaling down stats into destination partition:

declare
      v_copied boolean;
begin
   om_db_stats_pkg.copy_order_ptn_stats(v_copied,
       a_src_partition_name => 'P_000000000000400001',
       a_dst_partition_name => 'P_000000000000700001',
       scale_factor => '0.5');
end;

Note:

Because the scale_factor parameter is a varchar2 argument, it must be provided in single quotes. The value can be any positive decimal number.

To copy recent and valid statistics to the most recently created order partition, as well as to the corresponding partitions of all reference-partitioned tables, you can use:

declare
    v_copied boolean;
begin
    om_db_stats_pkg.copy_order_ptn_stats(v_copied);
end;
Using Statistics from a Statistics Table

You can also use the om_db_stats_pkg.export_order_ptn_stats to export partition statistics to a statistics table and then use om_db_stats_pkg.import_order_ptn_stats to import them to a partition. You could use this to save one or more snapshots of representative partition statistics and then use the underlying statistic tables as templates.

Using Statistics from Another System

Partition statistics exported to a statistics table using om_db_stats_pkg.export_order_ptn_stats can also be saved to the file system using om_db_stats_pkg.expdp__order_ptn_stats.

After you save partition statistics to a file, that file can be transferred to the DATA_PUMP_DIR directory on another system and you can load that file to a statistics table on that system using om_db_stats_pkg.impdp_order_ptn_stats. As long as user names are the same on both systems, you can then import these partition statistics using om_db_stats_pkg.import_order_ptn_stats.

This could, for example, be used to enhance the performance of a newly installed production system by importing partition statistics from a pre-production system.

Using Default Statistics Package

The default statistics package includes scripts, partition statistics seed data, and SQL templates. The package is installed by the OSM installer in the installation_root/Database/partition-statistic/processPartitionStatistic.sh folder.

To load the default partition statistics, run the shell script under installation_root/Database/partition-statistic/processPartitionStatistic.sh. The processPartitionStatistic.sh script imports partition-level DB optimizer statistics with SQL Plus and SQL Loader, which must be installed in the path.

Note:

The SQL Plus and SQL Loader tools are obtained by installing Oracle Instant Client. Refer to the OSM Compatibility Matrix for more information about which version to use.
The following abstract shows the parameters to use with the script:
Usage:
    processPartitionStatistic.sh parameters
      -u OSM User Name         : OSM schema name, optional, if not specified,
                                   value obtained from environment variable
                                   OSM_MAIN_USERNAME.
      -t Tablespace Name       : Tablespace name, optional, if not specified,
                                   value obtained from environment variable
                                   OSM_DEFAULT_TABLESPACE.
      -i Database URL          : Database URL, optional, if not specified,
                                   value obtained from environment variable
                                   OSM_DB_URL. Format is host:port/service.
      -s State Table Name      : State Table Name, optional, if not set,
                                   value obtained from environment variable
                                   OSM_ORDER_STAT_TABLE. When -s is not given
                                   and OSM_ORDER_STAT_TABLE is undefined, the
                                   default is OM_ORDER_STAT_TABLE.
      -a Source partition Name : Optional, source partition name, if not 
                                   specified, value obtained from environment 
                                   variable OSM_SOURCE_PARTITION.
                                   If OSM_SOURCE_PARTITION is not set, 
                                   partition statistic seed data is taken 
                                   from partition seed data. If defined, seed
                                   data is taken from the specified partition. 
      -b Target partition Names: Optional, comma delimited list of target 
                                   partition name, if not specified, value 
                                   obtained from environment variable
                                   OSM_TARGET_PARTITION.
                                   If OSM_TARGET_PARTITION is not set,
                                   partition statistic seed data is copied to
                                   all partition found in OSM schema.
                                   Otherwise, partition statistic seed data
                                   will be copied to the specified partitions.
      -l Log file Names        : Optional, SQL Loader (sqlldr) log file name. 
    or just -h for help
The following is an example of the script:
cd installation_root/Database/partition-statistic
chmod +rx processPartitionStatistic.sh
./processPartitionStatistic.sh -u osm741 -t OSM -l hostOrIp:port/pdb_service_name -s OM_ORDER_STAT_TABLE

Locking Partition Statistics

After copying statistics to a new empty partition, statistics should be locked on that partition if the automatic optimizer statistics collection maintenance task is enabled. Also note that this capability is not available in some OSM releases.

You can manually lock partition statistics using the om_db_stats_pkg.lock_order_ptn_stats procedure. For example, as the OSM order management user:

execute om_db_stats_pkg.lock_order_ptn_stats ('P_000000000000400001');

You should lock statistics on an empty partition after copying statistics into that partition and you should leave statistics locked on that partition when it becomes active.

Configuring a Partition When It Is No Longer the Active Partition

You can manually unlock partition statistics using the om_db_stats_pkg.unlock_order_ptn_stats procedure. For example, as the OSM order management user:

execute om_db_stats_pkg.unlock_order_ptn_stats ('P_000000000000400001');

You should unlock statistics on a partition when it matures (that is, once it is no longer active). This should be done following a switch to a new active partition.

Optimizer Statistics Error Handling Using Datapump

Optimizer statistics management error handling is available for automated copy partition statistics jobs and datapump jobs.

The om_db_stats_pkg.expdp_order_ptn_stats and om_db_stats_pkg.impdp_order_ptn_stats procedures submit datapump jobs to save or load partition statistics to or from the file system.

While it is unlikely that these jobs will fail and become stuck, you can determine if datapump jobs are stuck by running the following commands as a SYSDBA user:

select owner_name, job_name, operation, job_mode, state, attached_sessions
from dba_datapump_jobs
where job_name not like 'BIN$%'
order by owner_name, job_name;

As the OSM order management user, you can remove stuck export jobs using the following:

declare
    dpj number;
begin
    dpj := dbms_datapump.attach('EXPORT_ORDER_PTN_STATS', user);
    dbms_datapump.stop_job(dpj, 1, 0);
end;

You can remove stuck import jobs using the following commands:

declare
    dpj number;
begin
    dpj := dbms_datapump.attach('IMPORT_ORDER_PTN_STATS', user);
    dbms_datapump.stop_job(dpj, 1, 0);
end;

Optimizer Statistics Management Performance Tuning

This section presents various ways to tune optimizer statistics management performance.

Using Parallel Collection for Gathering Statistics

As your OSM database grows, it is important that you gather statistics in parallel. Otherwise the automatic statistics collection might not be able to process all tables and partitions. By default, Oracle Database uses the same number of parallel server processes specified as the Degree of Parallelism attribute of the table in the data dictionary. Because the degree of parallelism is 1 for all OSM tables and indexes, Oracle recommends that you set the DEGREE as a schema preference.

To do this, run the following command as the OSM order management user: 

execute DBMS_STATS.SET_SCHEMA_PREFS(user, 'DEGREE', 'DBMS_STATS.AUTO_DEGREE');

However, if you gather statistics manually while the database is processing a workload (for example, when gathering statistics for high volatility tables), you should temporarily set a low value for DEGREE.

Note that the actual degree of parallelism can be between 1 (serial execution) for small objects to DEFAULT_DEGREE for large objects.

Caution:

Do not change the degree of parallelism attribute of any OSM table or index. This is not supported.

Cursor Invalidations

When statistics are modified by DBMS_STATS, new cursors that are not yet cached in the shared pool use updated statistics to get execution plans. However, existing cursors that are cached in the shared pool cannot update their execution plans. Instead, such cursors are invalidated and new versions, children cursors, are created. This results in execution plans based on the updated statistics. This involves a hard-parse operation that is more expensive than a soft-parse, which simply reuses a cached cursor. For this reason, Oracle Database spreads cursor invalidations over a time period long enough for hard-parses not to cause noticeable spikes in resource usage. This time period is 5 hours by default and it is controlled by the _optimizer_invalidation_period initialization parameter (in seconds).

If your database has performance issues that are caused by bad execution plans because of stale or missing statistics, 5 hours is a long time to wait. Oracle therefore recommends that you decrease the value of _optimizer_invalidation_period. For example, the following command sets _optimizer_invalidation_period to 600 seconds. 

alter system set "_optimizer_invalidation_period"=600 scope=both;

If 10 minutes turns out to be too short to avoid significant spikes caused by parsing in your environment, increase the value accordingly.

Optimizer Statistics Management PL/SQL API Reference

This section describes: setup and tuning, maintenance, advanced, troubleshooting, and recovery procedures.

Setup and Tuning Procedures

This section describes setup and tuning procedures.

om_db_stats_pkg.lock_volatile_order_stats

This procedure locks statistics on volatile order tables. 

procedure lock_volatile_order_stats;

An order table is considered volatile if its volatility level is set to om_const_pkg.v_volatility_high.

om_db_stats_pkg.unlock_volatile_order_stats

This procedure unlocks statistics on volatile order tables. 

procedure unlock_volatile_order_stats;

An order table is considered volatile if its volatility level is set to om_const_pkg.v_volatility_high.

om_db_stats_pkg.set_table_prefs_incremental

This procedure sets the INCREMENTAL statistics preference for partitioned OSM tables that have the specified volatility level. 

procedure set_table_prefs_incremental(
    a_incremental boolean,
    a_volatility number);

Parameters:

  • a_incremental: Specifies whether you want statistics to be gathered incrementally on partitioned OSM tables that have the specified volatility level. When set to true, the PUBLISH preference is also set to true because this is required for incremental statistics collection.

  • a_volatility: Specifies the volatility level of partitioned OSM tables for which the INCREMENTAL statistics preference should be set.

Exception:

  • ORA-20165: Illegal argument: Invalid volatility level.

om_db_stats_pkg.set_table_volatility

This procedure sets the volatility level for an OSM table. The volatility level for OSM tables is configured in OM_$INSTALL$TABLE.

Parameters:

  • a_table_name: Specifies the name of the table on which to set the volatility level.

  • a_volatility: Specifies the volatility level to set. Valid values are om_const_pkg.v_volatility_none, om_const_pkg.v_volatility_low, om_const_pkg.v_volatility_medium, and om_const_pkg.v_volatility_high.

Maintenance Procedures

This section describes maintenance procedures.

om_db_stats_pkg.gather_cartridge_stats

This procedure gathers statistics for cartridge metadata tables.

procedure gather_cartridge_stats;
om_db_stats_pkg.gather_order_stats

This procedure gathers statistics for order tables configured with the specified volatility level. 

procedure gather_order_stats(
    a_volatility number default null,
    a_force boolean default false);

Parameters:

  • a_volatility: The level of volatility of order tables for which statistics should be gathered. Null by default, which means all volatility levels.

  • a_force: Specifies whether you want statistics to be gathered on order tables even if their statistics are locked. The default is false.

Exception:

  • ORA-20165: Illegal argument: Invalid volatility level.

om_db_stats_pkg.gather_volatile_order_stats

This procedure gathers statistics for volatile order tables. 

procedure gather_order_stats(
    a_force boolean default false);

Parameters:

  • a_force: Specifies whether you want statistics to be gathered on volatile order tables even if their statistics are locked. The default is false. An order table is deemed volatile if its volatility level is set to om_const_pkg.v_volatility_high.

om_db_stats_pkg.copy_order_ptn_stats

This procedure copies order partition statistics from the specified source order partition to the specified destination order partition.

procedure copy_order_ptn_stats(
    a_copied out boolean,
    a_dst_partition_name varchar2 default null, 
    a_src_partition_name varchar2 default null);

Parameters:

  • a_copied: Output parameter indicating whether statistics were successfully copied.

  • a_dst_partition_name: Specifies the name of the order partition to which you want to copy statistics. If you do not specify this parameter, the most recently added partition is used.

  • a_src_partition_name: Specifies the name of the order partition from which you want to copy statistics. If not specified, a partition with the most recent valid statistics is used, if available. If no valid partition statistics are available, a_copied is set to false.

Exceptions:

  • ORA-20142: Operation is not allowed: OSM schema is not partitioned.

  • ORA-20165: Illegal argument: Partition does not exist.

  • ORA-20165: Illegal argument: The source partition cannot be the same as the destination partition

  • ORA-20144: Function returned unexpected value. Internal error. Contact support: Cannot find the newest partition.

om_db_stats_pkg.lock_order_ptn_stats

This procedure locks order partition statistics for the specified partition. Statistics of the corresponding partitions of reference partition tables are also locked.

procedure lock_order_ptn_stats(
    a_partition_name varchar2);

Parameters:

  • a_partition_name: Specifies the name of the order partition to lock.

Exceptions:

  • ORA-20165: Illegal argument: Partition does not exist.

om_db_stats_pkg.unlock_order_ptn_stats

This procedure unlocks order partition statistics for the specified partition. Statistics of the corresponding partitions of reference partition tables are also unlocked.

procedure unlock_order_ptn_stats(
    a_partition_name varchar2);

Parameters:

  • a_partition_name: Specifies the name of the order partition to unlock.

Exceptions:

  • ORA-20165: Illegal argument: Partition does not exist.

Advanced Procedures

This section describes advanced procedures.

om_db_stats_pkg.export_order_ptn_stats

This procedure exports order partition statistics from the specified order partition to the specified statistics table. If that table already exists, it is dropped before exporting statistics to the statistics table.

procedure export_order_ptn_stats(
    a_exported out boolean,
    a_src_partition_name varchar2 default null,
    a_stat_table_name varchar2 default c_om_order_stat_table);

Parameters:

  • a_exported: Output parameter indicating whether statistics were successfully exported.

  • a_src_partition_name: Specifies the name of the order partition from which you want to export statistics. If not specified, a partition with the most recent valid statistics is used, if available. If no valid partition statistics are available, a_exported is set to false.

  • a_stat_table_name: Specifies the name of the statistics table to which to export statistics. Defaults to c_om_order_stat_table ('OM_ORDER_STAT_TABLE'). If this statistics table already exists, it is dropped and recreated before exporting statistics from the specified partition; if it is not a statistics table, the table is not dropped and an exception is raised.

Exceptions:

  • ORA-20142: Operation is not allowed: OSM schema is not partitioned.

  • ORA-20165: Illegal argument: Partition does not exist.

  • ORA-20165: Illegal argument: Invalid table name.

  • ORA-20165: Illegal argument: Table is not a statistics table.

om_db_stats_pkg.import_order_ptn_stats

This procedure imports order partition statistics from the specified statistics table to the specified destination order partition.

procedure import_order_ptn_stats(
    a_imported out boolean,
    a_dst_partition_name varchar2 default null, 
    a_stat_table_name varchar2 default c_om_order_stat_table);

Parameters:

  • a_imported: Output parameter indicating whether statistics were successfully imported.

  • a_dst_partition_name: Specifies the name of the order partition to which you want to import statistics. If you do not specify this parameter, the most recently added partition is used.

  • a_stat_table_name: Specifies the name of the statistics table from which to import statistics. The default becomes c_om_order_stat_table ('OM_ORDER_STAT_TABLE').

Exceptions:

  • ORA-20142: Operation is not allowed: OSM schema is not partitioned.

  • ORA-20165: Illegal argument: Partition does not exist.

  • ORA-20165: Illegal argument: Invalid table name.

  • ORA-20165: Illegal argument: Table is not a statistics table.

  • ORA-20144: Function returned unexpected value. Internal error. Contact support: Cannot find the newest partition.

om_db_stats_pkg.expdp_order_ptn_stats

This procedure saves order partition statistics from the specified statistics table to the DATA_PUMP_DIR directory. A .dmp suffix is added to the table name to form the name of the file to which statistics will be saved; for example, OM_ORDER_STAT_TABLE.dmp. If that file already exists, it is deleted before saving statistics to the file system.

procedure expdp_order_ptn_stats(
    a_saved out boolean,
    a_stat_table_name varchar2 default c_om_order_stat_table);

Parameters:

  • a_saved: Output parameter indicating whether statistics were successfully saved.

  • a_stat_table_name: Specifies the name of the statistics table from which to obtain statistics. The default becomes c_om_order_stat_table ('OM_ORDER_STAT_TABLE').

Exceptions:

  • ORA-20165: Illegal argument: Invalid table name.

  • ORA-20165: Illegal argument: Table does not exist.

  • ORA-20165: Illegal argument: Table is not a statistics table.

  • ORA-20142: Operation is not allowed: Failed to save partition statistics to file system.

om_db_stats_pkg.impdp_order_ptn_stats

This procedure loads order partition statistics into the specified statistics table from the DATA_PUMP_DIR directory. A .dmp suffix is added to the table name to form the name of the file from which statistics will be loaded; for example, OM_ORDER_STAT_TABLE.dmp.

procedure impdp_order_ptn_stats(
    a_loaded out boolean,
    a_stat_table_name varchar2 default c_om_order_stat_table);

Note:

If partitioned statistics came from another system, they can be imported only if user names are the same in both the source and destination systems.

Parameters:

  • a_loaded: Output parameter indicating whether statistics were successfully loaded.

  • a_stat_table_name: Specifies the name of the statistics table into which you want to load statistics. The default becomes c_om_order_stat_table ('OM_ORDER_STAT_TABLE'). If this statistics table already exists, it is dropped and recreated before loading statistics from the file system. If it is not a statistics table, the table is not dropped and an exception is raised.

Exceptions:

  • ORA-20165: Illegal argument: Invalid table name.

  • ORA-20165: Illegal argument: File not found in DATA_PUMP_DIR directory.

  • ORA-20165: Illegal argument: Table is not a statistics table.

  • ORA-20142: Operation is not allowed: Failed to load partition statistics from file system.

Troubleshooting Procedures

This section describes the troubleshooting procedures.

om_db_stats_pkg.lstj_copy_order_ptn_stats

This procedure lists active copy_order_ptn_stats jobs. The output includes a job ID that can be used to remove the job using remj_copy_order_ptn_stats. 

procedure lstj_copy_order_ptn_stats;
om_db_stats_pkg.get_order_ptn_stats

This procedure lists statistics for table partitions that match the given filter criteria.

procedure get_order_ptn_stats;
om_db_stats_pkg.list_order_ptn_stats

This procedure outputs statistics for table partitions that match the given filter criteria to dbms_output.

procedure list_order_ptn_stats;
om_db_stats_pkg.check_order_ptn_stats

This procedure validates the statistics on the schema to check for any errors. There are two versions of this procedure: one outputs it to dbms_output and the other returns it as a collection of strings (for external use).

procedure check_order_ptn_stats;

This procedure looks for the following conditions and creates the appropriate level message (in brackets) if the condition is violated:

  • Missing statistics on Order Data Tables (CRITICAL)

  • Empty or active partitions with unlocked statistics (CRITICAL)

  • Mature partitions with locked statistics (WARNING)

  • Statistics are locked with 0 rows on tables that should never have 0 rows when a partition is used to store orders (e.g., OM_ORDER_HEADER) (CRITICAL)

  • Statistics are locked with a small number of rows on tables that should have a large number of rows in order to be representative of a partition used to store a large number of orders (e.g., OM_ORDER_HEADER) (CRITICAL)

  • Incremental statistics do not work with locked partition statistics (CRITICAL)

  • Incremental statistics are not enabled on low or medium volatility tables (MAJOR)

  • Incremental statistics are enabled on high volatility tables (MAJOR)

  • Statistics are not locked on high-volatility tables (CRITICAL)

  • Statistics are locked on low or medium volatility tables (WARNING)

  • Attempting to check for partition statistics problems while statistics are being collected (WARNING)

Recovery Procedures

This section describes the recovery procedures.

om_db_stats_pkg.remj_copy_order_ptn_stats

This procedure removes the specified copy_order_ptn_stats job. 

procedure remj_copy_order_ptn_stats (
    a_job_id number);

Parameter:

  • a_job_id: Specifies the ID of the job to remove.

Exception:

  • ORA-20155: Job does not exist.