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
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
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…
6 Comments
Thank you very much! This is exactly what i need
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.
Thanks Jeff, I got it. 🙂
Cheers,
Edgar
I lovin it. Thanks for the hint. It is one of the well hidden features. Well …. or badly educated Yury 🙂
Well hidden – unintentionally, but we don’t make it easy for new folks to find 🙁