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
» 