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…

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.

6 Comments

  1. Dear Jeff,

    This is really an impressing feature.
    However, how can I generate the 2nd explain plan? It will be always generated in the same panel.

    Regards,
    Edgar

    • You need to pin the first plan before you generate a 2nd plan. You can see the pin in the first screenshot with the arrow pointing to the upper left hand corner.

  2. I lovin it. Thanks for the hint. It is one of the well hidden features. Well …. or badly educated Yury πŸ™‚

Write A Comment