Beaverton, OR Optimizing Oracle SQL class - Day 1

Day 1 of our Optimizing Oracle SQL Intensive course started off with 10 bright-eyed students today in Beaverton, Oregon. This week we're fortunate to be hosted by the Oregon Graduate Institute at their wonderful facility.

Things got off to a good start and day 1 ended with one student commenting "boy...my head is full"! And it's only day 1!

A word from Dilbert

I had to laugh at this. Sometimes I swear I think this argument works better than all the facts and figures in the world!


Of course, they probably didn't need to upgrade their servers either....it was probably just bad SQL!

[Dilbert comic strip from Aug. 5, 2006 - copyright Scott Adams, Inc.]

Home...yet on the road...

When I'm not on the road teaching, I have the wonderful luxury of working from home. But, after you've worked from home for a while, sometimes you just need a change of pace. So, today I took my laptop and headed to a nearby Starbucks. It's very strange, but I actually am getting as much work done as I had been while at home. Not to mention that I'm happily sipping a Grande Decaf Non-fat No-whip Caffe Mocha as I work! (This coffee delight may make the more hardcore coffee drinkers grimace. A friend of mine calls it a "why bother"!) A light jazz CD is playing in the background and I'm settled into a quiet corner. Not a bad way to work!

Now...if the course I'm developing ends up having lots of coffee and jazz references in the notes, you'll know where they came from!

Tell me why...

For the second time this week I was asked to take a look at a query that's used to generate a report. Problem is, when generating these web based reports, the sessions are timing out. The query I got on Monday ran in 15 minutes resulting in almost 2 million rows. Yes, 2 million! The query under investigation today ran in about three minutes resulting in about 160,000 rows...

Call me crazy, call me mad, but somehow I don't see the point. These queries would result in reports of thousands of screens long! I can't believe that's really what the business users want. So apart from trying to speed up the queries, we'll ask those business users what it is they really want. Chances are, we can add some default where clauses to these queries so the result sets will end up being a bit more sensible...

Jan van Mourik

On board

Hello all, Monty here, just saying hello and look forward to keeping track of all our happenings.

Everlost in Austin

I’ve use the Hertz Neverlost system many times all over the United States and it’s served me well everywhere. Except Austin Texas. What is going on in Austin? Is this like the Island in Lost and there is some huge electro magnetic disturbance here that it throwing off the navigation system? I got to my hotel only because I first printing off directions for yahoo maps at home. The Neverlost system had showed me a route to a spot where that was off by about 25 miles. The good thing was that I was next too the freeway I needed to get on to get to my hotel.

Last night I was sitting in the parking lot of the hotel and pulled up Neverlost, I went to Yellow Pages, then Hotels, then Nearest, I picked my hotel. Neverlost said my hotel was 25 miles away. Gee that’s funny I can look out the window of the car and the hotel is RIGHT THERE!

Well at least FedEx knows where my hotel was. The package sent to the hotel for me was here when I arrived as expected.

I tried using Neverlost to find a Kinko’s around here, again it was miles off. I wasn’t able to find the place until I asked a local where it was.

The map view on Neverlost does seem to work. And I hope it can find its way back to the airport at the end of the week.

Don't do work you don't have to do!

Thanks to my Hotsos colleague, Jan van Mourik, who sent me this example. The original SQL statement appeared to be spending alot of time doing sum(case...) constructs to count the number of times an order was created for each hour of a given day. The SQL was rewritten to simply get the counts by day and by hour and then pivot the results into the display format desired. The response time improvement was spectacular (as measured in two different production environments)!

Production Environment 1
OLD -- Elapsed: 00:00:07.01
NEW -- Elapsed: 00:00:00.06

Production Environment 2
OLD -- Elapsed: 00:00:19.06
NEW -- Elapsed: 00:00:01.06

Notice in the test output below how the LIO statistics for both the original and new SQL tests are identical. The plans are very similar as well. But, the big difference appears to be the time saved by removing the extra work required by the sum(case...) constructs.

The moral of the story here is that when writing SQL, you need to consider many different things and always make sure that you attempt to write the code to do the least amount of work possible to get the result you desire.

I say this to students in class all the time - Don't do work you don't have to do!

You can find the details of the tests (conducted on a 10.1 database) here.

Hotsos Education jumps into blogging

The folks at Hotsos Education are making the leap into the blog world today. We'd like to share our adventures as we travel from place to place teaching our Oracle performance optimization curriculum and meeting and greeting lots of great folks along the way.

This blog will be mostly about life on the road, people we meet and things we learn along the way. It's pretty amazing at how much we learn as we teach and we thought it would be fun to share some of the adventures with you.

So, come along for the ride!