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