Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

SQL Developer 4.2 Oracle Execution Plans – More Columns Available

1 Min Read

Someone on the Forums (yes, we read and respond!) noticed they couldn’t add Bytes or Time to their plan displays in Oracle SQL Developer.

So we just made all of the columns available now, even if a few them might not make sense, i.e. TEMP_SPACE, OTHER.

Preferences – Database – Autotrace/Explain Plan

New things highlighted/boxed…

Partition Columns is kinda new - it will just grab all 3 partition themed bits.
Partition Columns is kinda new – it will just grab all 3 partition themed bits.

So let’s look at a plan from V$SQL_PLAN…a la hitting this button –

Click the drop-down, then select the entry you want to see. If you don't see anything available, run your query again.
Click the drop-down, then select the entry you want to see. If you don’t see anything available, run your query again.

42_plans2

For AutoTraces you can also ask to hide a column if all its values are NULL.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

19 Comments

  1. Gerrat Reply

    I’ve recently switched computers, and re-installed SQL Developer (now at Version 17.4.0.355).
    On the old computer, whenever I clicked the “Explain Plan” button, the plan just automatically showed up in the lower pane.

    Now, it’s just blank (see: https://imgur.com/a/vwG6p). None of the “…” sections will expand (right-clicking and selecting either of the “Expand…” options does nothing)

    I have to actually:
    1. Execute the Query
    2. Select the top line from the dropdown next to the Explain Plan button

    I tried searching preferences for something that looked relevant, but didn’t see anything.
    Is there a way I can get back the previous setting where it automatically displayed?

    Thanks

    • thatjeffsmith Post Author

      it’s a bug in 17.4…easiest fix is to rebuild your local plan table, the other fix is to uncheck the other xml column

      your workaround is better, that will give you the dbms_xplan vs an Explain, which to be honest, could get you in trouble.

      this is fixed for 18.1 though.

    • Gerrat

      Thanks for the quick reply, Jeff.

      Unchecking the “Other XML Branch/Other XML Column” didn’t change anything.
      My current “workaround” is *painful* :).
      One of the things I use this for is to look at the plan of queries that may take a long time to run…now I have to actually run them.

      How do I rebuild my “local plan table”, or is there a way I can download a beta version of 18.1?

      Thanks.

    • Gerrat

      There was no (local) plan table on a database I tested this on.
      Running the `utlxplan.sql` & creating the plan table didn’t change anything with the (lack of) results (Oracle Database 10g Release 10.2.0.4.0 – 64bit Production).
      Reading another article on this topic, I then changed the plan_table synonym to point to sys.plan_table$.
      Still no results from SQL Developer Explain Plan.

      Is there something else I can try, or when will this bug be fixed?
      …alternatively, is there a place I can download an older SQL Developer version where this functionality is still working?

    • thatjeffsmith Post Author

      Hmmm. 10g. I’m betting we changed our code to do something that doesn’t exist in 10g, which we no longer support.

      Every version of SQL Developer is available for download. Go to the downloads page, scroll do the bottom. There’s a link for ‘older versions.’

    • Gerrat

      I deleted my SQL Developer preferences, and re-ran.
      The plan shows up now – just posting this in case someone else runs across the same issue.
      The difference turned out to be the “Other XML Branch” option.
      When that option is turned off, NO results display at all.
      Don’t really want this cruft in the results, but this option appears to be needed to get results at all.
      Thanks

    • thatjeffsmith Post Author

      Doh, I meant to say ‘check’ NOT ‘uncheck’ the other xml branch column in the plan preferences. Thanks Gerrat.

  2. avinash Reply

    Hi Jeff,
    So many columns are in explain plan OK but many of them are missing in explain plan window. e.g.- distribution,
    But description of each is missing.
    Can you please guide me to the documentation of multiple columns with same sort of name to select in options e.g.- execution order column vs overlay, other vs other_tag vs other xml branch vs other xml column ?

    What does it mean by different color coding in ‘projection’ node of plan tree? (e.g.- #keys =1 but there but why four columns are highlighted with same color)

  3. John Garmon Reply

    I see entries on the list, but I really need to click on it to know what I’m looking at. See my off-thread comment where dba is helping me.
    Thank you,
    john3

  4. John Garmon Reply

    DBA ran sql developer as me, on his pc… and it works. We think it may relate to my network work-group rights. My boss sees hers as grayed out also. Thanks for the help. I’ll try to come back and verify when I have it running.
    john3

  5. John Garmon Reply

    I use SQL Developer, only.

    I just had a dba run one as me, and the drop-down is not grayed out. This is looking like a permissions issue.

  7. John Garmon Reply

    I only see 3 grayed-out entries on the drop-down.

    How can I actually see an explain plan in SQL Developer?

    Sorry for posting here, but I found previous post asking the same question a few years back. At that time I think you said it was a bug? …and had been reported for fixing?

    Help?
    john3

    • John Garmon

      I do see the public synonym (v$sql_plan), but cannot see the table (V$_SQL_PLAN) under schema: sys. My SQL Developer drop-down looks very much like the demo, just grayed out. I feel like maybe I should just start a support ticket.

Write A Comment