JL Computer Consultancy

Undocumented secrets – or untested fairytales ?

Aug 2005


[Oct 2008: If you have arrived at this article from the “biased test cases” article on Don Burleson’s website and want to get some idea of how much of that article is true, check this link on my blog to see an earlier copy of the article and my commentary on it.]

Addendum (14th Dec 2007)

Addendum (23rd Jan 2007)

Addendum (7th Jan 2007)

Addendum (21st Jan 2006)

Addendum (22nd Oct 2005)

Addendum (28th Aug 2005)


The Internet is a dangerous place – I’ve just seen an article dated 17th August 2005, some 1,800 words in length, titled “Undocumented secrets for super-sizing your PGA” published on searchoracle.com by Don Burleson that contains some claims that should never have seen the light of day.

One claim it makes is that some of the sensible settings you may have for your workarea parameters clearly indicate that you don’t know what you are doing. (Let’s hope your boss doesn’t read it before your next job review.)

Another claim is that it would be perfectly reasonable to fiddle with some of the PGA management parameters in a high-risk fashion. In private, after assessing the situation properly and explaining the risks involved, an approach of this type might be acceptable. But the example used to explain the technique shows that the author doesn’t understand what his suggestion does, and hasn’t even tested the example he quotes. (If he had tested it, he would have found that the claim made in the example was wrong, and the technique made no difference to the point he was addressing – although it could have a very nasty side-effect on other aspects of the system).


Who’s ignorant ?

The article makes the following assertion:

For example, the following set of parameters indicates that the Oracle DBA does not understand PGA management.

·         sort_area_size=1048576              <-- sort_area_size is ignored when pga_aggregate_target is set and when workarea_size_policy =auto.

·         pga_aggregate_target = 500m     <-- The maximum default allowed value is 200 megabytes

Funnily enough, I happen to be running a 9.2.0.6 instance with exactly these settings, plus workarea_size_policy = auto.

So let’s build some sample data, and see what happens if I play around with the sort_area_size:

I’ve created a data set like this – it’s a general purpose data set I use quite often for testing theories about sorting mechanism:

create table t1 as

with generator as (

    select  --+ materialize

        rownum              id,

        substr(dbms_random.string('U',4),1,4)   sortcode

    from    all_objects

    where   rownum <= 5000)

select

    /*+ ordered use_nl(v2) */

    substr(v2.sortcode,1,4) || substr(v1.sortcode,1,2) sortcode

from

    generator   v1,

    generator   v2

where

    rownum <= 10 * 1048576

;

I’m also going to use a little snapshot code that will show me changes in my session’s statistics as I run some SQL. So all I have to do is start a new session, run the following SQL, and see what happens to the statistics ‘session pga memory max’ and ‘session uga memory max’.

alter session set sort_area_size = {something};

 

execute snap_my_stats.start_snap

 

declare

    v1  varchar2(6);

begin

    for r1 in (select sortcode from t1 where rownum <= 2000000 order by sortcode) loop

        v1 := r1.sortcode;

        dbms_lock.sleep(10);

        exit;

    end loop;

end;

/

 

execute snap_my_stats.end_snap

Here are a few results. Note especially that I started a new session for each test to avoid clouding the figures.

Sort_area_size

Approximate change in
UGA max memory

Approximate change in
PGA max memory

1 MB

1 MB

Nil

2 MB

2 MB

Nil

4 MB

4 MB

Nil

So the sort_area_size does have some relevance – even when all the new pga mechanisms are correctly enabled.

Maybe you’re not ignorant, maybe you do understand PGA management, maybe you (like me) are running with shared servers (formerly multi-threaded servers) and know that for Oracle 9i, the older parameters are still used to limit the memory that gets allocated in the SGA (which is where the UGA is going to be held when using shared servers).

Now let’s take a look at that comment about the pga_aggregate_target. Apparently the maximum default value allowed is 200 megabytes. So let’s see if there is any difference between running the same query (through a dedicated server this time) with a pga_aggregate_target of 200MB, as opposed to a pga_aggregate_target of 500 MB, or 1000 MB.

