Query Optimization in Oracle E-Business Suite

Oracle E-Business Suite and Query Optimization

Oracle E-Business Suite Release 12 uses cost–based optimization in order to choose the most efficient execution plan for SQL statements. Using this approach, the optimizer determines the most optimal execution plan by costing available access paths and factoring information based on statistics for the schema objects accessed by the SQL statement.

Oracle E-Business Suite requires several database initialization parameters to be set correctly in order to ensure optimal performance. Refer to My Oracle Support Knowledge Document 396009.1, and ensure that you have configured the parameters according to this note.

For the query optimizer to produce an optimal execution plan, the statistics in the data dictionary should accurately reflect the volume and data distribution of the tables and indexes. To this end, database statistics should be refreshed periodically. However, that does not necessarily imply that you should gather statistics frequently. Systems that are close to going live typically experience inserts of a large amount of data, as data from legacy systems is migrated. In that scenario, the statistics would probably need to be refreshed quite frequently (for instance, after each major load), as large loads could change the data distribution significantly. Once the system reaches steady state, the frequency of statistics collection at the schema/database level should be reduced to something like once a month. However, statistics on some volatile tables can be gathered as frequently as required.

Oracle E-Business Suite provides a set of procedures in the FND_STATS package to facilitate collection of these statistics. FND_STATS uses the DBMS_STATS package to gather statistics.

Warning: You should not run DBMS_STATS directly.

FND_STATS.GATHER_SCHEMA_STATS uses a parameter called OPTIONS. If set to GATHER AUTO, this option allows FND_STATS to determine automatically the tables for which statistics should be gathered, based on the change threshold. The Modifications Threshold can be adjusted by the user by passing a value for modpercent, which by default is equal to 10. GATHER AUTO uses a database feature called Table Monitoring, which needs to be enabled for all the tables. A procedure called ENABLE_SCHEMA_MONITORING has been provided to enable monitoring on all tables for a given schema or all Applications schemas.

Gathering Statistics for the CBO

Oracle E-Business Suite provides concurrent programs that use the package FND_STATS to gather statistics for your applications database objects. For information on DBMS_STATS, see Oracle Supplied PL/SQL Packages Manual.

The following concurrent programs are available for collecting and maintaining statistics:

Gather Table Statistics

The Gather Table Statistics program gathers the table statistics for the specified table. This program can optionally backup the existing statistics in the FND_STATTAB table before gathering new statistics. If the value of backup_flag is BACKUP, then FND_STATS exports the old statistics using dbms_stats.export_table_stats before gathering the new statistics. The exported data is stored in FND_STATTAB. If the value of backup_flag is anything other than BACKUP then the old table statistics are not saved. This program also gathers index statistics for the table by default. For a detailed description of the procedure used by this concurrent program, see: GATHER_TABLE_STATS Procedure.

Variable Description
Owner Name The owner of the table.
Table Name The name of the table.
Estimate Percent The sampling percentage. If left blank, a default value of 10 is used. The valid range is from 0 to 100.
Degree The degree of parallelism to be used for gathering statistics. If a Degree is not provided, it defaults to the minimum of parallel_max_servers and cpu_count.
Partition Name The name of the partition.
Backup Flag The backup flag indicates whether to backup statistics. Set this flag to "BACKUP" to back up your statistics.
Granularity The granularity of statistics to collect (only relevant for tables that are partitioned). Valid values are:
  • DEFAULT - Gather global and partition–level statistics.

  • SUBPARTITION - Gather subpartition–level statistics.

  • PARTITION - Gather partition–level statistics.

  • GLOBAL - Gather global statistics.

  • ALL - Gather all (subpartition, partition, and global) statistics.

History Mode This parameter controls the amount of history records that are created. Valid modes are LASTRUN, FULL and NONE. The default is LASTRUN. For an explanation of the different modes, please refer to the GATHER_TABLE_STATS Procedure
Invalidate Dependent Cursors This flag indicates whether cursors dependent on the table being analyzed should be invalidated or not. This parameter is ignored if you are running a database prior to Oracle 9i Release 2 (9.2.x).

Backup Table Statistics

This concurrent program backs up the current statistics of the given table into the FND_STATTAB table. This program also backs up the related index and column statistics by default.

