From oracle-l-bounce@freelists.org Fri Oct 1 13:55:11 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i91ItBG14502 for ; Fri, 1 Oct 2004 13:55:11 -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 i91ItBI14497 for ; Fri, 1 Oct 2004 13:55:11 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C9C8972CC2C; Fri, 1 Oct 2004 14:01:14 -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 06524-75; Fri, 1 Oct 2004 14:01:14 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3E52472CC1E; Fri, 1 Oct 2004 14:01:14 -0500 (EST) Message-ID: From: Freeman Robert - IL To: "''oracle-l@freelists.org ' '" Subject: Optimizer Choices - Part Two Date: Fri, 1 Oct 2004 13:59:37 -0500 MIME-Version: 1.0 x-iHateSpam-Checked: Checked by Spam Filter Service at 9/30/2004 4:59:25 PM X-MimeOLE: Checked by Spam Filter Service at 9/30/2004 4:59:25 PM Content-Class: Checked by Spam Filter Service at 9/30/2004 4:59:25 PM X-MS-Has-Attach: Checked by Spam Filter Service at 9/30/2004 4:59:25 PM Content-type: text/plain Content-Transfer-Encoding: 8bit X-archive-position: 10579 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: FREEMANR@tusc.com Precedence: normal Reply-To: FREEMANR@tusc.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Good afternoon folks... Continuing our discussion on the optimizer from yesterday. To review: I have the following statement: select a.empid, a.ename, b.dname from emp a, dept b where a.deptno=b.deptno and a.empid < 1000 And the optimizer chooses this plan (this is from the 10046 for that statement): STAT #1 id=1 cnt=999 pid=0 pos=1 obj=0 op='HASH JOIN (cr=1607 r=0 w=0 time=277989 us)' STAT #1 id=2 cnt=1 pid=1 pos=1 obj=71010 op='TABLE ACCESS FULL OBJ#(71010) (cr=3 r=0 w=0 time=104 us)' STAT #1 id=3 cnt=999 pid=1 pos=2 obj=71009 op='TABLE ACCESS FULL OBJ#(71009) (cr=1604 r=0 w=0 time=274428 us)' and I get these run stats (from autotrace): 0 db block gets 1607 consistent gets 0 physical reads 999 rows processed I change the statement thusly: select /*+ ORDERED */ a.empid, a.ename, b.dname from emp a, dept b where a.deptno=b.deptno and a.empid < 1000; I get this plan (from the 10046): STAT #1 id=1 cnt=999 pid=0 pos=1 obj=0 op='HASH JOIN (cr=1540 r=1423 w=0 time=2595993 us)' STAT #1 id=2 cnt=999 pid=1 pos=1 obj=71009 op='TABLE ACCESS FULL OBJ#(71009) (cr=1537 r=1422 w=0 time=2565364 us)' STAT #1 id=3 cnt=1 pid=1 pos=2 obj=71010 op='TABLE ACCESS FULL OBJ#(71010) (cr=3 r=1 w=0 time=26645 us)' and these stats: 0 db block gets 1540 consistent gets 0 physical reads 999 rows processed The question was, why didn't Oracle choose the plan with the smaller numbers of IO's. It was suggested that I send the 10046 and 10053 traces of each of these plans, which are attached to this message. It was suggested that the execution plan from autotrace and explain plan was incorrect. The plans you see above are from the 10046 trace file. The V$SQL_PLAN results are the same as well. Several folks indicated that the hash join will always take the smallest table as the has join, even dynamically switching the table at run time. Looking at the 10053, I think this is NOT the case if an /*+ ORDERED */ hint is used. Based on the 10053 traces, it seems to me that if an ORDERED hint is used, that Oracle does not dynamically change the join order for a hash join. I say this because of this line in the 10053 trace files: For join with no hint: Hash join one ptn Resc: 1 Deg: 1 (sides swapped) I'm guessing that (sides swapped) indicates a re-ordering of the join. In BOTH cases, the join with the ORDERED hint does not have the (sides swapped) syntax for this line (but the rest of the Hash join line is there). If oracle were to dynamically swap the join order, I would expect to see (sides swapped) in one of the ORDERED plans. Anyone have any thoughts on this? I appologize if I'm missing something basic here.... please help me understand why the optimizer is choosing a plan that, to me, seems less efficient. RF -- http://www.freelists.org/webpage/oracle-l