Pga_aggregate_target

Approximate change in
UGA max memory

Approximate change in
PGA max memory

200 MB

10 MB

11 MB

500 MB

25 MB

25 MB

1000 MB

50 MB

50 MB

Oh dear – increasing the pga_aggregate_target above 200MB does make a difference after all. It makes you wonder who it is exactly that doesn’t understand PGA management.

The confusion in Don Burleson’s mind is probably associated with a later comment in the same article:

_pga_max_size – this hidden parameter defaults to 200 megabytes, regardless of the setting for pga_aggregate_target.

A quick check of the description of _pga_max_size shows it to be: “Maximum size of the PGA memory for one process.”  The pga_aggregate_target is an accounting target for the sum of PGA memory usage across all processes in the instance; _pga_max_size is an accounting target for a single process, to limit the risk of a single runaway process acquire a huge fraction of the total allowance. Don seems to think that they both do the same, with _pga_max_size being a hidden limit on pga_aggregate_target.


Test! Test! Test!

There are many other errors and points of confusion in the article, but the second critical point I want to pick up is the final “expert suggestion”.

In a system like the example in Figure 1, the following settings would increase the default sizes for large sorts and hash joins.

·         pga_aggregate_target = 1000m

·         _pga_max_size = 1000m

·         _smm_px_max_size = 333m

With these hidden parameters set we see a 5x large size increase for parallel queries and sorts:

·         A RAM sort or hash join may now have up to 50 megabytes (5% of pga_aggegate_target) a 5x increase.

·         Parallel queries may now have up to 330 megabytes of RAM (30% of pga_aggegate_target), such that a DEGREE=4 parallel query would have 83 megabytes (333 meg/4).

Clearly (and there is a warning about this in Don’s article), you should not mess about with hidden parameters unless you really understand what they do and get approval from Oracle support. But let’s just check Don’s claims about what happens when you fiddle with the _pga_max_size and _smm_px_max_size.

The first point is that we can get a RAM sort increase by 5x … “up to 50 megabytes”. Just take a look a bit further up the page – I’ve already got a 50MB sort allocation by setting the pga_aggregate_target to 1000 MB without messing about with any hidden parameters.

How about the parallel query item? To demonstrate this one, I changed my query to:

set pause on

 

select

    /*+ parallel (t1, 4) */

    sortcode, rowid

from

    t1

order by

    sortcode, rowid

;

The purpose of the pause is to freeze the session with its parallel execution slaves still held so that I can check their session stats. And to check their stats I ran a query against v$px_sesstat from another session:

select

    st.qcsid || '/' || nvl(st.qcinst_id,&m_inst)            qcsid,

    st.server_group,

    decode(degree,

        null,null,

        st.degree || '/' || st.req_degree

    )   degree,

    st.server_set,

    st.server#,

    st.sid,

    sn.name,

    st.value

from

    v$px_sesstat    st,

    v$statname      sn

where

    sn.statistic# = st.statistic#

and st.value != 0

order by

    st.qcsid,

    st.server_group,

    st.server_set,

    st.server#,

    st.statistic#

;

