Using Stored Outlines to figure out a hint or two

Stored Outlines capture a set of hints which direct the optimizer how to execute a specific query.

I’ve already mentioned previously on a plan here to use these for Optimizer Plan Stability for an upgrade from 8i to 10g (however I think the current thinking is that we’re just going to wing it).

However, I’ve also mentioned that in our current 8i database some objects have some statistics (mostly stale) and some objects have no statistics, and when you mix both into a query the optimizer makes some potential dodgy guesses about how much data is in the objects without stats. And the same guesses/defaults apply to the usage of GLOBAL TEMPORARY TABLES and CASTing ARRAYs to with the TABLE operator.

As a result, some recent queries have been performing suboptimally.

Like most, I prefer to not use hints where possible and recognise that in most cases it is much better to leave a well-informed optimizer to make its own mind up, being a clever little thing with access to lots of information that I don’t. However, unfortunately, there are circumstances where it needs a bit of a hand and that’s where hints come in.

I don’t have a comprehensive understanding of hints and join mechanisms, but I’m working on it slowly. And I certainly can’t look at a query and visualise how it should all come together, well, not unless it’s pretty darn simple.

What I often find in a more complex query is that I’ll try to add in one or two hints and things might improve but they don’t quite arrive where I’d like them to.

So, and I have touched on the titled side-effect usage of Stored Outlines very briefly in another previous post, I tend to use Explain Plan in conjunction with Stored Outlines on a healthy development system (one with relevant statistics from like production data volumes and distribution) to tell me how it a fully-informed optimizer would do things:

create or replace outline myoutline
for category mycategory
on select.....

And then look at the hints here:

select * from user_outline_hints order by stage;

And this might involve creating a dummy table with some specific data in there, fully analysing it, and substituting that for a collection CAST or a GLOBAL TEMPORARY TABLE in the SQL before capturing the outline.

And then I tend to extract some key hints in the right order and bung them into the original SQL. Usually with the desired effect.

This won’t work in all scenarios but it does some/most of the time. In my situation, at least.

I do feel deeply uncomfortable using hints on SQL just because the statistics situation in a production database is not good. And obviously, in most cases that I’m talking about, these are workarounds precisely because of that. But sometimes the perceived risks of making changes (in this case here, sorting out the statistics and in fact, more like ‘often’ than ‘sometimes’ everywhere in IT these days) cause a stalemate that is difficult to break free from.