Oracle Optimizer: Moving to and working with CBO - Part 7 - Page 3

15. New Privileges

New privileges have been introduced in Oracle with the introduction of new features. For Optimizer maintenance, privileges for analyzing objects are the same as before. A user should have the ANALYZE ANY TABLE privilege in order to analyze objects owned by others.

example: Error when trying to generate statistics 
on objects without sufficient privileges.
jaJA>exec dbms_stats.delete_schema_stats('RON');
BEGIN dbms_stats.delete_schema_stats('RON'); END;
*
ERROR at line 1:
ORA-20000: Schema RON does not exist or 
insufficient privileges to analyze an object 
within it
ORA-06512: at "SYS.DBMS_STATS", line 3028
ORA-06512: at "SYS.DBMS_STATS", line 3050
ORA-06512: at "SYS.DBMS_STATS", line 3128
ORA-06512: at line 1

For creating stored outlines, the CREATE ANY OUTLINE privilege is required. Apart from this, for maintenance of stored outlines, the EXECUTE permission should be available on the DBMS_OUTLN/OUTLN_PKG package.

16. Oracle Applications specific information for CBO

Oracle Applications release 11i runs in CHOOSE mode that defaults to ALL_ROWS(COST). At my site, I found performance issues in some modules for online query screens. As users required better response time at my site, I had to make the queries run faster without actually changing the source.

The following Profile setup was one of the things I did to enforce a good response time. Please note that this should not be done if you are using CRM module; I came across a situation where the query screens did not return any data because of this setup.

Set profile value for sessions and concurrent requests:

Profile -> for users -> Initialization SQL statement - Custom

Set this to:

begin fnd_ctl.fnd_sess_ctl('FIRST_ROWS', 'ALL_ROWS', '', '', '', ''); end;

The above statement tells Oracle to run online sessions in FIRST_ROWS and concurrent requests in ALL_ROWS mode. This could be set for individual users or at the application level. Please try this out in test mode if you intend to use it, and check the Oracle Corporation Metalink site for more details. If you set this wrong, further logins will not be allowed, so be careful. You may query this setting from the backend using the following query:

select b.level_id, b.level_value, b.profile_option_id, b.profile_option_value
from   fnd_profile_options a, fnd_profile_option_values b
where  a.profile_option_name = 'FND_INIT_SQL'
and    b.profile_option_id = a.profile_option_id;

This could be reset from backend:

update fnd_profile_option_values
set    profile_option_value = null
where  profile_option_id = <profile_option_id>
and    level_id = <level_id>
and    level_value = <level_value>;

Another place where Optimizer mode could be set for a concurrent request in Oracle Applications is in the Concurrent Program definition screen. This will allow you to define mode settings at individual request programs.

SYSADMIN -> Concurrent -> Program -> Define -> Session Control.

My recommendation is to aim at achieving Nested-loop and Hash joins for online queries. Sort-Merge and Hash joins can be used for batch processing and heavy reporting tools. If your site has done customization in Oracle Applications check to see if you are generating statistics on the custom tables. Look out for Oracle recommendations for performance tuning on the Metalink site.

The direct use of the DBMS_STATS package in the Oracle Applications database is not recommended. Oracle Applications is provided with its own package, FND_STATS, for this very purpose. This package is invoked when a concurrent request (Gather Schema Statistics, Gather Table Statistics) is submitted. It can also be directly invoked from SQL*Plus.

E.g.: Generating statistics for Application schema. This is done from APPS schema login.
SQL> exec fnd_stats.gather_schema_statistics('MRP');

17. Conclusion

Start planning for the move to CBO!

Oracle is not a one-size fits all setup, it comes with a lot of features and options that should be customized and set as per the need of an organization. Oracle strongly recommends that applications should be thoroughly tested prior to moving the production box to CBO. This will help in identifying unforeseen circumstances and glitches that may be unique to your setup. I hope you enjoyed this series as much as I did writing it!

» See All Articles by Columnist Amar Kumar Padhi