Custom Schemas are not analyzed when Gather Schema Statistics are submitted.

Concurrent –> Request –> Submit Request –> Gather Schema statistics (Paramerters “ALL”)The above request gathers statistics for all schemas , however it skips custom schemas registered in Oracle Applications.Explaination : 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

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

Responsibility Alert Manager — > Systems –> Installations

Define custom application in this form , go 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.

Share This Post with Your Friends over Social Media!

About the Author Atul Kumar

Leave a Comment:

1 comments
Rupesh Sreenivas says October 19, 2009

Good Catch, leme add my piece:

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.

select count(table_name) from dba_tables where last_analyzed is not null and owner=

Here you realize none of the tables in custom schema are analyzed.

Refer to the Oracle Alerts documentation (page 7-27) on how to register the custom product installation.

The URL to the Alerts documentation is
http://download-west.oracle.com/docs/cd/B25516_08/current/acrobat/115alrug.pdf

Refer Note:394555.1
FND_STATS concurrent program does not PICK UP seemingly properly REGISTERED CUSTOM SCHEMAS

Reply
Add Your Reply