Re: CBO/10053 help, please

From: <DEEDSD_at_Nationwide.com>
Date: Fri, 16 Jul 2004 22:57:06 -0400
Message-ID: <OFB88FD7E4.32E857FE-ON85256ED4.000FB99C@lnotes-gw.ent.nwie.net>

I compared the 'parameters used by the optimizer' section line by line in side-by-side windows earlier. Since you noticed that right off the bat I figured I was staring at it too long and probably missed it. However, I just re-checked both trace files, and _UNNEST_SUBQUERY is set to true in both databases.

                                                                                                                          
                          "Jonathan Lewis"                                                                                
                          <jonathan_at_jlcomp.demon.  T                                                                      
                          co.uk>                   To:   <oracle-l_at_freelists.org>                                         
                                                   cc:                                                                    
                          Sent by:                                                                                        
                          oracle-l-bounce_at_freelis  bcc:                                                                   
                          ts.org                   Subject:                                          Re: CBO/10053 help,  
                                                   please                                                                 
                                                                                                                          
                                                                                                                          
                          07/16/04 04:06 PM                                                                               
                          Please respond to                                                                               
                          oracle-l

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

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

Received on Fri Jul 16 2004 - 21:53:58 CDT