| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Aging in the SGA
Thomas Kyte wrote:
>
> 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
> ~~~~~~~~~~~~
> This short article describes the fundamentals of the 'large pool'
> introduced in Oracle 8.0.
>
> What is the Large Pool
> ~~~~~~~~~~~~~~~~~~~~~~
> The large pool is an area of the SGA similar to the shared pool, but with
> restrictions on its usage such that only certain types and sizes of memory
> can be allocated in this 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:
>
> a. For the User Global Area (UGA) of sessions connected using MTS
> (multi-threaded server)
>
> b. Buffering for sequential file IO (Eg: as used by server managed
> recovery when there are multiple IO slaves)
>
> 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)
>
That makes perfect sense considering the number of users we have. I bet what happens is that all goes along fine until we get a certain number of users logged in and then when just one more tries to log in it starts throwing the 4031.
So, I guess the solution is to live with the large SGA size as long as I can't change how the INSERT statements are coded.
Thanks for the response.
............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. ............................................................. .............................................................Received on Thu Oct 28 1999 - 08:18:47 CDT
 |
 |