Sunday, April 13, 2008

Graham Wood customer meeting

Last Thursday, my customer got the chance to have a customer account meeting with Graham Wood at Oracle Netherlands. Normally this would be a DBA affair, but luckily my customer thought it was a good idea to have two developers join the three DBA's. And I was one of the lucky two developers, since I get to solve quite a lot of performance problems, that are spotted by our DBA's.

As a developer I do not have a lot of experience with tools like AWR, ADDM and ASH, so I did some prior reading to know a little more about what the tools do, and I also read the powerpoint of Graham's presentation at last year's Miracle Database Forum, called You Probably Don't Need SQL*Trace. Now that's a title that draws my attention of course. At first glance, the screens from the EM Diagnostic Pack to view ASH data, look very similar to the ones I am familiar with from Indepth for Oracle, which we use.

In the one-and-a-half-hour conversation, Graham answered three of our prepared questions, mostly RAC-related. And since time allowed it, I was able to ask a question myself about the previously mentioned presentation. In Indepth for Oracle it is very easy to spot when and where performance problems occur, since our systems are pretty well instrumented. However, when you drill down to the SQL-level and asking for the plan, you get to see an explain plan instead of the row source operation. This is not useful and therefore the DBA's usually send the SQL statements to me or a colleague to have a closer look. It would be nice if the row sources were displayed together with the time spent on each row source. And this is exactly what ASH will contain. So you are able to drill down from instance to session to module to SQL to row source level. Now that sounds promising. Unfortunately I don't recall in what version it will be available. Maybe it is already available now. If you know, please leave a comment.

Also, Graham was surprised to find out that version 8 of Indepth for Oracle also contains "real execution plans" as can be read here.
According to Graham that is hardly possible, as the information is only available to Oracle. As we will likely upgrade Indepth for Oracle, we will soon find out if it is truly so.

Graham also drew a picture on the whiteboard showing approximately how the first screen of the new interface will look like. Instead of having equally sized pictograms for each instance, it will show all instances as rectangles. The more time they have consumed, the larger the rectangle will be. And all rectangles will be displayed on one screen with the larger ones appearing at the top left corner, and the small - probably developer instance - rectangles at the lower right corner. Instances that show far more worse performance than usual will be displayed red. This sounds like a great interface, especially when the DBA has to monitor LOTS of databases.

I very much enjoyed the session, and although I was feeling a bit overwhelmed by all the information packed in such a short amount of time, I am glad for having had this opportunity. Graham's tour will now lead him to three different cities in Germany and after that he is off to Turkey.

4 comments:

  1. So you are able to drill down from instance to session to module to SQL to row source level. Now that sounds promising. Unfortunately I don't recall in what version it will be available. Maybe it is already available now. If you know, please leave a comment.

    I think this is an 11g feature - new columns in V$ACTIVE_SESSION_HISTORY include sql_plan_line_id, sql_plan_operation and sql_plan_options, to name a few. It also includes sql_exec_id and sql_exec_start for repeat executions of the same statement.

    I haven't played around with this too much yet, though.

    ReplyDelete
  2. Thanks Doug.

    Yes, that's a good clue of course that it is available in 11gR1.

    I also checked that those columns were not available in 10gR2.

    ReplyDelete
  3. It's what the optimizer blog if I recall it correct blogged about, bringing the statistics to the rowlevel in the execution plans. Indeed it's 11g.

    I don't think I3 has this kind of detail already with 10g. If it would, it would require to examine the stacks of all the oracle processes (think of what tanel blogged about, the list of ora-600 messages (which are code locations) on metalink, and how these relate to certain things the database does.)

    This means it *can* be possible, but highly unlikely.

    ReplyDelete
  4. Hi Frits,

    Don't underestimate Anjo ... :-)

    Thanks for sharing your thoughts though.

    ReplyDelete