.comment-link {margin-left:.6em;}

Oracle Sponge -- Now Moved To Wordpress

Please use http://oraclesponge.wordpress.com

Tuesday, May 31, 2005

ASM: Hello and godbye

So, my ASM install went tolerably well -- there was the usual bumbling about trying to translate installation instructions aimed at unix into stuff that worked on Windows

Hint to Windows users: when the documentation talks about "Cluster Ready Services" they mean the OracleCSService.

Hint to Oracle: Some people use Windows.

So in the end I had my ASM instance running, a diskgroup created, a tablespace created on it, and Oracle accessing data stored in that tablespace. Technically, a success.

However performance was dreadful -- really horrible. I was getting scattered read wait times an order of magnitude higher than pre-ASM, and because this was just a little exploration of the technology, and quite frankly I could do without the potential hassle of making my technology tower any higher, I canned the project and de-installed the ASM instance. It's not as if it was going to be the answer to any of my problems, because I really don't have any in the area of disk management.

So maybe I'll just take comfort from the idea that I had the common sense not to throw new technologies at a nonexistent problem. Sometimes it's good to just say "no" to these shiny toys.

Friday, May 27, 2005

Have a Good Weekend ..

... I'm off to Breckenridge to sponge up some mountain air for a few days.

Happy querying, MOP's.

Thursday, May 26, 2005

Junk Mail At Last