An identifier, commonly referred to as STATID, can be associated with the backup up statistics. This STATID allows you to restore a particular version of the statistics using the Restore Table Statistics concurrent program. Statistics for the same object can be backed up with different STATIDs. You can even backup different versions of the statistics for the same object by assigning different STATIDs.

For a detailed description of the procedure used by this concurrent

program, see: BACKUP_TABLE_STATS Procedure.

Variable Description
Schema Name The name of the schema. The value ALL means all Oracle E-Business Suite schemas.
Table Name The name of the table.
Statistics ID An optional identifier to associate with these statistics within FND_STATTAB. The default STATID is BACKUP.
Partition Name Name of the table partition. If the table is partitioned and if the partition name is NULL, then global and partition table statistics are exported.

Restore Table Statistics

This concurrent program allows you to restore the previously backed up table statistics for a given statistics identifier, commonly referred to as the STATID.

All index and column statistics associated with the specified table are restored as well.

For a detailed description of the procedure used by this concurrent

program, see: RESTORE_TABLE_STATS Procedure

Variable Description
Schema Name The name of the schema. The value ALL means all Oracle E-Business Suite schemas.
Table Name The name of the table.
Statistics ID An optional identifier to associate with these statistics within FND_STATTAB. The default STATID is BACKUP.
Partition Name Name of the table partition. If the table is partitioned and if the partition name is NULL, then global and partition table statistics are imported.

Gather Schema Statistics

This concurrent program gathers the specified schema level statistics.

Before gathering the statistics, this program can also create a backup of the current statistics, depending on the value of the Backup Flag. If for some reason, the earlier statistics need to be restored, that can be done using the Restore Schema Statistics concurrent program. The STATID used for this backup is NULL.

This program also creates histograms on the columns seeded in the FND_HISTOGRAM_COLS table.

For a detailed description of the procedure used by this concurrent program, see information on the GATHER_SCHEMA_STATS procedure.

Variable Description
Schema Name Schema for which statistics are to be gathered. Specify ALL for all Oracle E-Business Suite schemas (all schemas that have an entry in the FND_PRODUCT_INSTALLATIONS table).
Percent The sampling percentage. If left blank, the default value of 10 is used. The valid range is from 0 to 100.
Degree The degree of parallelism to be used for gathering statistics. If a Degree is not provided, it defaults to the minimum of parallel_max_servers and cpu_count.
Backup Flag The backup flag indicates whether to backup statistics. Set this flag to BACKUP if you wish to back up the current statistics into the FND_STATTAB table. If NOBACKUP is used, then the GATHER_SCHEMA_STATS procedure will not backup the current statistics. This way the GATHER_SCHEMA_STATS procedure will run faster.
Restart Request ID In the case where the Gather Schema Statistics run fails due to whatever reasons, the concurrent request can be re-submitted and it will pick up where the failed run left off, if you provide the concurrent request_id of the failed run.
History Mode This parameter controls the amount of history records that are created. The history records, stored in FND_STATS_HIST can be queried to find out when stats were gathered on a particular object and the amount of time it took to gather statistics on that object.
  • Last Run - History records for each object are maintained only for the last gather statistics run. Each subsequent run will overwrite the previous history record for the object. This is the default behavior.

  • Full - This mode does not overwrite any history information. History records are created for each run and are identified by the Request ID. If a Request ID is not provided, one is generated automatically. If this mode is used, the “Purge FND_STATS History Records” concurrent program should be run periodically to purge the FND_STATS_HIST table.

  • None - This mode does not generate any history information. If this mode is used, the run cannot be restarted.

Gather Options This parameter specifies how objects are selected for statistics gathering.
  • GATHER : All tables and indexes of the schema schemaname are selected for stats gathering. This is the default.

  • GATHER AUTO : Tables of the schema schemaname for which the percentage of modifications has exceeded modpercent are selected for statistics gathering. Indexes of these tables are selected by default. Table monitoring needs to be enabled before using this option.

  • GATHER EMPTY : Statistics are gathered only for tables and indexes that are missing statistics.

  • LIST AUTO : This option does not gather statistics. It only provides a listing of all the tables that will be selected for statistic gathering, if the GATHER AUTO option is used.

  • LIST EMPTY : This option does not gather statistics. It only provides a listing of all the tables that will be selected for statistics gathering, if the GATHER EMPTY option is used.

Modifications Threshold Applicable only to GATHER AUTO and LIST AUTO options. This parameter specifies the percentage of modifications (with respect to the total rows) that have to take place on a table before it can be picked up for AUTO statistics gathering.
Invalidate Dependent Cursors This flag indicates whether cursors dependent on the table being analyzed should be invalidated or not. By default, dependent cursors are invalidated. This parameter is ignored if you are running a database prior to Oracle 9i Release 2 (9.2.x).

Gather Column Statistics

This concurrent program should be used for gathering the Column Statistics, i.e. creating a histogram on a given column.

The procedure takes a backup into the FND_STATTAB table before gathering the statistics.

For a detailed description of the procedure used by this concurrent

program, see: GATHER_COLUMN_STATS Procedure

Variable Description
Table Owner The owner of the table.
Table Name The name of the table.
Column Name The name of the column.
Estimate Percent The sampling percentage. If left blank, a default value of 10 is used. The valid range is from 0 to 100.
Parallel Degree The degree of parallelism to be used for gathering statistics. If a Degree is not provided, it defaults to the minimum of parallel_max_servers and cpu_count.
Bucket Size The number histogram buckets.
Backup Flag The backup flag indicates whether to backup statistics. Set this flag to BACKUP if you wish to back up the current column statistics into the FND_STATTAB table. If left blank, it defaults to NOBACKUP.

Gather All Column Statistics

This concurrent program is obsolete.

Purge FND_STATS History Records

This program can be run to purge the history records from the FND_STATS_HIST table. This program should be scheduled to run periodically if statistics are being gathered with History Mode as FULL. You do not need to run this program if you gather statistics with History Mode as NONE or the default – LASTRUN.

Variable Description
Purge Mode The Purge Mode can take one of the two values: DATE or REQUEST. If the mode chosen is DATE, history records are purged based on the date range, otherwise, if it is REQUEST, records are purged based on the Request ID.
From Value Start Date or Request ID
To Value End Date or Request ID.

FND_STATS Package

The FND_STATS package provides procedures for gathering statistics for Oracle E-Business Suite database objects. It also provides procedures for backing up the current statistics into the table - FND_STATTAB, and restoring them back if desired. This package also allows users to specify the degree of parallelism. That helps speed up statistics gathering for large objects. FND_STATS can also maintain a history of its actions in a table called FND_STATS_HIST. The data in this table is used to provide restart ability, and can also be queried to find out the time taken to gather statistics on each object.

FND_STATS relies on the Oracle-supplied package DBMS_STATS to perform the actual statistics gathering. For more information on DBMS_STATS, refer to the Oracle database Tuning and Supplied Packages Reference manuals.

CREATE_STAT_TABLE Procedure

This procedure creates the table that is required for backing up the statistics.

There are two versions of this procedure. The first one does not need any arguments and creates the table with the default name - FND_STATTAB in the schema corresponding to the FND product. The second version allows you to provide the schema name, table name and the tablespace for the statistics table.

Syntax

FND_STATS.CREATE_STAT_TABLE ;

FND_STATS.CREATE_STAT_TABLE (
   schemaname  IN  VARCHAR2, 
   tabname     IN  VARCHAR2, 
   tblspcname  IN  VARCHAR2);

Parameters

Variable Description
schemaname Name of the schema.
tabname Name of the table.
tblspcname Tablespace in which to create the statistics tables. If none is specified, then the tables are created in the user's default tablespace.

BACKUP_TABLE_STATS

This procedure backs up the statistics for the given table in the FND_STATTAB table. Setting cascade to TRUE results in all index and column statistics associated with the specified table to be stored as well. An identifier, commonly referred to as STATID, can be associated with the backup up statistics. This STATID allows you to restore a particular version of the statistics using the RESTORE_TABLE_STATS procedure.

Syntax

FND_STATS.BACKUP_TABLE_STATS (
   schemaname  VARCHAR2, 
   tabname     VARCHAR2, 
   statid      VARCHAR2 DEFAULT 'BACKUP',
   partname    VARCHAR2 DEFAULT NULL, 
   cascade     BOOLEAN  DEFAULT TRUE);

Parameters

