Re: consistency in cost?
Interesting...CHOOSE means Oracle will decide whether to use RULE or COST. But a CHOOSE hint will force use of COST. Kind of diminishes the meaning of CHOOSE, doesn't it? Seems like a pointless hint... [EMAIL PROTECTED] 05/08/01 11:45PM If the system is Rule based and you code Hints, the CBO will be automatically invoked Sam - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, May 08, 2001 10:12 PM Forgive my ignorance but I thought hints were only for cost based optimizer. Have you checked in case anybody has changed any parameters while the system is running as I know that changing the hash_area_size can change the execution plan?. Are you using parallelism as if a table had to be recreated for any reason and its degree changed it might do a FTS? Cheers Iain Nicoll -Original Message- Sent: 08 May 2001 17:31 To: Multiple recipients of list ORACLE-L Jared, The only difference is about a weeks worth of extra data. Well, the hardware is also different (Ultra450 vs. Ultra 5000. Also 1 vs 4 CPU). But regardless, shouldn't init.ora optimizer_mode=choose be identical to optimizer_mode=rule with hint=choose? If I have the time, I'll try to set up a couple of systems and examine by moving stats and taking some 10053 dumps. (one of the ones giving me a problem is in production so I have limited play time there). Henry -Original Message- Sent: Tuesday, May 08, 2001 12:56 AM To: [EMAIL PROTECTED]; Henry Poras Henry, You say 'nearly identical'. What are the differences? Are the 2 databases on the same platform? If not, what are the differences, hardware and OS? Jared On Monday 07 May 2001 21:55, Henry Poras wrote: I am working with an 8.1.6 database on Solaris 2.6 and I am wondering if there is any consistency in the optimizer. We have two nearly identical databases (one a clone from two weeks ago). A five table join has nearly the identical execution plan on the two databases. The difference is in the access method of the fourth table in the join; in one case it is accessed by a FTS and in the other, by Index. This difference has a large effect on performance. Statistics are nearly identical for this table in both databases (I looked at dba_tables, dba_indexes, dba_col_tables). Also, the init.ora is the same. When I changed the optimizer_mode to rule and added a 'choose' hint to the query, the execution plan was different again. I will look into this a bit further and post my results. Just wondering about other's experiences. Thanks. Henry -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Henry Poras 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sam Roberts 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Sawmiller 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
Re: consistency in cost?
woo hoo! Bambi's back and we've got her! :) welcome home, you've been missed From: Bambi Bellows [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: consistency in cost? Date: Wed, 09 May 2001 14:31:01 -0800 Tim -- Oh, sure, bring LOGIC into it. Truth of the matter is that Oracle is trying to phase out the last vestiges of v5, and that's never EVER going to happen. Logic be dammed! Mwahahahaha! Bambi. --Original Message- Date: Wed, 09 May 2001 05:01:06 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Interesting...CHOOSE means Oracle will decide whether to use RULE or COST. But a CHOOSE hint will force use of COST. Kind of diminishes the meaning of CHOOSE, doesn't it? Seems like a pointless hint... [EMAIL PROTECTED] 05/08/01 11:45PM If the system is Rule based and you code Hints, the CBO will be automatically invoked Sam - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, May 08, 2001 10:12 PM Forgive my ignorance but I thought hints were only for cost based optimizer. Have you checked in case anybody has changed any parameters while the system is running as I know that changing the hash_area_size can change the execution plan?. Are you using parallelism as if a table had to be recreated for any reason and its degree changed it might do a FTS? Cheers Iain Nicoll -Original Message- Sent: 08 May 2001 17:31 To: Multiple recipients of list ORACLE-L Jared, The only difference is about a weeks worth of extra data. Well, the hardware is also different (Ultra450 vs. Ultra 5000. Also 1 vs 4 CPU). But regardless, shouldn't init.ora optimizer_mode=choose be identical to optimizer_mode=rule with hint=choose? If I have the time, I'll try to set up a couple of systems and examine by moving stats and taking some 10053 dumps. (one of the ones giving me a problem is in production so I have limited play time there). Henry -Original Message- Sent: Tuesday, May 08, 2001 12:56 AM To: [EMAIL PROTECTED]; Henry Poras Henry, You say 'nearly identical'. What are the differences? Are the 2 databases on the same platform? If not, what are the differences, hardware and OS? Jared On Monday 07 May 2001 21:55, Henry Poras wrote: I am working with an 8.1.6 database on Solaris 2.6 and I am wondering if there is any consistency in the optimizer. We have two nearly identical databases (one a clone from two weeks ago). A five table join has nearly the identical execution plan on the two databases. The difference is in the access method of the fourth table in the join; in one case it is accessed by a FTS and in the other, by Index. This difference has a large effect on performance. Statistics are nearly identical for this table in both databases (I looked at dba_tables, dba_indexes, dba_col_tables). Also, the init.ora is the same. When I changed the optimizer_mode to rule and added a 'choose' hint to the query, the execution plan was different again. I will look into this a bit further and post my results. Just wondering about other's experiences. Thanks. Henry -- Please see the official ORACLE-L FAQ: A HREF=http://www.orafaq.com; TARGET=_newFONT COLOR=BLUEhttp://www.orafaq.com/FONT/A -- Author: Henry Poras 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). -- Please see the official ORACLE-L FAQ: A HREF=http://www.orafaq.com; TARGET=_newFONT COLOR=BLUEhttp://www.orafaq.com/FONT/A -- Author: Nicoll, Iain (Calanais) 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). -- Please see the official ORACLE-L FAQ
Re: consistency in cost?
Tim -- Oh, sure, bring LOGIC into it. Truth of the matter is that Oracle is trying to phase out the last vestiges of v5, and that's never EVER going to happen. Logic be dammed! Mwahahahaha! Bambi. --Original Message- Date: Wed, 09 May 2001 05:01:06 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Interesting...CHOOSE means Oracle will decide whether to use RULE or COST. But a CHOOSE hint will force use of COST. Kind of diminishes the meaning of CHOOSE, doesn't it? Seems like a pointless hint... [EMAIL PROTECTED] 05/08/01 11:45PM If the system is Rule based and you code Hints, the CBO will be automatically invoked Sam - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, May 08, 2001 10:12 PM Forgive my ignorance but I thought hints were only for cost based optimizer. Have you checked in case anybody has changed any parameters while the system is running as I know that changing the hash_area_size can change the execution plan?. Are you using parallelism as if a table had to be recreated for any reason and its degree changed it might do a FTS? Cheers Iain Nicoll -Original Message- Sent: 08 May 2001 17:31 To: Multiple recipients of list ORACLE-L Jared, The only difference is about a weeks worth of extra data. Well, the hardware is also different (Ultra450 vs. Ultra 5000. Also 1 vs 4 CPU). But regardless, shouldn't init.ora optimizer_mode=choose be identical to optimizer_mode=rule with hint=choose? If I have the time, I'll try to set up a couple of systems and examine by moving stats and taking some 10053 dumps. (one of the ones giving me a problem is in production so I have limited play time there). Henry -Original Message- Sent: Tuesday, May 08, 2001 12:56 AM To: [EMAIL PROTECTED]; Henry Poras Henry, You say 'nearly identical'. What are the differences? Are the 2 databases on the same platform? If not, what are the differences, hardware and OS? Jared On Monday 07 May 2001 21:55, Henry Poras wrote: I am working with an 8.1.6 database on Solaris 2.6 and I am wondering if there is any consistency in the optimizer. We have two nearly identical databases (one a clone from two weeks ago). A five table join has nearly the identical execution plan on the two databases. The difference is in the access method of the fourth table in the join; in one case it is accessed by a FTS and in the other, by Index. This difference has a large effect on performance. Statistics are nearly identical for this table in both databases (I looked at dba_tables, dba_indexes, dba_col_tables). Also, the init.ora is the same. When I changed the optimizer_mode to rule and added a 'choose' hint to the query, the execution plan was different again. I will look into this a bit further and post my results. Just wondering about other's experiences. Thanks. Henry -- Please see the official ORACLE-L FAQ: A HREF=http://www.orafaq.com; TARGET=_newFONT COLOR=BLUEhttp://www.orafaq.com/FONT/A -- Author: Henry Poras 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). -- Please see the official ORACLE-L FAQ: A HREF=http://www.orafaq.com; TARGET=_newFONT COLOR=BLUEhttp://www.orafaq.com/FONT/A -- Author: Nicoll, Iain (Calanais) 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). -- Please see the official ORACLE-L FAQ: A HREF=http://www.orafaq.com; TARGET=_newFONT COLOR=BLUEhttp://www.orafaq.com/FONT/A -- Author: Sam Roberts 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,
RE: consistency in cost?
Jared, The only difference is about a weeks worth of extra data. Well, the hardware is also different (Ultra450 vs. Ultra 5000. Also 1 vs 4 CPU). But regardless, shouldn't init.ora optimizer_mode=choose be identical to optimizer_mode=rule with hint=choose? If I have the time, I'll try to set up a couple of systems and examine by moving stats and taking some 10053 dumps. (one of the ones giving me a problem is in production so I have limited play time there). Henry -Original Message- Sent: Tuesday, May 08, 2001 12:56 AM To: [EMAIL PROTECTED]; Henry Poras Henry, You say 'nearly identical'. What are the differences? Are the 2 databases on the same platform? If not, what are the differences, hardware and OS? Jared On Monday 07 May 2001 21:55, Henry Poras wrote: I am working with an 8.1.6 database on Solaris 2.6 and I am wondering if there is any consistency in the optimizer. We have two nearly identical databases (one a clone from two weeks ago). A five table join has nearly the identical execution plan on the two databases. The difference is in the access method of the fourth table in the join; in one case it is accessed by a FTS and in the other, by Index. This difference has a large effect on performance. Statistics are nearly identical for this table in both databases (I looked at dba_tables, dba_indexes, dba_col_tables). Also, the init.ora is the same. When I changed the optimizer_mode to rule and added a 'choose' hint to the query, the execution plan was different again. I will look into this a bit further and post my results. Just wondering about other's experiences. Thanks. Henry -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Henry Poras 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: consistency in cost?
Henry, I've run into the same problem in the past. Export a database to a different machine with a significantly different configuration, and you will get different execution paths. I was never successfull in getting the correct excution plans via database parameters. The solution was to use hints in the SQL to correct the problem. A TAR was opened on this problem, and Oracle support informed me of the above information. Jared On Tuesday 08 May 2001 08:32, Henry Poras wrote: Jared, The only difference is about a weeks worth of extra data. Well, the hardware is also different (Ultra450 vs. Ultra 5000. Also 1 vs 4 CPU). But regardless, shouldn't init.ora optimizer_mode=choose be identical to optimizer_mode=rule with hint=choose? If I have the time, I'll try to set up a couple of systems and examine by moving stats and taking some 10053 dumps. (one of the ones giving me a problem is in production so I have limited play time there). Henry -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 08, 2001 12:56 AM To: [EMAIL PROTECTED]; Henry Poras Subject: Re: consistency in cost? Henry, You say 'nearly identical'. What are the differences? Are the 2 databases on the same platform? If not, what are the differences, hardware and OS? Jared On Monday 07 May 2001 21:55, Henry Poras wrote: I am working with an 8.1.6 database on Solaris 2.6 and I am wondering if there is any consistency in the optimizer. We have two nearly identical databases (one a clone from two weeks ago). A five table join has nearly the identical execution plan on the two databases. The difference is in the access method of the fourth table in the join; in one case it is accessed by a FTS and in the other, by Index. This difference has a large effect on performance. Statistics are nearly identical for this table in both databases (I looked at dba_tables, dba_indexes, dba_col_tables). Also, the init.ora is the same. When I changed the optimizer_mode to rule and added a 'choose' hint to the query, the execution plan was different again. I will look into this a bit further and post my results. Just wondering about other's experiences. Thanks. Henry -- 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).
RE: consistency in cost?
Forgive my ignorance but I thought hints were only for cost based optimizer. Have you checked in case anybody has changed any parameters while the system is running as I know that changing the hash_area_size can change the execution plan?. Are you using parallelism as if a table had to be recreated for any reason and its degree changed it might do a FTS? Cheers Iain Nicoll -Original Message- Sent: 08 May 2001 17:31 To: Multiple recipients of list ORACLE-L Jared, The only difference is about a weeks worth of extra data. Well, the hardware is also different (Ultra450 vs. Ultra 5000. Also 1 vs 4 CPU). But regardless, shouldn't init.ora optimizer_mode=choose be identical to optimizer_mode=rule with hint=choose? If I have the time, I'll try to set up a couple of systems and examine by moving stats and taking some 10053 dumps. (one of the ones giving me a problem is in production so I have limited play time there). Henry -Original Message- Sent: Tuesday, May 08, 2001 12:56 AM To: [EMAIL PROTECTED]; Henry Poras Henry, You say 'nearly identical'. What are the differences? Are the 2 databases on the same platform? If not, what are the differences, hardware and OS? Jared On Monday 07 May 2001 21:55, Henry Poras wrote: I am working with an 8.1.6 database on Solaris 2.6 and I am wondering if there is any consistency in the optimizer. We have two nearly identical databases (one a clone from two weeks ago). A five table join has nearly the identical execution plan on the two databases. The difference is in the access method of the fourth table in the join; in one case it is accessed by a FTS and in the other, by Index. This difference has a large effect on performance. Statistics are nearly identical for this table in both databases (I looked at dba_tables, dba_indexes, dba_col_tables). Also, the init.ora is the same. When I changed the optimizer_mode to rule and added a 'choose' hint to the query, the execution plan was different again. I will look into this a bit further and post my results. Just wondering about other's experiences. Thanks. Henry -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Henry Poras 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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: consistency in cost?
If the system is Rule based and you code Hints, the CBO will be automatically invoked Sam - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, May 08, 2001 10:12 PM Forgive my ignorance but I thought hints were only for cost based optimizer. Have you checked in case anybody has changed any parameters while the system is running as I know that changing the hash_area_size can change the execution plan?. Are you using parallelism as if a table had to be recreated for any reason and its degree changed it might do a FTS? Cheers Iain Nicoll -Original Message- Sent: 08 May 2001 17:31 To: Multiple recipients of list ORACLE-L Jared, The only difference is about a weeks worth of extra data. Well, the hardware is also different (Ultra450 vs. Ultra 5000. Also 1 vs 4 CPU). But regardless, shouldn't init.ora optimizer_mode=choose be identical to optimizer_mode=rule with hint=choose? If I have the time, I'll try to set up a couple of systems and examine by moving stats and taking some 10053 dumps. (one of the ones giving me a problem is in production so I have limited play time there). Henry -Original Message- Sent: Tuesday, May 08, 2001 12:56 AM To: [EMAIL PROTECTED]; Henry Poras Henry, You say 'nearly identical'. What are the differences? Are the 2 databases on the same platform? If not, what are the differences, hardware and OS? Jared On Monday 07 May 2001 21:55, Henry Poras wrote: I am working with an 8.1.6 database on Solaris 2.6 and I am wondering if there is any consistency in the optimizer. We have two nearly identical databases (one a clone from two weeks ago). A five table join has nearly the identical execution plan on the two databases. The difference is in the access method of the fourth table in the join; in one case it is accessed by a FTS and in the other, by Index. This difference has a large effect on performance. Statistics are nearly identical for this table in both databases (I looked at dba_tables, dba_indexes, dba_col_tables). Also, the init.ora is the same. When I changed the optimizer_mode to rule and added a 'choose' hint to the query, the execution plan was different again. I will look into this a bit further and post my results. Just wondering about other's experiences. Thanks. Henry -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Henry Poras 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sam Roberts 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: consistency in cost?
Sam, Thanks for the answer. I am having no trouble invoking the CBO. What is confusing me is its apparent(?) inconsistency. If I EXPLAIN a query with the instance using 'CHOOSE' in init.ora I get a different plan than if I EXPLAIN the same query on the same database on the same hardware with the instance using 'RULE' but a 'CHOOSE' hint in the query. Henry -Original Message- Sent: Tuesday, May 08, 2001 11:45 PM To: Multiple recipients of list ORACLE-L If the system is Rule based and you code Hints, the CBO will be automatically invoked Sam - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, May 08, 2001 10:12 PM Forgive my ignorance but I thought hints were only for cost based optimizer. Have you checked in case anybody has changed any parameters while the system is running as I know that changing the hash_area_size can change the execution plan?. Are you using parallelism as if a table had to be recreated for any reason and its degree changed it might do a FTS? Cheers Iain Nicoll -Original Message- Sent: 08 May 2001 17:31 To: Multiple recipients of list ORACLE-L Jared, The only difference is about a weeks worth of extra data. Well, the hardware is also different (Ultra450 vs. Ultra 5000. Also 1 vs 4 CPU). But regardless, shouldn't init.ora optimizer_mode=choose be identical to optimizer_mode=rule with hint=choose? If I have the time, I'll try to set up a couple of systems and examine by moving stats and taking some 10053 dumps. (one of the ones giving me a problem is in production so I have limited play time there). Henry -Original Message- Sent: Tuesday, May 08, 2001 12:56 AM To: [EMAIL PROTECTED]; Henry Poras Henry, You say 'nearly identical'. What are the differences? Are the 2 databases on the same platform? If not, what are the differences, hardware and OS? Jared On Monday 07 May 2001 21:55, Henry Poras wrote: I am working with an 8.1.6 database on Solaris 2.6 and I am wondering if there is any consistency in the optimizer. We have two nearly identical databases (one a clone from two weeks ago). A five table join has nearly the identical execution plan on the two databases. The difference is in the access method of the fourth table in the join; in one case it is accessed by a FTS and in the other, by Index. This difference has a large effect on performance. Statistics are nearly identical for this table in both databases (I looked at dba_tables, dba_indexes, dba_col_tables). Also, the init.ora is the same. When I changed the optimizer_mode to rule and added a 'choose' hint to the query, the execution plan was different again. I will look into this a bit further and post my results. Just wondering about other's experiences. Thanks. Henry -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Henry Poras 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sam Roberts 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Henry Poras INET: [EMAIL PROTECTED] Fat City Network Services-- (858)
consistency in cost?
I am working with an 8.1.6 database on Solaris 2.6 and I am wondering if there is any consistency in the optimizer. We have two nearly identical databases (one a clone from two weeks ago). A five table join has nearly the identical execution plan on the two databases. The difference is in the access method of the fourth table in the join; in one case it is accessed by a FTS and in the other, by Index. This difference has a large effect on performance. Statistics are nearly identical for this table in both databases (I looked at dba_tables, dba_indexes, dba_col_tables). Also, the init.ora is the same. When I changed the optimizer_mode to rule and added a 'choose' hint to the query, the execution plan was different again. I will look into this a bit further and post my results. Just wondering about other's experiences. Thanks. Henry -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Henry Poras 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: consistency in cost?
Henry, You say 'nearly identical'. What are the differences? Are the 2 databases on the same platform? If not, what are the differences, hardware and OS? Jared On Monday 07 May 2001 21:55, Henry Poras wrote: I am working with an 8.1.6 database on Solaris 2.6 and I am wondering if there is any consistency in the optimizer. We have two nearly identical databases (one a clone from two weeks ago). A five table join has nearly the identical execution plan on the two databases. The difference is in the access method of the fourth table in the join; in one case it is accessed by a FTS and in the other, by Index. This difference has a large effect on performance. Statistics are nearly identical for this table in both databases (I looked at dba_tables, dba_indexes, dba_col_tables). Also, the init.ora is the same. When I changed the optimizer_mode to rule and added a 'choose' hint to the query, the execution plan was different again. I will look into this a bit further and post my results. Just wondering about other's experiences. Thanks. Henry -- 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).
