August 2007

There was a discussion about whether Oracle really allocates all memory for SGA immediately on instance startup or not. And further, whether Oracle allocates memory beyond the SGA_TARET if SGA_MAX_SIZE is larger than it.

It’s worth reading this thread first: http://forums.oracle.com/forums/thread.jspa?threadID=535400&tstart=0

I will paste an edited version of my reply to here as well:

There are two ways for diagnosing problems:

1. Checking for usual suspects and hoping to find a matching one
2. Following a systematic approach

Checking for usual suspects and hoping to find a matching one

The main design goal of Session Snapper was that it should not require any changes to be made into database.

And to achieve this goal, I was even willing to sacrifice some functionality.

So, for example there is no sorting capability in Snapper output. It would have been easy to create an SQL Type to database, use that as session statistics storage and query results out using an order by on statistics delta column – giving you (probably) most significant resource consumers first. But I didn’t do this as it would have violated the no-database-change-whatsoever design goal. (This problem could however be solved using manual sorting in PL/SQL code as done in Adrian Billington’s variation of runstats utility: http://www.oracle-developer.net/utilities.php )

This is my attempt for getting cheap popularity out of recent Oracle 11g release. This is not going to be another Oracle 11g new features list, I’ll be just posting any of my research findings here, in a semi-organized way.

The first post is is about Automatic Memory Management. AMM manages all SGA + PGA memory together, allowing it to shift memory from SGA to PGAs and vice versa. You only need to set a MEMORY_TARGET (and if you like, MEMORY_MAX_TARGET parameter).

You can read rest of the general details from documentation, I will talk about how this feature has been implemented on OSD / OS level (or at least how it looks to be implemented).