From oracle-l-bounce@freelists.org Wed Oct 20 21:19:15 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i9L2JFw21446 for ; Wed, 20 Oct 2004 21:19:15 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i9L2JFI21441 for ; Wed, 20 Oct 2004 21:19:15 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8835372D816; Wed, 20 Oct 2004 21:25:24 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 29885-58; Wed, 20 Oct 2004 21:25:24 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E756572D73F; Wed, 20 Oct 2004 21:25:23 -0500 (EST) Message-ID: <003601c4b715$026a3e90$350a5444@ryannew> From: "Ryan" To: , "Oracle Discussion List" References: Subject: Re: Why the optimizer is not choosing the best plan? Date: Wed, 20 Oct 2004 22:23:52 -0400 MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-archive-position: 11338 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: ryan_gaffuri@comcast.net Precedence: normal Reply-To: ryan_gaffuri@comcast.net X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org I've found that oracle only picks the wrong join type if table stats are not accurate. re-analyze your tables. check the number of records in each table vs. the row_nums column in dba_tables. see if they are close to being the same. Odds are they are not. oracle chooses join types and order based primarily on the following: 1. index on appropriate columns 2. estimate number of rows scaned 3. relative table sizes. 2 and 3 are based on stats. ----- Original Message ----- From: "Sami Seerangan" To: "Oracle Discussion List" Sent: Wednesday, October 20, 2004 3:49 PM Subject: Why the optimizer is not choosing the best plan? > Hi All, > > I have 4 tables join query(all tables statistics are upto date) that > is working fine if it goes for HASH_JOIN. But without HINT it is > always doing NL_JOIN and takes about 50 secs to execute. > > However using ORDERED hint it is going for HASH_JOIN and the response > time got reduced to less than 5 secs, but we don't prefer to implement > HINT based solution > > Why the optimizer is not choosing the best execution plan(In this case > hash_join rather than NL_join)? > > > -- > Sami Seerangan > Oracle DBA > -- > http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l