Gather Schema Statistics in Oracle Apps

Normally we follow the following steps to analyze Gather Schema Statistics.

•        Connect Oracle Application as System Administrator

•        Concurrent –> Request –> Submit Request –> Gather Schema statistics (Parameters “ALL”) .The above request gathers statistics for all schemas, however it skips custom schemas registered in Oracle Applications.

Whenever Custom schemas are registered in Oracle Applications , the entries are stored in two tables.

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;

 Please 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.

Here we have the solution to analyze custom schemas: 

We need to know how can we make an entry in fnd_product_installations so that it is picked up by Gather Schema statistics. For this we need to follow the below steps.

 Responsibility Alert Manager — > Systems –> Installations

Define custom application in this form, go to the last record and make entry for custom applications. After this is done , it will insert an entry in fnd_product_installations.

 Submit Gather Schema stats and then query dba_tables and we will realize, statistics are finally gathered for custom schemas as well.