| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Aging in the SGA
A copy of this was sent to Kenneth C Stahl <BlueSax_at_Unforgettable.com>
(if that email address didn't require changing)
On Wed, 27 Oct 1999 16:16:51 -0400, you wrote:
>This is a followup to some questions I've asked recently. I'm still baffled
>by this problem a bit.
>
>I have an application on a Unixware 2.1.2 box with Oracle v7.3.4.0
>configured for MTS that reads ascii files and extracts data which it then
>inserts into the database. The actual INSERT statement has literal values,
>so it looks somewhat like this:
>
>insert into mytable (col1, col2, col3) values ('val1','val2','val3');
>
>Since the values are always unique it means that for ever insert there is
>an entry in the v$sql area of the sga. Lots of them. Eventually what
>happens is apparently something comes along with a big request for space
>and we get a ora-4031 error. We have tried to get around this problem by
>increaseing the size of the sga, but if we leave the system up long enough
>the problem eventually recurs and we end up doing a "alter system flush
>shared_pool" until we can bounce the database and increase the
>shared_pool_size again. Right now we have shared pool set to 20M on some
>systems and have not seen the ora-4031 in a while, but on another system
>(that one using SunOS 5.6) we have the shared_pool set to 30M and if we
>don't regularly flush the shared_pool we will get the ora-4031 in a day or
>so depending on the volume.
>
>So, my question is this - why are these cursors not being aged out of the
>SGA. Is there some setting that I could apply to make tha aging process
>speed up? What are the rules for aging? Since these cursors are only being
>used once I would think that they would always appear as not being
>least-recently-used.
>
>I suppose I could keep growing the shared pool size, but at what point does
>it start to become too large? I have 640M of physical memory on the
>Unixware systems and 1G physical memory on the SunOS systems and in both
>cases SHMMAX is set to 80% of physical memory.
>
>On one hand, I know what the solution should be. The software should use
>bind variables rather than literal values in the cursor. I've experimented
>with this and have proven to myself that the cursor only appears in v$sql
>one time. However, I can't control the software because it is provided by a
>3rd party company and we just get the executible programs specific for each
>platform (Unixware or SunOS).
>
>If I were to go the route of increasing the SGA is there any point at which
>it would be big enough to accomodate all of these cursors and allow them to
>age out naturally without resorting to the flush? I figure that as long as
>we are doing the flush we are just covering up the problem rather than
>fixing it.
>
>Any thoughts?
mixing MTS (which moves large allocations into the shared pool - the ugs is there instead of a dynamic heap) with apps that don't use bind variables is a bad idea.
MTS makes largish allocations.
shared sql makes smallish allocations.
they are allocating from the same place.
over time -- you are leaving little wholes between big allocations -- the sum of the free memory in the sga is perhaps sizable but the largest contigous chunk is small. MTS comes in and asks for a relatively large piece and bamm -- 4031.
Now, Oracle8.0 adds a concept to help alleviate this, it is called the large pool. here is a short blurb on it:
Introduction
What is the Large Pool
The memory for the large pool does NOT come out of the shared pool but directly out of the SGA (system global area) thus adding to the amount of shared memory Oracle needs at startup.
The size of the large pool is set by <Parameter:LARGE_POOL_SIZE> and the minimum size chunk of memory which can be allocated is determined by <Parameter:LARGE_POOL_MIN_ALLOC>. By default there is no large pool allocated - it must be explicitly configured.
The two main uses of the large pool in Oracle 8 are:
The large pool is protected by the 'shared pool' latch for memory allocation and management. Unlike the shared pool there is no LRU (least recently used) mechanism so chunks of memory are never aged out of the large pool - memory has to be explicitly allocated and freed by each session. If there is no free memory left when a request is made then an ORA-4031 will be signalled.
Space usage in the large pool can be seen using the <View:V$SGASTAT>.
I'm would send you a larger support note, <Note:61623.1>, that goes into more detail about fixing this issue in 7.x and 8.x but email to your address bounces with user unknown (too large to post here -- if you have web access to support, you can get it)
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Oct 27 1999 - 16:01:35 CDT
 |
 |