From oracle-l-bounce@freelists.org Thu Sep 8 09:43:16 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j88EhEvI002867 for ; Thu, 8 Sep 2005 09:43:14 -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 j88Eh9IP002847 for ; Thu, 8 Sep 2005 09:43:09 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 86D2F1EB045; Thu, 8 Sep 2005 09:43:03 -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 11328-07; Thu, 8 Sep 2005 09:43:03 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0A4F51EB1B1; Thu, 8 Sep 2005 09:43:02 -0500 (EST) Content-Class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis X-MimeOLE: Produced By Microsoft Exchange V6.0.6603.0 Subject: RE: Temporary space Date: Thu, 8 Sep 2005 15:40:53 +0100 Message-ID: <1C6E45ADB2EC324F9553E468ABFE0F63030F0E3A@UKWMXM04> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Temporary space Thread-Index: AcW0e/9s69sV7wY9Q5Sxyvl26BbwmAAB0eqw From: "Hallas, John, Tech Dev" To: X-OriginalArrivalTime: 08 Sep 2005 14:40:53.0604 (UTC) FILETIME=[509BF240:01C5B483] X-archive-position: 25161 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: John.Hallas@gb.vodafone.co.uk Precedence: normal Reply-To: John.Hallas@gb.vodafone.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=-3.9 required=5.0 tests=AWL,BAYES_00 autolearn=ham version=2.63 I don't know the answer Enrique. But an educated guess might be to use the v$temp_space_header_view. Take a sample couple of your jobs and monitor that view to see what space I used (non parallel mode). Then re-run the jobs using parallel 2,4,8 (if you have time) and try and quantify what the ratio differences are. When you find out let the list know. My guess is that there will not be a substantial difference apart from some overhead (I am guessing no more than 10% increase in space utilisation when using degree=8) HTH John -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Enrique Fernandez-Pampillon Sent: 08 September 2005 14:47 To: oracle-l@freelists.org Subject: Temporary space Does anyone know where to find information related to how increase the necessary temporary space when I increase the parallel degree? We have a large data warehouse (20Tb). and lately some ORA-01652 have raised when we execute some insert /*+ append */ select. I have decrease the parallel_query, parallel_dml and parallel_ddl parameters and the insert /*+ append */ takes a long time but it works. Thank you very much -------------------------------------------------- Enrique -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l