Re: CBO/10053 help, please
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 16 Jul 2004 21:06:15 +0100
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@xxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
