This chapter describes how to gather optimizer statistics for the Oracle Communications Order and Service Management (OSM) product.
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, and the documentation for best practices for gathering optimizer statistics:
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-optimizer-stats-04042012-1577139.pdf
This section discusses methods of gathering optimizer statistics for OSM.
The automatic statistics collection job is enabled by default and gathers statistics during a predefined maintenance window for all database objects that are missing statistics or have stale statistics.
Oracle recommends that you:
Leave the automatic statistics collection enabled. If you have a valid reason to disable it, it should be enabled for at least the dictionary tables.
Schedule maintenance operations, which would render statistics stale, before the automatic statistics collection, such as purging partitions, and deploying, undeploying and purging cartridges. In this way, statistics will be updated by the automatic collection.
For more information about automated optimizer statistics collection, see the following knowledge articles, available from the Oracle support website:
FAQ: Automatic Statistics Collection Job (10g and 11g) [Doc ID 1233203.1]
Best Practices for Automatic Statistics Collection [Doc ID 377152.1]
How to Manually Execute the Optimizer Statistics Auto Task in Oracle 11g [Doc ID 731935.1]
How to Extend Maintenance Windows For GATHER_STATS_JOB for More Than 8 Hours [Doc ID 368475.1]
The procedures provided by the om_db_stats_pkg package, as well as native Oracle database statistics procedures, can all be executed online. However, when gathering statistics during peak hours you should temporarily lower the value of the DEGREE preference as described in "Parallel Execution".
If the automatic statistics gathering job is enabled, statistics of cartridge metadata tables are kept up-to-date and should not cause performance issues. However, if you perform extensive cartridge management operations, such as deploy, undeploy, purge, and so on, after the automatic statistics collection and before normal business hours, it is recommended that you gather cartridge metadata statistics manually before you start processing orders. To do so, use the following command:
execute om_db_stats_pkg.gather_cartridge_stats;
A volatile order table is one where the volume of data changes over time because the data is deleted automatically after it is processed.
The following order tables are always highly volatile because they are polled periodically and rows are very short-lived (seconds). These tables may have thousands of rows at peak workload but have little data or no data overnight when the automatic statistics collection job runs.
OM_JMS_EVENT
OM_JMS_THREAD
OM_ORDER_STATE_PENDING
OM_ORDER_STATE_EVENT_PENDING
You should not enable incremental statistics on highly volatile tables. However, Oracle recommends that you lock statistics for these tables.
execute om_db_stats_pkg.lock_volatile_order_stats;
OM_ORDER_FLOW_COORDINATOR and OM_COORD_NODE_INSTANCE have both partitions that retain order data and volatile partitions from which data is deleted after it is processed. These tables are configured with a medium level of volatility.
Oracle recommends that you enable incremental statistics for medium volatility tables.
execute om_db_stats_pkg.set_table_prefs_incremental(a_incremental => true,
a_volatility => om_const_pkg.v_volatility_medium);
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.
execute om_db_stats_pkg.set_table_prefs_incremental(a_incremental => true,
a_volatility => om_const_pkg.v_volatility_low);
Data from the following tables is deleted automatically after it is processed.
OM_ORDER_FLOW
OM_AUTOMATION_CTX
OM_AUTOMATION_CORRELATION
OM_ORDER_POS_INPUT
OM_UNDO_BRANCH_ROOT
OM_ORCH_DEPENDENCY_PENDING
The volatility of these tables varies depending on the solution, as discussed in the scenarios in this section.
If most of your orders complete in seconds, it is recommended that you manage these tables in the same manner as the highly volatile tables. By default, OSM is configured so that the tables in this section are set to a medium level of volatility. You can modify the configuration of these tables to a high level of volatility using the om_db_stats_pkg.set_table_volatility procedure.
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);
If the life cycle of your orders is mostly days or weeks, these tables contain data at the end of the day, although likely not as much data as during the day. In this case, you can manage these tables like low volatility order tables. By default, OSM is configured so that these tables are set to a medium level of volatility. You can modify the configuration of the tables above to a low level of volatility using the om_db_stats_pkg.set_table_volatility procedure.
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);
If the life cycle of your orders is mixed, with both short-lived and long-lived orders, the recent partitions where new orders are created have a mixed load of orders and experience both inserts and deletes. Whereas older partitions are less volatile because they have long-lived orders (or no orders at all) and few inserts. In this case, you can manage these tables like medium volatility order tables. By default, OSM is already configured so that these tables are set to a medium level of volatility.
It is recommended that you gather statistics for high volatility tables when the workload is representative, preferably at the peak, as long as the database has spare resources. You could schedule a job to re-gather statistics periodically on these tables, for example, once a day:
execute DBMS_STATS.SET_SCHEMA_PREFS(user, 'DEGREE', 2);
execute om_db_stats_pkg.gather_order_stats(a_force => true,
a_volatility => om_const_pkg.v_volatility_high);
execute DBMS_STATS.SET_SCHEMA_PREFS(user, 'DEGREE', 'DBMS_STATS.AUTO_DEGREE');
It is recommended that you gather statistics for medium volatility tables when the workload is representative, preferably near a peak as long as the database has spare resources. You could schedule a job to re-gather statistics periodically on these tables, for example, once every few days:
execute DBMS_STATS.SET_SCHEMA_PREFS(user, 'DEGREE', 2);
execute om_db_stats_pkg.gather_order_stats(a_force => true,
a_volatility => om_const_pkg.v_volatility_medium);
execute DBMS_STATS.SET_SCHEMA_PREFS(user, 'DEGREE', 'DBMS_STATS.AUTO_DEGREE');
A large volume of statistics, histograms, and incremental statistics synopses have a significant performance impact on partition purge and drop operations. When you drop partitions, statistics are deleted. When you purge partitions, the order data of the partitioned tables are moved into empty exchange tables using ALTER TABLE … EXCHANGE PARTITION operations. The exchange operation also exchanges statistics, including histograms and incremental statistics synopses, if possible. This is expensive because it involves a lot of inserts, deletes, and updates. Therefore, beginning with 7.2.0.10, the om_part_maintain.purge_xchg_prg_tables procedure deletes exchange table statistics automatically.
If a partition is exchanged with a non-partitioned table, synopses cannot be exchanged. In Oracle Database 12c, they are invalidated. However, in Oracle Database 11g the synopses are deleted immediately, unless you install patch 18238463: DML WAITS ON EXCHANGE PARTITION (LIB CACHE LOCK - WRI$_OPTSTAT_SYNOPSIS$). Install this patch on Oracle Database 11.2.0.3 or 11.2.0.4 to ensure the synopses are marked STALE (or invalidated) instead of being deleted. Oracle strongly recommends applying this patch. The patch is disabled by default; enable the patch by entering the following:
alter system set "_fix_control"='18238463:1' scope=both sid='*';
Oracle also recommends that you disable dynamic sampling before you execute OM_PART_MAINTAIN.SETUP_XCHG_TABLES to create exchange tables for purging partitions. This should be done for the session only:
alter session set OPTIMIZER_DYNAMIC_SAMPLING=0;
If the automatic statistics collection is enabled, you do not have to do anything additional to gather statistics on the other OSM tables.
The automatic statistics gathering job does not gather fixed object statistics, and dynamic sampling is not automatically used for SQL statements involving X$ tables when optimizer statistics are missing. If fixed object statistics are missing, the optimizer uses predefined default values that may not be representative and could lead to suboptimal execution plans. RMAN, Data Guard, Streams, and Grid Control make heavy use of the fixed tables through the DBA/V$ views and so often experience the worst performance issues. Another common symptom is extreme TEMP space usage, driven by poor plans against the fixed tables.
Oracle recommends that:
You gather statistics on fixed objects using the DBMS_STATS.GATHER_FIXED_OBJECTS_STATS procedure when there is a representative workload on the system.
After you install OSM, or perform a major OSM upgrade, you re-gather fixed object statistics when there is a representative workload.
In general, you re-gather fixed object statistics if you do a major database or application upgrade, implement a new module, or make changes to the database configuration.
For more information, see the following knowledge articles, available from the Oracle support website:
How to gather statistics on SYS objects and 'Fixed' Objects [Doc ID 457926.1]
GATHER_FIXED_OBJECTS_STATS Considerations [Doc ID 798257.1]
System statistics enable the optimizer to more accurately cost 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. System statistics are enabled by default, and are automatically initialized with default values that are representative for most systems. Therefore system statistics are not automatically collected as part of the automatic statistics gathering job.
If you choose to gather system statistics, use the DBMS_STATS.GATHER_SYSTEM_STATS during a representative workload time window, ideally at peak times. You may want to consider gathering system statistics under the following circumstances:
If you changed the CPU speed or I/O subsystem.
If you want to record the actual system performance for a representative OSM workload, for example, when you go live or after a significant increase in order volume.
If you gather system statistics, review the new statistics for correctness and compare with previous values. For more information, see the following knowledge articles, available from the Oracle support website:
How to Collect and Display System Statistics (CPU and I/O) for CBO use [Doc ID 149560.1]
Using Actual System Statistics (Collected CPU and I/O information) [Doc ID 470316.1]
System Statistics: Scaling the System to Improve CBO optimizer [Doc ID 153761.1]
System Statistics: How to gather system stats for a particular batch of work [Doc ID 427939.1]
Populating statistics on new partitions improves performance by avoiding the issues that can arise when queries are made on newly active partitions for which statistics have yet to be collected.
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. For example:
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;
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;
You can avoid manually copying recent and valid statistics to the most recently created partition by setting the db_stats_auto_copy_to_new_partition om_parameter to ON_ADD. By default, this parameter is set to OFF. For more information, see "Configuration Parameter for Managing Optimizer Statistics".
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.
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. Files are saved to the DATA_PUMP_DIR directory. As sysdba, you can find out the location of that directory using:
select directory_name, directory_path
from dba_directories
where directory_name='DATA_PUMP_DIR';
After you save partition statistics to a file, that file can be transferred to the DATA_PUMP_DIR directory on another system (for example, using FTP) 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 lab system.
The following configuration parameter in the om_parameter table affects optimizer statistics maintenance operations:
o db_stats_auto_copy_to_new_partition
This parameter specifies the policy for automatically copying statistics to new order partitions. Possible values are:
OFF (default): Statistics are not automatically copied to new order partitions.
ON_ADD: Valid and recent statistics are automatically copied to new order partitions when they are added. This happens when a new partition is created manually or automatically.
You can change the default OFF setting to ON_ADD with the following SQL statement:
update om_parameter set value = 'ON_ADD' where mnemonic = 'db_stats_auto_copy_to_new_partition'; commit;
Optimizer statistics management error handling is available for automated copy partition statistics jobs and datapump jobs.
OSM submits asynchronous database jobs to automatically copy partition statistics. While it is unlikely that these jobs will fail and become stuck, you can use the om_db_stats_pkg.lstj_copy_order_ptn_stats to determine if that is the case. You can then use om_db_stats_pkg.remj_copy_order_ptn_stats to remove individual 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, as sysdba, you can determine if datapump jobs are stuck using the following:
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 a regular 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:
declare
dpj number;
begin
dpj := dbms_datapump.attach('IMPORT_ORDER_PTN_STATS', user);
dbms_datapump.stop_job(dpj, 1, 0);
end;
This section presents various ways to tune optimizer statistics management performance.
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. You can configure the DEGREE as a global or schema preference using one of the DBMS_STATS.SET_*_PREFS procedures. DEGREE controls the number of parallel server processes that are used to gather statistics on each table partition and each non-partitioned table.
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:
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, 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 (PARALLEL_THREADS_PER_CPU X CPU_COUNT) for large objects.
WARNING:
Do not change the degree of parallelism attribute of any OSM table or index. This is not supported.
Oracle Database 11.2.0.2 introduced inter-object parallelism when gathering statistics. This is controlled by the global statistics gathering preference CONCURRENT. Gathering statistics on multiple tables, partitions, and subpartitions concurrently allows Oracle Database to fully utilize a multi-processor environment.
Because this is a new feature, Oracle recommends that you first test it in a non-production environment. If you have any issues with the concurrent collection, contact Oracle Database support.
Gathering statistics on partitioned tables consists of gathering statistics at both the table level (global statistics) and (sub)partition level. If you have a high volume of order data, the automatic statistics collection will likely be unable to gather all statistics even if it is done in parallel. In this case, Oracle recommends that you enable incremental statistics for order tables that retain order data, as discussed in the sections about "Low Volatility Order Tables" and "Medium Volatility Order Tables".
If the INCREMENTAL preference for a partitioned table is set to TRUE, Oracle Database will accurately derive all global level statistics by scanning only those partitions that have been added or modified, and not the entire table. Incremental global statistics works by storing a synopsis for each partition in the table. Aggregating the partition level statistics and the synopses from each partition accurately generates global level statistics, thus eliminating the need to scan the entire table.
Set the INCREMENTAL_STALENESS statistics preference to control how the database determines whether the statistics on a partition or subpartition are stale. This preference takes the following values:
USE_STALE_PERCENT: A partition or subpartition are not considered stale if DML changes are less than the STALE_PERCENT preference specified for the table. The default value of STALE_PERCENT is 10, which means that if DML causes more than 10% of row changes, then the table is considered stale.
USE_LOCKED_STATS: Locked partition or subpartition statistics are not considered stale, regardless of DML changes.
NULL (default): A partition or subpartition is considered stale if it has any DML changes. This behavior is identical to the Oracle Database 11g behavior.
You can enable incremental statistics by executing the om_db_stats_pkg.set_table_prefs_incremental procedure with the a_incremental argument set to true. This sets the INCREMENTAL and PUBLISH preferences to TRUE. For example:
execute om_db_stats_pkg.set_table_prefs_incremental(a_incremental => true, a_volatility => om_const_pkg.v_volatility_low);
To disable incremental statistics run om_db_stats_pkg.set_table_prefs_incremental with a_incremental set to false.
Oracle recommends that you set INCREMENTAL_STALENESS statistics for order tables that retain order data using the following statement:
begin
for r in (select table_name
from user_part_tables
where table_name not like 'XCHG_OM_%'
and table_name not in(
'OM_JMS_EVENT',
'OM_JMS_THREAD',
'OM_ORDER_STATE_PENDING',
'OM_ORDER_STATE_EVENT_PENDING',
'OM_ORDER_FLOW',
'OM_AUTOMATION_CTX',
'OM_AUTOMATION_CORRELATION',
'OM_ORDER_POS_INPUT',
'OM_UNDO_BRANCH_ROOT',
'OM_ORCH_DEPENDENCY_PENDING',
'OM_ORDER_FLOW_COORDINATOR',
'OM_COORD_NODE_INSTANCE')
)
loop
dbms_stats.set_table_prefs(user, r.table_name, INCREMENTAL_STALENESS, 'USE_STALE_PERCENT, USE_LOCKED_STATS');
end loop;
end;
For more information, see the knowledge article Collect Incremental Statistics For a Large Partitioned Table in 10g and in 11g [Doc ID 1319225.1], available from the Oracle support website.
When statistics are modified by DBMS_STATS, new cursors that are not yet cached in the shared pool use them 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, which get 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. Therefore, 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;
If 10 minutes turns out to be too short to avoid significant spikes caused by parsing in your environment, increase the value accordingly.
For more information, see the knowledge article Rolling Cursor Invalidations with DBMS_STATS.AUTO_INVALIDATE [Doc ID 557661.1], available from the Oracle support website.
If statistics are missing or stale, the optimizer uses dynamic sampling to improve execution plans. This is controlled by the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter. The default setting is 2, which means that the optimizer uses dynamic statistics if at least one table in the statement has no statistics.
Setting OPTIMIZER_DYNAMIC_SAMPLING to a value greater than 2 is not recommended for OSM and OLTP systems in general because it could increase parse times.
The METHOD_OPT parameter controls column statistics collection and histogram creation. While you can set it as a preference, Oracle strongly recommends that you leave METHOD_OPT at its default setting: FOR ALL COLUMNS SIZE AUTO. Oracle Database monitors the use of columns in queries and creates histograms for the columns that need it:
Setting METHOD_OPT to FOR ALL COLUMNS SIZE 254 causes Oracle Database to gather a histogram on every column. This unnecessarily extends the elapsed time and the system resources needed for statistics gathering, increases the amount of space required to store the statistics, and significantly increases the time it takes to purge partitions.
You should also refrain from setting METHOD_OPT to FOR ALL INDEX COLUMNS SIZE 254 because this setting causes Oracle Database to gather histograms on every column used in an index, which might waste system resources. This setting also has an undesirable side-effect of preventing Oracle Database from collecting basic column statistics for non-index columns.
Note that histograms with more than 255 distinct values are more likely to be inaccurate as the NDV increases because cardinality estimations become more inaccurate as more are added.
This section describes: setup and tuning, maintenance, advanced, troubleshooting, and recovery procedures.
This section describes setup and tuning procedures.
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.
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.
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.
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.
This section describes maintenance procedures.
This procedure gathers statistics for cartridge metadata tables.
procedure gather_cartridge_stats;
This procedure gathers statistics for order tables.
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.
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.
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.
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.
This section describes advanced procedures.
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: Table is not a statistics table.
ORA-20142: Operation is not allowed: Failed to save partition statistics to file system.
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; the table is not dropped and an exception is raised if it is not a statistics table.
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.