Product Design : VST

I think the VST diagrams are powerful on their own but my original goal was to show the execution path on top of the diagram in order to look at to execution plans side by side and quickly see the differences which isn’t possible with textual explain plans. Here is an example of two explain plans in text and then graphically

This graphic was one of my attempts to show executions on top of the VST diagram, and I’ve been working with how best to show the order. Because the diagram layout stays the same, and the order is overlaid on top, it’s easy to compare them side by side. I can also see why original execution plan, on the left, was wrong. The original execution started at E which joins to C producing 198201422 rows (as seen on the join line), yet the query only returns 44,000 rows (not shown). Also table E has no filtering, really, since 99% of it's rows are returned after applying the filtering condition. The filtering is represented in blue to the bottom right of the table as a percentage of rows returned from the table. The query should start at A where there is 2% filter ratio. After starting at A we should join to C or B (which is a subquery) because the result set sizes are the smallest 85K and 642K respectively. Making this change (via hints) took this query from running over 24 hours to 5 minutes)

Designing VST diagrams has been fun exciting and challenging. I wonder if you can imagine this: Have you ever been rock climbing ? and I’m not talking about the gym, but out on real cliffs? If so you probably know that 99% of the time people use a route map that shows exactly where to go and that indeed there is a way to climb up and off the cliff and so you won’t get stuck halfway up with nowhere to go. Climbing these routes is super scary and exciting, but the amazing thing is someone climbed them without a map, not knowing if they would get up 1000 feet and find that there is no way to finish, and somehow have to get down, which might not even be possible given the gear . Seriously life threatening.

That’s what I think about writing a completely new way of tuning SQL. Of course it’s not mortally life threatening J, but years of my life and my job are on the line. I have no idea where the end is, or what all the road blocks will be. I certainly didn’t when I started but I’m far enough along to see that the diagrams provide powerful information rapidly to the user in an easy to understand graphic way. Graphics can be misused even abused resulting worse information instead of better, but when graphics are used well they are much more powerful than the textual quantitative data. The graphical diagrams are a great way to understand the relationships in the query faster than reading the query text. It’s sort of like looking at the Google map and seeing the route drawn verses having to read the directions. Both are important but I can understand the route on map much faster than the directions, though I might want to read the directions after reading the map. The map with the route though is often all I need.

I’m not able to store vast amounts of information and see all the permutations like some Oracle experts can. This can be a curse and a blessing. It’s a blessing because it makes me look for ways where I can understand the problem space with out having to buffer tons of information and such solutions are valuable to the general public. On the other hand its scary in that I don’t see all the possible problems, but that can be a blessing as well because I try to just concentrate on the solutions that have the biggest return, the biggest bang for the buck , the issues that can be most easily solved, instead of getting distracted the overwhelming possibilities and issues.

In some ways the SQL optimization space seems overwhelming and in others ways it seems really small. I think that VST diagrams will help make the problem space seem much smaller, manageable and understandable as the VST diagrams mature.