Forgot your password?    
+ Reply to Thread
Results 1 to 1 of 1

Thread: How to Gather Statistics for a Schema in Apps

  1. #1
    Oracle DBA
    Join Date
    Oct 2011

    How to Gather Statistics for a Schema in Apps

    Gather Schema Statistics is a Concurrent Program usually scheduled to run every week from Oracle Applications in order to collect statistics.

    FND_STATS provides a mechanism to gather the statistics and generate Histograms using the procedure FND_STATS.GATHER_SCHEMA_STATS . This would gathers statistics for all objects in a schema. When gathering Statistics for a table or an entire Schema CBO will cascade down and gather statistics for all indexes on each table, and all the columns for that particular table or schema.

    To run concurrent program Gather Schema Statistics:

    1. Log on to Oracle Applications with
    Responsibility = System Administrator
    2. Submit Request Window
    Navigate to: List > Request > Run.
    3. Enter the appropriate parameters. This can be run for specific
    schemas by specifying the schema name or entering 'ALL' to gather
    statistics for every schema in the database.
    4. Submit the gather schema statistics program.

    We can also submit the Gather Schema Statistics Concurrent request directly from the OS prompt using CONCSUB.
    Details with example stated in : Using-Concsub-to-submit-Concurrent-Requests

    Gathering Statistics Concurrent Requests

    Oracle Applications provides a set of procedures in the FND_STATS package to facilitate collection of these statistics. FND_STATS uses the DBMS_STATS package to gather statistics.

    The following concurrent requests are available in Oracle Applications for gathering statistics:

    Gather All Column Statistics
    Gather Column Statistics
    Gather Schema Statistics
    Gather Table Statistics

    For Oracle Applications 11i it is recommended to use only the 'Gather Schema Statistics' or the 'Gather Table Statistics'.

    Common Parameters:

    You may enter ALL to analyze every defined App schema.

    Percentage of rows to estimate. If left empty it will default to 10%. The valid range is 0-99. A higher percentage will be more accurate, but take longer to run. If the object(s) that you are gathering statistics for do not change often or the object(s) has data entered that is very similiar you may choose a lower number. However, if the data changes frequently a larger number entered for this parameter would be recommended to provide a more accurate representation of your data.

    Enter the Degree of parallelism. If not entered, it will default to min(cpu_count, parallel_max_servers). Modifying the degree of parallelism on a table can cause the plan to change. Increasing the degree of parallelism is likely to make full table scans appear cheaper and more attractive while reducing it will make Full Table Scans look less attractive.

    Backup Flag
    If the value is 'NOBACKUP' then it won't take a backup of the current statistics and should run quicker. If the value is 'BACKUP' then it does an export_table_stats prior to gathering the statistics.

    Restart Request Id
    Enter the request id that should be used for recovering gather_schema_stats if this request should fail. You may leave
    this parameter null.

    Gather Options
    As of 11.5.10, FND_STATS.GATHER_SCHEMA_STATS introduced a new parameter called OPTIONS that, if set to GATHER AUTO, allows FND_STATS to automatically determine the tables for which statistics should be gathered based on the change threshold. The Modifications Threshold can be adjusted by the user by passing a value for modpercent, which by default is equal to 10. GATHER AUTO uses a database feature called Table Monitoring, which needs to be enabled for all the tables. A procedure called ENABLE_SCHEMA_MONITORING has been provided to enable monitoring on all tables for a given schema or all Applications schemas.

    Manual Execution
    In R11i customers should be using the FND_STATS command.
    Do not use the ANALYZE command or DBMS_STATS package directly, as doing so may cause incomplete statistics to be generated.

    Use the following command to gather schema statistics:

    exec fnd_stats.gather_schema_statistics('ONT') < For a specific schema >
    exec fnd_stats.gather_schema_statistics('ALL') < For all schemas >

    We use the Verify Stats report to determine whether the current statistics are accurate.
    This report is a utility provided with FND_STATS, and can be run as follows:

    SQL> set server output on
    SQL> set long 10000
    SQL> exec fnd_stats.verify_stats('schema', 'object_name');
    Last edited by Hemant; 12-10-2011 at 01:46 AM.

+ Reply to Thread

Similar Threads

  1. Gathering Oracle Database Statistics Using DBMS_STATS.
    By ajaychandi in forum Core Database Administration and Monitoring
    Replies: 0
    Last Post: 03-30-2012, 11:56 PM
  2. How to Gather Statistics for a Schema in Apps
    By Sachin in forum Concepts,Service Management,Apache,OC4J,OPMN,JServ,Forms, Issues,Troubleshooting,
    Replies: 0
    Last Post: 12-08-2011, 12:37 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training

Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics