Re: Shared Memory Problem

From: Glen Mitchell <glen.mitchell_at_peace.com>
Date: Sun, 13 May 2001 15:23:37 -0700
Message-ID: <F001.00302264.20010513151049@fatcity.com>

Upanesh,
Steve Adams' book, Oracle 8i Internal Services, has a really good explanation of the shared pool behaviour (and other SGA structures).  The following is a brief summary of what is happening in your case. Approximately half of the shared pool size is reserved (as free memory) when the instance is started up.  As the utilisation of the shared pool increases with time the free memory is released into the shared pool.  The ORA-4031 error occurs when all of the reserved free memory has been allocated.  Flushing the shared pool coalesces contiguous free memory chunks, creating larger memory chunks which can be allocated thus avoiding the 4031 error.
You could also pin large and frequently used objects into the shared pool to help alleviate the problem (especially sequences if you are going to flush the shared pool).  Pinning helps to reduce fragmentation of the shared pool.  By doing this you will also gain a better understanding of the optimum size that your shared pool should be. The final option is to increase the shared pool size (if you have memory resource available).
Glen
Upanesh Patel wrote:
Hello,
We're Running Oracle 8.1.6.2 on Sun 420R with solaris 7.  We have a front-end Siebel 2000 Sales Package.  We have about 50 concurrent users and a lot of Siebel Workflow Process (which basically just route the work from one user to the next... so lot of sql it send to the server...) and have an SGA of about 120M of which 60M is shared_Pool_size...
Problem:
ORA-04031: unable to allocate 4096 bytes of shared memory ("shared pool","unknown object","PL/SQL MPCODE","BAMIMA: Bam Buffer")
It looks like Siebel is using bind variables, so that should not be a problem.  Also, this erros occurs after the database has been up for about 5 days. Is there a bug or anything with Oracle 8.1.6.2? TIA,
Upanesh

---

Do You Yahoo!?
Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/
--

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

Author: Upanesh Patel
  INET: upanesh_oracle_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).

-- 

Glen Mitchell                   NZ Phone: +64 9 3730400

Energy Research Lab             URL: http://www.peace.com

Peace Software                  Email: glen.mitchell_at_peace.com

  Received on Sun May 13 2001 - 17:23:37 CDT