ora-04031 in 8.1.7.4.1

Hi,

I recently upgraded to 8.1.7.4.1. from 7.3.4.5.2. All worked o.k. until approx 1 week of usage.

I started to get ora-4031 errors. Recommended fix was to bump up shared_pool - mine is now 400 megs for 600 sessions.

I set the retained size to 100 megs and pinned a lot of shared_code. All went well for approx 10 days and we hit the problem again.

At any given time 30% of my shared pool is free, so I figure I am hitting fragmentation issues and not the 8.1.7 memory leak.

I monitored a large insert job which appeared to be using 80n megs of shared pool every hour of execution and just today moved this to a standalone database for processing.

I am hoping this will reduce the fragmentation but am worried that I will need to introduce frequent flushing and take on board the performance hit of using this. I have since not pinned any code as a flush frees up 80+ % of my shared_pool and I presume defragments also.

I am running a 3 cpu (700 mhz each) with 1 4.5 gb of ram = sga = 400 shared_pool + 1.8 for buffer cache. 600 users without MTS server. I did hit a problem when my Oracle process was running at 1.2 gb (reduce sort area size to 100 k) = now process at 900 megs. - did use /3gb and /pae but still couldn't get the process (less buffer cache) above 1.2 gb

Any help on the 04031 would be much appreciated as I am peed that I have hit issues on going from stable 7 to (apparently) unstable 8i

anybody experienced something similar??

Quote:

---

Originally posted by seanryan
I monitored a large insert job which appeared to be using 80n megs of shared pool every hour of execution and just today moved this to a standalone database for processing.

---

What kind of insert job could be using 80 megs of shared pool? What is it using such amount of shared pool for? Do you extensively use PL/SQL tables during that insert job?

It simply doesn't make to much sence to me, can you describe n more detail what this insert job is doing?

Another idea about that insert job:

Are you performing this insert in paralell (paralell DML)? If so, is it possible that you don't have LARGE_POOL defined in your instance? If there is no large pool, parallel execution uses shared pool for IPC messages between coordinator and slave processes and this can consume *a lot* of shared pool, not to mention that it contributes extensively to shared pool fragmentation!

the insert is from a bpcs system. approx 27,000 rows of data used for calculations of stock levels. After which it is deleted and inserted again (every hour)

Since this put a large strain on redo - the plan was to remove at some point.

I figured since it was the biggest shared pool user, it would contribute the most to fragmentation - as it grabbed and released a lot of memory at intervals.

The insert is a standard insert - no parallel