ThatJeffSmith

Quick Tip – Comparing Explain Plans with SQL Developer

I love it when a plan comes together!

SQL optimization and tuning is fun for a lot of folks. For others it’s a affirmation that the database is ‘magic.’ I fall somewhere in the middle. No matter where you find yourself on that spectrum however, it’s pretty safe to assume that at some point you will look at an execution plan for insight to your poorly performing SQL statement.

Viewing a plan in SQL Developer is pretty easy, just hit the ‘Explain Plan’ button, or F10.

What I want to do is take two versions of a query I am working on, and have SQL Developer help me identify any plan differences.

Step One: Pin the Plan

Pinning it forces it to stay available even after generating additional plans

Step Two: Generate Plan #2

F10 again, you will now have at least two Explain Plan panels in the worksheet.

Step Three: Right-click in the Explain Plan tab

You have to right-click in the plan tab, not the plan itself

Plan steps which differ will be highlighted appropriately.

While we can’t make tuning a brainless exercise, we can spare your mind a few CPU cycles with just a few clicks. Imaging a plan with several hundred steps that appears to be identical to your original plan…