Monday, July 30, 2007

Gather statistics for Oracle Applications 11i

Following the last post Monitoring DML operations, In this post I'm going to talk about Gathering Statistics in Oracle Applications 11i system.

All of us gathering statistics in our databases, the question is whether it is the best way to do it?
We are struggling with a lot of questions regarding for which schema needs to run? How much percent sampling? In what frequency it considered enough? Etc...

So I will try to make some light in the dark...

The topics in this post will be:
• The classic way to gather statistics - by the "Gather Schema Statistics" concurrent with Gather Options parameter set to "GATHER".
• Using "Monitoring" with Gather Schema Statistics to enhance Gather Statistics run time and efficiency.

You can read more about monitoring in my previous post Monitoring DML operations

The Gather Schema Statistics concurrent, based on fnd_stats package, gather statistics for the required schema(s) with some additional parameters:

Schema Name - the schema in which statistics should gathered. You can specify 'ALL' for gather statistics for all schemas registered in FND_PRODUCT_INSTALLATIONS table.

Estimate Percent - The sampling percents. Default value is 10.

Degree of parallelism - The degree of parallelism for gathering statistics. Default value is MIN (parallel_max_servers, cpu_count). (database init parameters)

Backup Flag - Indicate whether to backup last statistics (current situation). BACKUP/NOBACKUP - indicate to save/not save current statistics.

Restart Request Id - if Gather Schema Statistics concurrent failed, you can rerun it with this parameter set to the failed request_id. The concurrent will continue from where the failed request stopped.

History Mode - indicate how much history will save. History includes for each object when statistics gather for it, when it ends and more info'. Parameter options are: LAST_RUN - will save history only for the last time for each object. FULL - will save additional history each run. None - don't save history.

Gather Options - indicates for which objects statistics will gather. Parameter options are: GATHER (the classic & default) - gather stat' for all objects in the appropriate schema. GATHER_AUTO - This option considers how much DML operations executed on each object to decide whether gather statistics or not, I will explain later in details. GATHER_EMPTY - will gather stat only for tables/indexes with no statistics. LIST_AUTO - provide a list of objects for gather statistics if GATHER_AUTO is used. LIST_EMPTY - provide a list of objects for gather statistics if GATHER_EMPTY is used.

Modifications Threshold - relevant only when using GATHER_AUTO/LIST_AUTO in the previous parameter. This parameter specifies the percentage of modifications on objects that must execute before gathering statistics. I will explain later in details.

Invalidate Dependent - indicate whether to invalidate cursor for the analyzed objects. The default is 'Y'.

The Gather Schema Statistics concurrent should schedule to run periodically (usually once a week/2 weeks).
With gather options parameter set to GATHER and schema set to ALL, it will take a very long time to run since it gather statistics for all objects in database (for schemas registered in FND_PRODUCT_INSTALLATIONS table).


Using "Monitoring" with Gather Schema Statistics

To reduce Gather Schema Statistics concurrent run time, we can set the Gather Options parameter to "GATHER AUTO", it indicates to gather stats only for tables that have [Modifications Threshold] % changes since last analyze.

First we should enable monitoring for the relevant schemas.
We can do it by executing the following:
exec fnd_stats.ENABLE_SCHEMA_MONITORING (SCHEMA_NAME);


You can specify ‘ALL’ as parameter to enable monitoring for all tables in registered schemas.

The Gather Schema Statistics concurrent will do the following:

• Flush database monitoring info from SGA to dba_tab_modifications view
• Gather statistics for tables that cumulative modifications compared to the dba_tables.num_rows are more than Modification Threshold percents.
• Gather statistics for table that never analyzed (last_analyzed in NULL)
• Gather statistics for tables which does not have monitoring enabled and afterwards enable monitoring for them so we have data in dba_tab_modifications next time we Gather Statistics.

As you can see, it’s not necessary to enable monitoring before gather statistics with Gather Auto option, since it does it anyway.
(But I did…:-))

In fact, gather schema statistics become significantly more efficient, less run time, since we gather statistics for heavy using tables only.
This enables us to run Gather statistics more frequently for less time.

Try it!

For any questions or if you have additional information you are welcome to leave a comment.

7 comments:

ReggieP said...

Gather schema stats ALL conc program, spoils something and my reports run over an hour, when I use fnd gather schema all, command line, my reports run under 10 seconds. Why>

ReggieP

Aviad said...

Hi Reggie,

Gather schema statistics for ALL schemas probably should take more than a hour, so if something go wrong, it is the execution that run under 10 seconds... (I guess)
Basically, there is no differences between the Gather Schema Statistics concurrent and the FND_STATS.Gather_Schema_Stats procedure.
The Gather Schema Statistics concurrent invokes the FND_STATS.Gather_Schema_Stats procedure.

I suggest to check the parameters to the procedure, maybe something wrong there.
Also try to debug the procedure to see what it actually do.

Aviad

Reggie said...

Aviad,

Thanks for the help. I followed your article and I get the following result, please advise (from Reggie):

SQL> exec fnd_stats.ENABLE_SCHEMA_MONITORING ('ALL');
BEGIN fnd_stats.ENABLE_SCHEMA_MONITORING ('ALL'); END;

*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-06512: at "SYS.DBMS_STATS", line 11386
ORA-06512: at "SYS.DBMS_STATS", line 11410
ORA-06512: at line 1
ORA-06512: at "APPS.FND_STATS", line 138
ORA-06512: at "APPS.FND_STATS", line 159
ORA-06512: at line 1

Aviad said...

Hi Reggie,

When you execute fnd_stats.ENABLE_SCHEMA_MONITORING ('ALL'), it's alters all tables in all registered scemas in this way:
"alter table owner.table_name monitoring;"
If you have one of this tables locked, the alter statement will failed with this error (ORA-00054: resource busy and acquire with NOWAIT specified).
I recommend you, just for the first time in order to alter all tables to be monitored, to shutdown the application tier including all (forms, apache, concurrent managers etc) and bouncing the database and just then execute fnd_stats.ENABLE_SCHEMA_MONITORING ('ALL').

Hope it will help you,
Aviad

Anonymous said...

Aviad,
It might be worthwhile to mention herein that

fnd_stats.ENABLE_SCHEMA_MONITORING ('ALL')

will do nothing if the db version in use is 10g or higher...

10g's initialization parameter statistics_level is set to "TYPICAL" by default...

Aviad said...

Yes, you're right.
At the beginning of this post I directed to read my previous post about "Monitoring DML operations" and it was mentioned there.
Thanks anyway :-)

Aviad

Ramakrishna said...

How to run a gather schema statistics for particular module like gl,ar,po.......
through sysadmin user