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

I don't have an explanation, but:

The CBO uses floating point instructions a lot (I think). May be there is a difference in rounding between the Intel chip and the Sparc chip that causes the cost to differ just enough so that the index isn't/is used.

Anjo.

On Saturday 07 September 2002 01:48, you wrote:
> Interesting. This might explain a similar problem
> I had a few years ago. Oracle support did not
> have a good answer for it.
>
> Jared
>
> On Friday 06 September 2002 13:43, Toepke, Kevin M wrote:
> > 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-----
> > Sent: Friday, September 06, 2002 3:28 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> >
> > (see answer below)
> >
> > > -----Original Message-----
> > > From: Sam Bootsma [ mailto:SamB_at_cpas.com <mailto:SamB_at_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
>
> ----------------------------------------
> Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
> Content-Transfer-Encoding: 7bit
> Content-Description:
> ----------------------------------------

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Anjo Kolk
  INET: anjo_at_oraperf.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).