Like the picture on the 2nd page. Yes, I am visually oriented.
On page 4:
When automatic SGA memory management is enabled, the sizes of the different SGA
components are flexible and can adapt to the needs of a workload without requiring
any additional configuration.
However, the SGA components are flexible regardless of the setting for automatic SGA memory management (aka, automatic shared
memory management, ak ASMM). While I agree that ASMM as a conceptual feature is rather cool, there are known issues when
allocations and deallocations oscillate rapidly, causing locks on the underlying pools and decreasing performance overall.
To be truly effective, dynamic memory management would allow the kernel to dynamically allocate memory segments from the OS.
As it stands, 100% of greater(SGA_TARGET,SGA_MAX_SIZE) is allocated when the instance starts; if SGA_MAX_SIZE is greater, the
memory is allocated but not used, saved away for a potential future use. How is that dynamic?
From page 5:
"As the workload changes, memory is redistributed to ensure optimal performance."
*cough cough* Unless of course you hit a bug....
Good overview and discussion of the SGA and buffer cache in general.
"If the user process finds a dirty buffer as it searches the LRU list, it moves that buffer
to the write list and continues to search."
I did not realize that the user process could move dirty buffers to the write list; I thought only the DBWriter processes
could do that. Hmm...
Slightly disappointed that there is no talk about the negative ramifications of setting a large buffer cache, specifically
how it adversely affects the time to scan the LRU. The positive effects are fairly obvious. =) I was also hoping for more
coverage of the Redo Log buffer. But, the couple of paragraphs in the document (and more laterin Chap 9?) at least introduce
the concept. I still find it strange that the defaults are so small.
By the time I get to page 10, I am ready for another picture; it would be nice to start with the overall picture, and then
"zoom in" on each section to go along with the text a little bit.
Data Dictionary Cache, aka Row Cache. Is this the same cache that 11g is using to store sql results?
Like the detailed steps on page 12; this is the kind of details I have been waiting for. And now I can rip them up. =)
The first bullet of #2 states that the ANALYZE command (when used to update/modify stats) flushes the cursor from the shared
pool. People still use the ANALYZE command in 10g? I thought the dbms_stats procedures were preferred and more popular. In
any event, the underlying mechanics are still the same; if you alter the stats, the cursor is no longer valid and dropped.
Now if only we had a way to surgically splice out individual cursors without having to make relatively "impactful" changes...
Bullet 3 is interesting, I had no idea that changing the global name flushed the shared pool. Wow.
The couple of paragraphs about the Large Pool are succint, a bit too much so. How does Oracle decide when to use Large Pool
memory, as opposed to the Shared Pool? What are some good rules of thumb in terms of how much to allocate (probably covered
in the Performance Tuning Guide, but the Concepts Guide should say _something_)? No Large Pool Advisor? =)
The section on Streams Pool is even worse. It does not tell me what exactly the memory is used for, how to tune it, what to
do with it, nothing. And the 2nd paragraph is quite confusing; the Streams Pool may come from different parent pools
depending on how SGA_TARGET is set? Who signed off on that little bit of tom-foolerly?
The section on Dynamic SGA is almost misleading. The shared memory allocated to the server instance does not actually change
at all; 100% of the requested memory (even if it is not used) is given during instance startup. This concept of a "dynamic
memory usage" feature totally ignores that fact. So if the instance gets 50GB of memory but only uses 500MB, you are wasting
45GB for no good reason. That is not dynamic memory.
"Both the buffer cache and the SGA pools can grow and shrink at runtime according to some internal, Oracle-managed policy."
This is the Concepts Guide, for crying out loud! Some internal, Oracle-managed policy?!? Who wrote that?!? Worse, this
particular policy has some serious drawbacks, especially earlier in 10g. We observed that Oracle would thrash itself trying
to resize the "dynamic" pools many times a second, apparently oscillating between two different "optimal" thresholds. Some
LOCK_SGA: I have never heard of this parameter. And, oh, here is the reason why; we run Solaris.
Good thing the
Concepts Guide cleared that up. So, this is the Concepts Guide, right? I am not reading some PR BS, am I? Why would
LOCK_SGA be a bad thing? If you are running a Production database, especially on a host that is dedicated to Oracle, why
would I ever want memory pages to swap out?
SHARED_MEMORY_ADDRESS: Again, never heard of this one. In fact, Google is suspiciously quiet on this one as well. Why would I
want to set this parameter? Don't get me wrong, I am glad the Concepts Guide at least says *something*, but some explanations
would be nice. I assume this parameter is offset; if you set the same thing in multiple databases, I would have a hard time
believing that this parameter actually is meant to grab an absolute, OS Memory address. That would be just silly. Actually, I
am surprised this is not a underscore parameter. Which makes me wonder, is this short little blurb the only thing that keeps
this parameter from being "internally supported"?
Didn't realize the PGA had two independently-maintained subsections (pesistent area and run-time area). Good to know.
It is puzzling that the author would choose to include v$sesstat and v$sysstat as means of collecting performance statistics for the PGA without giving any insight whatosever as to what to look for; in fact, just browsing v$statname (the lookup table for the statistic#) does not make this any more clearer either. I personally find it quite unhelpful a document that purports to "demystify" something flippantly references a source that is not at all obvious.
I'll wrap up with that. I still find it hard to wade through these chapters; drains my brain. *grin* Overall I felt that I learned a few small things about the Memory Architecture, but I still feel like I have a ways to go before I fully grasp the skeleton that holds everything together. I hope this becomes more lucid as the chapters progress.