Discovering Oracle Database 11g Memory Management

It is very easy: I want my databases to consume as much memory as they need. But not more than a reasonable amount. And I want Automatic Memory Management and Automatic Shared Memory Management both enabled. Easy you say?

Ok, you’re right – but only if you are a full time Oracle database admin with a long, long time experience. Me, I’m a part-time DBA, with an outside view on the instances. For example, I’m writing scripts to automate the creation of instances, upgrade software and instances to new releases aso.

So I stumbled upon the . In chapter 7, I’ve found what I was looking for (reading the first 5 pages of this chapter). I’ve adapted one of my databases’ pfile and gave it a try.

From now on I will always set
memory_target=4294967296
memory_max_target=6442450944
to enable the Automatic Memory Management. And I will set
sga_max_size=3221225472
sga_target=2147483648
variables.

Hey, stop complaining: I know I should *not* set sga_max_size. But if I don’t do, querying the database for this parameters’ value it says “Dude, it’s equal . And so a simple show sga shows me values, which I don’t like.

This database instance has a small memory footprint now, nearly what I wanted to achieve.

BTW. All other params (db_cache_size,shared_pool_size,large_pool_size, and java_pool_size) have not been set in the pfile.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s