Oracle now offers SQL tuning recommendations. November 2004
I started working for a bank called Sparekassen SDS 1st of January 1987. They had just bought Oracle, and that's how I ended up in the database world.
In 1990 I joined Oracle Denmark's support organisation under the magnificient leadership of Jannik Ohl.
He was fired by Peter Perregaard in 1998 or so, because they didn't like each other. Until then things were fantastic. After that things were not.
Jannik was replaced by Allan Marker, who was not nearly his equal in any which way you choose to look. Especially when it comes to the art of thinking instead of wondering how you can survive in the corporate culture for the next few months.
But that's how things are. Peter made a mistake, and he regrets it to this day, I'm sure (as in: sure).
So Jannik went into geo-stationary orbit. In other words: He joined the Oracle EMEA organisation (Europe, Middle East, Africa).
When you "go into orbit", ie. join EMEA or some global stuff, you're never heard of again. In space, nobody can hear you scream, as they say.
Until it's time to lay off some bodies. So Jannik, uhm, resigned just now.
Today I served a bit of Miracle beer for my friend Jannik in Oracle Denmark's canteen.
To honour the best boss I ever had.
And to honour one of the most creative minds I've met. Really.
He was the one that came up with the idea of doing serious database stuff in Lalandia (which is why Miracle now do two conferences there a year).
He was the one that told me: "With all this internet stuff and not-being-able-to-call-a-person thing going on in Support, people will pay for extra services that allow them to talk to people and get their problems resolved without too much bullshit" - and we now have 130 Miracle Support customers.
He came up with the idea of having a credit-card thing for Good Oracle Customers (GOC).
Miracle Support shouldn't be allowed to live. It's feeding off the failings of the big vendor support organisations, because they're failing. That's wrong. But it's a fact.
I just hope Jannik doesn't do the boring thing of leaning back and waiting for the early-age pension to arrive. He's not old, he's not spent. We need him.
As for the headline (The beginning of Oracle Denmark) I'll just say this piece of information from an unknown source:
The beginning of Oracle Denmark: Jørgen Balle, Ole Bisgaard, Hanne Cederberg & Jannik started at the same time. Then came Pete Francis, og later Klaus Holse Andersen.
We need more details, folks :-))
There has been an interesting and somewhat heated discussion going on about a recent blog post by Dominic Brooks and referenced by Doug Burns about the relative value of data vs. applications. Actually, most of the heat seems to be directed at a comment made by Tim Gorman on several mailing lists in which he states that:
Data, not programs, is the only thing that matters — applications are transient and have no value except to acquire, manipulate, and display data. Data is the only thing with value.
I’ve deliberately taken the quote out of context — for that is how it’s being reacted to, fairly or unfairly on Doug Burns’ blog entry.
I’m not actually going to add any fuel to that fire, only offer up some observations. I think I agree with many who are stating that data that lies about, unexploited by any application, is a pretty useless waste of storage. That the true value of data comes from an ability to use it through an application which allows one to analyze, manipulate and visualize information synthesized from the data soup. One reason I’m excited about the new company I’m with is its focus on helping people increase their ability to exploit their data.
To that end, one of my burning interests is in the ease of which the average employee has access to data and the means to create value out of it. This includes data accessibility combined with compliance controls as well as tools and applications which allow the employee to tease ideas out of the data. I wish Excel was a better data manipulation and analysis tool, since it’s so ubiquitous. But my real concern is my perception that the language of data access has been kicked into a corner, shunned by end users and application programmers alike. I find the lack of SQL knowledge and use appalling in most of the technologists I’ve encountered. And that’s a real shame — for SQL’s ability to make data accessible I find second to none. I have an idea about why SQL ability is failing, and I think it goes back to its original development. The following is from a fascinating interview at McJones titled: The 1995 SQL Reunion: People, Projects, and Politics
Don Chamberlin: So what this language group wanted to do when we first got organized: we had started from this background of SQUARE, but we weren’t very satisfied with it for several reasons. First of all, you couldn’t type it on a keyboard because it had a lot of funny subscripts in it. So we began saying we’ll adapt the SQUARE ideas to a more English keyword approach which is easier to type, because it was based on English structures. We called it Structured English Query Language and used the acronym SEQUEL for it. And we got to working on building a SEQUEL prototype on top of Raymond Lorie’s access method called XRM.
At the time, we wanted to find out if this syntax was good for anything or not, so we had a linguist on our staff, for reasons that are kind of obscure. Her name was Phyllis Reisner, and what she liked to do was human-factors experiments. So she went down to San Jose State and recruited a bunch of San Jose State students to teach them the SEQUEL language and see if they could learn it. She did this for several months and wrote a paper about it, and gained recognition in the human-factors community for her work., 31 I’m not sure if the results were very conclusive; it turned out that sure enough if you worked hard enough, you could teach SEQUEL to college students. [laughter] Most of the mistakes they made didn’t really have anything to do with syntax. They made lots of mistakes - they wouldn’t capitalize correctly, and things like that.
Looking back on it, I don’t think the problem we thought we were solving was where we had the most impact. What we thought we were doing was making it possible for non-programmers to interact with databases. We thought that this was going to open up access to data to a whole new class of people who could do things that were never possible before because they didn’t know how to program. This was before the days of graphical user interfaces which ultimately did make that sort of a revolution, and we didn’t know anything about that, and so I don’t think we impacted the world as much as we hoped we were going to in terms of making data accessible to non-programmers. It kind of took Apple to do that. The problem that we didn’t think we were working on at all - at least, we didn’t pay any attention to it - was how to embed query languages into host languages, or how to make a language that would serve as an interchange medium between different systems - those are the ways in which SQL ultimately turned out to be very successful, rather than as an end-user language for ad hoc users. So I think the problem that we solved wasn’t really the problem that we thought we were solving at the time.
Anyway, we were working on this language, and we adapted it from SQUARE and turned it into English and then we started adding a bunch of things to it like GROUP BY that didn’t really come out of the SQUARE heritage at all. So you couldn’t really say it had much to do with SQUARE before we were done. Ray and I wrote some papers about this language in 1974. We wrote two papers: one on SEQUEL/DML and one on SEQUEL/DDL. We were cooperating very closely on this. The DML paper’s authors were Chamberlin and Boyce; the DDL paper’s authors were Boyce and Chamberlin, for no special reason; we just sort of split it up. We wanted to go to Stockholm that year because it was the year of the IFIP Congress in Stockholm. I had a ticket to Stockholm because of some work I’d done in Yorktown, so Ray submitted the DDL paper to the IFIP Congress in Stockholm, and the DML paper we submitted to SIGMOD. This is the cover page of the SEQUEL/DML paper. It was 24 pages long. These were twin papers in our original estimation. We wrote them together and thought they were of comparable value and impact. But what happened to them was quite different. The DDL paper got rejected by the IFIP Congress; Ray didn’t get to go to Stockholm. I still have that paper in my drawer; it’s never been published. The DML paper did get accepted at SIGMOD. Several years later I got a call from a guy named Larry Ellison who’d read that paper; he basically used some of the ideas from that paper to good advantage. [laughter] The latest incarnation of these ideas is longer than 24 pages long; it’s the ISO standard for the SQL language, which was just described last week at SIGMOD by Nelson Mattos. It’s now about 1600 pages.
It’s from this quote that I believe SQL gained its second-class status — it’s not for programmers, but it’s “too complicated” for end-users who became used to graphically interacting with applications.
Do you have someone on staff who really knows SQL? Who can make the data super easily accessible to application programmers and end-users alike? Who removes the barrier and lowers the hurdle in the way of turning data into value? You’re probably gathering more and more relational data every day — and probably shredding your XML and storing your BLOBs there too. I’m not saying that SQL is more important than data or the means to analyze it — I am saying that experts at SQL can make your databases perform better AND make it easier for your application people to focus on delivering that data to the people who want to use it. Don’t put it in the limbo land of being not for programmers and not for end-users.
Update: I wanted to give credit to the source of my quote:
Copyright (c) 1995, 1997 by Paul McJones, Roger Bamford, Mike Blasgen, Don Chamberlin, Josephine Cheng, Jean-Jacques Daudenarde, Shel Finkelstein, Jim Gray, Bob Jolls, Bruce Lindsay, Raymond Lorie, Jim Mehl, Roger Miller, C. Mohan, John Nauman, Mike Pong, Tom Price, Franco Putzolu, Mario Schkolnick, Bob Selinger, Pat Selinger, Don Slutz, Irv Traiger, Brad Wade, and Bob Yost. You may copy this document in whole or in part without payment of fee provided that you acknowledge the authors and include this notice.
My ringtone on my mobile is currently Highway To Hell with AC/DC, but I thought Chris Rea's The Road to Hell was more appropriate as a title today. I hope you'll understand why after reading this.
I've just come home from 10 days in a Danish town called Horsens doing a reality TV show called "The Secret Millionaire", which has run for two seasons in England.
Now they've done 11 programs in Denmark. Mine will probably be shown in the fall of this year.
Basically, a TV crew of three followed me all day long while I (complete with a cover story) visited places where good souls help out people in need. At night I stayed in a borrowed, Turkish immigrant apartment.
At the end of the 10 days I put on one of my Armani suits and told the good people that in fact I was not that much down and out, and that I'd like to donate some of my own money to their cause (a total of 250.000 Danish kroner, to be exact).
In fact I'm not a millionaire in the sense that I can take out that amount from my bank account at all. Instead, we had to take a loan in our house, which my wife Anette was OK with (and thank you so much for that!).
The 10 other folks look a LOT more like millionaires than me, let me tell you that.
Folks like the guy behind JustEat, a guy with his own investment bank in London, a big IT-guy called Asger Jensby, and so on and so forth. Some of them with private chauffeurs, one live in a French castle, for crying out loud. You know the type.
The filming ended last Thursday - a week ago - and it was a good day. Lots of happiness, tears, and much more. And of course I threw a big party with more than 200participants at the end of it.
Fantastic. But perhaps the most emotinally draining thing I've tried.
Then last Friday (the day after) after spending 30 minutes in my house while re-packing and re-grouping, I found myself with my co-director Lasse racing snow scooters and drilling holes through 70 cm ice on frozen lakes in Northern Iceland with hard, Icelandic men around me.
Talk of a change of scene within 24 hours.
A couple of rough days here in Denmark, and it was time to relax on this beautiful Easter Thursday...
The plan was to eat brunch with my friend Søren (who buys breweries for Carlsberg) and his family. Well, I made it, but late of course, due to all sorts of things.
Then I left around 1400 hours in order to drive back to my town Maaloev and pick up three kids and then take them to a football match between Brondby and FC Midtjylland due to take place at 1500 hours. Running a bit late...
I had 10 free tickets from Brondby because I tried to help them with a social project called "Fra Bænken Til Banen" (from the bench to the field) where they try to get kids into jobs (they've been so successful that they're now starting to find jobs for the kids' fathers, too).
But nobody wanted my free tickets, so I ended up throwing six of them away. Bah.
I was running a bit late for the game. Perhaps that's why I was driving too fast on the street where the Police was checking speed.
I was charged with driving 97 where 60 was the limit. Ouch.
That means 2500 kroner in fine (that's OK) and I have to take a new driving test (which cost a lot more and takes a lot of time). Hmm.
But hey, I get to learn about all the new street signs and rules that have appeared since I learned to drive back in 1982. Might even get one of those new, fancy credit-card style driving licenses.
While I was standing there talking to the cops, another car was stopped for speeding in the opposite direction of me. Turned out he had just been at Brondby Stadium, but had discovered that the game had been moved from 1500 hours to 1800 hours due to demands from Viasat television, since they had another important sport thing to cover today, too.
So evidently, one should check game times and not rely on whatever is written on the tickets. Anyway, that's how I discovered that it wasn't neccessary for me to drive faster today :-)).
So we drove back, and then we went to Brondby and saw a fine match (2-1 to Brondby) in rain and snow, then back to Maaloev with the kids and then back into Copenhagen to pick up my girl Nathalie (9 years of age) who had stayed with Søren to play with his daughter Louise.
Shortly before arriving in the street where Søren lives, I hit something with my right front tire and all air went out. Then I spent half an hour in heavy rain and sleet trying to change the #%&/Q tire.
Due to very slippery cobble stones, the jack kept slipping and the car crashing down. That happened four times, the last time with the tire only half way off, and that's when I called for professionel help (and a professionel jack).
They came, tire was changed, we drove home.
All through this I was looking forward to a nice evening with my wife Anette (whom I hadn't seen too much of in the last couple of weeks) and some cheese and champagne that she had promised me.
Like in 'Driving home for Christmas'. Chris Rea. Can't wait to see those faces. Oh, I'm driving down that lane.
It was late when I finally made it home, and Anette had had to go to sleep, of course. She had been up early and had been taking care of little Viktor all day.
Bummer. But there's always email and blogging for you, then.
But I just got a text message from my oldest daughter Christine (18 years old), who's on some kind of survival training thing with the scouts.
She wrote: "By the way: I love you, dad. Have I ever told you that?".
No, you haven't. And you never needed to. But it was the finest of timings when you did :-)).
I've just poured myself a large Bowmore 12 year single malt (Enigma edition).
Here's to life.
Oracle provides a cross-session cache for user-defined PL/SQL function results. January 2008 (updated June 2010)
During my 2006 Hotsos presentation I mentioned 2 “rules of 5″ that I like to use — I didn’t come up with them myself, but I’m pleasantly surprised when I find evidence to support them. Of course, the human brain always finds evidence to support it’s own prejudiced hypotheses (for an excellent read that demonstrates this concept, try Focault’s Pendulum by Umberto Eco). Anyway, the 2 rules of 5 are:
Of course, you need to know what LIOs are — a depressingly larger and larger number of DBAs I meet don’t have the foggiest notion of them.
I point you at an excellent blog post by Shakir Sadikali at the Pythian Group which shows off a ten-node RAC cluster brought to its knees by unindexed foreign keys (doh!). Fixing that and other tuning operations has allowed them to reduce the cluster down from 10 nodes to 2 nodes (or, 1/5th their original hardware). Score one for #1!
BTW, most people argue #2 by talking to me about aggregates. My standard response is that any aggregate that is queried heavily is an opportunity for derivation, pre-calculation or optimization.
I’ve uploaded my presentation and the DDL code generation scripts I referenced in my talk. Just scroll down on the right hand side of this blog to the section marked “Content”.
We can now view the evolution of our data with flashback version query. August 2005
Native dynamic SQL gets an optimisation to match DBMS_SQL for efficiency. June 2004
A while ago I went on a crusade within my organization to review and clean up our init.ora files. Many of them had been around since versions 7.3 and 8.1 of Oracle and were simply added to over time. I still like the text-based init.ora files that I can check into source code control and liberally comment. I’m liking the fact that you can comment on parameters in spfiles too — they even have the comment fields displayable in DB Console and Grid Control.
I’m constantly amazed at the places I go where I still see the following text in their init.ora files:
# Use the following table to approximate the SGA size needed for the
# three scenarios provided in this file:
# ——-Installation/Database Size——
# SMALL MEDIUM LARGE
# Block 2K 4500K 6800K 17000K
# Size 4K 5500K 8800K 21000K
I’m guessing the init.ora file isn’t being reviewed at those places.
Anyway, I started doing this when I realized that many of the default values for particular parameters were higher / better than the ones we had “set”. And we didn’t have any documented reasons for setting them. I ended up with 2 goals:
End result was a lot more clarity around our settings and why we needed them. We also were able to basically make an init.ora template for ALL databases, since we made such heavy use of defaults.
What’s your policy for init.ora files? Do you even have one?