How to Gather schema statistics in oracle apps R12
- 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 App modules. This choice uses the “estimate” method with a Auto sample size (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 a particular schema
exec fnd_stats.gather_schema_statistics('AP');
To gather statistics for ALL schema in the database
exec fnd_stats.gather_schema_statistics('ALL');
To gather statistics for a particular table
exec fnd_stats.gather_table_stats('GL','GL_JE_LINES');
Some more command
exec apps.fnd_stats.GATHER_TABLE_STATS('GL','GL_POSTING');
To gather stats at 10%
exec apps.fnd_stats.GATHER_TABLE_STATS('QP','QP_LIST_HEADERS_TL',10);
To gather stats at 30%
exec apps.fnd_stats.GATHER_TABLE_STATS('CSM','CSM_MTL_SYSTEM_ITEMS_ACC',35);
To gather stats at 25% with 4 parallel worker
exec apps.fnd_stats.GATHER_TABLE_STATS('ONT','OE_TRANSACTION_TYPES_ALL',25,4);
To gather stats with auto_sample_size
exec apps.fnd_stats.GATHER_TABLE_STATS('HR','PAY_RUN_BALANCES',dbms_stats.auto_sample_size);
Similar commands
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 the 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 requests 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 than the 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 gathering stats taken on the schema, we can use the 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 recommended to gather system statistics with the NOWORKLOAD option, as in:
execute DBMS_STATS.GATHER_SYSTEM_STATS (gathering_mode => 'NOWORKLOAD');
But for the Exadata database use the following command instead :
execute DBMS_STATS.GATHER_SYSTEM_STATS ('EXADATA');
Typically, this only needs 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 schemas, including SYS and SYSTEM, and other optional schemas, such as CTXSYS and DRSYS.
- Typically, this only needs to be done once after any major architectural changes are made to the DB.
This concludes the steps for Gathering optimizer Statistics in the Oracle EBS environment. Hope you like this post on Gather schema statistics in Oracle apps R12
Also Reads
Oracle concurrent Manager
Optimizer hints
https://docs.oracle.com/cd/E18727_01/doc.121/e12893/T174296T174306.htm
How to verify stats for the table in EBS