SIEBEL PERFORMANCE RBO to CBO
- From: <joseph.m.coffey@xxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 4 Feb 2007 09:48:15 -0600
Paula,
In addition to the advice already provided, I can offer the following:
1. I assume that you are on Siebel 7.8 or higher with Oracle 9i or
higher. Lower versions of either are only supported in RBO.
2. Siebel was very well tuned to run in the RBO. Its vanilla index
structure and recommended instance settings (almost) always resulted in
the right plan for the queries it generates. Sometimes "performance
issues" arise after an upgrade because things that used to be
un-necessary with the RBO are now very important with the CBO - such as
properly generating statistics.
3. In that sense, treat this as an Oracle instance with a schema
that someone else created - not as a black box provided by Siebel. Use
statspack, trace sessions, capture the SQL and run it via command line
in a test instance to determine what the "right path is" and experiment
with session parameters that way. Don't forget the various alter
session statements that Siebel runs at the beginning of each session.
4. Most problems can be fixed by creating or dropping indexes.
This was true with the RBO and is true now. Some indexes are present
for specific application screens that most sites do not use.
5. We have found inappropriate plans that we fixed by adjusting
OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_MAX_PERMUTATIONS. The settings
vary and you should test them yourself.
6. We have also found what I call the "FIRST_ROWS10 rock and a hard
place." Siebel sessions are altered to use FIRST_ROWS10 - apparently to
help out with "sorted all views" and "pop up applets" in the vanilla
application that provide the user a list of all of a particular business
component (contacts, accounts, addresses, etc.) in the system. The
wisdom of this application design aside, this amounts to an order by on
some highly select columns with some filters on not very selective
columns. FIRST_ROWS10 speeds this condition up considerably.
Unfortunately, it also slows down the similar query run later with a
highly selective query - such as one that was executed during the login
process on version 7.7... We have also used stored outlines to address
this problem.
7. This is not to say that stored outlines are always the answer.
If you have a strong DBA capability and sufficient time to test the
impact on the rest of the application, then you "should" be able to
solve this by managing statistics and optimizer settings.
Bottom line - there is no "silver bullet" setting you can take from the
list aside from that "others have successfully made the transition."
Just treat this like any other performance problem.
Joe
This message is for the designated recipient only and may contain privileged,
proprietary, or otherwise private information. If you have received it in
error, please notify the sender immediately and delete the original. Any other
use of the email by you is prohibited.
Other related posts:
