Sunday, June 29, 2008

Plan execution statistics

I recently saw a thread at AskTom where someone had asked a question about using tkprof for tuning a query. Tom provided an example of tracing the execution of a query and then using tkprof to summarize the trace file into a readable report and finally how to interpret what the report shows.

The part that caught my eye (and the eye of another reader) was the execution plan output display in the report. It looked like this:


Rows Row Source Operation
--------- ---------------------------------------------------
1 SORT AGGREGATE (cr=1840797 pr=1840758 pw=0 time=262104893 us)
128000000 TABLE ACCESS FULL BIG_TABLE (cr=1840797 pr=1840758 pw=0 time=384004887 us)


Note the statistics (in parentheses) for each row source operation. The time shown for the first step is time=262104893. This is also close to the approximate total elapsed time shown for the query (~262 seconds). But, look at the second step which is a child of step 1. It shows a time=384004887 (~384 seconds). How can that be? Child steps roll their totals into their parents so that the parent totals are all their children plus themselves. So, there is no way the child took around 384 seconds but the parent took 262 seconds. Tom offered an explanation of why the numbers were different that basically shows how where instrumentation calls are placed can cause measurement errors to creep in.

Well, while I know measurement errors do exist, I think the specific problem here isn't related to any type of measurement error. It has to do with the setting of the STATISTICS_LEVEL parameter. The STATISTICS_LEVEL parameter is set to TYPICAL by deafult, and at the default level, plan execution statistics are DISABLED. You can see this with these two queries:


SQL> select value from v$parameter where upper(name) = 'STATISTICS_LEVEL' ;

VALUE
------------------------------------------
TYPICAL

1 row selected.

SQL> SELECT session_status,
2 system_status,
3 activation_level,
4 session_settable
5 FROM v$statistics_level
6 WHERE statistics_name = 'Plan Execution Statistics'
7 /

Session System Activation Session
Status Status Level Settable
---------- ---------- ---------- ----------
DISABLED DISABLED ALL YES

1 row selected.


The first query shows my current parameter setting (TYPICAL) and the second shows what setting the plan execution statistics have at this level (DISABLED). The second query also shows that the activation level for collecting this information is at ALL.

So, I recreated the same test as Tom used and here's my plan display (note the time= values are "off", just like Tom's):


Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=120 pr=0 pw=0 time=11988 us)
28736 INDEX FAST FULL SCAN BIG_TAB_SO_IDX (cr=120 pr=0 pw=0 time=287550 us)(object id 52645)


Next, I executed ALTER SESSION SET STATISTICS_LEVEL = ALL and ran the test again. Now look what I get:


Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=120 pr=113 pw=0 time=148169 us)
28736 INDEX FAST FULL SCAN BIG_TAB_SO_IDX (cr=120 pr=113 pw=0 time=93170 us)(object id 52645)


After setting the STATISTICS_LEVEL parameter to ALL, my child time is less than the parent indicating that the parent appears to have correctly rolled up the child into its totals. Therefore, if you want completely correct plan execution statistics, you must have the STATISTICS_LEVEL parameter set to ALL. Otherwise, the only number that is correct is the topmost step in the plan (id=1).

I think the plan execution statistics are extremely helpful when evaluating query performance, so I like to have my parameter set to ALL for session testing.

However, I'd like to know why Oracle doesn't just show the child steps with null values if they are not correct. Notice that the cr and pr values seem to be correct in both cases in my example, but I think that's coincidence. I recall seeing other cases where this hasn't been true (at least in early 10 releases).

What I'd like to know is what is going on under the covers? But, lacking that knowledge, I've learned to ignore all the row source operation execution statistics except for the topmost one if the parameter isn't set to ALL.

Wednesday, June 25, 2008

What technological advances provide

There are so many things that technological advances have added to our lives. I know many of them I take for granted. My 96 year old grandmother has seen so much change in her lifetime and I often wonder at her perspective on it all. I asked her once if she could name a thing or two that she thought were the best things she's seen become commonplace in her lifetime and she answered with "indoor plumbing" and "washing machines". My grandmother was a mother of 6 so both of these "bests" seem quite fitting. :)


