Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Same SQL statement, Same Oracle, Different OS ==> Different E

RE: Same SQL statement, Same Oracle, Different OS ==> Different E

From: Toepke, Kevin M <ktoepke_at_trilegiant.com>
Date: Fri, 06 Sep 2002 12:43:25 -0800
Message-ID: <F001.004CA4DE.20020906124325@fatcity.com>




RE: Same SQL statement, Same Oracle, Different OS ==> Different Expla



I the RBO, the order the indexes were created in is important! I was able to show this to management on a project I was on. How? By doing a difinitive proof (follows)
 
Import the table and data into an empty database.
    Create index A
    Create index B
    EXPLAIN PLAN shows query using index A.
Drop table
Import the table and data into an empty database
    Create index B
    Create index A
    EXPLAIN PLAN shows query using index B.
Drop table
Import the table and data into an empty database
    Create index A
    Create index B
    EXPLAIN PLAN shows query using index A.
 
All other things being equal, the RBO will choose the index with the lower object_id!
 
Proof took place in Oracle 8.0.5 on a Sun Solaris box.
Kevin
-----Original Message-----
From: Jacques Kilchoer [mailto:Jacques.Kilchoer@quest.com]
Sent: Friday, September 06, 2002 3:28 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Same SQL statement, Same Oracle, Different OS ==> Different E

(see answer below)

> -----Original Message-----
> From: Sam Bootsma [mailto:SamB@cpas.com]
>
> One of our developers is encountering a situation where
> Oracle 9.0.x explain
> plan chooses one index when on UNIX, and chooses a different
> index when
> running on Windows NT.  I'd appreciate any insights or
> similar experiences.
>
> The following are the facts:
> 1. The explain plan is run against the same SELECT statement on both
> platforms
> 2. It has been confirmed that there are no statistics on either of the
> databases
> 3. Both databases are using RBO (not CBO)
> 4. The UNIX database has about 100 times as many rows (in
> this table) as the
> NT database
> 5. The SELECT statement that gives different explain plans on
> different
> platforms is:
>
> SELECT FN.*, FN.ROWID
> FROM UNITFUND FN
> WHERE FN.FU_CODE  = :cFuCode AND
> FN.MKEY = :cMkey AND
> FN.CLNT = :sKey AND
> FN.PLANNO = :sKey AND
> FN.DATE_FROM <= :dDate AND
> FN.SOURCE = :cSource AND
> FN.TSTATUS  =  'O'
> ORDER BY FN.DATE_FROM, FN.TSECOND;
>
> 6. Between the following 2 indexes, Oracle 9.0x chooses (2)
> on Unix and (1)
> on Windows NT.
>
> 1) clnt, mkey, planno, fu_code, date_from, source, tracode, tsecond...
> 2) date_from, clnt, planno, mkey, fu_code


Just a wild guess, but maybe the optimizer is just picking the first index it finds because it thinks both are equally good candidates. Were both indexes created in the same order on both databases?

I.e. is Object_id (from dba_objects) for Index A smaller than object_id for Index B on the UNIX database, but the reverse is true on Windows?

You say the databases are using RBO. How do you know? Remember that if you use some new features
(from the manual: Partitioned tables and indexes
Index-organized tables
Reverse key indexes
Function-based indexes
SAMPLE clauses in a SELECT statement
Parallel query and parallel DML
Star transformations and star joins
Extensible optimizer
Query rewrite with materialized views
Enterprise Manager progress meter
Hash joins
Bitmap indexes and bitmap join indexes
Index skip scans )
the query optimizer will use CBO because new features are not supported by RBO.

Finally, this section of the manual may help you guess what's happening:
Oracle9i Database Performance Guide and Reference
Part Number A87503-02
Chapter 8
Using the Rule-Based Optimizer
...
Understanding Access Paths for the RBO


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Toepke, Kevin M
  INET: ktoepke_at_trilegiant.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Fri Sep 06 2002 - 15:43:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US