Variable Description
schemaname Name of the schema.
tabname Name of the table.
statid Optional identifier to associate with these statistics within FND_STATTAB.
partname Name of the table partition. If the table is partitioned and if partname is NULL, then global and partition table statistics are exported.
cascade If TRUE, then column and index statistics for this table are also exported.

BACKUP_SCHEMA_STATS Procedure

This procedure can be used to backup the statistics for an entire schema. The statistics are backed up into the FND_STATTAB table. A different version can be stored by specifying a different statid. An identifier, commonly referred to as STATID, can be associated with the backup up statistics. This STATID allows you to restore a particular version of the statistics using the RESTORE_SCHEMA_STATS procedure.

Syntax

FND_STATS.BACKUP_TABLE_STATS (
   schemaname  VARCHAR2, 
   statid      VARCHAR2 DEFAULT NULL);

Parameters

Variable Description
schemaname Name of the schema. ALL means all Oracle E-Business Suite schemas.
statid Optional identifier to associate with these statistics within FND_STATTAB.

RESTORE_SCHEMA_STATS Procedure

This procedure restores statistics for the given schema, that were previously backed up in the FND_STATTAB table, into the dictionary. Statid can be provided to distinguish between different sets of statistics for the same object.

Syntax

FND_STATS.RESTORE_SCHEMA_STATS (
   schemaname  VARCHAR2, 
   statid VARCHAR2 DEFAULT NULL
   );

Parameters

Variable Description
schemaname Name of the schema. ALL means all Oracle E-Business Suite schemas.
statid Optional identifier to associate with these statistics within FND_STATTAB.

RESTORE_TABLE_STATS Procedure

This procedure restores statistics for the given table from the FND_STATTAB table for the given statid (optional) and transfers them back to the dictionary. Setting cascade to TRUE results in all index and column statistics associated with the specified table being imported also.

Syntax

FND_STATS.RESTORE_TABLE_STATS (
   ownname  VARCHAR2, 
   tabname  VARCHAR2, 
   statid   VARCHAR2 DEFAULT NULL,
   partname VARCHAR2 DEFAULT NULL,    
   cascade  BOOLEAN  DEFAULT TRUE,
   );

Parameters

Variable Description
ownname Name of the schema.
tabname Name of the table.
statid Optional identifier to associate with these statistics within FND_STATTAB.
partname Name of the table partition. If the table is partitioned and if partname is NULL, then global and partition table statistics are exported.
cascade If TRUE, then column and index statistics for this table are also exported.

RESTORE_COLUMN_STATS Procedure

This procedure restores statistics for the given column from the FND_STATTAB table for the given statid (optional) and transfers them back to the dictionary. There are two versions of this procedure. One first one requires the table owner, table name and column name to be supplied. The second version restores the statistics for all the columns seeded in the FND_HISTOGRAM_COLS table.

Syntax

FND_STATS.RESTORE_COLUMN_STATS (
   ownname  VARCHAR2, 
   tabname  VARCHAR2, 
   colname  VARCHAR2,
   partname VARCHAR2 DEFAULT NULL,    
   statid   VARCHAR2 DEFAULT NULL
   );

FND_STATS.RESTORE_COLUMN_STATS (
   statid   VARCHAR2 DEFAULT NULL
   );

Parameters

Variable Description
ownname Name of the schema.
tabname Name of the table.
colname Name of the column. Optional identifier to associate with these statistics within FND_STATTAB.
partname Name of the table partition. If the table is partitioned and if partname is NULL, then global and partition table statistics are exported.
statid Optional identifier to associate with these statistics within FND_STATTAB.

ENABLE_SCHEMA_MONITORING Procedure

This procedure should be used for enabling the Monitoring option for all tables in the specified schema. Monitoring option should be enabled before using the GATHER AUTO or LIST AUTO option of GATHER_SCHEMA_STATS. If the value of the schemaname argument is ALL, then the Monitoring option is enabled for all tables that belong to all schemas registered in Oracle E-Business Suite.

Syntax

FND_STATS.ENABLE_SCHEMA_MONITORING (
    schemaname	 VARCHAR2 DEFAULT ‘ALL’);

Parameters

Variable Description
schemaname Name of the schema for which Monitoring should be enabled.

DISABLE_SCHEMA_MONITORING Procedure

