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.
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
Backup Table Statistics
Restore Table Statistics
Gather Schema Statistics
Purge FND_STATS History Records
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:
|
| 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). |
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. |
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. |
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.
|
| Gather Options | This parameter specifies how objects are selected for statistics gathering.
|
| 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). |
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. |
This concurrent program is obsolete.
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. |
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.
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.
FND_STATS.CREATE_STAT_TABLE ; FND_STATS.CREATE_STAT_TABLE ( schemaname IN VARCHAR2, tabname IN VARCHAR2, tblspcname IN VARCHAR2);
| 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. |
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.
FND_STATS.BACKUP_TABLE_STATS ( schemaname VARCHAR2, tabname VARCHAR2, statid VARCHAR2 DEFAULT 'BACKUP', partname VARCHAR2 DEFAULT NULL, cascade BOOLEAN DEFAULT TRUE);
| 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. |
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.
FND_STATS.BACKUP_TABLE_STATS ( schemaname VARCHAR2, statid VARCHAR2 DEFAULT NULL);
| 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. |
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.
FND_STATS.RESTORE_SCHEMA_STATS ( schemaname VARCHAR2, statid VARCHAR2 DEFAULT NULL );
| 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. |
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.
FND_STATS.RESTORE_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, statid VARCHAR2 DEFAULT NULL, partname VARCHAR2 DEFAULT NULL, cascade BOOLEAN DEFAULT TRUE, );
| 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. |
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.
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 );
| 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. |
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.
FND_STATS.ENABLE_SCHEMA_MONITORING (
schemaname VARCHAR2 DEFAULT ‘ALL’);
| Variable | Description |
|---|---|
| schemaname | Name of the schema for which Monitoring should be enabled. |
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.
FND_STATS.DISABLE_SCHEMA_MONITORING (
schemaname VARCHAR2 DEFAULT ‘ALL’);
| Variable | Description |
|---|---|
| schemaname | Name of the schema for which Monitoring should be disabled. |
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.
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'
);
| 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). |
ORA-20000: Schema does not exist or insufficient privileges. ORA-20001: Bad input value.
This procedure gathers statistics for the specified index.
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'
);
| 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. |
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.
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'
);
| 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. |
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.
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'
);
| 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. |
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 .
FND_STATS.GATHER_ALL_COLUMN_STATS (
ownname VARCHAR2 ,
percent NUMBER DEFAULT NULL,
degree NUMBER DEFAULT NULL,
hmode VARCHAR2 DEFAULT 'LASTRUN',
invalidate VARCHAR2 DEFAULT 'Y'
);
| 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. |
This procedure has been made obsolete.
This procedure has been made obsolete.
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.
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);
| 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. |
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.
FND_STATS.CHECK_HISTOGRAM_COLS (
tablelist VARCHAR2,
factor NUMBER DEFAULT 75,
percent NUMBER DEFAULT 10,
degree NUMBER DEFAULT NULL);
| 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. |
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.
FND_STATS.VERIFY_STATS (
schemaname VARCHAR2 DEFAULT NULL,
tablelist VARCHAR2 DEFAULT NULL,
days_old NUMBER DEFAULT NULL,
column_stat BOOLEAN DEFAULT FALSE);
| 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. |