From oracle-l-bounce@freelists.org Tue Aug 2 13:40:40 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j72IeeQx012174 for ; Tue, 2 Aug 2005 13:40:40 -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 j72IecIP012164 for ; Tue, 2 Aug 2005 13:40:39 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 52E811DBE10; Tue, 2 Aug 2005 13:39: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 12856-05; Tue, 2 Aug 2005 13:39:44 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id CEAC21DBD22; Tue, 2 Aug 2005 13:39:43 -0500 (EST) From: ryan_gaffuri@comcast.net To: ldutra@toyota.com.br, Oracle-L@freelists.org Cc: "Leandro Guimaraes Faria C. Dutra" Subject: Re: PL/SQL memory usage? Date: Tue, 02 Aug 2005 18:37:50 +0000 Message-Id: <080220051837.22055.42EFBD7E00020837000056272200762194079D9A00000E09A1020E979D@comcast.net> X-Authenticated-Sender: cnlhbl9nYWZmdXJpQGNvbWNhc3QubmV0 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="NextPart_Webmail_9m3u9jl4l_22055_1123007870_0" X-archive-position: 23193 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: ryan_gaffuri@comcast.net Precedence: normal Reply-To: ryan_gaffuri@comcast.net 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.0 required=5.0 tests=AWL,BAYES_00,HTML_MESSAGE, MIME_BOUND_NEXTPART,MIME_HTML_NO_CHARSET,NO_REAL_NAME autolearn=no version=2.63 --NextPart_Webmail_9m3u9jl4l_22055_1123007870_0 Content-Type: text/plain Content-Transfer-Encoding: 8bit memory allocation for pl/sql tables is unbounced. you can make your arrays so big that you use up so much memory on a server that you cannot make a SSH connection to the server and cannot connect to sqlplus. A guy on a previous project of mine did a large unbounced bulk collect and we had to hard kill oracle(we already had connections the server) from the OS level. not sure where it gets stored, but it will keep taking free memory from your system. i dont think storing all this in an array would make that much of a difference. The best way to i mprove performance of this kind of thing is to look at the algorithm and see if there is a better way to do it and to see if you can parallelize your steps. For example, can you open up some oracle jobs and run 3 things at the same time. --NextPart_Webmail_9m3u9jl4l_22055_1123007870_0 Content-Type: text/html Content-Transfer-Encoding: 8bit

memory allocation for pl/sql tables is unbounced. you can make your arrays so big that you use up so much memory on a server that you cannot make a SSH connection to the server and cannot connect to sqlplus.

A guy on a previous project of mine did a large unbounced bulk collect and we had to hard kill oracle(we already had connections the server) from the OS level.

not sure where it gets stored, but it will keep taking free memory from your system.

i dont think storing all this in an array would make that much of a difference. The best way to i mprove performance of this kind of thing is to look at the algorithm and see if there is a better way to do it and to see if you can parallelize your steps. For example, can you open up some oracle jobs and run 3 things at the same time.

--NextPart_Webmail_9m3u9jl4l_22055_1123007870_0-- -- http://www.freelists.org/webpage/oracle-l