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.