Re: ORA-04031, how to prevent fragmentation?

From: A. Bardeen <abardeen1_at_yahoo.com>
Date: Sun, 28 Oct 2001 18:52:48 -0800
Message-ID: <F001.003B6986.20011028190517@fatcity.com>

Marc,

If you haven't applied the 8.1.7.2 patchset, your ORA-4031 errors could be due to bug 1397603 "MEMORY LEAKS OBSERVED WHEN RUNNING CONNECTION TESTS" If this is the case, you should see a growth in the memory allocated to state objects:

select to_char(sysdate,'mm/dd/yy hh24:mi:ss'),s.* from v$SGASTAT s
  where name = 'State objects';

Growth is normal after the db is started, but should stabilize after 20-30 minutes of normal activity. If you are seeing growth I would recommend applying the 8.1.7.2 patchet. As a workaround, you can set _db_handles_cached = 0 in the init.ora, just keep in mind that this will increase latch contention so you could experience performance degradation if you already have a problem with latch contention.

HTH,

• Anita
  • "Blum, Marc" <Blum_at_soptim.de> wrote:
    > 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
    

    ---

Do You Yahoo!?
Make a great connection at Yahoo! Personals. http://personals.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: A. Bardeen
  INET: abardeen1_at_yahoo.com

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).

Received on Sun Oct 28 2001 - 20:52:48 CST