SGA limit on windows with 8i

Recently upgraded to 8.1.7 and set my shared_pool to 600 megs and buffer cache to 2400 megs.

The first strange thing I noticed with 8i was monitoring the Oracle process under task manager showed only the shared_pool + user sessions combined. i.e. 600 megs + sessions.

I don't use MTS and my session levels climb over 600. This combined with a 1 meg sort area size caused me to get a 12500 error when the session level reached approx 524 sessions.

I dropped the buffer cache and hit the problem at similar session level. I was using my old 7.3.4. experience and figured it was the combined settings = limit.

Dropped down the shared pool by 100 megs and this allowed me to take on more sessions. But I hit the problem at 1.2 gb again (user process + shared_pool).

Finally dropped the sort area size to reduce the per session memory requirement. This keeps the process below 1 gb at all times.

I use the boot.ini settings of /pae and /3gb.

Has anyone else hit a problem like this.????

I'm not sure what the max sga size is, however 600mb for a shared pool seems excessive. I assume you have over 3gb of ram. I would suggest dropping the shared pool to 100mb and dropping the buffer cache to 2gb.

You may want to look at MTS. You need to understand how much sorting each session will do to know what to set the sort area size to. When you only have 20 session at a time you can make the sort area rather large and not think twice with 600 sessions and 1mb sort area size thats 600mb for sorting alone. You may want to drop sort area retained size so that session give up memory when they don't need it. But definately look at mts.

try set:use_indirect_data_buffers=true and try again.
Another thing:
You can use orastack to reduce the memory consumed by per connection.Search metalink and you can get it.
It seems that you are using NT4 Enterprise edition and you mentioned the 3GB limit?

Quote:

---

Originally posted by chao_ping
You can use orastack to reduce the memory consumed by per connection.Search metalink and you can get it.

---

It's described in Metalink Note:46001.1 "Oracle Database and the Windows NT memory architecture, Technical Bulletin". Excelent description of Win$ memory architecture and how oracle process is dealing with it.

I set the sort area reatined size to 0 after dropping the sort area size.


Looks like MTS is a good option - does this required tnsnames client changes? i.e. logon different listener

I also consired stack changes, but this made me a little nervous.

I am using windows 2000 advanced server. Without the /pae /3gb the db would not even open with large buffer cache settings. (same as nt with /3gb switch)

ho hum..

I previously got this error "12500" when session reached a level. Since then I have tuned down the sort_area_size to 100k from 1 meg.


I then started hitting ora-4031 which forced me to up my shared pool x 100 megs to 400 megs

This morning I got a ora-12500 at session level 669 - which showed an Oracle process level (in task manager) of 890 + megs.

So it appears I am hitting a problem I hit before at 1.2gb for user processes+sharedpool joined. Now I am hitting it at 900 megs.

I have not bounced the Database yet but it the session connection problem persists i.e. can't handle new log-ons I will be forced to reduce the shared pool by 100 megs - this may push me back into the Ora-4031 problem again.

To put mildly, help!!!. I didn't experience any of these problems with 7.3.4 - since moving to 8i I have hit two show stoppers which has forced to to put in changes + bounce the Database each week.