Gather Schema Statistics - Oracle Applications
Gather Schema Statistics :
Gather Schema Statistics program generates statistics that quantify the data distribution and storage characteristics of tables, columns, indexes, and partitions.
The cost-based optimization (CBO) uses these statistics to calculate the selectivity of prediction and to estimate the cost of each execution plan.
As a general rule, run Gather Schema Statistics under the following circumstances:
1. After there has been a significant change in data in either content or volume.
2. After importing data.
3. Any time end-users notice deterioration in performance in routine day-to-day business transactions or when running concurrent programs.
4. Run on a regular basis (weekly at a minimum) and anytime after application of patch, conversion, etc.
Estimate Percentage / Modification threshold defines the percentage which should be used to initiate gather stats for those objects which have actually changed beyond the threshold.
The default is 10% (i.e. meaning any table which has changed via DML more than 10%, stats will be collected, otherwise it will be skipped).
How to run Gather Schema Statistics concurrent program:
1. Log on to Oracle Applications with
Responsibility = System Administrator
2. Submit Request Window
Navigate to: Concurrent > Requests
3. Query for the Gather Schema Statistics
4. Enter the appropriate parameters. This can be run for specific schemas by specifying the schema name or entering ‘ALL’ to gather statistics for every schema in the database
5. Submit the Gather Schema Statistics program
Parameters :
------------------
Schema Name: Schema for which statistics are to be gathered. Specify ALL for all Oracle 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 ofparallel_max_servers and cpu_count.
Backup Flag: 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: 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
Gather Options: GATHER: All tables and indexes of the schema schema name are selected for stats gathering. This is the default
Modifications Threshold: Applicable only to GATHER AUTO and LIST AUTO Options
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.
How to Gather the Statistics of Custom Schema when we submit the concurrent request called Gather Schema Statistics :
When we submit Gather Schema Stats with Parameter ALL, concurrent request will complete successfully, and DBAs will not realize that custom schemas are not analyzed.
Sql > select count(table_name) from dba_tables where last_analyzed is not null and owner= <custom_schema_name>;
Here you realize none of the tables in custom schema are analyzed.
Gather Schema Statistics program gathers statistics for all schemas , however it skips custom schemas registered in Oracle Applications.
Reason:
Whenever Custom schemas are registerd in Oracle Applications , the entries are done in 2 tables
ie FND_ORACLE_USERID and FND_APPLICATIONS_TL
However , when Gather schema statistics is submitted it uses the below query to get schema information
Sql > select distinct upper(oracle_username) sname
from fnd_oracle_userid a,
fnd_product_installations b
where a.oracle_id = b.oracle_id
order by sname;
Note : When custom schemas are created the entry is not made in FND_PRODUCT_INSTALLATIONS and hence it is not picked up in the above query.
Solution :
How can we make an entry in fnd_product_installations so that it is picked up by Gather Schema Stats. Follow below steps
Go to the Responsibility called Alert Manager and Navigate to the form -> Installations under Systems Menu.
Define custom application in this form. Go to the last record and make entry for custom applications. Once this is done , it will insert an entry in fnd_product_installations.
Submit Gather Schema stats and then query dba_tables and you will realize , stats are being gathered for custom schemas as well.
Gather Schema Statistics :
Gather Schema Statistics program generates statistics that quantify the data distribution and storage characteristics of tables, columns, indexes, and partitions.
The cost-based optimization (CBO) uses these statistics to calculate the selectivity of prediction and to estimate the cost of each execution plan.
As a general rule, run Gather Schema Statistics under the following circumstances:
1. After there has been a significant change in data in either content or volume.
2. After importing data.
3. Any time end-users notice deterioration in performance in routine day-to-day business transactions or when running concurrent programs.
4. Run on a regular basis (weekly at a minimum) and anytime after application of patch, conversion, etc.
Estimate Percentage / Modification threshold defines the percentage which should be used to initiate gather stats for those objects which have actually changed beyond the threshold.
The default is 10% (i.e. meaning any table which has changed via DML more than 10%, stats will be collected, otherwise it will be skipped).
How to run Gather Schema Statistics concurrent program:
1. Log on to Oracle Applications with
Responsibility = System Administrator
2. Submit Request Window
Navigate to: Concurrent > Requests
3. Query for the Gather Schema Statistics
4. Enter the appropriate parameters. This can be run for specific schemas by specifying the schema name or entering ‘ALL’ to gather statistics for every schema in the database
5. Submit the Gather Schema Statistics program
Parameters :
------------------
Schema Name: Schema for which statistics are to be gathered. Specify ALL for all Oracle 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 ofparallel_max_servers and cpu_count.
Backup Flag: 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: 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
Gather Options: GATHER: All tables and indexes of the schema schema name are selected for stats gathering. This is the default
Modifications Threshold: Applicable only to GATHER AUTO and LIST AUTO Options
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.
How to Gather the Statistics of Custom Schema when we submit the concurrent request called Gather Schema Statistics :
When we submit Gather Schema Stats with Parameter ALL, concurrent request will complete successfully, and DBAs will not realize that custom schemas are not analyzed.
Sql > select count(table_name) from dba_tables where last_analyzed is not null and owner= <custom_schema_name>;
Here you realize none of the tables in custom schema are analyzed.
Gather Schema Statistics program gathers statistics for all schemas , however it skips custom schemas registered in Oracle Applications.
Reason:
Whenever Custom schemas are registerd in Oracle Applications , the entries are done in 2 tables
ie FND_ORACLE_USERID and FND_APPLICATIONS_TL
However , when Gather schema statistics is submitted it uses the below query to get schema information
Sql > select distinct upper(oracle_username) sname
from fnd_oracle_userid a,
fnd_product_installations b
where a.oracle_id = b.oracle_id
order by sname;
Note : When custom schemas are created the entry is not made in FND_PRODUCT_INSTALLATIONS and hence it is not picked up in the above query.
Solution :
How can we make an entry in fnd_product_installations so that it is picked up by Gather Schema Stats. Follow below steps
Go to the Responsibility called Alert Manager and Navigate to the form -> Installations under Systems Menu.
Define custom application in this form. Go to the last record and make entry for custom applications. Once this is done , it will insert an entry in fnd_product_installations.
Submit Gather Schema stats and then query dba_tables and you will realize , stats are being gathered for custom schemas as well.
0 comments:
Post a Comment