Aging in the SGA

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? Received on Wed Oct 27 1999 - 15:16:51 CDT