Niall's Oracle Pages

My feed just disappeared from feedburner - and hence orablogs. Its a conspiracy I tell you. Or cock up on my part obviously.

I note today that another reference to pga_memory_management has appeared on Don Burleson's Oracle News site. Its a reprint of a tip submitted to dba-village by Franck Pachot.

Presumably its intended as yet more support for the Undocumented Secrets article on SearchOracle - though perhaps its a replacement for the various comments on Burleson Consulting related websites on the subject that have disappeared after being criticised.

Whatever its intention, I'm a little bemused by the whole disussion now. The current thrust of Don's arguments seems to be that you can increase the memory actually available to Oracle for sorts (presumably to avoid disk sorts) by setting some documented and undocumented parameters so that you can use really large sort areas, that is sort areas in the hundreds of megabytes or gigabytes. To me this begs the question as to whether you really wouldn't be better off concentrating on tuning the queries that create the requirements for these monster sorts. Typically you will see these in two situations.

First when the developer has missed, or wrongly coded, joins. Tuning wrong code is rather pointless here.

Second in large datawarehouse environments - in these I can't help but wonder if looking at materialized views, bitmap indexes and star transformations might be of more use. However as I don't do proper datawarehouses I could be off beam here.

In other words, whatever the merits of the various parameters, wouldn't it be better to ask why I think I need such large sorts in the first place.

Finally I attach some interesting output from a production 9206 database. You'll see that the pga_aggregate_target is set to 120m and the undocumented parameter untouched. There are no parallel queries (its OLTP as well). Somehow I still managed 16 optimal work area operations in the 256-512mb range. I don't have an explanation for this, but just wanted to add observation to all the words.


