From oracle-l-bounce@freelists.org Thu Aug 4 03:49:08 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j748n8DV025611 for ; Thu, 4 Aug 2005 03:49:08 -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 j748n4IP025599 for ; Thu, 4 Aug 2005 03:49:04 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5AEFB1DBEAE; Thu, 4 Aug 2005 03:48:59 -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 19116-07; Thu, 4 Aug 2005 03:48:59 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id CB08C1DBE07; Thu, 4 Aug 2005 03:48:58 -0500 (EST) From: "Lex de Haan" To: , Subject: RE: Buffer Sort explanation Date: Thu, 4 Aug 2005 10:47:06 +0200 Organization: Natural Join BV MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_0031_01C598E1.DBA45250" In-Reply-To: <20050803213035.60093.qmail@web52813.mail.yahoo.com> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 thread-index: AcWYcssJtTFJHTERR3SRrjneN1OjIwAXb7Bg Message-Id: <20050804084707.72A7D1DBBE6@turing.freelists.org> X-archive-position: 23349 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: lex.de.haan@naturaljoin.nl Precedence: normal Reply-To: lex.de.haan@naturaljoin.nl 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=-3.1 required=5.0 tests=AWL,BAYES_00 autolearn=ham version=2.63 ------=_NextPart_000_0031_01C598E1.DBA45250 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit a BUFFER SORT typically means that Oracle reads data blocks into private memory, because the block will be accessed multiple times in the context of the SQL statement execution. in other words, Oracle sacrifies some extra memory to reduce the overhead of accessing blocks multiple times in shared memory. this has nothing to do with sorting ... additions/corrections welcome, kind regards, Lex. ------------------------------------------------------------------ Steve Adams Seminar http://www.naturaljoin.nl/events/seminars.html ------------------------------------------------------------------ -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Deepak Sharma Sent: Wednesday, August 03, 2005 23:31 To: oracle-l@freelists.org Subject: Buffer Sort explanation What does the 'BUFFER SORT' step mean and how does it get calculated, say in below example? It is out of a tkprof output of a star transformation query. 105 BITMAP MERGE 351549 BITMAP KEY ITERATION 127009880 BUFFER SORT 1607720 TABLE ACCESS FULL SYS_TEMP_4254956840 351549 BITMAP INDEX RANGE SCAN OBJ#(3441108) PARTITION: 1 177 (object id 3441108) __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- http://www.freelists.org/webpage/oracle-l ------=_NextPart_000_0031_01C598E1.DBA45250 Content-Type: text/x-vcard; name="Lex de Haan.vcf" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="Lex de Haan.vcf" BEGIN:VCARD VERSION:2.1 N:de Haan;Lex FN:Lex de Haan ORG:Natural Join B.V. TEL;WORK;VOICE:+31.30.2515022 TEL;HOME;VOICE:+31.30.2518795 TEL;CELL;VOICE:+31.62.2955714 TEL;WORK;FAX:+31.30.2523366 ADR;WORK:;;Pieter Breughelstraat 10;Utrecht;;3583 SK;Netherlands LABEL;WORK;ENCODING=3DQUOTED-PRINTABLE:Pieter Breughelstraat = 10=3D0D=3D0AUtrecht 3583 SK=3D0D=3D0ANetherlands URL;WORK:http://www.naturaljoin.nl EMAIL;PREF;INTERNET:lex.de.haan@naturaljoin.nl REV:20040224T160439Z END:VCARD ------=_NextPart_000_0031_01C598E1.DBA45250-- -- http://www.freelists.org/webpage/oracle-l