Whilst troubleshooting a recent performance problem where a good (all things are relative) plan had gone bad, the bad plan was no longer in memory. However, I was able to pull the bad plan from the AWR repository and also a number of variations on the good plan.

However, this was a big plan – some 3000 lines – and illustrates some display issues which you can get.

The OUTPUT column is wrapped and does not make such investigations and interpretations any easier.
Note that this is NOT a SQL*Plus display issue as far as I can work out. It’s not the whole line which is wrapping.

As far as I know, there is no solution to this.
I have an Oracle SR open for confirmation but after 4 days I’ve yet to even receive confirmation of my ticket…

And a good point point about getting around this by looking at dba_hist_sql_plan (take Tanel’s script and repoint to dba_hist_sql_plan via plan_hash_value rather than sql_id and child number – the latter which is not in the hist table)

On which note, it’s a shame that dba_hist_sql_plan has columns for captured filter_predicates and access_predicates but which are left unpopulated up to and including 11.1.0.6 at least.

I see that’s a plan more than 700 lines – so maybe it really does need that level of indentation; but it’s posisble that your also hitting the bug where Oracle gets the “depth” value wrong when there are multiple filter subqueries – which is an even better reason for using one of the old fashioned “connect by” queries against the underlying objects (possibly with subquery factoring or a no_merge view to pull the driving data out of the main tables first).

Yep – it’s 2800 lines when pulled from dba_hist_sql_plan. I’ll be putting the plans up in a bit on another article.

What’s the details of that bug? Is it a bug on v$sql_plan? Wondering whether that potentially means that the information in dba_hist_sql_plan could be wrong as that’s populated by a select on v$sql_plan?

There have been some changes in how often it goes wrong – but I never raised it as a bug with Oracle, and haven’t been checking it through the versions – it’s just something I remember when I see lots of subqueries and a heavily indented execution plan.