SQL> SELECT LOW_OPTIMAL_SIZE/1024 low_kb,
2 (HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
3 optimal_executions, onepass_executions, multipasses_executions
4 FROM v$sql_workarea_histogram
5 WHERE total_executions != 0;

LOW_KB HIGH_KB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS MULTIPASSES_EXECUTIONS
---------- ---------- ------------------ ------------------ ----------------------
16 32 9077203 0 0
32 64 81237 0 0
64 128 14718 48 0
128 256 9252 0 0
256 512 8479 24 0
512 1024 34921 40 0
1024 2048 18942 48 0
2048 4096 65558 120 0
4096 8192 2715 478 0
8192 16384 153 1061 0
16384 32768 88 758 0
32768 65536 32 362 0
65536 131072 88 345 16
131072 262144 12 24 16
262144 524288 16 0 0
524288 1048576 0 0 1

16 rows selected.

SQL> SELECT
2 a.ksppinm parameter,
3 a.ksppdesc description,
4 b.ksppstvl session_value,
5 c.ksppstvl instance_value
6 FROM
7 x$ksppi a,
8 x$ksppcv b,
9 x$ksppsv c
10 WHERE
11 a.indx = b.indx
12 AND a.indx = c.indx
13 AND substr(a.ksppinm,1,1) = '_'
14 and a.ksppinm like '%pga%'
15 /

PARAMETER DESCRIPTION
---------------------------------------------------------------- -------------------------------------------------------
---------
SESSION_VALUE
------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
INSTANCE_VALUE
------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
_pga_max_size Maximum size of the PGA memory for one process
209715200
209715200


1 row selected.

SQL> select name,value
2 from v$parameter
3 where name like '%pga%';

NAME
------------------------------
VALUE
------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
pga_aggregate_target
125829120


1 row selected.

SQL>



Thanks to Gabe on Tom Kyte's blog I became aware that blogger now attempts to stop spambots by the use of word identification. I've enabled this, and also enabled anonymous comments again.

More to the point one of the things that came out of the Undocumented secrets debacle was the problem of rating good bad or indifferent web pages. David Aldridge pointed us towards stumble upon. This tool (its a browser extension for Mozilla and IE) appears to offer a good first step to dealing with this problem. Basically it allows you to positively or negatively rate sites, and appears to offer the ability to comment upon articles that wouldn't otherwise be commentable on.

We'll see how it goes - I'm not sure corporates will like it and it could well get abused - but it certainly seems like a good first stab. You can get it here for IE and here for Firefox/Mozilla.

From Sarah Satterthwaite on the Oracle-l mailing list

When documenting your code, be sure to also document your doubts about how
you did it. I find that most of the bugs that are found in my code are
things I wasn't very confident about at the time!

I'm guessing that many of you that read this, also read Tom Kyte and Jonathan Lewis. The reverse is unlikely to be true for good reasons. Those of you that don't need to do some background reading now.
1. Tom's blog and comments
2. Jonathan's article
3. Don's revised article (registration required - but its free and its an ok site, just avoid the spam).

I'm actually quite glad of this disagreement because for me it serves to illustrate a few things.

The importance of wide experience, or at least knowledge of your own limited experience. Were I a betting man, I'd wager that the various articles around the internet - and indeed on Metalink - that state that XXXX_AREA_SIZE is ignored when PGA_AGGREGATE_TARGET is non-zero and WORKAREA_SIZE_POLICY is set to AUTO come from 2 things. A cursory reading of the docs, the implication of the word dedicated is easy to miss, and from generalized personal experience. By the latter I mean that we tend to extrapolate from our own experience as if it were universally true (or at least representative).

The importance of test cases and demos, complete with explanation. Jonathan's article is an exemplar here. You could run Jonathan's test case on your own system - no shared server sessions - get different results and conclude differently. Your conclusions wouldn't be universally true, and the wisdom of setting a parameter without reading the official docs and searching the suppliers website seems to say the least lacking, but it would be a valid test for you. The genius of course of Jonathan's test is he explains under what circumstances he would get different results.

Knowing what UNDOCUMENTED may mean, in practice it probably only means that Oracle Support would undo all of your careful changes in order to troubleshoot a problematic system, it could mean that your tinkering could render the system unsupported. Messing with undocumented parameters definitely has a place for the curious. production systems aren't that place. I can't of course resist pointing out that the major undocumented parameters discussed in the 'undocumented secrets' article are in fact documented.

It does help to set out the technical basis for ones assessment and conclusions. I seem to recall being required to do this at 'A' and 'O' level science. Don's original article was somewhat helpful here - it stated what its assumptions were. Unfortunately for the author a clear majority were incorrect and some assumptions did go unstated, but stating ones assumptions is good practice.

It doesn't help to name call and use emotive language. Don's original article contains the sentence For example, the following set of parameters indicates that the Oracle DBA does not understand PGA management. for which, rightly in my view, he was pulled up. Equally responses to Tom's blog include lines such as Tom,

Good to see people like you are no longer ignoring people like Don Burleson who most of the time post half truths and totally wrong "technical Information". You are doing a great service in pointing out such issues and I know this is not the first time you are doing it. It's almost impossible to imagine the same poster replacing 'people like Don Burleson' with 'Oracle Support' in the above sentence. Yet the metalink article I posted above would provide the same degree of justification when read on its own. It is by no means the only example either.

All technical sites should include at least a feedback link - or ability to comment.

I just noticed, thanks to Rob Vollman, that Anjo Kolk - of YAPP and Oraperf fame has started a blog. Definitely one to keep your eye on.

Brother Roger of the Taize community in France was murdered yesterday. Those that know my religious background will likely not be surprised that this man, and his community, was and is, a huge influence on me. Roger Schutz was a man who, in WW2, in France founded a community that welcomed french, germans and brits in peace and for who they are. Taize today still follows that example. Those who know anything of Christian politics may judge this protestant for the respect and influence he had on the Roman Catholic and Orthodox churches. Today is a sad, sad day.

Then I'll begin.

This is how Listen with Mother started, way back when you could make sexist assumptions about the make up of your daytime radio audience. It's a great line and still used on the rather excellent cbeebies today.

Mothers were also used in an interesting comment on usability on Tom Kyte's blog, to whit What would my mother do? That's the test I always apply to Windows software, and it usually fails. If I can't fix it, what would my 70 year-old mother do?

I truly believe that for surfing the web, she'd be better off with Linux and Firefox/Opera. its an excellent test, one I rather suspect Joel's Fog Creek software may have had in mind when creating co-pilot.

Unfortunately, I rather suspect that its a test not applied often enough to 'Geek' software, I have two examples;

1. Linux. It just so happens that I have installed Fedora Core 4 at home today - yes 10g R2 will go on it - later. It asked me for a root password, for partitioning information and a list of packages to install. All of which were easy enough for me to take in, but someone who doesn't know how computers work? probably not.

2. EM10g. Now here we need, I think, to revise our mental image of the user a little, we need someone competent with computers who has just taken over responsibility for a database. Think the 2nd line support tech just promoted.
what do they do with this? Oh yes - the failed backup is wrong and the number of up instances is incorrect as well.

A talent Ricky Ponting and Shane Warne appear to possess in spades. Well done Australia. Now stop seeding our clouds - we have enough rain as it is.

Each time that Oracle release a new version of the product a lot of attention is paid to the new features in that release. In fact once you've got an OCP then you get exams specially targetted at you, that only look at new features.

Something that gets little attention is that the feature set differences between editions also tends to change. In particular Enterprise Edition features have a habit of appearing in Standard Edition after a while. This has happened at least as follows.

New in 10g Standard
Incremental Backups
RAC
Synchronous Change Data Capture
New in 9i Standard
Function Based Indexes
Advanced Queuing

Transparent Application Failover (defined as select failover) appears to work fine in 10gSE RAC and is listed as EE only in earlier versions - but then so was clustering.

A long time ago, I took a first degree in Economics at the University of Bristol. Apart from explaining my tendency to waffle, and my desire to see theories backed up by evidence, it was where I first came across the latin ceteris paribus.

This phrase translates roughly 'all else the same' and is used in thought or math experiments by economists to imagine all other variables being artificially held constant in order to investigate the effect of a particular input variable on some complex system.

It cropped up again today in a discussion on the merits of linux vs windows as an underlying OS for Oracle databases. The problem of course is that all else isn't the same. It is extraordinarily rare these days to have two systems that are close enough to each other to compare let alone imagine that windows vs linux as an os represents a single discreet change.

Game On.

Oracle service will be resumed shortly.

If anyone else has downloaded the 10g Release 2 Developer Preview for Windows (aka beta 3 so far as I can tell) have you had any joy recreating the general purpose db - my creation fails because the restore of the users tablespace fails. I'm curious as to whether I got a bad download or it is repeatable error.

I noticed on Chris Foot's blog, and confirmed by way of the readme, that in 10gR2 the CONNECT role has been neutered.

Historically I, and many others, have advocated the banning of the CONNECT and RESOURCE roles, instead recommending using user-created roles or granting required privileges directly to user accounts. The CONNECT role has been a particular bug bear since it rather suggests that it allows a user to connect to a database, rather than its actual purpose of allowing a user to connect to a database and then create objects, database links to remote databases, alter sessions to set diagnostic events and so on. In 10gr2 CONNECT has one privilege and one only - CREATE SESSION.

I applaud this decision. Its brave, because it will break applications where required privileges haven't been thought about, but it is the right thing to do.

I also like the enhancements to security for external procedures run as jobs, though rather than grant the new privilege CREATE EXTERNAL JOB to all users with the CREATE JOB privilege on upgrade I'd have preferred a report that stated something like

The following users have the ability to schedule execution of operating system scripts and commands. For security reasons this privilege has been removed by default in 10g Release 2. If a user requires this functionality please grant the new CREATE EXTERNAL JOB privilege to that account.