From oracle-l-bounce@freelists.org Thu May 27 05:33:39 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i4RAXOm01819 for ; Thu, 27 May 2004 05:33:34 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i4RAXE601776 for ; Thu, 27 May 2004 05:33:24 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6520772C88C; Thu, 27 May 2004 05:20:51 -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 32228-01; Thu, 27 May 2004 05:20:51 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 86A7F72C89A; Thu, 27 May 2004 05:20:50 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 27 May 2004 05:19:34 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E0E7872C851 for ; Thu, 27 May 2004 05:19:33 -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 30923-72 for ; Thu, 27 May 2004 05:19:33 -0500 (EST) Received: from smtp802.mail.ukl.yahoo.com (smtp802.mail.ukl.yahoo.com [217.12.12.139]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id 3D40172C845 for ; Thu, 27 May 2004 05:19:33 -0500 (EST) Received: from unknown (HELO Primary) (oracle-l@freelists.org@217.43.124.103 with poptime) by smtp802.mail.ukl.yahoo.com with SMTP; 27 May 2004 10:35:11 -0000 Message-ID: <01a001c443d6$4d056cc0$7102a8c0@Primary> From: "Jonathan Lewis" To: References: <07BA8175B092D611B1DE00B0D049A31501B0B819@exchange.ad.starkinvestments.com> Subject: Re: 4031 - errors Date: Thu, 27 May 2004 11:35:13 +0100 MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1409 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 1364 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: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org It is quite possible that the 'cursor_space_for_time' setting is relevant. When set to true, this pins cursor run-time memory into the library cache (faking the situation of every cursor in the cache being open and active. Since this memory can no longer be freed, you can easily run out of memory if you have made a small error in your estimate of number of sessions and number of different sql statements. Bringing a new module into the system, adding a new user to the system, allowing someone to run ad hoc SQL, adding a monitoring tool - anything which increases the number of SQL statements, or number of sessions executing shared sql statements, could take you into 4031. On the other hand, is could just be a bug where Oracle is generating lots of copies of cursors that should be shared but aren't. I've just come back from a site where there were several hundred copies of some cursors visible in v$sql - after only a handful of invalidations or reloads - and only a few dozen active sessions. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st ----- Original Message ----- From: "Duret, Kathy" To: Sent: Wednesday, May 26, 2004 8:53 PM Subject: 4031 - errors Had a strange problems on Friday afternoon. We are on 8.1.7.4 Solaris Had a ton of Bam - ora_04031 errors. Tried to flush the shared pool and it would release some space but a large chuck was not being released and this was strange since we have only acouple of objects pinned. When I tried to pin an object that was failing right after I would flush the shared_pool I couldn't get space. Nothing new was put in that week as far as I know. Put in a tar with Oracle and they suggested to bounce the database - which I did later, increased the shared_pool and session_cached_cursors We have pinned some more objects that are being loaded alot. I looked at the sql area and nothing looked horrible, all the same normal stuff. In fact there was really no load on the database or machine, no swapping or paging. It was very strange. It looked like "something" had memory and wasn't releasing it. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------