Managing the cost-based optimizer in E-Business Suite

Joe Imbimbo, OAUG

Managing cost-based optimizer statistics is one of the most important and challenging tasks an Oracle database administrator performs. When doing this in an Oracle E-Business Suite (EBS) environment, there is a superset of tools that you should use to correctly manage statistics. This article highlights two components available in EBS Release 12.X and EBS Release 11i CU2 (11.5.10.2).

Oracle Applications Users Group (OAUG) logoThe primary consideration is to avoid the temptation of directly running the various procedures in the DBMS_STATS package. This is not supported in an EBS database and results in suboptimal execution plans. Instead, use Oracle-supplied concurrent programs or make calls to the FND_STATS package of procedures outside of the concurrent manager.

Locking statistics using the cost-based optimizer

There are occasions when the database administrator may want to lock statistics for a table. For example, the table may be static, and running statistics on the table serves no purpose. The other occasion may be to lock statistics on volatile tables where a set of existing statistics are empirically representative of the table. Running new statistics on a regular basis will hurt execution plans if statistics are collected after uncharacteristic database operations on the data set.

Joe Imbimbo, OAUG board memberLocking statistics may be performed using the FND_STATS package. Statistics may be stored in the default table FND_STATTAB located in the APPLSYS schema, or one may create others in particular schemas using the FND_STATS.CREATE_STAT_TABLE. Our example will deploy the standard APPLSYS FND_STATTAB table. The syntax:fnd_stats.load_xclud_tab('INSERT', 'APPLICATION ID associated with the table', 'TABLE_NAME').

So, for example, where we are satisfied that we have representative statistics on volatile tables in an HR system, we might do the following:

BEGIN
fnd_stats.load_xclud_tab('INSERT',800,'HR_LOCATIONS_ALL');
fnd_stats.load_xclud_tab('INSERT',800,'HR_NAME_FORMATS');
fnd_stats.load_xclud_tab('INSERT',801,'PAY_COST_ALLOCATIONS_F');
fnd_stats.load_xclud_tab('INSERT',801,'PAY_COST_ALLOCATION_KEYFLEX');
fnd_stats.load_xclud_tab('INSERT',801,'PAY_ELEMENT_ENTRIES_F');
fnd_stats.load_xclud_tab('INSERT',801,'PAY_ELEMENT_ENTRY_VALUES_F');
fnd_stats.load_xclud_tab('INSERT',801,'PAY_ELEMENT_LINKS_F');
fnd_stats.load_xclud_tab('INSERT',801,'PAY_ELEMENT_TYPES_F');
fnd_stats.load_xclud_tab('INSERT',801,'PAY_INPUT_VALUES_F');
fnd_stats.load_xclud_tab('INSERT',800,'PER_ALL_ASSIGNMENTS_F');
fnd_stats.load_xclud_tab('INSERT',800,'PER_ALL_PEOPLE_F');
fnd_stats.load_xclud_tab('INSERT',800,'PER_JOBS');
END;

If there is degradation in functionality supported by any of the above tables, you can unlock the statistics using the same package, with new statistics generated during the next collection period. An example:

BEGIN
fnd_stats.load_xclud_tab('DELETE',800,'HR_LOCATIONS_ALL');
fnd_stats.load_xclud_tab('DELETE',800,'HR_NAME_FORMATS');
fnd_stats.load_xclud_tab('DELETE',801,'PAY_COST_ALLOCATIONS_F');
END;

The above code will remove the tables from the "exclude" list.

Backing up statistics with the code-based optimizer

The FND_STATS.BACKUP_TABLE_STATS procedure can accommodate the need to backup statistics, or the DBA may deploy the concurrent program"Backup/Restore CBO Statistics." The parameters of the registered program include an action (BACKUP or RESTORE), schema name (such as HR, PA or GL), a table_name, the name of the stat table and the STAT_ID. If one used the default stat table, no argument is required for that parameter. There is another concurrent program, called "Backup Table Statistics," that permits a larger and more descriptive stat ID, but does not provide a list of values for the available schemas and tables, as this particular program does.

A stat ID is also optional, but providing a descriptive name is beneficial during the restore operation should statistics need to be rolled back and if different sets of statistics exist for the same object or collection of objects. My preference is to use concurrent manager programs where they exist when managing statistics in a database, so that the operations may be documented via the running of the programs. Concurrent manager log files may be extracted and saved to a file system that is not purged by cleanup and purge operations. In a hypothetical run of the concurrent program, we may specify the arguments:  BACKUP, HR, PAY_ELEMENT_ENTRIES_F, NULL (to use the default stat table in the APPLSYS schema) and "feb2013" (the STAT_ID).

After the concurrent manager program completes, a query of the FND_STTAB table indicates that:

SQL> SELECT COUNT(1) FROM FND_STATTAB 
2 WHERE STATID =’feb2013’; 
COUNT(1)
----------    
    288

I recommend backing up statistics prior to the creation of new statistics. Most of the FND_STATS procedures, whether run via the concurrent manager or PL/SQL programs, enable complete database operations (affects performance if this is done outside of an upgrade) and schema operations (such as HR, PA or GL), as well as specific table objects. Another concurrent program, Backup Schema Statistics, contains a parameter for a BACKUP option during the gathering of fresh statistics on ALL schemas or specific schemas.

Cost-based optimizer in conclusion

The items covered are only two of the tools provided by Oracle that enable DBAs to properly manage cost-based optimizer statistics in an EBS environment. The reader is encouraged to explore the other concurrent manager programs related to cost-based optimizer management, as well as the array of FND_STATS procedures provided by Oracle.  The chapter, called "Managing Optimizer Statistics" in the Oracle Database Performance Tuning Guide, provides the DBA with background necessary to deploy the tools described in the Oracle E-Business Suite System Administrator's Guide -- Configuration (E18727_01). No two EBS environments are the same because no two companies run the exact same EBS products in the same configurations. For this reason, there is no single way to handle cost-based optimizer statistic management.

About the author
Joe Imbimbo is a member of the
Oracle Applications Users Group (OAUG) board of directors and works as an Oracle Applications DBA for PPG Industries in Pittsburgh. He has worked as an Oracle Applications DBA, Unix system administrator and Oracle DBA for public sector, educational sector and private sector entities since 1994.

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: