UP FRONT: Work in Progress
33,000 and CountingBy Tom Haunert
People have questions, and Tom Kyte has answers.
Tom Kyte, architect in server technologies at Oracle, is the man with the answers in his Ask Tom column in Oracle Magazine and at the AskTom Website (asktom.oracle.com). Oracle Magazine Editor in Chief Tom Haunert recently sat down with Kyte to ask him still more questions. The following is an excerpt from that interview. Download the full podcast at oracle.com/magcasts.
Oracle Magazine: When did you start answering questions, and how many questions have you answered so far?
Kyte: Well, it actually started in about October of 1994. That was the first time I posted to the internet Usenet news groups, which is the discussion forum precursor to Facebook and Twitter and everything else like that. I spent most of my time in comp.databases.oracle.server answering questions about Oracle Database.
From ’94 to about 2000, I posted about 12,000 times to those forums. And then starting in January/February of 2000, I started doing the Ask Tom column in Oracle Magazine, and the AskTom Website started in April of that year. So it’s been about a dozen years of Ask Tom and going on 17 or 18 years of participating in the community answering questions online.
Oracle Magazine: What has motivated you to answer all of these questions?
Kyte: I like the education aspect of it: being able to convey to someone the right way to do something and the best way to do a particular thing inside the Oracle Database software. And I like seeing the lightbulb come on in someone.
Over time I’ve enjoyed watching people “graduate.” Many of the people who used to ask me questions are now the speakers at Oracle OpenWorld, IOUG [Independent Oracle Users Group] conferences, user groups, technical symposiums, and so on. I enjoy watching the people come up through the ranks, and I’ve seen many more than one go from asking the questions to answering the questions to delivering the technical material. That’s a pretty cool thing.
Oracle Magazine: You’ve been quoted as saying that using Oracle Exadata involves some level of unlearning certain Oracle Database practices. What does that mean?
Kyte: The vast majority of us, close to 100 percent of us, learned everything we know about databases and tuning in a transactional environment that I’ll call a small rows environment. In that environment, you’re running queries that might hit tens or hundreds of rows. Your processes affect a small number of rows at a time, so doing things row by row works very well.
When you get into an Oracle Exadata environment, in particular a data warehousing environment, you have to unlearn some of the things you’ve learned. For example, in an OLTP [online transaction processing] environment, you were taught many times that if you were going to retrieve a small percentage of a table through a query, an index was the right way to go. So if you were going to retrieve 1 percent of the rows in the table, you would use an index.
Now that works well on a small number of rows—hundreds or thousands. But if you’re going to retrieve a million rows out of a 100-million-row table, all of a sudden that approach doesn’t scale very well. That would result in a million single-block operations against the database table, and the typical I/O might take 5 milliseconds, so you’re talking about almost an hour and a half of processing time. You could run a full table scan on that information over and over again many hundreds of times in that hour-and-a-half period.
So the tuning techniques—the toolkit—that you’ve developed based on predominantly OLTP small-number-of-rows implementations do not scale up in an Oracle Exadata warehousing environment. I/O is suddenly not a problem in this environment. You need to look at different mechanisms and different methods for doing what you do.
Oracle Magazine: You’ve been participating in a multiyear tour with some other notable Oracle technologists. Tell us about the tour and who is involved.
Kyte: IOUG came to us and said, “It would be cool if some of your performance experts could come out and talk to our user base and explain their real-world performance techniques.” So three of us put together a daylong seminar, which became the Real World Performance Tour.
The three people involved are myself; Graham Wood, who’s been with Oracle since the early 1980s and is actually the father of Statspack, Automatic Workload Repository, and related tuning technologies; and Andrew Holdsworth, who also has been with Oracle longer than I have. He is the senior director of the Real World Performance group within Oracle and works with customers and prototypes and on fixing systems that are not working in the real world. We get together for a day and talk about data warehousing in the morning; then in the afternoon, we talk about OLTP.
If people are interested in these Real World Performance Tour sessions, we’ll be doing more in 2012. Just look at ioug.org under Events, and you’ll see a list of dates that we’ll be appearing—maybe in a location near you.
Oracle Magazine: Tell us more about what goes on at these Real World Performance Tour events.
Kyte: Well, they’re very interactive. We have an audience that’s typically anywhere from 40 to 100 people, and even with 100 people, you’d be surprised at the degree of interaction and the back and forth that goes on. And while we’ve given the seminar more than 15 times now, we’ve never covered the same material in the same way twice. In fact, sometimes we don’t even cover the same set of slides twice in a row because the conversations we’re having take us down different paths.
One of the unusual things about the Real World Performance Tour is that we have a rather large machine with us. We have access to an Oracle Exadata full-rack machine, so we’re able to turn around and prove the points that we’re talking about. We can put numbers behind what we’re saying. So sometimes there’s a lot of unlearning on the part of the attendees during these days. Unlearning something that you’ve learned is true for the last 10 or 20 years can be a very painful process.
Oracle Magazine: In so many years of answering questions, are there any that really stand out? Is there a best question or maybe a worst question?
Kyte: There have been so many questions—more than 33,000 on AskTom in the last dozen years. So coming up with the best or worst question is not really possible, but let me say this: I usually start the morning by answering some questions. If I can get through 10 questions and I haven’t had to get into the database to answer anything, that’s sort of a bad day for me. That means the questions have been asked and answered previously. I simply answer them by taking the subject of the question and running a search of AskTom and saying, “This is what I found when I searched—what did you find?” So that sort of describes the worst questions—the ones that are easily answered because they’ve been answered so many times and people didn’t bother searching.
Oracle Magazine: What’s the best way to ask a technology question?
Kyte: Step one should be to go ahead and look to see if you can’t solve it yourself really quickly by taking a look at the documentation or actually using the search features on the sites where you’re going to post the question. Because 9 times out of 10, you’ll find that your question has been asked and answered by someone else.
Once you think you have a unique question that you can’t answer yourself, when you post the question—and this goes for Ask Tom as well as any other forum—ask the question as if you were asking your mom the question. Give that level of detail. The people you’re asking haven’t been sitting staring at the problem for a day or a week, like you have. We don’t have all the information you have. So rather than being very terse and saying, “How can I get this output from this data?” explain what the output is. Explain the logic behind getting that output.
And then when you have the data that we’re supposed to get this output from, phrase it in a series of CREATE TABLEs and INSERT INTOs. If you want us to help you develop a SQL query, for example, it would be really useful if we had your tables and some of your data. If you just post the output from a SQL*Plus SELECT statement, it’s going to take us 5 or 10 minutes to reverse-engineer that, create a CREATE TABLE statement, and insert all the data into it. Save us that time, and make it easy for us to answer you. Give a lot of detailed information, and provide a reasonable test case.
Tom Haunert (email@example.com) is editor in chief of Oracle Magazine
Send us your comments