This procedure should be used for disabling the Monitoring option for all tables in the specified schema. If the value of the schemaname argument is ALL, then the Monitoring option is disabled for all tables that belong to all schemas registered in Oracle E-Business Suite.

Syntax

FND_STATS.DISABLE_SCHEMA_MONITORING (
    schemaname	 VARCHAR2 DEFAULT ‘ALL’);

Parameters

Variable Description
schemaname Name of the schema for which Monitoring should be disabled.

GATHER_SCHEMA_STATS Procedure

This procedure gathers statistics for all objects in a schema. Statistics are gathered with the granularity of DEFAULT. This procedure is also available through the concurrent program ”Gather Schema Statistics.” If this procedure fails at any time during operation, supplying the request ID for the request that failed can restart it. The request ID can be captured when the program is started from concurrent manager or can be queried from the FND_STATS_HIST table.

GATHER_SCHEMA_STATS cannot be executed directly in sqlplus because of an OUT parameter. The procedure GATHER_SCHEMA_STATISTICS has been provided for gathering schema statistics from the sqlplus prompt.

Syntax

FND_STATS.GATHER_SCHEMA_STATS (
    schemaname        VARCHAR2,
    estimate_percent  NUMBER DEFAULT NULL,
    degree            NUMBER DEFAULT NULL,
    internal_flag     NUMBER DEFAULT NULL,
    Errors 	OUT Error_Out,
    request_id   NUMBER default null,
    hmode        VARCHAR2 default 'LASTRUN',
    options in   VARCHAR2 default 'GATHER',
    modpercent   NUMBER default 10,
    invalidate   VARCHAR2 default 'Y'
);

FND_STATS.GATHER_SCHEMA_STATISTICS (
    schemaname        VARCHAR2,
    estimate_percent  NUMBER DEFAULT NULL,
    degree            NUMBER DEFAULT NULL,
    internal_flag     NUMBER DEFAULT NULL,
    request_id        NUMBER DEFAULT NULL,
    hmode             VARCHAR2 DEFAULT 'LASTRUN',
    options in        VARCHAR2 DEFAULT 'GATHER',
    modpercent        NUMBER DEFAULT 10,
    invalidate        VARCHAR2 DEFAULT 'Y'
);

Parameters

Variable Description
schemaname Schema to analyze. ALL means all Oracle E-Business Suite schemas.
estimate_percent The sampling percentage. If a value is not provided, the default value of 10 is used. The valid range is from 0 to 100.
degree The degree of parallelism to be used for gathering statistics. If a degree is not provided, it defaults to the minimum of parallel_max_servers and cpu_count.
internal_flag The backup flag indicates whether to backup statistics. Set this flag to BACKUP if you wish to back up the current statistics into the FND_STATTAB table. If NOBACKUP is used, then the GATHER_SCHEMA_STATS procedure will not backup the current statistics. This way the GATHER_SCHEMA_STATS procedure will run faster.
errors User defined Type for holding the Error messages .
Request_id A request_id can be provided to identify the history records for a given statistics gathering run. This parameter is also used for providing restart ability. In case, a statistics gathering run fails due to whatever reasons, subsequent submission can pick up where the failed run left off, if you provide the request_id of the failed run.
Hmode This parameter controls the amount of history records that are created. The history records, stored in FND_STATS_HIST can be queried to find out when statistics were gathered on a particular object and the amount of time it took to gather statistics on that object.
LASTRUN - History records for each schema are maintained only for the last gather statistics run. Each subsequent run will overwrite the previous history record for the index. This is the default behavior.
FULL - This mode does not overwrite any history information. History records are created for each run and are identified by the Request ID. If a Request ID is not provided, one is generated automatically. If this mode is used, the “Purge FND_STATS History Records” concurrent program should be run periodically to purge the FND_STATS_HIST table.
NONE - This mode does not generate any history information. If this mode is used, the run cannot be restarted.
Options This parameter specifies how objects are selected for statistics gathering.
GATHER - All tables and indexes of the schema <schemaname> are selected for stats gathering. This is the default.
GATHER AUTO - Tables of the schema schemaname for which the percentage of modifications has exceeded modpercent are selected for statistics gathering. Indexes of these tables are selected by default. Table monitoring needs to be enabled before using this option.
GATHER EMPTY - Statistics are gathered only for tables and indexes that are missing statistics.
LIST AUTO - This option does not gather statistics. It only provides a listing of all the tables that will be selected for statistic gathering, if the GATHER AUTO option is used.
LIST EMPTY - This option does not gather statistics. It only provides a listing of all the tables that will be selected for statistics gathering, if the GATHER EMPTY option is used.
Modpercent Applicable only to GATHER AUTO and LIST AUTO options. This parameter specifies the percentage of modifications (with respect to the total rows) that have to take place on a table before it can be picked up for AUTO statistics gathering.
Invalidate This flag indicates whether cursors dependent on the table being analyzed should be invalidated. By default, dependent cursors are invalidated. This parameter is ignored if you are running a database prior to Oracle 9i Release 2 (9.2.x).

