PGA Memory and PGA_AGGREGATE_TARGET, is there Something Wrong with this Quote?

4 08 2010

August 4, 2010

I started reading the “Expert Oracle Database Architecture: Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions” book cover-to-cover.  Very interesting book so far, and it appears that a lot of time was spent reworking the previous release of the book so that it targets Oracle Database 11g R2, while not losing the book’s usefulness for prior releases of Oracle Database.  On page 131 of the book I found an interesting quote that made me wonder… is there something wrong with this quote (roughly the same quote is found on page 125 of the first edition of the book)?

“… You may well have multiple work areas in your session for multiple queries, or more than one sort or hash operation in a single query, but each work area will be about 5 percent or less of the PGA_AGGREGATE_TARGET.”

Visit the above Google books links above for the quote’s context.

Is there something wrong with the above quote?  (Possible hint _SMM_MAX_SIZE.)


Actions

Information

8 responses

4 08 2010
joel garry

http://christianbilien.wordpress.com/2007/05/01/two-useful-hidden-parameters-_smm_max_size-and-_pga_max-size/ mentions ” default value is the least of 5% of pga_aggregate_target and of 50% of _pga_max_size. A ceiling of 100MB also applies.” though I haven’t checked it and it may be a 9i thing. Tom puts enough caveats in the context, on the surface it looks ok. I long ago threw up my hands about pga_aggregate_target, it seemed more productive just to complain the docs are off and twiddle it until it works according to the advisor. The “variation of cache hit ratio with PGA target” on that EM chart is a horizontal line on my system, as if anyone would care about a cache hit ratio.

4 08 2010
Charles Hooper

Joel,

That is a good link – it contains a key piece of information. It was similar information from my own testing (see the link attached to the period at the end of this article) that caused me to stop and create this blog article. A couple of more related links:
http://forums.oracle.com/forums/thread.jspa?threadID=844027 (see Randolf’s post)
http://books.google.com/books?id=pIcgos-wwy8C&pg=PA17 (“Secrets of the Oracle Database” book)
http://books.google.com/books?id=TGSd3pkMx5IC&pg=PA338 (“Cost-Based Oracle Fundamentals” book)
http://www.oramoss.com/presentations/fivetuningtipsforyourdatawarehouse.ppt (specifically slides 27 and 29)

One final clue: the statement is correct in the first edition of the book…

5 08 2010
Fahd Mirza

May be its naive, but I was hoping that for most systems, we can take a sigh of relief after setting memory_target in 11gR2.

best regards

Fahd

5 08 2010
Charles Hooper

Fahd,

I have not experimented very much with the MEMORY_TARGET parameter, other than as a solution to allow Oracle Database 11.2.0.1 to run on a netbook computer with 1GB of memory (the documentation states that Oracle Database 11.2.0.1 requires a minimum of 2GB of memory). It appears that using MEMORY_TARGET is not possible on all operating system platforms, and I believe that it might have trouble working when large memory pages are used to help minimize the memory that is used by the operating system when managing individual memory pages.

http://download.oracle.com/docs/cd/E14072_01/server.112/e10839/tuning.htm#BABBJHAC

http://download.oracle.com/docs/cd/E14072_01/server.112/e10595/memory003.htm

Using MEMORY_TARGET might work, but I wonder what might happen if a run-away process causes the used memory to approach the MEMORY_TARGET value due to excessive use of PGA memory. When the PGA_AGGREGATE_TARGET is reached (and MEMORY_TARGET is not set), Oracle Database will try to exceed that limit so that sessions generally do not crash when requesting PGA memory. Sure, the server will probably become slow when the physical memory in the server is exhausted and swap file space is used extensively, but at least the database is “up”. Would the same be true when MEMORY_TARGET is used, or would it appear that the database is “down” to certain sessions. I guess that is just one more thing to stop and think about – MEMORY_TARGET might be the perfect solution for certain problems.

There are a lot of hidden parameters that can be watched to understand why certain things happen. I think that it is important to not adjust those parameters manually, but rather to understand their purpose so that the non-hidden parameter that indirectly controls the hidden parameter may be adjusted, if necessary, to correct unexpected behavior. The following link may be helpful:

http://www.jlcomp.demon.co.uk/untested.html

It is important to recognize that the limits for memory usage per sort/hash operation (_SMM_MAX_SIZE, for instance) apply to *each* of the concurrent sort/hash operations. For example, this blog article shows a sort operation with two concurrent hash operations:

http://hoopercharles.wordpress.com/2010/01/18/pga-memory-the-developers-secret-weapon-for-stealing-all-of-the-memory-in-the-server/

As more and more memory is used, and the PGA_AGGREGATE_TARGET parameter’s value is approached, the amount of memory that sessions will be able to use for each new concurrent sort/hash operation will decrease – what had previously completed as an optimal (in memory) execution 5 minutes ago may require either a single-pass (use of the temp tablespace) execution or a multi-pass execution to complete now – because now there is a greater demand for PGA memory. When the PGA memory is no longer needed, it is returned.

5 08 2010
josh

it depends on the few parameters check this paper from Joze Senegacnik

5 08 2010
Charles Hooper

Josh,

Very helpful link. Slides 30 and 31 seem to describe why the quote in this blog article is correct in the first edition of the book. Now the question, why might it not be correct for the second edition of the book?

5 08 2010
Sean Molloy

After reading Joze’s slides, it seems the behaviour changed. In 91/ 10gr1:
Serial operations: _smm_max_size = min(5% PGA_AGGREGATE_TARGET, 100MB)

10gr2: Serial operations: for P_A_T 1000 MB the parameter _smm_max_size = 10% of P_A_T (upper bound not limited?)
P_A_T = PGA_AGGREGATE_TARGET

(comments were combined)
for P_A_T Less Than 500MB the parameter _smm_max_size = 20% of P_A_T
for P_A_T between 500MB and 1000MB the parameter _smm_max_size = 100M
for P_A_T Greater Than 1000 MB the parameter _smm_max_size = 10% of P_A_T (upper bound not limited?)
(/comments were combined)

Your argument probably is that the first edition covered Oracle up to 10gr1 and was therefore correct.
The second edition is covering up to 11gr2 and the same explanation is no longer correct.

6 08 2010
Charles Hooper

Sean,

Nice summary of the changes between releases.

This might be one of the few paragraphs in the book that was not updated for Oracle 10g R2 and above (when OPTIMIZER_FEATURES_ENABLE is also set to 10.2.0.1 or greater). Overall, I am impressed with the effort that Tom put into revising the book. The book almost reads like it was freshly written specifically for Oracle 11g R2 with differences for older releases indicated when they exist, rather than a rehash of a rehash of a book (Expert One on One Oracle).

I think that I found one or two other errors in the first 1/3 of the book – much better than many of the other books that I have reviewed.

One of the assumptions that this book seems to make is that most programmers who read the book will be writing code that will only access the Enterprise Edition of Oracle, and likely with a couple of extra-cost licensed items. At least in the early portion of the book the author makes little effort to state “by the way, this will only work for some of the customers who use your programs” – I guess that is a pet peeve of mine.

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




Follow

Get every new post delivered to your Inbox.

Join 141 other followers

%d bloggers like this: