It’s very rare that a query is developed in a single take. You start with a framework, and then trim down to the essential elements. Then you start working about sort orders, performance, and formatting. As you develop your SQL, you may want to keep several iterations of the data and their plans available.

SQL Developer’s Worksheet allows you to ‘pin’ the Query Results, Explain Plan, and AutoTrace panels.

Pin multiple result sets or plans for review and comparisons.

While pinning the Query Results panel allows you to have multiple result sets persist, pinning the AutoTrace and Explain Plan panels allow you to COMPARE the output auto-magically.

So generate your plans and pin them. Then mouse-right-click on one plan tab, and choose ‘Compare with AutoTraceX.’

Comparing AutoTrace and Explains is a snap with SQL Developer!

Bonus Tip: Rename Panels

If you are going to be working with a plan or two for awhile, go ahead and right-click on the plan label and give it a more descriptive name. Something like ‘FirstRows’ or ‘Parallel+2’ always make more sense than Explain 1 and Explain 2!

So after asking for the compare, here is what SQL Developer presents:

Plans and Execution Stats Side-by-Side

It’s pretty eye-opening to see the deltas, especially around the index usage when you do a IN versus a NOT IN query. I’m not a performance tuning junkie, but there are plenty of blog posts out there on the subject.

Jeff, This Isn’t My Code Though, It’s That Darn Developer’s!

As you’re tuning a problematic report or form, you may find that ‘eye opening’ plan that would explain everything for the person who wrote the offending SQL. SQL Developer makes it really easy to save off the findings and share it with someone else. Simply right-mouse in the compare panel and choose ‘Export HTML.’

SQL Developer HTML Explain Plan Comparison Report

HTML Plan Comparison Report

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.

7 Comments

  1. Hi Jeff,

    Your Tips are most useful and saved a lot of time, Im not sure if this is possible, still i have a gut feeling that you’d know for sure.

    I run so many queries continuously and i need to see the query result tabs with a name instead of seeing them as Query result 1 , Query result 2 and so on.

    I know there is an option to right click on it to rename, but is there anyway similar to “set worksheetname NAME” ?

    Your help or advice would mean a lot to me.

    Thanks in Advance
    Chandru.

  2. Mukul Pandey Reply

    Super amazing feature…didn’t know about it…very handy tool for comparing performance of 2 queries…thx 4 sharing…:)

  3. Mike Miller Reply

    Is there a way to programatically set the query result set tab? Something like the SET workspacename command?

    • I’d like to set the query result set tab name from within my SQL script so I can share my analysis of a tricky a problem with a coworker and if they decide to pin pieces of the intermediary steps it will have a nice name rather than “Query Result X”.

      My initial thought is that if they hit CTRL+Enter it would pull the tab name from a comment within the statement, e.g.:

      /*ResultTabName=Step 3: Check out the count of frobits */
      select * from dual;

    • Joachim D

      I searched for the same feature like Tim (renaming of query result tabs without clicking…).

      As you have shown the possibility to rename worksheets (set worksheetname NAME) – I never need, because – if a worksheet would be important – I would save it – and then it shows the filename…

      … still impressed about Oracle’s Sqldeveloper features…

Reply To Mike Miller Cancel Reply