Could someone please explain Baselines to me?

I see the word baseline used a good deal these days. Enterprise Manager or Grid Control (or whatever it's called this week) either has or will have the ability to collect baselines, I'm told.

My first problem with this is that I don't know the definition of a baseline, let alone a baseline for a database-centric system. If somebody has a good definition, please let me know. How exactly does a baseline rise from the collection of, say, statspack reports?

My second problem with baselines is that I cannot see the usefulness of them, but that could easily be because of my first problem.

Here's my background for putting this topic forward:

1. As mentioned, I see the word used, but I never see any examples of an implementation of baselining.

2. If everything in a system is slow (controller died, disk system exploded, months' end, etc.) then I want to know how a baseline can pinpoint the reason.

3. If a batch job or a report or a form is slow, then I'd like to know how (if) a baseline can find the reason.

The max value of Null

Nulls are always hot to discuss, especially as it often ends up being something DBA's and developers can argue about for entire lifetimes. Surprisingly few lifetimes are shortened because a DBA and a developer get into a fight over the Very Important Topic of ... Nothing/Null.

Well, two recent examples on how to handle nulls in important systems are so funny that I have to tell them, then I shall say no more on this topic. After all, Chris Date has written entire books on the topic, while another good friend, Lex de Haan, somehow restricts himself to entire days of the (three-valued) logic of Nulls.

First Case: A huge, public system is based on a system developed for another database than Oracle, and so the whole application is made "database independant".

I quoted Jonathan Lewis' chapter 10 of Tales of The OakTable where he says that only many users or much data will create problems for database-independant designs, and since this system will be big and busy, it should be fun to watch in the decades to come.

For one thing, no RI stuff was implemented using the standard SQL way of doing so. After all, who needs PK, FK, and all the rest when you can - hold on to your tables and chairs - can implement the whole thing using PL/SQL in triggers and procedures. Hey, never mind the famous /*+ no_trigger */ hint available in some versions. We're going on that picnic.

Second, nulls were implemented as 192 blanks. No, I'm not drunk and I haven't eaten funny mushrooms. I haven't even had more than a lot of beers in the last hour. 192 blanks it is. Of course I think 192 is a beautiful number, near-perfect, to tell you the truth. But why exactly that number to represent a null value? We'll possibly never know.

Second Case: A DBA told me this story: It had been a long and hard Summer, trying to convince the developers to use, say, only not null columns in primary keys - and not succeding. Well, there were glimpses of hope, they were beginning to understand things about ER diagramming.

That's what the DBA was thinking, hoping, perhaps even believing... until the other day.

The other day one of the developers had come to the DBA with a Very Good Question:

"How do I find the max value of nulls?"

That's when the DBA started crying, left work early, and consumed a bottle of alcohol that night.

/developer/null is the only thing I can add.

How often should stats be collected?

If you ask Microsoft (or Oracle from version 10g), then just leave it to the database and the clever developers to find out when new stats should be gathered.

Right.

When you collect new stats you are almost guaranteed to change some execution plans in your system. Do you want that? No, you want the same execution plans as long as they have the response time your users want. They haven't asked you for a better (or worse) plan.

Here's a response I saw on a list the other day:

-------------------------------------------------------------
Oracle suggests the following best practices for collecting statistics. http://www.oracle.com/technology/books/pdfs/book_10g_chap9_ce1.pdf

· Collect statistics for normal data dictionary objects using the same interval that you would analyze objects in your schemas. In addition, you need to analyze the dictionary objects after a sufficient amount of DDL operations have occurred.

· Use the procedures gather_database_stats or gather_schema_stats with options set to GATHER AUTO. With this feature, only the objects that need to be re-analyzed are processed every time.

· For fixed objects, the initial collection of statistics is usually sufficient. A subsequent collection is not usually needed, unless workload characteristics have changed dramatically. In the next section, we will examine the changes introduced
-------------------------------------------------------------

And here's my reply:
---------------------------------------------------------------------------
I'd argue that you should be scared of socalled best practices on this and other issues.

How can people seriously write about best practices, when in reality we're talking practices that worked for a few sites that will never, ever resemble your site anyway?

Better to document more and more worst practices, as Cary Millsap suggested several years ago - that way you raise the bar instead of just placing it at a certain position.

By the way: Note the vagueness: "...the same interval" (what interval?), "...after a sufficient amount of DDL operations have occurred" (how much?). Can't be otherwise, but that doesn't make it more usable.

It's like the recommendations from Microsoft regarding SQL Server: Always run automatic stats gathering. Always set parallel to automatic. Always....

Two schools inside Oracle are interesting to study (and we're talking the real experts, not the document writers):

Benchmark guys: Collect 1% estimate after initial load, then never again.

Real World Performance group: Collect compute stats once, then never again.

But there's a better way, I think:

Analyze if it's neccessary!

It's only neccessary if the response time for a SQL statement becomes worse. So monitor the elapsed_time and the hash_plan_value (or plan_hash_value) in V$SQL to see if something goes South. If yes, one possible reason could be stats that are no longer representative for the data (more employees in department 42, much more data due to aquisition of a new company, whatever). THEN it's time to analyze.

Don't do it if you don't have to. Monitor the things that matter.

The reason you can't find any useful rules is that ... you can't find any useful rules.
------------------------------------------------------------------

Plan_hash_value and elapsed_time were added to V$SQL in 9i. Before that you'd need to do your own hashing after an "explain plan for..." command in SQL*Plus and store the result. Peter Gram from Miracle has done it for a 7.3 database.

I could talk for ages about this. How customers have run splendidly for years without analyzing (or precisely because of it), and how it's always ground-hoc day tuning when something changes: A system doesn't "become slow", an application doesn't "become slow". It's one or more SQL statements that change execution plan(s) for the worse. Since we never monitor those we do the same thing over and over again: Find the SQL statement with the largest response time, tune it, back to start.

But what if the SQL statement with the longest response time was NOT the one that changed? What if the SQL statement with the longest response time already had been optimised as best as could be done? That happens more often than you'd like to know.

So instead of zero'ing in on the SQL statement that changed execution plan (and therefor response time) for the worse, and return it to its former state or find out why it went bad, we end up tuning another statement that might not be easily tunable.

Hence my abstract for the Hotsos Symposium 2006: "No, Cary, we're STILL not tuning the right SQL statements" :-).

GCC - Global Communist Conspiracy...

GCC - the Global Communist Conspiracy

Thanks to David Kurtz for these pictures:

http://www.flickr.com/photos/davidkurtz/sets/1285559/

They were taken in Holland recently (week 42, of course) during the Steve Adams seminar arranged by Lex de Haan.

During the evening dinner event I presented my GCC theory.

To make it short, Bill Gates, Sam Palmisano and Larry Ellison are really sleeping Russian agents trying to:

1) de-stabilise all our important IT-systems and

2) turn men into non-men through bureaucracies worse than anything seen in the old Eastern Europe days.