Exceptions

ORA-20000: Schema does not exist or insufficient privileges. 
ORA-20001: Bad input value. 

GATHER_INDEX_STATS Procedure

This procedure gathers statistics for the specified index.

Syntax

FND_STATS.GATHER_INDEX_STATS (
    ownname 		VARCHAR2,
    indname 		VARCHAR2,
    percent 		NUMBER DEFAULT NULL,
    partname 		VARCHAR2 DEFAULT NULL,
    backup_flag 	VARCHAR2 DEFAULT NULL,
    hmode 		   VARCHAR2 DEFAULT 'LASTRUN',
    invalidate   VARCHAR2 DEFAULT 'Y'

);

Parameters

Variable Description
ownname Schema of index to analyze.
indname Name of index.
percent The sampling percentage. If left blank, the default value of 10 is used. The valid range is from 0 to 100.
partname Partition name.
backup_flag The backup flag indicates whether to backup statistics. Set this flag to BACKUP if you wish to back up the current column statistics into the FND_STATTAB table. If left blank, it defaults to NOBACKUP.
Hmode This parameter controls the amount of history records that are created.
LASTRUN - History records for each index are maintained only for the last gather statistics run. Each subsequent run will overwrite the previous history record for the index. This is the default behavior.
FULL - This mode does not overwrite any history information. History records are created for each run and are identified by the Request ID. If a Request ID is not provided, one is generated automatically. If this mode is used, the “Purge FND_STATS History Records” concurrent program should be run periodically to purge the FND_STATS_HIST table.
NONE - This mode does not generate any history information. If this mode is used, the run cannot be restarted.
Invalidate This flag indicates whether cursors dependent on the index being analyzed should be invalidated. By default, dependent cursors are invalidated.

GATHER_TABLE_STATS Procedure

This procedure gathers table, column and index statistics. It attempts to parallelize as much of the work as possible. This operation does not parallelize if the user does not have select privilege on the table being analyzed.

Syntax

FND_STATS.GATHER_TABLE_STATS (
    ownname 		VARCHAR2,
    tabname 		VARCHAR2,
    percent 		NUMBER DEFAULT NULL,
    degree 		NUMBER DEFAULT NULL,
    partname 		VARCHAR2 DEFAULT NULL,
    backup_flag 	VARCHAR2 DEFAULT NULL,
    cascade 		BOOLEAN DEFAULT TRUE,
    granularity VARCHAR2 DEFAULT ‘DEFAULT’,
    hmode 		VARCHAR2 DEFAULT 'LASTRUN',
    invalidate VARCHAR2 DEFAULT 'Y'
);

Parameters

Variable Description
ownname Owner of the table.
tabname Name of the table.
percent The sampling percentage. If left blank, the default value of 10 is used. The valid range is from 0 to 100.
degree The degree of parallelism to be used for gathering statistics. If a degree is not provided, it defaults to the minimum of parallel_max_servers and cpu_count.
partname Name of the partition.
backup_flag The backup flag indicates whether to backup statistics. Set this flag to BACKUP if you wish to back up the current table statistics into the FND_STATTAB table. If left blank, it defaults to NOBACKUP.
cascade When set to TRUE index statistics are gathered in addition to gathering statistics for the specified table.Index statistics gathering is not parallelized. Using this option is equivalent to running the GATHER_INDEX_STATS procedure on each of the table’s indexes
granularity The granularity of statistics to collect (only relevant for tables that are partitioned). Valid values are:
DEFAULT - Gather global and partition–level statistics.
SUBPARTITION - Gather subpartition–level statistics.
PARTITION - Gather partition–level statistics.
GLOBAL - Gather global statistics.
ALL - Gather all (subpartition, partition, and global) statistics.
Hmode This parameter controls the amount of history records that are created.
LASTRUN - History records for each index are maintained only for the last gather statistics run. Each subsequent run will overwrite the previous history record for the index. This is the default behavior.
FULL - This mode does not overwrite any history information. History records are created for each run and are identified by the Request ID. If a Request ID is not provided, one is generated automatically. If this mode is used, the “Purge FND_STATS History Records” concurrent program should be run periodically to purge the FND_STATS_HIST table.
NONE - This mode does not generate any history information. If this mode is used, the run cannot be restarted.
Invalidate This flag indicates whether cursors dependent on the index being analyzed should be invalidated. By default, dependent cursors are invalidated.

