Re: RE: Help with Shared Pool Problem

From: <tim_at_sagelogix.com>
Date: Fri, 15 Oct 2004 09:39:26 -0600 (MDT)
Message-ID: <6974617.1097854766748.JavaMail.oracle@ocs.sagelogix.com>

I disagree. Commits and rollbacks have nothing to do with cursors in the shared pool. Check your application code. Someone has forgotten to close the cursors after they use them. Check V$OPEN_CURSOR to verify...

Attached file included as plaintext by Ecartis --
File: RE: Help with Shared Pool Problem

Return-Path: <oracle-l-bounce_at_freelists.org> Received: from mail.sagelogix.com by ocs.sagelogix.com

with ESMTP id 61271241097845621; Fri, 15 Oct 2004 07:07:01 -0600 Received: by mail.sagelogix.com (Postfix, from userid 16)

id 49C35A83C7; Fri, 15 Oct 2004 06:50:48 -0600 (MDT) Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180])

	by mail.sagelogix.com (Postfix) with ESMTP id 8A203A832A
	for <tim_at_sagelogix.com>; Fri, 15 Oct 2004 06:50:42 -0600 (MDT)
Received: from localhost (localhost [127.0.0.1])
	by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP
	id E9F2772C891; Fri, 15 Oct 2004 08:02:24 -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 02330-80; Fri, 15 Oct 2004 08:02:24 -0500 (EST) Received: from turing (localhost [127.0.0.1])

	by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP
	id 46FB272C8D2; Fri, 15 Oct 2004 08:02:24 -0500 (EST)

X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.181 Content-Class: urn:content-classes:message MIME-Version: 1.0
Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit
Subject: RE: Help with Shared Pool Problem Priority: normal
Date: Fri, 15 Oct 2004 08:00:49 -0500
Message-ID: <270A0BDDFDE54E41B78F0F06D82A66B850910F_at_okcexg3.kmg.com> X-MS-Has-Attach:
X-MS-TNEF-Correlator:
Thread-Topic: Help with Shared Pool Problem thread-index: AcSyG3TSgfVrV0iTT0ORQFvkVjmEZQAAg6pQACZBavA= From: "Smith, Ron L." <rlsmith_at_kmg.com>
To: "Bobak, Mark" <Mark.Bobak_at_il.proquest.com>,

"Paul Drake" <bdbafh_at_gmail.com>
Cc: <ganesh.raja_at_gmail.com>, <shaharul.anuar_at_intecbilling.com>,

<oracle-l_at_freelists.org>

X-OriginalArrivalTime: 15 Oct 2004 13:00:50.0185 (UTC) FILETIME=[FECCE790:01C4B2B6]
X-archive-position: 11134
X-ecartis-version: Ecartis v1.0.0

Sender: oracle-l-bounce_at_freelists.org
Errors-To: oracle-l-bounce_at_freelists.org X-original-sender: rlsmith_at_kmg.com
Precedence: normal
Reply-To: rlsmith_at_kmg.com

X-list: oracle-l
X-Virus-Scanned: by amavisd-new at freelists.org
X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on mail.sagelogix.com
X-Spam-Status: No, hits=0.0 required=3.0 tests=none autolearn=no version=2.63
X-Spam-Level:

The version of Oracle is 8.1.7, but I had a similar problem on 7.3.4. In both cases the application is doing a mass insert of data, probably without bind variables. For recoverability and restart ability a commit is only issued at the end of the process. From what you have all said, this is causing the shared_pool to fill up with thousands of similar but not identical SQL insert statements. This is causing the problem

Thanks for your help!
Ron

-----Original Message-----
From: Bobak, Mark [mailto:Mark.Bobak_at_il.proquest.com]=20 Sent: Thursday, October 14, 2004 1:43 PM To: Paul Drake
Cc: ganesh.raja_at_gmail.com; Smith, Ron L.; shaharul.anuar_at_intecbilling.com; oracle-l_at_freelists.org Subject: RE: Help with Shared Pool Problem

Good point, Paul.

See this item over at AskTom for full details: http://asktom.oracle.com/pls/ask/f?p=3D4950:8:5150260736162217859::NO::F4= 9
50_P8_DISPLAYID,F4950_P8_CRITERIA:17989406187750, Original poster: What version of Oracle are you using???

-----Original Message-----
From: Paul Drake [mailto:bdbafh_at_gmail.com] Sent: Thursday, October 14, 2004 2:27 PM To: Bobak, Mark
Cc: ganesh.raja_at_gmail.com; rlsmith_at_kmg.com; shaharul.anuar_at_intecbilling.com; oracle-l_at_freelists.org Subject: Re: Help with Shared Pool Problem

IIRC, in 9.2.0.5 - the behavior of open_cursors changes. I remember while attending a presentation at NYOUG (http://www.nyoug.org) given by Steven Feuerstein, that if your code is keeping cursors open that are not using bind variables, that you are in serious trouble.

Wait - it might have been at Jonathan Lewis' seminar. I'll have to get back to you on that.

Check as far as the number of session_cached_cursors and open_cursors. Perhaps Jonathan might have something to add.

Paul

On Thu, 14 Oct 2004 11:55:52 -0400, Bobak, Mark <mark.bobak_at_il.proquest.com> wrote:
> Ganesh,

>=20

> While ORA-4031 can mean that there is no space in the shared pool, it=20
> can also mean there is not enough contiguous memory in the shared=20
> pool. So, if you need 1k for a particular SQL statement, and the=20
> largest available chunk is 900k, then=3D20 Oracle will signal an=20
> ORA-4031. In this case, sometimes, flushing the shared pool can help,

> but not always. =3D20

>=20

> Yes, there is an LRU mechanism for certain components in
> the shared pool. See the scripts I previously mentioned
> on Steve Adams' website, IxOra.

>=20

> -Mark

>

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


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

Received on Fri Oct 15 2004 - 10:44:04 CDT