But, since I've always had indoor plumbing and a washing machine handy, I was trying to think what I'd consider my top two things. I'd certainly not be very happy if I had to head to the outhouse or wash my clothes in a bucket with a scrub board, but I don't think that either of those things is something I'll ever have to revert to doing. I'll bet, if you've read my recent blog posts, you'd think I'd say the Kindle or the MacBook. :) Well, I think I can certainly say that many of the cool gadgets I use every day are in my top 10, I think what is at the top of my list is of a slightly different, although related, caliber.

First, the explosion of services that allow me to do many of the tasks I used to have to do in person to be done over the internet. Banking. Shopping. Research. I can't remember the last time I went into a bank. The closest I've come is the drive-thru ATM. My paychecks are deposited electronically, I pay all my bills with internet banking. I can order books, clothes, toys, gifts, movies, music and even order my groceries and have them delivered to my door. I can research any topic with ease and get piles of information (admittedly, some not so good) within seconds.

Second, how easily and readily I can communicate with anyone, anywhere. I have my mobile phone, internet phone, land line phone, email, blogging... The ability to connect with people is so easy. Some people feel that we've taken it too far and that the pervasive use of communication technology is taking away from the time we have instead of adding to it. I think any tool or technology can be misused so that it leads away from the problem or situation it was invented to help. But, in general terms, having this particular technology available (in my opinion) makes communicating and staying connected with people so much better and easier.

There are so many things that advances in technology bring and I can't really imagine all the things that I'll see in my lifetime if I live to my grandmother's age. But, I have to say I'm glad I'm part of this generation and not my grandparent's generation who had the experience of visiting outhouses and doing wash by hand! If the worst "remember when" I have to share with my daughter is that I once only had a corded telephone, that's OK by me.

Tuesday, June 24, 2008

Kindle update

I posted a couple of weeks ago about my new Kindle. Since then, I've already come to love the thing so much that I can't imagine life without it. I've completed three novels and have two more downloaded and ready to read. Plus, I've purchased several of the books I had in paper copy so I've always got them handy on my Kindle. I've learned to make notes and highlight the passages that have special meaning to me or have things I want to remember. And, I've converted a couple of my technical books that were in PDF form to Kindle format and loaded them.

I had wondered if I was going to miss my books. Well, I'm still not ready to say I'm off of paper books entirely, but if I can continue to get the books I want in Kindle format, I'm beginning to think that my bookshelf is going to be much less crowded. I'm not missing my hard copy books so far. As a matter of fact, I'm really liking the ease of having them so readily available on my Kindle. I don't have to worry about transporting several books with me as I travel. For instance, I just made a trip to Dallas for the week and on my flights here, I finished one book and started another and then spent a bit of time reading sections of two others before going back to the main novel I'm engrossed in. And I did that all without doing anything other than use my thumbs!

I did check out the features of my Kindle that are under the menu title of "Experimental". This is where you can use the "free" Amazon Whispernet wireless service to connect to the web. The browser interface is pretty crude, but if you want to just read your Google, or other web mail, it works OK. But, I've decided that feature doesn't really "get me" like the book reader does. I don't envision my Kindle becoming my one do it all device, but for my books, it's aces.

I've read some reviews of other people's experience and it seems to be much the same as mine. Once you start using a Kindle, all the reasons you thought you might want to have "real" books start to disappear. Much like my switch over to a Mac, this switch may be another one that truly changes the way I do things...and very much so for the better!

