How to Gather statistics in EBS
- In order to use Cost Based Optimizer effectively, the schema statistics must be current. Fortunately, Oracle EBS has provided some scripts and concurrent programs(Gather Schema Statistics and Gather Table statistics) to make this administrative task easier. FND_STATS is a PL/SQL package for the Applications that has numerous functions that will assist in this administrative task. This package can be invoked either from a seeded Concurrent Program, or directly from SQL*Plus.
- It is not recommended to use DBMS_STATS with Oracle apps Database.
- When using the ‘Gather Schema Statistics’ concurrent program, it is recommended that you only pass the schema name parameter and let the other parameters default to their default values. The schema name passed can be a specific schema name (e.g. GL) or ‘ALL’ if you prefer to gather CBO stats for Apps modules. This choice uses the “estimate” method with a sample size of 10% (default).
- When using the ‘Gather Table Statistics’ concurrent program, only pass the owner of the table (schema name) and the table name. Let all other parameters default automatically, except when the table is a partitioned table.
To manually execute FND_STATS from SQL*Plus to gather CBO stats for one or all schemas, or for a particular table, use the following syntax:
To gather statistics for particular schema
To gather statistics for ALL schema in database
To gather statistics for particular table
Some more command
To gather stats at 10%
To gather stats at 30%
To gather stats at 25% with 4 parallel worker
To gather stats with auto_sample_size
exec apps.fnd_stats.GATHER_TABLE_STATS('HR','PAY_PAYROLL_ACTIONS',10,4); exec apps.fnd_stats.GATHER_TABLE_STATS('HR',HR_CODES',35); exec apps.fnd_stats.GATHER_TABLE_STATS('CS','CS_INCIDENT_TYPE_TL'); exec apps.fnd_stats.GATHER_SCHEMA_STATS('GL',25);
With 11g and 12c Database, It is recommended to start using GATHER AUTO and AUTO SAMPLE SIZE for gathering statistics
This can be done through concurrent request also or through the sql
Gather for the APPLSYS schema:
exec apps.fnd_stats.gather_schema_statistics(schemaname=> 'APPLSYS',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE ,options => 'GATHER AUTO');
Gather for ALL schemas: (may or may not want to do this)
exec apps.fnd_stats.gather_schema_statistics(schemaname=> 'ALL',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE ,options => 'GATHER AUTO');
This runs quite faster to other above ones and it is very accurate also. It is faster as it generates stats for stale objects only and that too with auto sample size
If you want to know the timing gather stats took on the schema’s, we can use below query
select owner, min(last_analyzed) , max(last_analyzed) ,( max(last_analyzed) - min(last_analyzed))*24 hours_took from dba_tables where last_analyzed is not null and trunc (last_analyzed) = '&enter_date_DD-MON-YY' -- Example : '01-APR-19' group by owner order by hours_took ;
Gathering system statistics with 11i and R12
For E-Business Suite, for non-Exadata databases, it is recommend to gather system statistics with NOWORKLOAD option, as in:
execute DBMS_STATS.GATHER_SYSTEM_STATS (gathering_mode => 'NOWORKLOAD');
But for Exadata database use following command instead :
execute DBMS_STATS.GATHER_SYSTEM_STATS ('EXADATA');
Typically, this is only need to be done once after any major architectural changes made to the DB.
Gathering dictionary statistics with 11i and R12
- You must have the SYSDBA (or both ANALYZE ANY DICTIONARY and ANALYZE ANY) system privilege to execute this procedure.
execute dbms_stats.gather_schema_stats(''SYS'', method_opt=>''for all columns size 1'', degree=>30,estimate_percent=>100,cascade=>true);
- If you are using Database version 10g/11g, you can use the following syntax instead
execute dbms_stats.gather_dictionary_stats ();
- This procedure will gather statistics for all system schema’s, including SYS and SYSTEM, and other optional schema’s, such as CTXSYS and DRSYS.
- Typically, this is only need to be done once after any major architectural changes made to the DB.
This concludes the steps for Gathering optimizer Statistics in Oracle EBS environment. Hope you like post on Gather Schema Statistics Using FND_STATS in EBS 11i and R12