title: Memory Pool Settings for Oracle Database 10g link: https://onemoretech.wordpress.com/2008/04/15/memory-pool-settings-for-oracle-database-10g/ author: lembobro description: post_id: 540 created: 2008/04/15 15:36:26 created_gmt: 2008/04/15 15:36:26 comment_status: open post_name: memory-pool-settings-for-oracle-database-10g status: publish post_type: post

Memory Pool Settings for Oracle Database 10g

We’re in the process of setting up a backend database for a test instance of Oracle Identity Manager, and have reached the point where the memory pools need to be allocated.

Out of the box Oracle Database 10g adjusts itself to work within the amount of RAM being reported by the O/S. In my case, with only 1 Gb allocated for the CentOS virtual machine we’re running in, that’s way below the minimums we know (from previous painful experience) the app will need.

Since I’m also now working my way through Oracle’s 2-Day DBA course, I decided to make the necessary adjustments through the EM Console, rather than hacking the spfile for my target database.

Basically all I did was go into “All Initialization Parameters” (SPFILE tab) and put the following values in for the parameters indicated (by default they’re blank):

java_pool_size: 60M large_pool_size: 60M pga_aggregate_target: 130M

Which on a good day resulted in the following on the “Memory Parameters” page:

SGA
Shared Pool: 84
Buffer Cache: 64
Large Pool: 60
Java Pool: 60
Other: 4
Maximum SGA Size: 272

PGA
Aggregate PGA Target: 130 MB
Current PGA Allocated: 56495
Maximum PGA Allocated: 64959
Cache Hit Percentage: 67.36

That last number looks pretty bad, but this is a test db, and we are in a memory constrained situation (running multiple virtual machines on a desktop with only 4 Gb RAM).

There’s more info on how all this works on OTN. One very good article is Automatic Shared Memory Management. Of course the author of that article had the advantage of something like 8 Gb or more of system memory to work with (his pools add up to 5 Gb, which means the total RAM would need to be somewhat more to cover application and operating system overhead).

Copyright 2004-2019 Phil Lembo