I have OTHER checked in the Execution Plan Preferences, but I don’t get any of the information from OTHER_XML. I would love to see what’s displayed in Notes section of DBMS_Xplan.
There is actually an OTHER column in the plan table that the “Other” setting in the plan table prefs is referring to. We currently don’t have a way to show the contents of the OTHER_XML column, but if you look in the plan table, you’ll see that it’s not data that is human-readable. It’s certainly not what you see in the Notes section of DBMS_XPLAN. I’ve looked in the plan table for the notes - I’ve never been able to find them. I think the DBMS_XPLAN package must generate the notes on the fly.
OTHER_XML does appear to contain information displayed in Note section, at least in this particular case:
- SQL plan baseline “MDRS_UPDATE_20160419” used for this statement
Part of OTHER_XML
I can send the entire OTHER_XML output, 1171 length, if you want. A bunch of info and hint nodes for my plan.
I guess this proves that you are not human! haha. just kidding.
I found some other_xml data with a “select * from all_all_tables”. That’s my go-to explain plan test query since the plan is pretty big.
Unfortunately, there’s nothing you can do to get that column to show up in your copy of Toad. I did some work on it here and could get it to show up with a few changes, but it’s pretty awful looking with all that unformatted XML appearing in the tree. I wonder what else shows up in the notes besides baseline info, if any.
Regarding your last question, a couple more notes I’ve seen are:
- dynamic sampling used for this statement (level=7)
- automatic DOP: Computed Degree of Parallelism is 16 because of degree limit
and other messages regarding DOP.
The only issue I’ve found, so far, with setting Display Mode to DBMS_XPLAN is that in RAC, you have to be connected to the same instance as the session you’re trying to monitor. Doesn’t appear to be any easy solution to that - no INST_ID parameter for DBMS_XPLAN.display_cursor, and dbms_xplan.display_cursor@ is not supported.