(This is a query that can be run repeatedly as a parallel query executes to show you the way the memory allocation (and other statistics) change over time. I modified it to report fewer columns, and just the PGA memory statistics for the purposes of this article. This is what I got as the sort completed:

Set  Sno   SID Statistic                                       value

---- ---- ----- ---------------------------------------- ------------

   1    1    14 session pga memory                          6,631,492

                session pga memory max                     60,305,476

 

        2    15 session pga memory                          5,582,916

                session pga memory max                     60,305,476

 

        3    16 session pga memory                          5,582,916

                session pga memory max                     60,305,476

 

        4    17 session pga memory                          4,010,052

                session pga memory max                     60,305,476

Two things to note, that’s a 57 MB change, not 83 MB as suggested by Burleson (the limit in 10g was a lot closer to 50MB – I think the variation is due to an anomaly in whether the PX overheads and I/O overheads are considered to be part of the work area or not) – but the most significant thing was that these results did not change when I fiddled with the hidden parameters and restarted the instance.

The trivial error that Don has made here is in assuming that the _smm_px_max_size is the upper limit on the sum across the slaves of the memory for a single work area operation. Clearly he hasn’t tested this or even thought about the associated parameters. Each slave is limited by the _smm_max_size – the 5% of the pga_aggregate_target that Don mentions elsewhere in his article. The limiting effect of the _smm_px_max_size doesn’t kick in until you start running at a degree of parallelism greater than six. (The 30% parallel limit allows for 6 times the 5% serial limit ). (Oct 2005 – see addendum)

The serious error is that he doesn’t understand the significance of the _pga_max_size. And again has not tested his guesses about it. Consequently he has advised you to allow a single session to acquire the entire accounting limit.

Don’s article started by commenting that the new pga management mechanisms avoid the old problems of having to set the old sort_area_size and hash_area_size so that a single process can hog all the resources, and finishes with the suggestion that you should abuse the new mechanism to allow a single processes to hog all the resources.  (Maybe, for those very special cases, it would be better to use ‘alter session’ commands to set the workarea_size_policy back to manual and set explicit values for hash_area_size and sort_area_size, rather than fiddling with hidden parameters that you don’t know much about).


Conclusion

In his article, Don Burleson uses his ignorance to issue a blanket claim that a perfectly sensible setting for a couple of database parameter proves that you don’t understand PGA management.

At the end of the article, he then claims to be offering expert advice – which consists of doing something dangerous to your system, doesn’t have the effect he claims, and is obviously something he has not tested properly.

Footnote

There is a note on one of Don Burleson’s website’s saying:

We are under no obligation whatsoever to provide reproducible evidence or test-cases to beginners and it is our readers trust in our judgment and real-world experience that counts.  Resist the temptation to fall-in to the “prove it” trap.

Of course he’s under no obligation to prove what he says – but when he says things that are obviously wrong, and clearly haven’t been tested, the average reader is going allow him just as much trust as he merits.


Addendum 28th August 2005

I see that Don Burleson has updated the article in question. Perhaps this article of mine made a difference, or maybe it was because the accuracy of his original was questioned by someone writing in Don Burleson’s very own forum.

Alas, rather than trying to correct the more glaring errors, Don seems to have put most of his efforts into claiming that he must be right because he’s only copying other people which means it’s not his fault anyway. He’s also included two ‘success stories’ which are presumably offered as proof that he is right.

In a recent blog entry, Mike Ault referred to my article as a “bait and switch”. According to Wikipedia, this means I’ve tempted you in by advertising something at a really good price, with the intention of persuading you to buy a more expensive product because the advertised one is ‘no longer available’. Well there’s no bait, just the truth, and no switch – but Mike has given me the idea that Don’s updates merits a new, bigger, better article, rather than just an addendum to this one.


Addendum 22nd October 2005

I’ve been sent an email that made me realise that the following comment needed some clarification:

The limiting effect of the _smm_px_max_size doesn’t kick in until you start running at a degree of parallelism greater than six. (The 30% parallel limit allows for 6 times the 5% serial limit ).

The author of the email interpreted this comment to mean that for operations running with a degree of six or less, the _smm_px_max_size would be ignored, and the _smm_max_size would be applicable. This is not the case. The text would probably have been less ambiguous if I had said:

Assuming you haven’t been fiddling with hidden parameters, you will not be able to see the effect of the _smm_px_max_size until you run a query with a degree of parallelism greater than six because the limit set by _smm_max_size will be override the limit set by _smm_px_max_size. The limit set for each slave in the operation is least(_smm_max_size, _smm_px_max_size/degree of parallelism).”

For example – assume pga_aggregate_target = 1000MB, the defulat _smm_max_six is therefore 50MB, and the default _smm_px_max_size is 300MB. So we get the table (with thanks to a sharp-eyed reader who spotted a silly typo in the original):

Degree of parallelism

_smm_max_size

_smm_px_max_size / degree

Limit per slave

4

50 MB

75MB

50MB

6

50 MB

50MB

50MB

7

50 MB

42.8MB

42.8MB

As you can see, it is only when _smm_px_max_size/degree is less than _smm_max_size that you can see the effect of the PX parameter. Which, for the default settings, means the degree of parallelism has to be greater than six.

I have copied this comment into The Snark Method of Research, where the statement had been quoted.


Addendum 21st January 2006

I’ve been send a note by Jeff Moss (blog site: http://oramossoracle.blogspot.com). He had been doing some research for a presentation he was preparing for the UKOUG Business Intelligence SIG on performance tuning for data warehouses, and in reading this article he had noticed that the previous addendum was still incomplete, and could mislead.

The point I had overlooked, of course, was that there is a hidden limit built in to _pga_max_size (200MB for 9.2) which imposes a limit on _smm_max_size (100MB in 9.2) but doesn’t impose a limit on the _smm_px_max_size.

For example, if you set your pga_aggregate_target to 4,000MB in a 9.2 instance, then your _smm_px_max_size would be 1,200 MB (30% of pga_aggregate_target) but your _smm_max_size would be only 100MB (smaller of 5%, and _pga_max_size/2).

Consequently, the limiting effect of the _smm_px_max_size would only become visible once your degree of parallelism exceeded 12 – until that point, every slave would be allowed to get up to 100MB for a single operation; go above that level, and the maximum for a slave would be 1,200/degree of parallelism.

I have copied this comment into The Snark Method of Research, where the same addendum appears.


Addendum 7th January 2007

(Transferred from The Snark Method of Research 13/1/07, where it was originally posted in error)

It’s amazing to realise that it’s been over a year since the big PGA kerfuffle – but time passes, and most of us manage to correct our mistakes and move on. However, I see that Don Burleson has decided to bring it up again. In an article titled Superfluous Overhead in Oracle, Don Burleson shows that he, just like Harry Conway in one of the discussions of this article, finds an explanation that is two lines below a test result too “far down” in a document to be noticeable.

For example, Lewis once "proved" that the behavior of the Oracle PGA was not as I described it, but he failed to mention up-front that he used an important non-default feature which radically changed the default results.  Lewis only mentions this critical non-default (Shared Servers) far down in his article, where readers are likely to discount its importance.

I mention this because the problem with “long” documents seems to appear quite frequently in Burleson’s attempts to assess my posting describing the potential risks of “just not doing” updates that appeared to be redundant even though they were specified in an SQL statement – for example, at one point, he says:

It also appeared that many of his readers did not fully understand his writing:

·         As you say, this check is dangerous staff [sic]

·         And yes, an ‘off-switch’ would be a very good idea should Oracle introduce such a smart update ‘feature’.

·         All this seems like the check was not introduced (years ago…) because CPU was not so powerful and now it’s not introduced because DISK are so fast…

But if you read my posting, these three readers seem to have understood exactly what I was on about and why a hidden optimisation could be a very bad idea. You will also notice that my posting already covers the dangers inherent in the change to locking activity that would occur if Oracle took up the following suggestion from Burleson:

The Oracle optimizer already performs query rewrite for materialized views, adds WHERE clause predicates for Virtual Private Database (VPD) queries, and re-writes some subqueries.

The Oracle SQL optimizer could be enhanced to protect against the unnecessary overhead of superfluous updates by negating the SET clause in the WHERE clause to bypass rows that do not need to be updated.  Very simple, logically correct, and the exact same redo and supplemental logging results as Lewis' manual fix.

If this type of subversion does happens automatically, is it really going to be logically correct every single time? Check my posting again, especially the bit where I made sure that the updates were happening by accident, not by design. You don’t get the locking if you avoid the updates with a where clause. By the way – do we really want a simple fix that works like VPD? Do you know what that does to your shared pool and CPU usage if you don’t implement it properly – it took Oracle three major versions to address the traps and overheads of VPD. Do you want that happening automatically and invisibly to every single statement you execute? And how do you hack the code (whether it’s by adding where clauses, eliminating set clauses, or with VPD) if a notional update to six columns means you need to change columns A, B, and D in one row, and columns C, D and F in the next ?  And what’s that going to do to FORALL bulk processing? Is it really going to be a simple fix? 

Burleson then goes on to mangle my discussion about Streams – but at least this bit of the article includes the comments:

If I understand his argument correctly    -- that’s okay, then, because he clearly doesn’t

And

Huh?                                                                           -- generally a sign of bafflement: that’s okay, not everyone understands Streams

Since the Streams argument has bypassed Burleson, I’ll try and clarify it:

To eliminate the overheads of redundant updates in the general case, you have to take out the redundant redo and undo for the columns that did not change. But Streams normally depends on the undo for the columns (typically the primary key) that you have declared for the supplemental log – even if they haven’t been changed. So your “simple fix” has to be very careful that it doesn’t accidentally take out the “not really redundant” undo that has just been generated for the supplemental log, otherwise streams fails. This is just one example of why getting the “simple fix” right at the kernel level is probably not a simple task.

Updated 8th Jan 2007:  Earlier on today, I discovered that the article in question had apparently disappeared, so I removed the above comments from my website as they no longer served any useful purpose. However the article hadn’t disappeared I had simply dropped a couple of letters from the URL without realizing it; so I’ve reposted the addendum with a corrected link.

The notes above refer to the version that was visible a couple of days ago, and had been viewed (according to the print-out I saved) 221 times in the days that it had been visible.  There have been a number of minor changes since I first trapped this article (which has now reached 235 views).  In particular the “Huh?” has been replaced by a more cogent comment about:

some unknown requirements of supplemental logging on which he does not fully elaborate, and I don't fully understand.  Maybe it's correct (maybe not), but I'll withhold judgment until I see a repeatable example of such a failure.

I thoroughly approve of Burleson’s desire to see repeatable examples. In this case, unfortunately, it would first require the Oracle kernel developers to write the code to eliminate redo (without looking at my crib-sheet in case it gave them a clue about something they might otherwise overlook). However, I hope that the brief description above elaborates the mechanisms of supplemental logging sufficiently clearly to indicate why the “simple fix” might be more complicated than it sounds at first.

Burleson has also introduced this eminently sensible paragraph to this article:

Remember, just one test case is all that is needed to show that a DML re-write approach would not always work.  Any software-side solution must be 100% safe, in all cases, on all releases, for each and every obscure feature

I agree with the sentiment – and can only add that sometimes you don’t even need to worry about looking at the obscure features before you find the places where a new feature breaks something. In this case, just check my comments above about the side-effects of locking.

There is one point in the newer version of the article that puzzles me though, and that’s the statement:

I've debated Lewis in-person on several occasions and he knows that I'm extremely knowledgeable

There may be some special (possibly American) meaning of the word “debated”, or the expression “in-person” that Burleson has in mind; but I am not aware of any such debates (in the UK English sense of the word), although I have answered one, or possibly two, questions that he sent me by email. The only indicator I have of his knowledge about Oracle comes from the articles, presentations, and postings on the Internet.


Addendum 23rd January 2007

I’ve noticed that Burleson has updated the “Superfluous Overheads” article again. One major change is the removal of the section where he explains how hard it is to deal with long documents. More significantly he has removed the section where he claimed to have “debated Lewis in-person on several occasions”.


Addendum 14th December 2007

This item has started to appear more frequently in the viewing statistics for the website. But since 10g is now quite firmly entrenched, it’s important to remember that the article is about 9i and that 10g introduced a number of changes. Joze Senegacnik has given various presentations on this topic, and a fairly recent one was at the Miracle Scotland conference a few months ago. The presentation can be downloaded from this URL which links to a Powerpoint presentation. There is a lengthy document (40+ pages) that goes with this presentation, but Joze has not yet published it on the internet (apart from a copy on the “Members-only” pages of the UKOUG website.


 Back to Index of Topics