You see, when men can't work, their testosterone-producing thingies fall off, and they can no longer defend their countries.

There's nothing we can do. The Sarbanes-Oxley's of the World are taking over.

I just thought you should know...

UKOUG, Scotland, Tuomas & 7th NF

Turning a comment into a post....

Thank you all for the welcome. Good to see people like Roderick on this blog planet. For those of you who doesn't understand Roderick's last (Danish) comment, it means "Bottoms up or the rest in the hair", ie empty your glass, or you will have to pour the rest over your own head. Oh yes, we have many ways of making the long winters bearable here.

Apart from that, it was a fine UKOUG conference last week, again making it one of the finest Oracle things you can go to. The UKOUG staff was doing it even better than last year, and especially Rachel deserves a promotion to Army Chief of Staff or something.

After UKOUG in Birmingham I went to Edinburgh for the Chris Date event there and finally managed to meet up with my wife Anette and our two months old son Viktor. After all the travelling I've done, I either had to buy her something made out of gold or spend time and money on a nice stay somewhere. We chose Edinburgh, famous for castles, whiskies, beer and rain. What more could Anette ask for?

Tuomas Pystynen, an OakTable member and an old-timer from Kernel Development in Oracle, was with us, too, and he's a guy who knows stuff. His company is called Deepbase, and I think that is a pretty good name for what he can. He also knows his middleware and much more.

Chris Date presented the 6th normal form (oh yes), which basically means that ALL your tables consist of two columns: A meaning-less key (joke, Lex, joke!) and a column with data.

Next: The 7th normal form, where all tables only consist of a meaning-less key (joke, Chris and Lex and Fabian, joke!!).

Strange names & The Fat Index Guy

Mogens' is pronounced with a silent g, so it becomes 'Moens' which in Danish sounds just like 'Moans'.

'Nørgaard' is impossible, but Nor-gaard is good enough for me. Nogood is shorter, easier and a bit funnier, though.

My middlename 'Langballe' means 'long cheek' in Danish, and so the jokes about www.miracleAS.dk are not too far off the mark.

So these days I tend to register myself at conferences as Moans Longballs Nogood. Much easier.

Speaking of strange names, ever heard about Tapio Lahdenmäki? Me neither. He's written a book together with Michael Leach called "Relational Database Index Design and the Optimizers" which is not too bad a book at all, to put it mildly. They cover optimizeres and indexes, and that's about what matters. They are focused on response times, which is what matters. They kill myths. They have ideas. Hence, according to Tuomas, they call Tapio The Fat Index Guy...

This book can seriously focus/simplify your thinking about indexes. More info on www.Tapio1.com...

My first blogging

After some very useful help from Doug Burns, I'm now also a blogger.

I haven't had any beers yet today at the UKOUG, so I can't be creative right now, but I shall be back...