---- Update to this post ----
I just read a post at Seth Godin's blog entitled Random Thoughts about the Kindle. Some of his ideas are interesting about how he thinks the Kindle could be improved that may appeal to some. I find I have mixed emotions about these "improvements" he mentions. As I mentioned above, I like my Kindle because it makes reading easier, faster and it's just cool (I love it when people stop to comment on it when they see me reading it). Seth talks about having a more interactive component that allows you to comment, see other people's comments, and alot more. But, when I'm reading an "I don't want to think" novel and just be entertained, I'm not sure the thought of stopping in the middle of a paragraph to comment on the passage and read what 40 other people are saying about it is what I want. I think I'd like that functionality available for lookup, but not taking up screen real estate on my display as I'm reading. It'll be interesting to see how Amazon and the public feels about these kinds of features and if any of them get implemented in future Kindle software updates.

Wednesday, June 18, 2008

Oracle Performance Problems and Root Canals



Take a look at this picture. Would you know if this guy was having a toothache or a bad Oracle performance problem?

As I had a root canal this morning, I had the opportunity to ponder how a root canal and a nasty Oracle performance problem are alike. Yes, this is a stretch, but my mouth is sore and I'm in a mood...so this is it.

Anyway, when you have a problem with a tooth that requires a root canal to alleviate the pain, there are several things going on.
1) The pain is nearing the point where you're fine with any solution that will just stop the pain. You don't really care about what happens afterwards, you just want the current pain to end.
2) There is no amount of money that is too much to pay to get the relief you want/need.
3) All the soft, gentle words about how everything's going to be alright from the guy who is going to fix the problem only tick you off. (Remember #1.) Just shut up and fix it already!

Have you ever had a really bad performance problem hit and everyone is having a fit to get it fixed? Your users are screaming, your management is screaming...you know the drill, right? Think about it for a second...I think the same 3 things that happen when you're in need of a root canal are the same 3 things that happen when you've got a performance problem and it needs to be solved. I actually had a colleague say to me "this is about as fun as a root canal" once when we were working on a problem that was a very big pain to the business (and therefore to us).

Now that I've experienced both, I can say the similarities are so alike as to certainly be in the same category. But, after today's root canal, I'm of the mind that I'd much rather have the pain of an Oracle performance problem to suffer through. Well...except for a performance problem they don't give you nice pain killers and anesthetic to get you through it! Maybe the root canal isn't so bad after all... :)

Tuesday, June 17, 2008

MacBook convert


I used to think Macs were for wimps. I used to think only graphic designers, artists and school kids used them. And people used to think the world was flat.

I have seen the light. I am now a Mac convert. I switched over in February to using a MacBook Pro and while it's taken me a while to fully leave behind my Dell laptop, I think I can officially say that I'm there. I haven't had to use my old laptop to do anything in several weeks. Everything I need or want to do is getting done on my MacBook. Yippee!

This laptop is a thing of beauty. Every time I sit down in front of it, I get happy. It just plain looks cool for starters! It did take me a bit of getting used to being in Mac-land, but now that I've made friends with Finder and got all the applications I need installed and working, I can't ever see myself going back to PC-land.

Since I didn't get my MacBook with OS X Server, I have loaded VMWare Fusion so I could have virtual machines for Windows and Linux so I could have an Oracle database install handy. But, that was no big effort and I can easily start/stop an image and even use the Unity feature of VMWare fusion to make my virtual machine "merge" with my Mac desktop so that things look like I'm in a single environment. I could've gone with BootCamp (comes with Mac OS) or Parallels, but VMWare has been a really good fit for me.

I've come a long way from the days when I thought Macs were for wimps. Not only does my MacBook Pro do everything I need it to, it even does stuff I have yet to use. So, if you're thinking of going Mac, I can highly recommend it. Once you go Mac, you never go back!

Cool....just cool!

Friday, June 13, 2008

I only thought it was the future of presenting...

