Different execution plans on two identical schema's with the RBO

Yes, I know. We should all be using the CBO. Since oracle version 7.3, or at least since version 8.1.7.

But sometimes it isn’t used. It’s sometimes even troublesome to see the ignorance of the oracle DBA documentation and tuning tips. Sometimes we just have to work with what we get…
(It helps us in our daily whining about bad applications 🙂

But now the case:
-Oracle database version 9.2.0.5 (EE)
-RBO (optimizer mode CHOOSE, no statistics)

Two schema’s:
-‘A’
-‘B’

We got a table in both schema’s: MD_REQUESTS:

MD_REQUEST_ID number(10) not null
CONNECT_EAN varchar2(18) null
REQ_PARTY_EAN varchar2(13) not null
MD_BATCH_ID number(10) null
REASON_ID varchar2(3) null
REQUEST_DATE date(7) null
STATUS_GROUP_ID number(2) null
REQUEST_REJECTED number(1) null
FILTER_DATE date(7) null
REQ_PARTY_REF varchar2(35) null
PROVIDER_EAN varchar2(13) null
PARENT_REQUEST_ID number(10) null

Indexes in schema ‘A’:

PK_MDR md_request unique
MDR_IDX_1 provider_ean,status_group_id,md_request_id unique
MDR_IDX_2 req_party_ean,status_group_id,md_request_id unique
MDR_IDX_3 req_party_ean,md_batch_id,md_request_id unique
MDR_IDX_4 parent_request_id,md_request_id unique
MDR_IDX_5 md_batch_id nonunique
MDR_IDX_6 connect_ean,provider_ean nonunique

Indexes in schema ‘B’:

PK_MDR md_request unique
MDR_IDX_1 provider_ean,status_group_id,md_request_id unique
MDR_IDX_2 req_party_ean,status_group_id,md_request_id unique
MDR_IDX_3 req_party_ean,md_batch_id,md_request_id unique
MDR_IDX_4 parent_request_id,md_request_id unique
MDR_IDX_5 md_batch_id nonunique
MDR_IDX_6 connect_ean,provider_ean nonunique

Now we execute the following query:

SELECT ''
FROM A.md_requests mr
WHERE mr.parent_request_id = 90109886
AND mr.req_party_ean = '8716665189995'
AND mr.md_batch_id IS NOT NULL

Which results in the following execution plan:

Execution Plan
-----------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MD_REQUESTS'
2 1 INDEX (RANGE SCAN) OF 'MDR_IDX_3' (UNIQUE)

(1269512 consistent gets/141903 physical reads)

And we execute:

SELECT ''
FROM B.md_requests mr
WHERE mr.parent_request_id = 90109886
AND mr.req_party_ean = '8716665189995'
AND mr.md_batch_id IS NOT NULL

Which results in the following execution plan:

Execution Plan
-----------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MD_REQUESTS'
2 1 INDEX (RANGE SCAN) OF 'MDR_IDX_4' (UNIQUE)

(4 consistent gets/0 physical reads)

Obviously, the second one (schema B, using index MDR_IDX_4) is more efficient.
If I put a hint ( SELECT /*+ INDEX( mr,mdr_idx_4 ) */ ”) in the statement in schema A, it also runs efficient (5 consistent gets/1 physical read)

So, why does the RBO choose a different execution plan?

The RBO uses rules to make the execution plan, rather than the CBO, which calculates cost.

So, following that argument, there must be something that makes the RBO fullfill its need. This is what came up:


select o.obj#, u.name, o.name
from sys.obj$ o, sys.user$ u
where o.owner# = u.user#
and o.type = 1
and o.name like 'MDR_IDX_%'
order by 2,1

Which gave the following output:


OBJ# NAME NAME
---------------------- -------------------------------- ----------------------
17424 A MDR_IDX_4
17489 A MDR_IDX_5
17490 A MDR_IDX_6
17491 A MDR_IDX_1
17493 A MDR_IDX_2
17495 A MDR_IDX_3

17424 B MDR_IDX_1
17425 B MDR_IDX_2
17426 B MDR_IDX_3
17737 B MDR_IDX_4
17738 B MDR_IDX_5
17740 B MDR_IDX_6

The RBO scans possible indexes using the object id (obj#) from the highest number to the lowest number, and uses the first one in that order that fullfills its need!

This means that the order in which indexes are created (strongly) influence the RBO execution path!

Advertisements
3 comments
  1. execution plan instability with RBO – that’s something new. 🙂

  2. Administrator said:

    Yep, that’s the reason I posted this.

  3. Yuri said:

    Already ran into this some time ago.

    When doing exp/imp to a different database the creation times and object numbers change. A good reasons to clone you’re database when doing performance tests. (Get an exact copy!).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: