RE: ORA-04031, how to prevent fragmentation?

From: Orr, Steve <sorr_at_rightnow.com>
Date: Fri, 26 Oct 2001 09:15:00 -0700
Message-ID: <F001.003B5C98.20011026091519@fatcity.com>

Regarding flushing the shared pool...
A couple of weeks ago I had hard copies of 2 Metalink docs side by side on my desk and I highlighted paragraphs on each which were completely

contradictory. One said flushing did absolutely no good and the other said
it could help. In practice it doesn't seem to do much for severe 4031 type
fragmentation. Pinning large stuff at db start up can help but if you've got

a lot of SQL with literals then consider setting cursor_sharing = TRUE as well. With a small db just enlarging the shared pool may be enough. Of course if you have control over the code then I think using bind variables is still the preferred solution.

Steve Orr

-----Original Message-----

Sent: Friday, October 26, 2001 6:05 AM
To: Multiple recipients of list ORACLE-L

You can either issue an "alter system flush shared pool", or increase the value of your shared_pool_size. Another option would be to pin some or your most used/larger packeges in to your shared pool.

HTH Mark

-----Original Message-----

Sent: Friday, October 26, 2001 10:55
To: Multiple recipients of list ORACLE-L

Dear list,

we have a small database with very few users, Oracle 8.1.7, NT 4 SP5. A NT-service holds a permanent connection and checks, if some data export are about to be done. The database had now an uptime of several months and each new connect raises ORA-04031. Seems to be a fragmentation problem of the shared pool. How can I prevent that?

Thanx very much

Mit freundlichen Grüßen

i.A. Marc Blum

SOPTIM AG
Grüner Weg 22-24
D-52070 Aachen

Telefon: +49 241 / 9 18 79-33
Fax: +49 241 / 15 40 38

mailto:marc.blum_at_soptim.de
http://www.soptim.de

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Blum, Marc
INET: Blum_at_soptim.de

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Mark Leith
INET: mark_at_cool-tools.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message