In my post from a few days ago, I wrote about a cool new software product, SlideRocket, and the shift I've been making to doing more visually appealing presentations. I still think SlideRocket is one of the coolest things I've seen in a while (especially now since I've gotten a beta account to play with!) but I saw a demonstration of some new technology that was amazing.

So, how'd you like to see a presentation delivered using live holographic video feeds from across the world? It sounds like Star Trek, but it's here now. Cisco CEO John Chambers, speaking live on stage in Bangalore, "beamed up" two of his senior VPs from San Jose, CA using new holographic video conferencing and they had a discussion of the future of Cisco TelePresence. Now that is cool!

Somehow, I don't think people will want to go to all the trouble and expense of holographic technology to be bored out of their minds with a holographic image in front of a boatload of dull, bullet pointed slides. It may be one of the ways that the future of presenting will be driven back to a more personal and interactive approach. Wouldn't it be cool if the hologram looked like it was looking right at you, no matter where you sat in the room? You'd feel as if you were getting a personal session with the presenter. Just the two of you sitting down for a chat. No bullet points needed.

I'm sure the technology will be a while in filtering it's way to the general public, but I was just thinking that perhaps by the time my daughter goes to college (she's 1 now), that we'll be able to talk using our holographic images. How cool would that be? I could see her just like she was standing (or sitting) in the room with me. I could fuss at her for biting her nails or for coloring her hair fuschia. No hiding behind voice only communications! Ah, the possibilities!

It's amazing to me to think of all the things that technology will bring in my lifetime. My grandmother, who is soon to be 96, has gone from living in a home with no electricity or indoor plumbing to all the modern conveniences. I can only imagine what advances I'll look back on if/when I reach her age. And maybe holographic conferencing will be just another everyday thing in my home by then. Amazing...simply amazing.

Thursday, June 12, 2008

My new toy



I am now the owner of an Amazon Kindle, and I am enthralled. This lovely little device is smaller and lighter weight than every book I'm currently reading in hard copy. Once you buy a Kindle version of the book you want, it is delivered wirelessly to your unit within a minute. You can sit it in your lap and click a single button to turn the page. When you're ready to stop, the unit remembers where you left off and will bring you right back to that spot when you return. You can have up to 200 books (that's what the specs say anyway) loaded on the unit and add even more with an SD card should you desire.

I was hesitant about this purchase for multiple reasons. First, I love books. Love 'em. I have shelves of them of all different genres. I love the feel of books. I love making them my own with notes and highlights. I have some books in my library that I've had for 25 years. So, I wasn't sure about an electronic book. I have a few technical books in PDF format that I like having for reference use so that I don't always have to turn to the book to find something I'm looking for. But, the Kindle would mean starting a whole different relationship with my books. Could I do it?

Second, is the cost. It's not a cheap little gadget. But thanks to a cross country trip to visit family and a deal on my credit card to get triple Amazon points, I ended up with almost enough Amazon bucks to cover the cost.

So, I took the plunge. And so far, this little baby is awesome. I haven't even begun to play with features like access to newspapers, magazines, popular blogs and so forth, but I have downloaded my first book. Oh joy, oh bliss! How sweet it is!

This could be dangerous. I can carry a boatload of books with me every where I go. No more trying to figure out which books to carry on the plane with me and which ones to pack. Everything comes with me in one conveniently small package. If I want a new book, I just type in the name, click Buy and there it is! Dangerous...yes, very dangerous.

Even non-Kindle books can be converted to be used on the Kindle with a simple, free converter program. So, that means virtually any book I can get in PDF form can go on my Kindle with just minimal effort. Hmmm...I'm liking this more and more!

I think I'll still have to buy some books in hard copy. Not everything has a Kindle version (yet). And, I don't think I can go completely cold turkey on having my hands on a "real" book. But, if my first few hours of ownership are any indication, I think I'm going to have a very long and wonderfully meaningful relationship with my new Kindle.

Wednesday, June 11, 2008

Optimizer NOWORKLOAD statistics

I am often asked about statistics collection. People want to know the magic formula for collecting them. When? How much? But, the truth is there isn't one formula, magic or otherwise, that works for everyone. Think about it... If there were, Oracle would have already implemented it, don't you think? But hope is still alive that someday the mystery about it all will be revealed.

I think Oracle is getting better and better in its attempts to automate the process of statistics collection. But there continues to be alot of questions and discussion on how to "do it right" in the meantime.

So, I thought I'd start trying to put together some of the questions I've received and do a little research and testing to see if I can't answer some of them here in the blog. Today, I wanted to start with a few words about system statistics, particularly about noworkload statistics. Starting with 10g, noworkload statistics are always available. Even if you delete them, if the database restarts, then they are automatically regathered.

The noworkload statistics are basically a few generic benchmarks used as performance measures for your system. The stats that are maintained are:

CPUSPEEDNW
The number of operations per second (in millions) that a CPU can process.
IOSEEKTIM
Average number of milliseconds needed to find data on the disk (default 10).
IOTFRSPEED
Average number of bytes per millisecond that can be transferred from the disk (default 4,096).

I've never touched the noworkload statistics on my laptop test database so I started by checking them to see what they were (they should be the defaults from when my database was first started). I used dbms_stats.get_system_stats to retrieve the values.

SQL> @getsysstats
iotfrspeed: 4096
ioseektm : 10
cpuspeednw: 773.806

I then executed a single SQL statement and grabbed the execution plan. The statement was a 4 part UNION.


Then, I gathered noworkload statistics to see what they would be now. I started the collection and then executed several queries in another session while they were being collected.

SQL> exec dbms_stats.gather_system_statistics (gathering_mode => 'noworkload');

Using the gathering_mode parameter set to 'noworkload' will collect the values in about a minute. I'm not totally positive, but I believe the CPU speed is likely measured by executing some sort of calibration operation in a loop and the I/O is measured by doing different sized reads on several different datafiles.

These were my new values.

SQL> @getsysstats
iotfrspeed: 29126.045
ioseektm : 14.856
cpuspeednw: 917.075

So, I ran the same SQL and checked the plan and you can see the difference the new noworkload statistics made.



I notice two key things: 1) the cost for this plan went down, but the estimated %CPU went up. This makes sense if you look at the new statistics. The iotfrspeed was significantly larger than my original value. This would mean that the optimizer now thinks it is much less expensive to transfer data from the disk than it was previously and reduce the cost of those operations.

In my example, the optimizer still chose the same plan it originally chose, but now, in comparison to any other plan choices, this plan is even more likely to be selected due to the much lower cost.

Several people have commented to me that they've chosen not to use system stats in their 10 databases because they had trouble initially when upgrading from 9i. But, what they often don't realize is that they are using system statistics no matter what. They are simply using the noworkload variety is they disable/delete the others. And, as you can see in this simple example, noworkload stats can change and cause the optimizer's calculations to change as as result. This could mean plans change for apparently no reason. This is one of those cases when someone swears "nothing has changed" but then they tell me that the database had just been restarted for some reason. When that happened, the noworkload stats could've been recomputed. Even if that were the only thing that changed, it might be enough to cause some plans to break.

So, as I've mentioned before in a previous post: instead of insisting nothing has changed, evaluate what might have changed that might not be on your radar. Something is bound to have changed somewhere.

More stats stuff to come in later posts.

Monday, June 9, 2008

The Future of Presenting?


Cary pointed me to an interview with Mitch Grasso, co-founder of SlideRocket. It's impressive. What do they do? From their web site, they say:
"We make a fully featured, rich internet application (RIA) called SlideRocket (TM). It's offered as a service with a variety of prices to suit different needs, including free!"
I've been seriously working on changing my technical presentation style to be more dynamic and visual. I posted back on May 22 about a book by Garr Reynolds called Presentation Zen that has really had a profound effect on the way I look at the content of my presentations.

Here's an example of how I took one slide out of one of my presentations and converted it to a new, more visual style.



The "After" style requires the speaker to provide the details, but I think that's kinda the point, huh? I've heard it said before (and agree) that if your slides say everything, then why are you there? I really like the impact the "After" version will likely have on the audience. I think they'll be much more engaged with me than if I'd just popped up one detailed slide with alot of text for them to read.

I use PowerPoint. I never really thought or knew there was another option. As long as I've been doing presentations that weren't printed on transparencies (oh yeah, I did a bunch of those), I've used PowerPoint. I know there is KeyNote for the Mac, but since I've just recently seen the light and converted to a Mac (my MacBook Pro rocks!), it's never been on my radar.

But, having seen the new SlideRocket, I think they're really on to something. Creating presentations becomes more about making your point (and making it well) with lots of easy options for formatting, adding photos, dynamic content and a bunch of other nifty stuff.

What I've noticed for myself is that my presentations were always put together while thinking in bullet points. So, that's what came out on the slides. If you shift to thinking about your story...the thing you want to communicate to the audience... you immediately move away from bullet point thinking. I think SlideRocket will facilitate this process much better. Take a look at the interview and their web site and see what you think.

Wednesday, June 4, 2008

Famous last words

Jonathan Lewis' recent blog post, entitled Nothing Changed, got me to thinking about how frequently I hear "nothing changed" in relation to a performance issue. I really do think those are quotable (and laughable) famous last words. Think about it: if everything was the same, it's not possible for something to change, right?

A query plan doesn't change without reason. Response time doesn't change without reason. In general terms, it's rare that nothing ever changes. So, instead of immediately asking "why" and claiming "nothing changed", I think it'd be much more productive to start asking "what changed?".

When we insist on believing nothing changed, we limit our options of what could have happened. We close off our creativity. We limit our mind's ability to pursue possibilities.

I think one of the best skills a good performance analyst has is the ability to see lots of options. And, I think that the reason why people, like Jonathan Lewis, Tom Kyte, Cary Millsap, and many others, are so good at what they do is that they always stay open to many possibilities. Skill and knowledge is certainly important, but the ability to look at many alternatives and not get stuck demanding that things must be "just so" is just as critical.

In the end, those who continue demanding "nothing changed" might find the famous last words of composer Ludwig van Beethoven most fitting: "Friends applaud, the comedy is finished."

Tuesday, June 3, 2008

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

I recently was sent a query to review that was performing poorly (the database version, if you're curious, was 9.2.0.7 ). The developer told me that the plan cost for the query in the test environment was 3 (so it should be really fast, right?) but the query took a little over a minute (74 seconds) to complete and returned no rows (that seems slow, right?). But, it had been put into production and....well, it's kinda easy to do that math on this one, huh? When it executed in production it took f-o-r-e-v-e-r.

Now, the test database was approximately 10% of the production database size. The plan looked like this (click on image for enlarged view):



The statement used a WITH clause to separate the accesses on the tables involved in the query into separate pieces. By that I mean the developer was attempting to do the table filtering in separate steps and then at the end, join them all back together. There were 10 tables used in the query and one of the tables was used 3 times. The work required by Oracle to execute each table access as an individual operation vs. joining the tables in a single multi-table join meant that thousands of additional blocks had to be retrieved to access rows that eventually got thrown away.

By simply rewriting the query to be a multi-table join, the response time came down to under 1 second in the test environment. That means that approximately 99% of the work the query did originally was removed.

Don't do work you don't have to do. Well...don't make Oracle do more work that it has to! Sometimes we can write queries in such a way that we help the optimizer do a better job of getting an optimal query execution plan. But, sometimes what we think is helpful just throws a wrench in the works.

The bottom-line:
We need to do our job of writing good, well thought out, "clean" code and let Oracle do it's job in return. Hopefully, most of the time, both of us will get it right.

Monday, June 2, 2008

PIVOT/UNPIVOT

Oracle 11 has introduced new functionality to handle pivoting data. The PIVOT and UNPIVOT functions are the coolest thing I've seen in a long time. Today, Tom Kyte blogged an example case using this new feature. Very, very cool.

Now all I wish was that I would actually get to use Oracle 11 more often. Trying to accomplish what Tom did in a pre-11 version of Oracle is not fun. And I really, really like fun...