From oracle-l-bounce@freelists.org Wed Feb 25 17:35:58 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i1PNZw618686 for ; Wed, 25 Feb 2004 17:35:58 -0600 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 i1PNZwo18680 for ; Wed, 25 Feb 2004 17:35:58 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0F04D394EC1; Wed, 25 Feb 2004 18:37:24 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 25 Feb 2004 18:36:21 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from smtp2.corpsys.csedge.com (unknown [12.28.86.142]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 30497394DCC for ; Wed, 25 Feb 2004 18:36:17 -0500 (EST) Received: from CSMAIL.corpsys.csedge.com ([172.21.100.103]) by smtp2 with InterScan Messaging Security Suite; Wed, 25 Feb 2004 17:37:31 -0600 Received: by csmail.corpsys.csedge.com with Internet Mail Service (5.5.2653.19) id <19GPKCKB>; Wed, 25 Feb 2004 17:39:40 -0600 Message-ID: <0C3690346404A94CBF4DDDD4724FC61409BB6192@csmail.corpsys.csedge.com> From: Bob Lofstrand To: oracle-l@freelists.org Subject: Determining library_cache usage Date: Wed, 25 Feb 2004 17:39:39 -0600 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C3FBF8.A27147E0" X-archive-position: 2018 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: blofstrand@csedge.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l ------_=_NextPart_001_01C3FBF8.A27147E0 Content-Type: text/plain; charset="iso-8859-1" We get this error: ORA-04031: unable o allocate 40048 bytes of shared memory ("shared pool","SELECT : SASOFPD.PD_DT, : ...","sqa area","text : kpp_extend") :-4031 when trying to run a monstrous 26.1kb query. Yes that is the amount of text. The one query involves 9 case statements, some nested in others and several involving more than 50 when clauses. It also has 8 sum(), over 200 to_date() and a group by clause... Don't ask. My guess is that we are running out of library_cache when this beast is parsed out. Sound reasonable? Also, does anyone know of a way to determine how much library_cache/shared_pool a session is using. -------------------------------------- Bob Lofstrand mailto:blofstrand@csedge.com Oracle Database Administrator www.csedge.com Corporate Systems Inc. 806-337-3906 "There is no silver bullet." -Frederick P. Brooks. The Mythical Man-Month. The information contained in this communication, including attachments, is strictly confidential and for the intended use of the addressee only; it may also contain proprietary, price sensitive, or legally privileged information. Notice is hereby given that any disclosure, distribution, dissemination, use, or copying of the information by anyone other than the intended recipient is strictly prohibited and may be illegal. If you have received this communication in error, please notify the sender immediately by reply e-mail, delete this communication, and destroy all copies. Corporate Systems, Inc. has taken reasonable precautions to ensure that any attachment to this e-mail has been swept for viruses. We specifically disclaim all liability and will accept no responsibility for damage sustained as a result of software viruses and advise you to carry out your own virus checks before opening any attachment. ------_=_NextPart_001_01C3FBF8.A27147E0 Content-Type: text/html; charset="iso-8859-1"

We get this error:

ORA-04031: unable o allocate 40048 bytes of shared memory ("shared
pool","SELECT : SASOFPD.PD_DT, : ...","sqa area","text : kpp_extend")
:-4031

when trying to run a monstrous 26.1kb query. Yes that is the amount of text. The one query involves 9 case statements, some nested in others and several involving more than 50 when clauses. It also has 8 sum(), over 200 to_date() and a group by clause... Don't ask.

My guess is that we are running out of library_cache when this beast is parsed out. Sound reasonable?

Also, does anyone know of a way to determine how much library_cache/shared_pool a session is using.

--------------------------------------
Bob Lofstrand                   mailto:blofstrand@csedge.com
Oracle Database Administrator   www.csedge.com
Corporate Systems Inc.         806-337-3906
"There is no silver bullet." -Frederick P. Brooks. The Mythical Man-Month.

The information contained in this communication, including attachments, is strictly confidential and for the intended use of the addressee only; it may also contain proprietary, price sensitive, or legally privileged information. Notice is hereby given that any disclosure, distribution, dissemination, use, or copying of the information by anyone other than the intended recipient is strictly prohibited and may be illegal. If you have received this communication in error, please notify the sender immediately by reply e-mail, delete this communication, and destroy all copies.

Corporate Systems, Inc. has taken reasonable precautions to ensure that any attachment to this e-mail has been swept for viruses. We specifically disclaim all liability and will accept no responsibility for damage sustained as a result of software viruses and advise you to carry out your own virus checks before opening any attachment.

------_=_NextPart_001_01C3FBF8.A27147E0-- ---------------------------------------------------------------- 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 -----------------------------------------------------------------