Explain Plan tree view - any way to show OTHER_XML info from plan_table?

Toad for Oracle - Beta
1

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.

2

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.

3

OTHER_XML does appear to contain information displayed in Note section, at least in this particular case:

Note

  • SQL plan baseline “MDRS_UPDATE_20160419” used for this statement

Part of OTHER_XML

“MDRS_UPDATE_20160419”

I can send the entire OTHER_XML output, 1171 length, if you want. A bunch of info and hint nodes for my plan.

4

I guess this proves that you are not human! haha. just kidding. :slight_smile:

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.

5

Regarding your last question, a couple more notes I’ve seen are:

Note

  • dynamic sampling used for this statement (level=7)

Note

  • 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.