From oracle-l-bounce@freelists.org Thu Jul 7 01:53:39 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j676rYjB027948 for ; Thu, 7 Jul 2005 01:53:34 -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 j676rSIP027933 for ; Thu, 7 Jul 2005 01:53:33 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 22E921C920D; Thu, 7 Jul 2005 01:53:28 -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 05075-03; Thu, 7 Jul 2005 01:53:28 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9ADA51C90A9; Thu, 7 Jul 2005 01:53:27 -0500 (EST) Message-ID: <01fb01c582c0$50a5c4e0$7b02a8c0@Primary> From: "Jonathan Lewis" To: References: <04DDF147ED3A0D42B48A48A18D574C4502361222@NT15.oneneck.corp> Subject: Re: Why the monstrous SORT? Date: Thu, 7 Jul 2005 07:51:28 +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: 22153 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.6 required=5.0 tests=AWL,BAYES_00 autolearn=ham version=2.63 I see you've printed the 'execution plan' lines from the tkprof output - can you confirm that the 'row source operation' lines show exactly the same plan. If they differ, this is the one that is telling lies. 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/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) -- http://www.freelists.org/webpage/oracle-l