Oracle 9.2 memory size.

[Paul Thomson_2]

Hi

Having recently converted to oracle, it would seem each connection is taking a lot more memory than previously. Could someone confirm my thoughts ?
On a non converted system

ps -efl | grep pid shows a SZ of 2586 (around 10mb of RSS or used RAM)

A converted system for the same running application process shows SZ as 16846 (65mb of RSS or used RAM)

Is this correct as the new oracle version seems much hungrier and Im thinking needs to be tuned down.

Thanks

[Don Morris_1]

It may not be hungrier -- it may simply be getting larger pages to improve performance [whether it needs them or not is always the question].

Check the chatr settings on the new binary for data page size (man chatr for details on this, look at the +pd / +pi sections), and check your tunable settings for vps_ceiling, vps_chatr_ceiling and vps_pagesize. [If you're running 11i v2, there's a man page for each in man 5... otherwise, http://docs.hp.com/en/TKP-90202/re105.html is a start to the 11.11 documentation].

[Steven E. Protter]

Shalom,

Check the settings of the oracle SGA. This can have an impact on memory consumption as well.

SEP

[TwoProc]

Your sort_area_size is the biggest determinant of how large each individual connection gets.
But, don't set that too small, as you'll start doing sorts on disk, instead of in memory.

However, I think the size of around 10Mb per is fine, depending what you're trying to get done in each. I've got many that will go as large as 30Mb toward the end of the day.

And yes, over time, the connections in Oracle seem to hog more memory in each sucessive version, as well as the database footprint on disk and in SGA memory as well.