From oracle-l-bounce@freelists.org Thu Jul 7 02:52:48 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j677qmeL005173 for ; Thu, 7 Jul 2005 02:52:48 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j677qiIP005161 for ; Thu, 7 Jul 2005 02:52:44 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 374C81C91C6; Thu, 7 Jul 2005 02:52:44 -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 19833-04; Thu, 7 Jul 2005 02:52:44 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id AA7DB1C947B; Thu, 7 Jul 2005 02:52:43 -0500 (EST) Message-ID: <020c01c582c8$9759c500$7b02a8c0@Primary> From: "Jonathan Lewis" To: References: <04DDF147ED3A0D42B48A48A18D574C4502361222@NT15.oneneck.corp> Subject: Re: Why the monstrous SORT? Date: Thu, 7 Jul 2005 08:50:49 +0100 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-archive-position: 22154 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jonathan@jlcomp.demon.co.uk Precedence: normal Reply-To: jonathan@jlcomp.demon.co.uk X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=-2.7 required=5.0 tests=AWL,BAYES_00,UPPERCASE_50_75 autolearn=no version=2.63 I've cut out the critical bit of the execution plan: 4768 NESTED LOOPS (OUTER) 4769 NESTED LOOPS (OUTER) ... 4334 VIEW 1127322080 SORT (GROUP BY) 237018 NESTED LOOPS **** 2 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'OE_CONTROL' 237018 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'AR_DOC_LINE' You have a nested loop outer join into a view - Oracle has had to instantiate the view because of the outer join. (8i has to do this, 9i can sometimes merge the view in these circumstances, both may be able to push join predicates). The statistics are misleading - Oracle has to instantiate the view 4,769 times because of the nested loop - and each instantiation performs a join returning 237,018 rows (line marked **** above), and those rows have to be sorted for each instantiation. 1127322080 / 237018 = 4,756 Oracle doesn't always report the right numbers in the right places. From time to time you get numbers reported which are input counts rather than output counts, sometimes you get numbers which are 'per execution' counts rather than 'for all executions' Regards Jonathan Lewis Now waiting on the publishers: Cost Based Oracle - Volume 1 http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/appearances.html Public Appearances - schedule updated June 22nd 2005 ----- Original Message ----- From: "Allen, Brandon" To: Sent: Thursday, July 07, 2005 12:29 AM Subject: Why the monstrous SORT? Hi, I've got an application query that is taking hours to run. After tracing and running tkprof, I see the execution plan below. Yes, it's a monster of a query, but Oracle seems to handle it pretty well except for the huge amount of rows being returned by the SORT (GROUP BY) step. How can it possibly have to sort so many rows (1.1 Billion!), when it is only getting 237,018 rows from the previous NESTED LOOP step? Any idea how to prevent or minimize this sort? Thanks! Brandon Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 11 MINUS 14 SORT (UNIQUE) 14 FILTER 285 SORT (GROUP BY) 4768 NESTED LOOPS (OUTER) 4769 NESTED LOOPS (OUTER) 4769 NESTED LOOPS 4769 NESTED LOOPS 285 HASH JOIN 197 VIEW OF 'AR_SALESPERSON_CODE' 197 SORT (UNIQUE) 197 SORT (GROUP BY) 197 HASH JOIN 212 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'CT_TEAM_MEMBER' 5637 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'CT_ADD_NAMES' 284 NESTED LOOPS 2 MERGE JOIN (CARTESIAN) 2 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'AR_CUSTOMER_MASTER' 2 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'AR_CUSTOMER_MASTER_PK' (UNIQUE) 2 SORT (JOIN) 1 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'OE_CONTROL' 284 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'OE_HDR' 285 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'OE_HDR_2' (NON-UNIQUE) 5052 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'OE_LINE' 5052 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'OE_LINE_PK' (UNIQUE) 9536 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'OE_SUB_ENTITY' 9536 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'HM_OE_SUB_ENTITY_UK1' (UNIQUE) 4768 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'HM_INVOICE_SUB_ENTITY_CONTROL' 9536 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'HM_INVOICE_SE_CONTROL_PK' (UNIQUE) 4334 VIEW 1127322080 SORT (GROUP BY) 237018 NESTED LOOPS 2 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'OE_CONTROL' 237018 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'AR_DOC_LINE' 3 SORT (UNIQUE) 3 FILTER 4 SORT (GROUP BY) 8 NESTED LOOPS (OUTER) 9 NESTED LOOPS (OUTER) 9 NESTED LOOPS 9 NESTED LOOPS 4 HASH JOIN 3 NESTED LOOPS 2 MERGE JOIN (CARTESIAN) 2 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'AR_CUSTOMER_MASTER' 2 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'AR_CUSTOMER_MASTER_PK' (UNIQUE) 2 SORT (JOIN) 1 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'OE_CONTROL' 3 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'OE_HDR' 285 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'OE_HDR_2' (NON-UNIQUE) 197 VIEW OF 'AR_SALESPERSON_CODE' 197 SORT (UNIQUE) 197 SORT (GROUP BY) 197 HASH JOIN 212 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'CT_TEAM_MEMBER' 5637 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'CT_ADD_NAMES' 11 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'OE_LINE' 11 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'OE_LINE_PK' (UNIQUE) 16 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'OE_SUB_ENTITY' 16 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'HM_OE_SUB_ENTITY_UK1' (UNIQUE) 8 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'HM_INVOICE_SUB_ENTITY_CONTROL' 16 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'HM_INVOICE_SE_CONTROL_PK' (UNIQUE) 0 VIEW 1891480 SORT (GROUP BY) 237018 NESTED LOOPS 2 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'OE_CONTROL' 237018 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'AR_DOC_LINE' ******************************************************************************** Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it. -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l