Re: RBO changes plan from 8i to 9i
- From: "Sergey Popov" <sspopov@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 12 Jul 2006 09:19:29 -0400
Hi,
I also got Siebel on my immediate responsibility list a couple of months
ago. The DB is already on 9i and I'm trying to convince app owner to try CBO
instead of RBO in test.
Were you able to confirm that RBO is the only supported optimizer for
Siebel? The only note I was able to find so far on this is "Siebel Server
Installation Guide fo Microsoft Windows version 7.5.3". Over on page 307 it
says:
------------------------
NOTE: Siebel optimizes and certifies Siebel applications on a standard
Oracle installation using Rule-Based Optimizer (RBO). Using advanced
features like Cost-Based Optimizer (CBO) and table partitioning are not
supported and could have a detrimental effect on query performance.
If there is a reason to believe that the issues are caused by CBO, table
partitioning, or other database features that are not currently supported,
Siebel Support will request that you reproduce issues against a standard
installation using RBO.
--------------------------
With CBO making much better decisions on execution plan I am not really sure
I want to talk to Siebel side of Oracle about performance of their
application at all.
As far as your execution plan changes I would agree with Alex. Optimizer did
not switch over to a new index but changed the order of the steps.
Sergey
On 7/12/06, amonte <ax.mount@xxxxxxxxx> wrote:
Hi all
I am migrating a Siebel 7.x CRM database from 8i to 9i under HPUX. This
version of Siebel only supports RBO therefore we will be using RBO in 9i as
well.
I noticed that many execution plan changed when migrated to 9i. I thought
RBO is not affected by init.ora parameters, version changes and its
development stopped ages ago.
Any clues?
Most change are like this:
ORIGINAL:
-----------------
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 SORT (ORDER BY)
2 1 NESTED LOOPS
3 2 INDEX (RANGE SCAN) OF 'S_ESCL_RULE_M1' (UNIQUE)
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'S_ESCL_STATE'
5 4 INDEX (RANGE SCAN) OF 'S_ESCL_STATE_M1' (NON-UNIQUE)
9i:
--------------------
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 SORT (ORDER BY)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'S_ESCL_STATE'
3 2 NESTED LOOPS
4 3 INDEX (RANGE SCAN) OF 'S_ESCL_RULE_M1' (UNIQUE)
5 3 INDEX (RANGE SCAN) OF 'S_ESCL_STATE_M1' (NON-UNIQUE)
tia
Other related posts:
