jump to navigation

Friday Philosophy – Should the Software or the User be the Stupid One? August 7, 2009

Posted by mwidlake in internals, performance.
Tags: , ,

Oracle’s performance engine is complex and copes with a lot of database situations automatically – or to be more precise, it tries to cope with lots of database situations automatically.

Over the last few versions, Oracle has added many, many things to allow the database to cope automatically with all sorts of different data volumes, spreads of data, relationships between tables, use of different oracle technologies (By this I mean bitmap tables, index tables, partitions, clusters, external tables). All of these things aim to allow the database to just “cope” with whatever you need it to do, with less and less effort by the users {by users, I mean technical users; DBAs and Developers}. Thus it allows for “stupid” users. {I mean no offence, maybe read “inexperienced” instead of stupid}.

As an example, you can now have some very large tables consisting of several partitions and some status look-ups. You query against them. Oracle’s CBO will automatically ignore partitions it can ignore, use indexes or full table scans to use the least amount of IO,use histograms to spot where clauses are on low-cardinality values, Hash joins rather then nested loops as appropriate depending on memory availability, use bitmap indexes when it thinks it can and merge the results from several bitmap indexes, use function based indexes to support functions in where clauses….
It even self-gathers the information to look after all this. Column usage and table modifications are tracked, statistics are gathered when needed and in ways to support data skew, PGA and SGA can be automonitoring and managing…

It all sounds great. In fact, most of the time, for most people, it is great. {I know, most people reading this post are probably people who have encountered the problem systems and so know it goes wrong and so you need more knowledge to cope – you are a biased set of people. In the nicest way, I should add 🙂 } The idea is, I believe, that you do not neet to be smart to look after Oracle.

If it is not great, if this highly complex system gets it wrong and tries to satisfy SQL statements in sub-optimal ways, then the User has to step in and fix things. ie You.

It is now horrifically complex for us technical users to understand what is going on. You have to not only be “not stupid”, but “not average” either. Sometimes you have to be “not great”, ie brilliant.

In my example, we need to look at if the SQL is constructed to allow the indexes to be used, are functions correctly laid out to use function indexes, are partitions being maintained correctly, when were stats last gathered, did it include histograms and do they help, has oracle missed the need for histograms, are the indexes analyzed at a high enough sample size, are the bitmaps greatly slowing down inserts, have hints been used on the code, are initialisation parameters set to override default fucntionality…

You get the idea, I won’t drone on further. I didn’t even mention memory considerations though {OK, I’ll shut up}.

My point is, the more complex the software, the more “intelligent” it is, the more it is designed to allow for “stupid” users, then the more super-intelligent the user has to be to cope when it breaks.

How about an alternative?

How would it be if we went back to the Rule Based Optimizer and no automatic management of complex situations?

Oracle would maybe need to add a few rules to the RBO for it to cope with later developments, so it would be slightly more complex than V6 but not a lot.
Everything else, the User decides. You only gather stats you decide to gather, on objects you decide need them. No you don’t, it’s a Rule Based Optimizer – no stats gathering! {But see below}.

No automatic memory management. No automatic anything.

The User {the technical user, the DBA and Developer} would have to be smart. Not brilliant, just smart. You would probably have to do more, but most of it would be easier as the levels of complexity and interdependence are reduced. All those tweaks and tricks in the CBO and all the monitoring to cope with “complex” would not exist to go wrong.

Plus it might solve another concern I have. I think there is a chasm growing as there is no need to solve simple problems as Oracle copes but then having to solve complex problems when Orcle does not cope. If you don’t develop skills and experience solving the simple problems, how do you solve the complex ones? I think this is why most Oracle performance and architecture experts are old {Sorry, pleasantly middle-aged}. Young people new to the arena have a massive learning mountain to climb.

So, if we have stupid software, maybe we can get away with more stupid “smart” expert users. ie ALL of us can cope. You cut your teeth on smaller, simpler systems and learn how to cope with the stupid software beast. As you learn more, you learn to cope with more complex situations and they never get that complex as the database is not so “clever”

I’d actually still argue that all the intelligence gathering the Oracle database does should still continue – stats gathered on objects, the ability to gather information on memory usage and thus advice on changes, tracking column usages and table changes. But We, the Stupid Users get to look at it and use it as we see fit for our systems.

I’m sure many systems would not work quite so fast in my senario, but I’d rather have a system working at 75% it’s theoretical fastest all the time rather than one working at 95% and breaking regularly, and in ways so complex it needs weeks to work out and fix.

I now await all the comments to tell me how stupid I am {I can be blindlingly stupid, especially on Fridays}.



1. Martin Berger - August 8, 2009


A nice post, and a powerful introduction into a discussion.
So I will take the baton and will try to answer.
There are several points to contradict, I will start with 3 different layers, to keep it short.

