Using XPLORE to Investigate Unexpected Plan Changes

I’m sure by now you’re thinking that SQLTXPLAIN is a pretty cool tool to be familiar with, if you want to tune Oracle SQL. In Chapter 11 we discussed the test case utility and how that could build an entire environment for testing SQL, system parameter, optimizer settings, objects statistics, enough of the settings in fact to make the optimizer behave as if it was on the source system. Once you achieve that state you have a superb environment to go exploring the optimizer’s choices. This intelligent, directed approach is extremely fruitful when you have a rough idea what the problem might be and you suspect that you are only a few changes away from the solution. In those kinds of situations you can create the test case, try your changes, and test the result. This is a good way to solve tuning problems, but what if you had no idea what had caused a regression to some SQL after an upgrade or patch to Oracle or if a SQL plan changed unexpectedly. Then what could you do? There are hundreds of parameter changes you could try, but it would take far too long to go through them all. On the other hand, computers are pretty good at churning through hundreds of choices to find the best one, and this is exactly what XPLORE does.