Apr 17

SQL Dev 4.2 Top SQL

One of the things that I’ve always liked about SQL Developer is how it saves me time. Sure, I get this fancy looking GUI tool that enables me to enter SQL commands to the database. But I’ve had that capability from the very beginning with SQL*Plus and Server Manager (if you’ve been around long enough to remember that one). But SQL Dev is a great time saver and its the reason why I rarely use SQL*Plus any more.

SQL Dev 4.2 was released to production last week and one of its new features I like is the Top SQL section from the Instance Viewer. This information is nothing new. I’ve been able to get to this information with SQL scripts and from Oracle’s Enterprise Manager. But SQL Dev saves me time once again.

To use this new feature, one obviously has to be running SQL Dev 4.2. If you do not have a copy of this latest version, you can get it here. Then go to the DBA panel and connect to your instance of choice. Then launch the Instance Viewer. You should be able to see the TOP SQL section quite easily.

You can click on those column headings to sort by the metric of your choice.

If you double-click on a specific SQL statement, the first time you do that for this instance, SQL Dev tries to protect you from potential harm because what follows requires the option Tuning Pack.

After you press Yes, and only do so if you are licensed for this optional pack, you are given details of that SQL statement. You can see the Explain Plan:

One of my two favorite parts of this new feature is the ability to get easy access to bind variable values. All too often when tuning SQL statements, I need to determine what a user supplied for bind variable values.ย 

Just by clicking on the SQL Tuning Advice tab, I can see the results of the Tuning Advisor. This is my other favorite feature and saves me a ton of time having to launch a browser and connect to Enterprise Manager, and then make about 10 other clicks until I get to the same information.

The last bit of information is showing the elapsed time history for this SQL statement.

When I first read the early adopter information for SQL Dev 4.2, a lot of it was focused on the new Formatter. While that is a great time saver to many, I rarely use it. But this is one of the new features I am really excited about.