1 person found this helpful
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_TARGETis too small. When this happens, the Oracle Database cannot honor the value of
PGA_AGGREGATE_TARGETand 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_TARGETas 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:
Thanks for your answer. Can we conclude that the memory consumed can be more than MEMORY_TARGET but cannot be more than MAX_MEMORY_TARGET?
Requesting a revert as it is getting confusing to get the concepts right.
I found the page of no matching entries found by clicking on the page. You can open the hyper link from your post and see.
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.