1) Your subject compares the cleverness (or stupidity) of Software and User.
This implies, Software could be clever (or stupid). But Software is only a concrete implementation of designs and minds of Designers and Developers. So if you substitute “Software” with “a bunch of (Oracle) Designers and Developers” your comparison sounds slightly different.
And with this substitution, I personally hope “a bunch if Designers and Developers” is cleverer as the single User. Otherwise Oracle should rething it’s employment strategy.

2) You complain about the automatism, the (Oracle) RDBMS provides.
To your misfortune, this is the nature of a RDBMS. They where introduced to separate the clever heads which define _what_ an application must do from the other clever heads which helps, _how_ to do it the best way. Maybe you have red Dave Ensors Chapter in Tales of the Oak Table about the history of Oracle.
Also SQL is all about that: It does only tell _what_ to do, but not _how_.
Only this separation enables a flexible interface between those, who know about Business logic and the other experts in relational logic, algebra of sets, etc. I’m pretty sure, you did not want to argue about SQL itselve, but at the end, when you agree to use SQL, you agree with automatism. Otherwise, be consequent and make the application developers code their joins, transactions and all the funny stuff themselves. Then they have really full control (again) and decide about everything 🙂

While the first 2 points tried to show my opinion about some basics, the 3rd is more an opinion:

3) The learning mountain will rise with every release of software, as new features are implemented. I totally agree in this.
But nowadays it’s much easier to cope with the necessary information. There are MetaLink (maybe someday also MyOracleSupport will be as usable), mailing lists, forums, blogs; and most of them quite usable indexed by google et al. And there are a lot of really great and greater minds willing to share their knowledge for _free_ (sometimes you just have to ask _polite_). So even the mountain will be bigger, there are well prepared climbing systems all around. You just have to grab them.
My personal opinion is, the mountain of available information grows faster than the bunch of needed informations to solve a problem.

I am totally aware I didn’t respond to the main target of your article, but especially in my first 2 points I weakened the foundation of your argumentation.

After all, thank you for this great article which forced me to stand back from my beloved command line and think about some aspects of the work I’m doing. Please do not see this comment as an affront.

best regards,

mwidlake - August 9, 2009

Take offence? No, not at all, you have given an in-depth answer to the original post, I’m grateful for that.
Point 1
You argue {if i read correct} that a set of Oracle Corp technicians have more intellectual grunt than single users.
The oracle designers are attempting to give a generic answer to everyone’s questions and that is where I wonder if it is the wrong approach. I’ve known a couple of people working on Oracle’s low-level ‘C’ development to answer questions. The people I have known are brilliant, extremely clever and very talented. But they are still trying to give generic answers to open questions.

As an example, my background allows me to know something about genetic sequence analysis. Oracle, to their credit, built something called “BLAST” into V10. It allows comparison of genetic and amino acid sequences. It works and is potentially useful. However, any bioinformatics group that needs BLAST probably still does it outside the database, irrespective of keeping their genetic/amino acid data in an Oracle database, as Oracle implmented the generic BLAST code, the slow version, not the fast version. This highly specific example highlights the issue that, if you know what you want to do, it is often faster and more efficient to do it yourself.
If you have a set of flat files of a given format, some Perl or C code to do the match will beat a generic solution by Oracle (or IBM, or Microsoft or HP…) To Oracle’s credit, they are the ONLY major software house to put BLAST into their product.

2) You are right, I am not arguing about SQL itself {I suspect I might lose if I did 🙂 }, but if you know your data, you can use full hints to tell Oracle how to execute your query in the way you have worked out is best for statements that are a problem . The failure of my argument here is that 99.9% of sites can’t afford to work this out for problem statements, let alone all SQL statements, but currently Oracle gets it right for X percent and to work out why the 100-X percent is wrong is now bloody complex. With Rule Base you could work through the rules for poorly performing statements and work out how to control those rules for those statements alone. You would now have to worry about: how the Multi block read count is REALLY calculated; the impact of standard and hidden parameters; whether histogram existed for skewed columns and such histograms picked up the skew (if you have 100 buckets, will it spot a value that is used for 1/1000 of rows? No); are stats gathered and when etc… It is getting very, very, very, very, very complex.

SQL is a fantastic tool, it allows you to ask near-natural-language questions of the sofware engine and get results. But as anyone who has programmed against more than one relational database knows, ANSII standard SQL may work {Note, MAY} but to get the best out of your system, you have to use the platform-specific apsects and then add on your domain knowledge.

3) Heck, we agree on this one. I, like you, use Google and metalink and good books to keep up. Problem is, even with the background knowledge I have after 20 years {OK, let’s make that 15, I was a manager for a while}, my personal impression, right now, is that in year 2000 I stood on the final ascending flanks of the mountain. Now I stand at base camp 1, as the mountain has grown! It’s a long climb up.

