Some time ago, I wrote a note about using the packaged function dbms_xplan.display_cursor().
I’ve just seen a note on Rob van Wijk’s blog where he investigates many more parameter values that can be used with the function.
When I tried to add a comment to the blog listing a couple more of the options that I knew, I found that I couldn’t do so without creating a google or blogger account. So I’ve posted them here, and I’d be happy for someone with a suitable acccount to add them to his blog so that they’re all in the same place.
‘All’: Prints the Query block/Object Alias section, the Predicate information, and the Column Projection information after the basic plan.
‘Advanced’: as for ‘All’, but also include the Outline information (the set of hints that will reproduce the plan) and the peeked bind variables used to optimise the query.
‘Outline’: prints just the Outline information and the Predicate information after the basic plan.
I got the ‘Advanced’ one from one of the Oak Table members – I think it may have been Christian Antognini – and guessed the ‘Outline’ one when I saw that it was possible to get the outline from the advanced output.
Update [Sept 2009] – I’ve just discovered another option (which should have been obvious really – especially given my comment about how I discovered the “outline” option)
‘Projection’: Print the “column projection” information. It’s a little obscure in places, but it tells you which columns have been passed to their parent by each line – and the size of those columns.