Re: CBO/10053 help, please

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 16 Jul 2004 21:06:15 +0100
Message-ID: <001601c46b70$595c8000$7102a8c0@Primary>

Are you sure the init.ora parameters are the same ? It looks as if the _unnest_subquery parameter has been set to false in the second plan.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated July 14th

Original Message ----- From: <DEEDSD_at_Nationwide.com> To: <oracle-l_at_freelists.org> Sent: Friday, July 16, 2004 8:56 PM Subject: CBO/10053 help, please

I have a query that runs on two different databases, production and a copy of production. The init.ora parameters are the same. I'm not sure whether the tables were analyzed or dbms_statted. Production runs very nicely with two full scans of the ppay_sections table and a hash join. Development takes forever and does index scan of the primary key and then full scans the ppay_sections table and then filters.

I've noticed the density is dramatically different between the two 10053 traces. What I don't understand is why the development CBO is picking BEST_CST at 575.00 when the tsc is 24, especially since it is joining the same table to itself and it chooses an FTS later in the plan. The production trace also has column statistics, where the development trace does not. Oracle 9.2.0.4 EE on Solaris. Any ideas? I'm stumped.

The production trace:

2004-07-16 14:21:06.527 QUERY Select case_seq_id from ppay_sections where section_id = 'PP' and case_seq_id not in (select case_seq_id from ppay_sections where section_id = 'PS')

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Fri Jul 16 2004 - 15:02:53 CDT