GATHER_COLUMN_STATS Procedure

This procedure should be used for gathering the Column Statistics, i.e. creating a histogram on a given column.

There are two versions of this procedure. The first one gathers statistics on all columns seeded in the FND_HISTOGRAM_COLS for the given appl_id. If NULL, all seeded histograms are created. The other version gathers column statistics for the specified column.

Syntax

FND_STATS.GATHER_COLUMN_STATS (
    appl_id     NUMBER DEFAULT NULL,
    percent 	  NUMBER DEFAULT NULL,
    degree      NUMBER DEFAULT NULL,
    backup_flag VARCHAR2 DEFAULT NULL,
    Errors      OUT Error_Out,
    hmode       VARCHAR2 DEFAULT 'LASTRUN',
    invalidate  VARCHAR2 DEFAULT 'Y'
);

FND_STATS.GATHER_COLUMN_STATS (
    ownname      VARCHAR2,
    tabname      VARCHAR2,
    colname      VARCHAR2,
    percent      NUMBER DEFAULT NULL,
    degree       NUMBER DEFAULT NULL,
    hsize        NUMBER DEFAULT 254,
    backup_flag  VARCHAR2 DEFAULT NULL,
    partname     VARCHAR2 DEFAULT NULL,
    hmode        VARCHAR2 DEFAULT 'LASTRUN',
    invalidate   VARCHAR2 DEFAULT 'Y'
);

Parameters

Variable Description
appl_id Application ID.
ownname Owner of the table.
colname Column name.
tabname Table name.
partname Name of the partition.
percent The sampling percentage. If left blank, the default value of 10 is used. The valid range is from 0 to 100.
degree The degree of parallelism to be used for gathering statistics. If a degree is not provided, it defaults to the minimum of parallel_max_servers and cpu_count.
hsize Number of buckets in the histogram.
backup_flag The backup flag indicates whether to backup statistics. Set this flag to BACKUP if you wish to back up the current column statistics into the FND_STATTAB table. If left blank, it defaults to NOBACKUP.
errors User defined Type for holding the Error messages.
hmode This parameter controls the amount of history records that are created.
LASTRUN - History records for each index are maintained only for the last gather statistics run. Each subsequent run will overwrite the previous history record for the index. This is the default behavior.
FULL - This mode does not overwrite any history information. History records are created for each run and are identified by the Request ID. If a Request ID is not provided, one is generated automatically. If this mode is used, the “Purge FND_STATS History Records” concurrent program should be run periodically to purge the FND_STATS_HIST table.
NONE - This mode does not generate any history information. If this mode is used, the run cannot be restarted.
Invalidate This flag indicates whether cursors dependent on the index being analyzed should be invalidated. By default, dependent cursors are invalidated.

GATHER_ALL_COLUMN_STATS Procedure

This procedure gathers column statistics, i.e. creates histograms on all columns that are seeded in the FND_HISTOGRAM_COLS, belonging to the specified schema .

Syntax

FND_STATS.GATHER_ALL_COLUMN_STATS (
    ownname    VARCHAR2 ,
    percent    NUMBER DEFAULT NULL,
    degree     NUMBER DEFAULT NULL,
    hmode      VARCHAR2 DEFAULT 'LASTRUN',
    invalidate VARCHAR2 DEFAULT 'Y'

);

Parameters