So, all in all, I can argue against your points but your points are still valid:-
Oracle is coded by some very, very talented people (and in fact, when Oracle does not work well it is often down to lack of knowledge by the users).
Oracle, like all SQL, is abstracted from the data itself to give you a way of asking simple questions.
Oracle adds features all the time.

But I still wonder, would we be, industry wide, better off having more stupid software and expecting more of our average users, rather than having our software get it right X percent more often for having to have N*X more knowledge and skills to fix it when it fails.

If you have over 20 years’s experience in the field and have the N*X knowledge, you are on to a winner.

I suspect I might not. 🙂

2. Martin Berger - August 9, 2009

Point 1 you can also see totally revert {and you read me correct 🙂 }:
The oracle designers are attempting to give a generic answer to everyone’s questions and that is where I wonder if it is the wrong approach.
I see this as the correct approach:
The usage of the {any?} RDBMS widens every minute. Today it’s used for some purpose which was unthinkable some years ago. To keep in track, product managers, and in their follow architects and developers must catch up to make their product give the best answers {they know so far} to broader and broader unknown questions. If they would not do so, they would fall behind.
Your “BLAST”-example shows the perfect contradiction to your initial theory: in this particular case, the software {Oracles BLAST implementation} is the stupid and you {the User} is the clever one _not_ to use it. Thank you for this example, how clever a user can be!
In respose to Point 2, you accept the vast majority of statements is treaded quite well.
Complaining about the minor rest is quite justified. But even here I have to object in 2 points:
2a) You are correct, ANSI SQL {if this really exists anywhere} is implemented differently in all vendors product. But thats is, how standardisation works: If you gain a driving license, you are allowed to drive. And all cars you can buy will meet the legal requirenments of your country. Nevertheless all these cars will have different switches for light, window washer and even gearshift. That’s a wide shown aspect of standardisation: even the main goals are clear, every vendor has its space for exposition.
2b) this is really subjective: You are saying, if a statement is going wrong, it’s ery, very, very, very, very complex (to fix it).
I dont’t thint so: If the physical design is done right and the users know about the correct access method and skeweness of the data, it’s not hard to bring oracle to use the right access method, even in curent, highly automated versions. But as this is only an opinion, it’s hardly worth an argument. sorry.

I cannot add anything to your reply to point 3. I just never stood on the top of the mountain (I just recongiced how to spell Oracle in 2000) – so I never could loose this probably great feeling.
At the end, I totally agree in …expecting more of our average users…, but I personally hope, the software will not become more supid.

I like to have a huge mountain in view, even I know, I will never climb it. But better there is a huge mountain I can never climb than a small hill I can climb, but I can not use it for anything really valuable.

best regards,

3. PdV - August 10, 2009

Great Topic!

we just have to be careful not to ramble too much.
The systems have indeed become so cleverly-complex that they become a risk in some situations. And when a complex system breaks, impact is often higher, and the fix is often more difficult.

I like the idea of “disabling” the CBO for specific cases/tables/systems (e.g. the old “rule” hint.) And I agree that it wouldnt/shouldnt take a rocket-sciencits to configure/run a relatively straightforward
optimizer (e.g. old fashioned rule with some additions…).
A number of high-end and critical systems still (try to) do this to gain better control and avoid surprises but Oracle is making that “switch off” more and more difficult.

Meanwhile, the CBO and its automated jobs and various assistants will develop further and become reliable at some time. But while it is cutting its teeth, we should be able to switch some of it off.
(un-PC remark:) I sometimes think we are all just guinea pigs for the development groups.

Think of it like switching off ABS and EPS on some cars, not uncommon in the early days of electronic traction control systems, but less and less needed nowadays as systems become more clever. It was Useful only in extreme situations, and only if you know what you were doing.

I’ll now get branded as a dinosaur, no doubt.

mwidlake - August 13, 2009

I like the comparison to stability control and brake assist in cars. I continue to see this “simplex” approach by people using the RULE hint, but more an more it is done by people who have no clue about the rule-based optimiser – it is becoming an Urban Myth/Dumb Oracle Trick.

I love your car-based metaphors (and the dinsoaur ones, you pachycephalosaurus Piet). Trouble is, just like “rocket science”, both your examples are actually pretty simple problems in reality, compared to more complex things like genetics, psychology, biochemistry, proteomics… And IT. OK, IT is less complex than biology, by {I would guess} 3 orders of magnitude, but still more complex than rocket science. How hard is it to calculate thrust, tragectory, mass alteration and friction as a four-way calcualtion huh? 🙂

Compared to how to work out how the CBO works, an easy task 🙂 As for working out how the minds of a DBA team work….Scary.

I feel a Friday Philosophy posting boiling under the lid on top of the simmering pan that is my brain.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: