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:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Same SQL statement, Same Oracle, Different OS == Different E
Title: RE: Same SQL statement, Same Oracle, Different OS == Different Expla (see answer below) -Original Message- From: Sam Bootsma [mailto:[EMAIL PROTECTED]] 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
RE: Same SQL statement, Same Oracle, Different OS == Different E
Title: 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:[EMAIL PROTECTED]]Sent: Friday, September 06, 2002 3:28 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Same SQL statement, Same Oracle, Different OS == Different E (see answer below) -Original Message- From: Sam Bootsma [mailto:[EMAIL PROTECTED]] 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
Re: Same SQL statement, Same Oracle, Different OS == Different E
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:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ] 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: Jared Still INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