My "professional" email address (ie. the one that isn't plastered across the entire internet) has been amazingly junk free (and by this I mean 100% junk free) for two years now, but finally my run of luck is over. Three emails offering me "discount" Microsoft products within two days.

I carry no torch for the Microsofts of this world, but I console myself by forwarding every one of these emails to piracy@microsoft.com, often within seconds of their arrival. It's the little things that please.

Tuesday, May 24, 2005

Slow Datafile Creation on Windows Sort-of Solved

While struggling with some issues surrounding i/o performance on my non-representative foreign script kiddy development hardware (WinXP Pro, 1.5Gb, erm ... some disks and other stuff) I found myself creating and dropping tablespaces on various combinations of RAID0, single disk, cooked and raw disks. Tablespace creation performance was terrible -- bursts of 4Mb/sec write activity followed by stretches of nothing that averaged things out to 1Mb/sec.

Now, while investigating the impact of db_file_multiblock_read_count on read performance I noticed that performance went to hell-in-a-hand-basket when the multiblock read size reached 1Mb. Which is curious, because that's the maximum size that Windows will permit (based on level 8 10046 tracing).

So thinking that sauce for the goose may be sauce for the gander, I found a hidden parameter _db_file_direct_io_count which was coincidentally set to 1Mb. Dropping it down to 512kb had no effect, so I dropped it further to 128kb.

Lo! and the disks did perform. Well, sort of. They're writing at 20Mb/sec -- nothing to write home about, but not as bad as before I guess. Although for two WD740GD 10,000rpm SATA disks, which WD's own utility will read at 100Mb/sec combined, it's a bit off the pace.

Now, if only I could work out why my two disk RAID0 array is no faster than a single disk ... hmmm.

The Eternal Temporary Tablespace Question

Haven't seen this one in a while, but it used to be all over the forums like a rash -- "Upgraded to 8i and now my Temp tablespace is always full".

Needless to say, it's documented.

Thursday, May 19, 2005

v$filestat And Multiblock Read Statistics

More Updates: 19 May 2005, about 45 minutes later

  • Jonathan Lewis writes to point out that X$KCFIO does include multiblock statistic columns, but that the numbers are not consistent. So maybe later on I'll think about a methodology for determining what the cause of the inconsistency might be. But not now, because ...
  • I'm in the middle of an upgrade of my development instance to ASM. W00t, as the youngsters say. Otherwise I'd also be checking to see if my own math is consistent with the numbers in X$KCFIO. Actually, I don't see how they can be but it'll provide a diversion.
  • Another cause of the smaller block counts occurs to me -- that individual blocks are already in the buffer cache, hence they are not requested from disk and hence the number of blocks requested is below DFMRC. This is unlikely to be an issue on my (or any) data warehouse where parallel query is in use because direct reads are used (hence the buffer cache is not inspected), and the blocks are also less likely to be in the buffer cache due to the static nature of the data. I think this is highly unlikely to be the case with the Peoplesoft instance.
  • Happily though, the validation of the DFMRC was just a side effect -- what i was really looking for here was the average wait time. If the numbers in the X$KCFIO are not completely consistent then that may not be a problem. As long as some major class of read is not completely omitted, or the numbers on which the average wait are not all updated consistently, then the methodology may be viable. Can't test it right now though, of course.
  • I think I'm going to change the subtitle of my blog.
  • Note to self: get JL to check my tax returns next year. He's a sharp-eyed devil-for-detail. Thanks in advance, JL.

Update 19 May 2005 (Original posting below)

Jeff & Jan, thaks for the feedback there.

I'm thinking that some of the issues there may be due to extent sizing -- multiblock reads obviously won't fetch the full DFMRC on blocks if the extent is not that large, so I'm thinking of a query to breakout files according to extent size (that ain't going to be "performant", I'd guess). But because what we see from v$filestat is an average then numbers based on any file with lots of small extents (which may be more likely to get FTS and hence multiblock read requests) would be skewed by this.

The method might have limited application because of this -- in a data warehouse there's probably many tablespaces with only fact tables, therefore with only large extents. Not a problem, because those are the ones where the multiblock reads would be prevalent, of course.


Original Article 17 May 2005

So, the v$filestat view gives us some statistics on the number of disk reads and their duration, documented for 9i here. In 10g we have the same structure, with the enhancement that historical snapshots of v$filestat are preserved in DBA_HIST_FILESTATXS. It would be a pretty trivial task of course, to automate this collection in prior versions through DBMS_JOB. The v$filestat table is mostly based on X$KCFIO, by the way, but there do not appear to be any additional columns in X$KCFIO of interest here.

The view breaks out read statistics (and write, but I'm just interested in read right now) as follows:
  • PHYRDS: Number of physical reads done
  • PHYBLKRD: Number of physical blocks read
  • SINGLEBLKRDS: Number of single block reads
  • READTIM: Time (in hundredths of a second) spent doing reads
  • SINGLEBLKRDTIM: Cumulative single block read time (in hundredths of a second)
So here's my question: given that we have total read statistics and single-block read statistics available, can we use these as the basis for calculating multiblock read statistics? Logically, it would seem so, although maybe this would also include direct reads as well as the common or garden variety.

So, we might define the following:
  • MULTIBLKRDS: The number of physical reads in multiblock mode = (PHYRDS-SINGLEBLKRDS)
  • MULTIBLKBLKRD: The number of blocks physically read in multiblock mode = (PHYBLKRD-SINGLEBLKRDS) ... because a single block read by definition is a read of a single block.
  • BLKSPERMULTIBLKRD: The average number of blocks read for each multiblock read = MULTIBLKBLKRD/MULTIBLKRDS = (PHYBLKRD-SINGLEBLKRDS)/(PHYRDS-SINGLEBLKRDS)
  • MULTIBLKRDTIM: The total time spent in multiblock reads = (READTIM-SINGLEBLKRDTIM)
  • AVGMULTIBLKRDTIM: The average time taken per multiblock read = MULTIBLKRDS/MULTIBLKRDTIM = (PHYRDS-SINGLEBLKRDS)/(READTIM-SINGLEBLKRDTIM)
The tests that I've performed on my PC look pretty encouraging. I was looking in particular for BLKSPERMULTIBLKRD to be equivalent to DB_FILE_MULTIBLOCK_READ_COUNT (based on a single tablespace block size being used throughout the database), and that looked OK. Haven't had a chance to run it on a production database yet though.

Here's the final query I came up with:
select
file#,
PHYRDS-SINGLEBLKRDS MULTIBLKRDS,
PHYBLKRD-SINGLEBLKRDS MULTIBLKBLKRD,
(PHYBLKRD-SINGLEBLKRDS)/
Decode(PHYRDS-SINGLEBLKRDS,0,Null,PHYRDS-SINGLEBLKRDS)

BLKSPERMULTIBLKRD,
READTIM-SINGLEBLKRDTIM MULTIBLKRDTIM,
(PHYRDS-SINGLEBLKRDS)/
Decode(READTIM-SINGLEBLKRDTIM,0,null,READTIM-SINGLEBLKRDTIM)

AVGMULTIBLKRDTIM
from v$filestat
/


Perhaps you could hive it a try and see if it works for you. Feedback and comments are very welcome.

Wednesday, May 18, 2005

More Technology Problems

The other day I described the passing on of my crappy Lexmark printer, and since then technology has been kicking back.

My crappy Phillips DVD/VCR combo now refuses to play any disks, and from searching the web this appears to be a common issue. No help from "support" so it's getting relegated to a kids VCR player. Hopefully they won't be too gentle with it. I've replaced it with the cheapest I can find -- $38.

Then I went to a coffee shop and took out my new Tungsten C, then nearly dropped it because it was too hot to hold! This was one seriously toasty piece of electronics -- some electrical fault I suppose, but needless to say that was the end of it. Circuit City swapped it for a new one no-questions-asked though, so we'll see how Son of Tungsten C holds up.

Hopefully trouble comes in threes and there'll be no more cheeky electronic shenanigans from now on, 'cos that garage floor is still available and it ain't getting any softer if you know what I mean.

Tuesday, May 17, 2005

A Literary Diversion

Back in my school days, when I was forced to study "Animal Farm" for a term or two, I didn't appreciate George Orwell nearly as much as I should have, and although I reacting strongly to the message of "1984" the shine was taken off of it by the execrable film adaptation.

It seems that the vision of IngSoc lives on -- persons can be turned into unpersons, and history can be deleted to pretend that events never happened, and we get a new appreciation for the principle that "Who controls the past controls the future: who controls the present controls the past".

And as you watch people post intelligent comments and advice in a forum you are sometimes reminded of Winston's thought, "Unquestionably, Syme will be vaporized ... there was something that he lacked: discretion, aloofness, a sort of saving stupidity" -- although not in an unkind way, of course. But you know that pretty soon all of their helpful advice will be gone and their existence will no longer be acknowledged, because Big Brother was watching them.

You know what I'm talking about?

Of course you do. But if not, here's that book recommendation again: 1984 by George Orwell.

The Direction of The Sponge

Have you noticed that there are an enormous number of articles available to the Modern Oracle Professional (MOP) that highlight and praise other articles? It's sort of like a scientific citation of a previous work, I suppose.

But who, I ask you, is drawing attention to the less-than praiseworthy articles? The articles that represent documentation dumps, or which give you no useful information whatsoever. You know the ones I mean -- you read them and are then left with a hollow, empty feeling inside. "What did I just learn from that?", you wonder. "What information did this give me that I can take away and make use of?", you cry.

No-one, that's who. Well actually some people are but it's often an incidental exercise, not the major thrust of their article.

Maybe that is the niche that the Sponge can occupy -- the fair and balanced debunkings of bad articles. Nothing distasteful* you understand, nor anything disrespectful to the author, but an honest appraisal of both good and bad points.

Ground rules are obviously essential, and here's a few off the top of my head.
  1. Spelling mistakes will be penalised heavily
  2. Bad grammar is inexcusable, except where English is the second (or third etc) language of the author. (OK, so it is excusable by that then).
  3. There will always be a right-of-reply. No exceptions.
  4. It is acknowledged that the authorship of a Bad Article is not a sign of moral degeneracy or bad character.
  5. Extra penalty points shall be awarded if the article was publically presented.
  6. False conclusions, straw men, non sequiturs*, and that Latin phrase that TK quoted are inexcusable.
  7. Citing other articles that are not publicly* available in support of your advice is a Very Bad Thing.
  8. Citing other articles that do not support your advice, in support of your advice is also a Very Bad Thing.
  9. Erm ...
  10. That's it
Additional ground rule suggestions are always welcome.

So what about it, MOPs? Can you go for such a trend?

* Spelling corrections courtesy of a man who spells his name with a Y instead of an I. Grrrr.

Monday, May 16, 2005

Histogram Meme Spreading?

Back in April I wrote up a notion I had on the usefulness of histograms. The main point was that histogram usefulness is not confined to skew on existing values -- if there are values between the lowest and highest for a column that might exist but do not, then that is a form of skew that we can usefully document for the optimizer in the form of a histogram.

Did that makes sense? Oh, just read the article and the comments. And here is the one from before that was a little faulty.

While I hold my head high with the claim of "original thought" (on account of it being my own idea -- the kids are really proud of me btw), maybe it's one of those ideas that lots of people get at the same time. Like lightbulbs. Is it called Morphic Resonance?

Well anyway, here we go. I guess it might be spreading. If Arup, Jeff or Mike have anything to add that I missed I'd be happy to hear from them. I love a consensus.

* Clang * -- yer out!

Jeff has been expelled from Don Burleson's forum.

He was doing great until he questioned the wisdom of hosting a production database on a $750 computer from CompUSA -- bad Jeff, bad Jeff. What took you so long, dude?

Here's a question that this provokes though -- if your production server is a single processor PC-architecture $750 box from CompUSA, can a single-user test on a desktop PC then be called a performance "proof"? Opinions, as always, welcome.

To be fair to Don though, he's having an unhappy day. Bad Howard! And all who associate with him. Bad, bad, bad.

Meanwhile ... we're debating weighty issues of "RAC - Good Or Bad For Oracle Data Warehousing" at The Dizwell Forum. Why not pop over and chip in your two-cents?

Wednesday, May 11, 2005

* Sigh * More on Myths

Well I've ploughed my way through Part 2 of the article here, and have come away even less impressed.

quote: In this, our last installment we examine the nature of Oracle myths and understood how changing technology effects mythological status.

That may have been the plan, but the opening sentence is the last we hear of this. I believe that "mythological status" and "the status of myths" are two completely different concepts, by the way.

quote: Today we see two “types” of Oracle Professionals, each saying that other Professionals are perpetuating new Oracle Myths. In one group, the “Researcher” seeks “laws”, tautologies about Oracle behaviors that are true, while the Empiricist seeks “correlations” and deals in probabilities rather than absolutes.

WTF "happened" to the doublequote "key" in this "sentence"? And isn't a tautology always true by definition? Does the author know the meaning of the word tautology? I doubt that anyone one claim that what researchers seek are "empty or vacuous statement[s] composed of simpler statements in a fashion that makes [them] logically true whether the simpler statements are factually true or false".

quote: Many Research Professionals believe that “rules-of-thumb” (ROT) are very dangerous, and note that if an ROT can be shown as invalid, even in a single artificial test, the ROT is not scientifically valid

In general things that are shown to be invalid are not scientifically valid. In fact, things that can be shown to be invalid are generally not valid, full-stop. It's called an "oxymoron".

When are rules-of-thumb dangerous? When they are applied without thought or understanding, often under the influence of a mentor who actively discourages understanding by telling people to just trust them, even when their advice is demonstrably incorrect.

quote: Detractors note that simple, generalized “rules” form the basis for many of the Oracle 10g automation features, such as Automatic Memory Management (AMM) and the SQLTuning advisor.

AMM uses simple generalized "rules"? Or simple generalized rules? There we go with the double-quotes again. Based on previous postings, I don't believe that Mike knows how it works. SQLTuning advisor may indeed use simple generalized "rules" (or rules, whatever), leading to debacles such as the one described here. So much for simple, generalized rules.

quote: This myth says that running single-user SQL*Plus scripts to “prove” how an Oracle change will behave in production are almost always invalid under multi-user database activity. Detractors say that it is not necessary to run large-scale, multi-user benchmarks to “prove” how Oracle behaves.

Read that first sentence closely. "This myth says that running blah-blah-blah to "prove " blah-blah-blah are almost always invalid under multi-user database activity." "... are almost always invalid"? Very poor and clumsy grammar. Moving along though, this is a lovely dismissal of Robin Schumacher's "indexes love large blocksizes" script.

And what does "almost always" mean? What a vague and useless phrase. If I demonstrate with a script that an index organized table can be accessed with multiblock reads, is that invalid in a multiuser environment? Nope.

quote: So, are these myths? Borrowing from Don Burleson’s hilarious DBAZine article “What type of DBA are you?”, we see several Oracle personality types. In fairness, he created these deliberate generalizations to illustrate the mindsets of the Empirical and Scientific professionals.

So now we're supposed to decide whether these technical issues are myths based on an allegedly-hilarious article that deliberatly generalizes mindsets? There we have the essence of the "empirical" "mindset" -- don't discuss the technology, don't try and understand the internals, just use smoke and mirrors to detract from the lack of substance.

quote: Empirical professionals utilize the heuristic approach to observe behavior and apply it to new situations. ... Heuristic 1. A rule of thumb, simplification, or educated guess that reduces or limits the search for solutions in domains that are difficult and poorly understood. Unlike algorithms, heuristics do not guarantee optimal, or even feasible, solutions and are often used with no theoretical guarantee.

Hmmm, so professionals are using a method that does not guarantee even a feasible solution, eh? Hopefully that's spelled out in the consultancy agreement. "The client acknowledges that their domain is difficult and poorly understood, and that the consultant's solutions shall not be guaranteed to be feasible." Where do I sign up for that then?

quote: The Empiricist DBA does testing with benchmarks, not test script.

A fine distinction, I expect. Not helped by the following.

quote: Does this mean the Empiricist blindly charges in making bold changes to clients databases without testing, checking statistics, waits, IO rates and timing and myriad other factors before applying heuristics?

Oh so the empiricist does test -- presumably not with a script however. With some other technique. The same technique used to check statistics, waits, IO rates etc.. But not a script, right?

quote: The Research DBA has the motto is “Prove it,” and “Trust, but Verify”.

OK, that's the last grammar warning. "... has the motto is ..." indeed!

If this is the case then the empiricist's motto(s) must be "Don't bother proving it" and "Trust without verifying" I guess.

quote: The Research DBA believes that a database can be described with simple SQL*Plus test scripts and every assertion about database behavior can be proven with such research.

No they don't. That's what someone would want you to believe if they wanted you to distrust research methodologies in favour of just "trusting the wise consultant".

quote: Mark Rittman notes that the Empirical DBA approach is problematic because it suggests that novices cannot become expert in Oracle tuning without many years of experience

Not just problematic, but downright wrong. Novices can become expert in Oracle tuning by understanding the internals of Oracle and the way that problems can be detected.

quote: Some authors are misleading their trusting followers with the mantra of “Prove it”, and they never note that “your mileage may vary”, especially for performance-related proofs.

Some authors post articles on how indexes and temporary tablespaces love large block sizes with neither proof nor "your mileage may vary" disclaimers. However when given a test script, anyone can test exactly how much their mileage will vary before (for example) rebuilding their database with a 32kb system blocksize in order to get a 32kb temporary tablespace size, which it turns out is a complete waste of time. A series of scripts could have proven to them that temporary tablespace access uses direct i/o. Instead, I guess they just trusted the expert. * sigh *

quote: Often when faced with real-world situations they will retreat into the mantra “Rebuild the entire application and call me in the morning…”, a response that while is probably true, doesn’t really help in the real-world where down time or lost time results in lost money.

I have no idea what "rebuild the entire application" means, but apparantly it's "true". Does that mean "valid"? I have no idea. I'm sure that there are anecdotes available to *ahem* "support" this stereotype.

quote: The research DBA believes the Empirical DBA is lax and sloppy, and does not pay enough attention to details.

Some of them are, I expect. There's always lax and sloppy people in every profession, but that doesn't mean that they're all empirical. The problem is that when an empirical consultant is lax and sloppy you have absolutely no way to find out. When someone bases their advice on facts, then you have the basis for discovery.

quote: They make bold assertions about Oracle behavior that can be shown wrong under certain circumstances ..

"... that can be shown to be wrong ...".

quote: The Research DBA believes that the Empirical DBA is a “loose cannon” and cannot understand their impatience and disregard for elegant proofs and detail. Secretly, they think that the Empirical DBA is dangerous, and cringes at their propensity to rush into every database problem without supporting justification.

Not "secretly" at all. Here: "Advising someone to create their database with a 32kb system block size in order to reap a temporary tablespace performance advantage is dangerous, and it makes me cringe, and I believe that there is no valid justification for it". There you go.

quote: These “Script Kiddies” don’t test their hypothesis on large multi-user databases and they don’t understand how a high concurrency and load Oracle environment will change the results.

Their hypotheses are written down, and are therefore testable of large multiuser databases. They do understand how a high concurrency and load environment will change the results because they understand the internals of Oracle.

quote: They believe that the scientific method is the only way to effectively approach an Oracle performance problem, and all broad-brush tuning (changes to system-wide parameters, using faster hardware) are unscientific, non-elegant and offensive.

"Yes" to the first part, "rubbish" to the second. You change system-wide parameters when required, and you use faster hardware when required. And ironically there is no such word as "non-elegant" -- it is just an inelegant way of saying "inelegant".

quote: With math you can prove that for a given gravity well, a feather and a lead ball will accelerate at the same rate toward the center of that gravity well. In the real world we know the affects of the surface area to weight and resistance will result in widely different rates of acceleration.

Yeah, that whole lead ball and feather thing has been a mystery to scientists since ... erm ... oh hang on no it isn't a mystery is it? It's called aerodynamics, or fluid dynamics, and it is pretty scientific stuff as far as I recall.

quote: The successive refinement of their heuristic rules form the experiential basis for “expert” Oracle tuning as noted in the book “Oracle Silver Bullets”.

Yeah there's nothing like backing up your arguments by mentioning a book written and published by your empirically-inclined employer, although when you put the word expert in doublequotes it kind-of implies that you don't think that he's an expert, Mike. Just a little career tip for you there. ;)

quote: This from Robin Schumacher, author of “Oracle Performance Troubleshooting”: http://www.rampant-books.com/book_2003_1_perf.htm ... It's fine for DBAs to perform trials and postulate theories as long as they realize those theories may crash and burn in the real world. Or, as someone well said a while back, "watch out when a beautiful theory meets a brutal gang of facts."

Does that apply to his "indexes love large block sizes" script as well, d'ya think?

quote: While single-user proofs validate how Oracle reacts in a single-user environment rely on real-world benchmarks whenever possible for decisions involving multi-user systems.

But if that benchmark "proves" that you should use a 32kb block size to make your temporary tablespace access more efficient, then run like hell.

Myths About Myths

In case you "mythed" it (it's a pun!) here is YAFMA (Yet Another Feckin Myth Article) by Mike Ault, going down the well-worn path of Oracle Myths. Thanks to Bill S for pointing it out.

So this posting is on the subject of myth generation, because (pause for effect) this article itself is in danger of generating a new one! That's right, before our very eyes a new myth is born. See if you can spot it in this quoted excerpt ...

Ancient Myth: A data buffer hit ratio (DBHR) should be kept greater than 90%

This myth was also propagated by Oracle Corporation in the early 1990’s when almost all Oracle databases were I/O-bound and SGA size was constrained by the 32-bit server technology. Oracle-based products such as SAP also note in their manuals that the DBHR should be over 90%. Oracle author Robert Freeman notes:

It has been demonstrated on many occasions that it is easy in a basic proof to prove just about anything. Given a basic proof I can prove that the buffer cache hit ratio means nothing, or I can prove that it is the most important thing in the world.

I know of several scripts which can be run against your database to give you any DBHR you desire. Does this make a myth? Oracle does not seem to think so, and ratio-based advisories form the foundation of the Oracle10g Automatic Memory Management utility and the v$db_cache_advice advisory.


See it? You might miss it at first glance, but it's nestled in there all the same. Here it is: "ratio-based advisories form the foundation of the Oracle10g Automatic Memory Management utility and the v$db_cache_advice advisory".

I can think of two ways of interpreting this:
  1. "The Buffer Cache Hit Ratio (BCHR) cannot be completely invalid because the Buffer Cache Advisory (BCA) functionality uses a form of BCHR as the basis for it's 'advice'".
  2. "The Buffer Cache Hit Ratio (BCHR) cannot be completely invalid because the Buffer Cache Advisory (BCA) functionality presents its results in the form of a ratio"
If the former is the correct interpretation then I'm going to call "bullshit" on it right now. Anyone who has seen a BCA result graphed will see that there are generally all sorts of flat spots and dog legs involved, and there's no way that those came from the interpretation of a single BCHR number. I had a discussion on this with two luminaries of the Oracle world, and they were unanimous in decrying the "BCA is based on BCHR" theory - it is much more complex than that and hopefully a more complete explanation will be published when their time permits.

On the other hand, maybe the second interpretation is the correct one? Maybe, but in that case the argument is just nonsense. The BCA ratio is of "physical reads (current buffer size)" to "projected physical reads (modified buffer size)", and that's not a BCHR -- it's nothing to do with BCHR. To infer that the presentation of a trend of PIO:PIO ratio by the BCA is somehow an endorsement of the BCHR is bizarre and threatens to mislead the "neophyte". Hey, I could present you with a series of ratios depicting frequency of a two-year old's evening bowel movements in relation to the number of sugary fruit beverages drunk at day care, but while it would be both a valid and a useful ratio it would not mean that all ratios are useful. Would it?

So the article is ambivalent on the subject of BCHR's. Is their usefulness as a tuning aid a myth or not? And because of it's imprecise wording it threatens to spawn off a myth of it's own: that Oracle's buffer cache advisory is based on buffer cache hit ratios.

Sloppy work. We await a clarification of the intent of this section.

Tuesday, May 10, 2005

More Trigger Fun

Another scenario that will not work in a multiuser environment. Seems like a case for an on-commit fast refresh materialized view defined as ...

Select COUNT (*) num_rows
FROM mytable
Where relationship = 'friend'
having count(*) > 2;

Place a check constraint on num_rows to say 1=0 and there you go ... multiuser compatibility for a multirow constraint.

Monday, May 09, 2005

The Durability of Technology

My Lexmark multifunction printer/copier/fax had always been a worthless piece of crap, so this morning I was pleasantly surprised by it's apparant robustness as I smashed it to pieces on the concrete floor of my garage.

The machine's print quality had always been abysmal, even when the paper feed didn't snatch so violently at one corner of one-or-more sheets that it would be fed through all skew-whiff, and the crappiness of the supplied control software had reduced it's status to that of a mere fax machine. The printing function has been taken over by an OKI B4200, which sits quietly churning out vast copies of high-quality and low cost black and white prints without fail. Let me emphasise that -- the OKI B4200 has never done anything to give offense, nor has it ever failed to do it's job, and to me that is the purpose of technology -- to avoid irritating it's owner, in my case "me".

The Lexmark irritated me from Day 1, and when this morning it failed in it's one remaining task of sending a fax, by either failing to feed in any of the to-be-transmitted pages or by feeding them all in at once, it was time for it to meet it's end. I could have donated it to Goodwill of course, but that would have been a crime against humanity. Like a vial of 1957 H2N2 influenza virus it had to be destroyed for the good of mankind.

It took the first drop manfully, losing only it's paper feed support tray thingy and it's aura of smug self-satisfaction, and no real damage was noted until the third drop, a front-right corner-first effort that made the control panel shoot off sideways and my cat to hide under the bed. Two more drops later and it was finished. I had expected it to fly apart on the first drop, but it surprised me to the end.

It occurs to me that it's functionality has not only remained undiminished by the mornings events, but it is a sight more energy efficient sitting in the trash with the diapers/nappies than it ever has been before.

Friday, May 06, 2005

New Link

... to Pete_s' blog, where he mentions both chocolate and data warehousing -- some would say it's the perfect combination. He needs an article on there about performance benefits of tea-drinking though.

Writing Good SQL -- Example

Have a quick browse through this thread.

The solution has it's own problem -- using a CASE expression to workout the earlier of two dates is too complex. As I wrote here, "There are around 170+ built-in SQL functions in Oracle 9i. You don't have to learn them all, but you do need to know which ones exist. If you think that a particular built-in function ought to exist then it probably already does."

The required function is LEAST(). Not only would the intent of the SQL be more clear and the code more concise, I bet that the performance difference could be benchmarked also. As a rule-of-thumb* the built-in functions are going to be faster than anything that you jury rig to do the same thing.

* I think this is my first mention of RoT. Hooray!

Thursday, May 05, 2005

Mutating Tables and Hash Clusters

Here's an example of a problem that is probably caused by too much denormalization. In many cases designers are trying to get fast access to an aggregated number by storing it in a seperate table -- from my own observation it's usually a SUM(), but here we have a MAX() . Same principle.

Often an attempt is made to use a trigger to maintain a higher-level table by reading the table on which the trigger acts. Here's some issues with this.
  • Obviously there is a mutating table condition here, which requires complex code to work around. In this case the requirement could actually be fulfilled by just querying the aggregate table of course, to see if the new version number is higher than that already stored there.
  • It is not very obvious to a reader of the application code how the higher level aggregate is maintained, because the code that performs the maintenance is nowhere near the code that modifies the triggered table. So once you have established the practice of using triggers as part of an application you have instantly made the maintenance job more difficult.
  • To get an accurate value stored in the aggregate table you must serialise the application on the update operation.
  • Oh, there's just so many more. I don't like triggers for application code.
If the aim is to be able to quickly work out what the aggregate value is for a particular key or set of key values then scanning the detail records may actually be a fast option. An index range scan is likely to be used (providing that the appropriate indexing is in place of course) and the "trick" to good performance is to ensure that the rows for the detail records are co-located in the table. And how do we ensure such a thing? Well, typically with a hash cluster. In fact if the detail table is often subjected to range scans on the some key value (as might be the case with an invoice detail table, or an employee's pay records, to take two examples), then a hash cluster may be beneficial in many other queries.

You'd be a fool to go taking my word* for this of course, so if this is all jibba-jabba to you then go and take half-an-hour to read the relevant sections of the Concepts Guide (link in side panel), then go play around with the structure. Pay attention to the possible negative impacts of such a beast also -- remember, if hash clusters were 100% advantageous and 0% disadvantageous then every table would have one. Evidently, that is not the case.


* To be fair to me, I should say "don't take anyone's word for it". :)

Added new articles

Rbaraer suggested adding a link to the "Writing Good SQL" posting in the sidebar, so I'll be adding that and a couple more. Thanks, R.

If anyone wants to get links in there to anby other articles then I'll be happy to oblige.

Wednesday, May 04, 2005

Inverting the Myths

Mike Ault has an article at searchoracle.com titled "Oracle myths debunked".

* sigh *

The problem is that he has the myths backwards! The way that he phrases them it it the lack of taking some action that is the problem -- apparantly not seperating tables and indexes is a myth, and not reorganising tables and indexes is a myth, and not using multiple blocksizes is a myth.

All of these are of course convenient actions that a person could advise someone with a performance problem to take -- if your client can't touch the code, then what are you going to advise them to do? Rebuild indexes, reorganize tables, recreate your databases with a 32kb system and temp tablespaces block size?

Setting aside that quandry, Mike's arguments do not stand up to scrutiny, and in some cases just represent straw men.

Take the seperate table/index tablespace issue. If you want to monitor i/o by seperating them, then that's fine -- use the tablespaces as administrative tools. No problem, and I'm not aware that anyone would disagree with that opinion. But Mike lets himself down with some self-defeating logic when it comes to performance issues here. Let's follow Mike's logic.
  1. Table and index access is sequential (OK, common basis of understanding)
  2. If table and index are on the same drive then excessive head movement is required even in a single user system (I can see that -- sure)
  3. "In a multi-user environment, it fails to take into consideration all of the above plus the effects of multi-user access against co-located tables and indexes."
But in a multi-user environment, the heads are always moving around anyway. If any environment was going to benefit from seperating tables and indexes it would be with a single-user! To me this is forehead-slappingly obvious -- that the argument in favour of not seperating tables and indexes for performance reasons is strengthened by consideration of a multi-user environment. Ah, go figure.

The multiple blocksize issue is in my opinion, the most nonsensical. The only evidence that has ever been put forward for this is a single script to demonstrate that with double the block size you get half the number of logical i/o's (as if anyone needed a script to tell them that!), and a single anecdotal comment with no detail to back it up. That's it! Oh, apart from "Trust me". I've seen the most appalling technical nonsense talked about using large block sizes for temporary tablespaces, when it just does not (and cannot) make a difference to performance.

And don't think I haven't tried to get some technical discussion going on this -- but there is apparantly no further evidence available. Oh yes, you'll hear that TPC-C benchmarks use multiple blocksizes -- that's true, but I haven't seen one yet where they were used for TEMP tablespaces or for storing indexes. Draw your own conclusions, men.

So please, for the love of God will someone from Burleson Consulting please step up to the plate and tell us how TEMP tablespaces benefit from large block sizes, and why TPC-C benchmarks don't put indexes on 32kb tablespaces.

Please guys, I'm begging you.

I'm on my knees right now as I write this.

Perhaps in the second half of this two-part article, Mike could actually explain this in technical terms, and address his critics.

Because you know fellas, this is really how myths get started.

PeterK is a Six Letter Word

PeterK was a marked man over at the Oracle DBA Forum. Posting a critique of DKB's article "User is a four-letter word"caricaturing users got him zinged by Mike Ault for not having a sense of humour. Then he posted a new question on "Locking topics" here.

Can't find the threads? They been zapped. Can't find Peter? He been zapped too.

Thus the BBB club expands by one more honourable soul. Peter will find comfort and support in this, his time of need, over at DBASupport.com, where apparantly even hitting on the board administrator is not enough to get you "disciplined"! Oddly enough I don't anyone propositioning the administrator of the Oracle DBA Forum....

Yesterday's High Point

Normally Mr Hanky provides laughs in the form of misguided political "philosophy" and homo-erotic references (sometimes, both in the same posting), but his reply to the praise of imose here was one of the funniest things that happened to me yesterday. Which is a pretty damning indictment of yesterday, I'd say.

Tuesday, May 03, 2005

Articles

I put links in the side panel to some of my articles.

Uh Oh.

UPDATE:

Howard's back at a modified address -- link has been updated appropriately.


ORIGINAL POSTING:

Dizwell's been hacked.

http://www.dizwell.com/BB/index.php

BETWEEN ... a rock and a hard place

UPDATE:

Coincidentally while planning the downfall of the BETWEEN joins I had an email containing a relevant report SQL that had jumped from 10 minutes execution time to 31 minutes. A prime candidate for testing the theory ...

And it works. Report time dropped to 40-50 seconds -- depending on how you look at it, that's an improvement of between 12 and 40 times. Not too shabby, though I say so myself.

ORIGINAL POSTING:

One of the recent enhancements to my client's system called for some rather unconventional financial reports to be available. They're the sort of thing much beloved of accountants, where row one is the sum of rows two-through-eight minus rows nine, and row eight is the sum of rows 8a through 8g (except for locations beginning with the letter G) etc.. Some gentle probing showed that there were about five different attributes that had to be considered when deciding which rows of a fact table would contribute towards which rows in the report. Roughly speaking these were General Ledger Account (GLA), Customer Class, Source of Funds, Account Classification and Location.

Yuck.

Furthermore, this series of reports were not static but could be expected to change their definition month by month, so that added another new variable making six in all.

Now it's possible that this could have been done through a Business Objects full client report -- the interface is wonderfully maleable and all sorts of things are possible in it, but the Web Intelligence interface did not allow anything close to this flexibility, and we really needed to get this available in the WebI environment.

so that meant that something had to be done in the database.

A little further probing showed that each line in the report could generally be defined by a series of ranges of attributes. For example, line 8 might be defined as the sum for all general ledger accounts between X and Y at locations beginning with the letter G. When all these combinations had been identified the design became based around a hierarchy of tables that defined ...

* The report: Name of report, subtitle of report etc
* The report line: Line number, Line display order, Line description etc.
* The report calculation: sets of ranges of attributes that contributed to the report line: Start date, end date, start GLA, end GLA, start location, end location etc.

This had the advantage that we could transparantly change the definition of a line calculation at any time, and have different calculations used in the same displayed report for each month if we (or the client) chose. The fact table was joined to the report calculation table with a series of joins such as ...
fct.gla between calc.start_gla and calc.end_gla and
fct.month between calc.start_month and calc.end_month
.. and everyone was happy.

That is, they were happy until a third party put a rule on the firewall that disconnected http connections if they were idle for more than two minutes.

You see, although the method was pretty robust and flexible, it was a long way from being very fast. The reason for this is that when we started using BETWEEN joins against the fact table, we lost our ability to use hash joins. Now for those in the OLTP world the hash join is sometimes a bit, shall we say "maligned". In fact it's often disabled in such environments because it's really only suited for joins involving at least one large data set and requiring many of those rows to be joined -- typically this is between a fact table and a dimension table, and it's a key part of a Oracle's (patented, apparantly) star transformation. But hash joins only work where A = B, not where A > B, and certainly not where A between B and C.

And so the optimizer was using a nested lookup join between a multi-million row fact table and a few-hundred row sort-of dimension table, and this means S-L-O-W with a lot of single block logical i/o's and a lot of CPU usage.

So a little while ago I thought of a reasonably elegant workaround for this problem. The solution is either ...

1. To reduce the number of rows involved in the nested loop join, or
2. To make the join to the fact table an "A=B" type, thus allowing a hash join to be used.

The solution that I've come up with is that we will "materialize" the results of the join between the report line calculation table and the fact table. So we perform the same BETWEEN join as before, but between the report line calculation table and a "Select distinct gla, month ... from fct" query (which I reckon to be around 10,000 rows instead of several millions). This will give us a result that we can equi-join to both the report line table and the fact table.

Does any of that make sense? It adds a new stage in to the ETL process in which we refresh the "materialized" join, but that's not too bad because we can probably just consider the new data being added to the fact table with each load cycle.

My development system likes this a lot more, but I'll have to send some scripts over to the production boys to get them to check up on it, just to CMA ;)

Monday, May 02, 2005

Tracking Software

If you were going to be super-finicky then you'd say that this is not strictly speaking an Oracle-related matter, but Wired Magazine has an article on the tracking of tea-brewing in an office environment. Since I've worked with teams of DBA's whose very life-force only endured through application of a carefully judged ratio of tea and beer, it is in some sense at least semi-related to Oracle.

It would be refreshing for students to have to design a database schema to support this tea-tracking functionality, instead of the usual student-database crap. Or DVD Rental crap.

It's funny how the really great ideas only seem obvious when someone else has thought of them, but there you go, that's life I suppose. I regret that as a home-worker I always have to make the tea myself.

The Monday Morning Flakey Hypothesis

Today's flakey hypothesis is this:

When you use DBMS_Stats.Gather_System_Stats() (note: no parameters to be passed) then Oracle works out system stats by reading all the table segments in the first system tablespace data file, and it does it in single block reads. Or something like that.

I really have no idea if this is true, but here is the flakey work on which this hypothesis depends.
  1. A 10046 trace reveals that a list of datafiles is retrieved, the first of which is the (single) data file for SYSTEM.
  2. The procedure takes almost exactly 90 seconds.
  3. PerfMon shows 90 seconds of read on the disk drive that SYSTEM is on.
  4. Therefore I deduce that the process read about 270Mb of data.
  5. There are 254 Mb of table segments in SYSTEM
quod erat flakeystrandum

Pretty thin stuff, eh? It makes some sense though. The procedure as executed is recommended for empty databases, so what would you read?