Variable Description
ownname Schema for which seeded histograms have to be created. ALL means all Applications schemas.
percent The sampling percentage. If left blank, the default value of 10 is used. The valid range is from 0 to 100.
degree The degree of parallelism to be used for gathering statistics. If a degree is not provided, it defaults to the minimum of parallel_max_servers and cpu_count.
Hmode This parameter controls the amount of history records that are created.
LASTRUN - History records for each index are maintained only for the last gather statistics run. Each subsequent run will overwrite the previous history record for the index. This is the default behavior
FULL - This mode does not overwrite any history information. History records are created for each run and are identified by the Request ID. If a Request ID is not provided, one is generated automatically. If this mode is used, the “Purge FND_STATS History Records” concurrent program should be run periodically to purge the FND_STATS_HIST table.
NONE - This mode does not generate any history information. If this mode is used, the run cannot be restarted.
Invalidate This flag indicates whether cursors dependent on the index being analyzed should be invalidated. By default, dependent cursors are invalidated.

ANALYZE_ALL_COLUMNS Procedure

This procedure has been made obsolete.

LOAD_XCLUD_STATS Procedure

This procedure has been made obsolete.

PURGE_STAT_HISTORY Procedure

This procedure should be used for purging the unwanted history records from the fnd_stats_hist table. There are two versions of this procedure. The first one takes in a range of request ids and deletes all history records that fall within that range. The second version takes a range of dates as arguments and all the history records falling in-between that range are deleted. The delete takes place as an autonomous transaction.

Syntax

FND_STATS.PURGE_STAT_HIST (
    From_req_id	 NUMBER,
    To_req_id 	 NUMBER);

FND_STATS. PURGE_STAT_HIST(
    Purge_from_date 	 VARCHAR2,
    Purge_to_date 	 VARCHAR2);

Parameters

Variable Description
from_req_id Start Request ID.
to_req_id End Request ID.
purge_from_date Start Purge Date.
purge_to_date End Purge Date.

CHECK_HISTOGRAM_COLS Procedure

For a given list of comma-separated tables, this procedure checks the data in all the leading columns of all the non-unique indexes of those tables and determines if histograms need to be created for those columns. The algorithm for this procedure is:

select 
decode(floor(sum(tot)/(max(cnt)*FACTOR)),0,'YES','NO') HIST
from (select count(col) cnt , count(*) tot
from tab sample (PERCENT)
where col is not null
group by col);

The decode statement determines whether a single value occupies 1/FACTOR or more of the sample PERCENT.

If sum(cnt) is very small (a small non-null sample), the results may be inaccurate. A count(*) of at least 3000 is recommended. The procedure is run from a SQL prompt after setting the server output on.

Syntax

FND_STATS.CHECK_HISTOGRAM_COLS (
	tablelist	VARCHAR2,
	factor		NUMBER DEFAULT 75,
	percent		NUMBER DEFAULT 10,
	degree		NUMBER DEFAULT NULL);

Parameters

Variable Description
tablelist A comma separated list of tables. It should be of the form schema.tablename. A wildcard in the tablename is also allowed. For example, tablelist=>'oe.so%head% , pa.pa_exp% , ar.ra_customers'. The owner part is mandatory.
factor The factor for calculating the histograms.
percent Sample percent.
degree Degree of parallelization.

VERIFY_STATS Procedure

For a given list of comma-separated tables, or for a given schema name, this procedure reports the statistics in the data dictionary tables for the tables, indexes, and histograms.

Syntax

FND_STATS.VERIFY_STATS (
	schemaname    VARCHAR2 DEFAULT NULL,
	tablelist     VARCHAR2 DEFAULT NULL,
	days_old      NUMBER DEFAULT NULL,
	column_stat   BOOLEAN DEFAULT FALSE);

Parameters

Variable Description
schemaname The name of a schema. If schemaname is NULL (which is the default), then the procedure reports on the given list of tables.
tablelist A comma-separated list of tables. If the tablename is not of the form <schema>.<tablename> then the schema is the value of the schemaname parameter. If the tablelist is NULL (the default), then the procedure reports on all the tables for the specified schemaname.
days_old The procedure only reports those tables whose statistics are older than the days_old number of days. The default is NULL, which means the procedure will report on all the tables.
column_stat If TRUE, the procedure reports column statistics for the export_table_stats table. The default is FALSE.