I wanted to know all the possible factors which can account for the increase in the memory required for an Oracle 11g database. Let’s take an example,
1) Say if the memory allocated for an Oracle 11g instance is 8GB which is 50% of the memory of 16GB allocated to the machine on which Oracle 11g is installed.
2) The max sessions are 300.
With this example in mind, I had the following queries:
1) Is the memory taken by 100 sessions or 300 sessions going to be within the limit of 8GB allocated to the Oracle?
2) Say if the max sessions is increased to 400, would the memory taken the 100 extra sessions would be within 8GB or would that be extra?
I hope my query is clear.
Please revert with the reply to my query.
the answer depends on your parameters settings. Memory allocated to Oracle consists of two pieces, SGA (shared structures, such as buffer cache, library cache etc.) and PGA (session memory and other private stuff). In 11g, you can control them either together (automatic memory management where you set target size for the total Oracle memory) or separately (automatic shared memory management or manual memory management).
In order to answer questions in your example exactly we need to know which of these ways you chose to allocated the memory to your Oracle instance. But in general, session memory doesn't come as "extra", it's an integral part of PGA and its sizing is governed by PGA sizing parameters.
Memory is allocated for the session only when that session is created by the listener, and the memory used by that session will fluctuate depending on the workload it's doing.
300 or 400 it won't pre-allocate all the memory for the fixed maximum number of sessions, it's dynamic. What oracle does is allocated all the memory you gave it in the PGA and the SGA and will do it's best to work with it for the current workload, it won't suddenly eat more memory without you telling it's okay to.
The only scenario I could see this happening is if you have a lot of sessions and a small PGA. In that case, oracle does go ahead and eat more memory than you gave it because it can't make due with the current PGA. So it eats more memory so sessions can be created and maintened. See PGA over- allocation.
To quote the good ol' docs:
Over allocating PGA memory can happen if the value of
PGA_AGGREGATE_TARGET is too small. When this happens, the Oracle Database cannot honor the value of
PGA_AGGREGATE_TARGET and extra PGA memory needs to be allocated.
Thanks for your answer but as an example if the max_memory_target is 6 GB, then immaterial of whether there are 300 or 400 sessions, the memory occupied by the sessions (including SGA & PGA) cannot be greater than 6GB. Is my understanding correct? Request you to revert with the complete details.
nice explanation however it seems to not agree with the Fine Manual noted & quoted below
"Total memory usage can grow beyond the value of
MEMORY_TARGET. For example, memory is allocated to PL/SQL tables and varrays regardless of the value of
MEMORY_TARGET as long as memory is available at the operating system level."
Not really no. It agrees with the Fine Manual.
PL/SQL tables & VARRAY reside in the PGA.
The PGA can suffer over allocation as I have said in my previous post, which justifies your quote.
Notice the documentation for the SGA_TARGET which excludes the PGA mentions no sudden growth beyond the target. That is due to the PGA over-allocation that the MEMORY_TARGET can be subjected to since it also sizes the PGA.
Apologies if it was not clear enough on my original post.
the meaning of MAX_MEMORY_TARGET is explained in the documentation:
It is NOT a hard limit on the total memory consumption by an Oracle database. Rather, it's an upper limit on MEMORY_TARGET parameter, which in its turn sets the target (but again, not the hard limit) for SGA+PGA.
This means that there is no telling how much memory the 300 or 400 sessions would consume -- it will depend on how much work these sessions are doing, how much memory this work requires, what your memory settings are, and what is the situation with the memory on the box.
In addition to the official Oracle documentation, this thread on AskTom will probably also be useful to you:
There are few things to observe in this:
1) There is a field of PGA_USED_MEM in the v$process table which can give the amount of memory occupied by the process.
2) There is no field for memory in v$session table which can give the amount of memory occupied by a session (which in turn is the amount of memory occupied by a process).
Are my 2 observations above correct?
Request you to please revert.