Using SQL Developer to optimizer your queries in the cloud or not.

At Hotsos we are all about performance.  And at the heart most performance problems is a select  statement more often than not.   In the past my go-to tool to do performance optimization and training has been SQL*Plus.  Of late I’m moving more and more to SQL Developer. Because it has changed from a tool that was so-so several years ago to a powerhouse of features and functionality. 

In this short bit I just want to talk about the autotrace feature in particular and how to use it for performance optimization of a select.   Some key things to focus on when optimizing a query is logical IOs (LIOs), Starts and of course time.  And pretty much in that order.  Time is of course the goal, make it run fast. How to make it faster? Make sure it does less work.  How do you measure work of a query?  LIOs.   Now I take a short look at how to use autotrace to do this.   I’m using the 17.4 drop of SQL Developer.

This is a query that returns some customer information and the last date they placed an order. The code looks like this:

Just a quick tip, avoiding distinct when not needed. It does invoke extra work so make sure it’s really necessary, here the code needs it because a customer could place more than one order per day.  Using autotrace in SQL Developer we can see this for the plan:

The column headings that are cut off are: LAST_STARTS, LAST_OUTPUT_ROWS, CARDINALITY, LAST_CR_BUFFER_GETS, LAST_ELAPSED_TIME and QBLOCK_NAME.  With these columns you have really good idea what is happening with your query. 

Looking the top line you can see the query did 267,478 LIOs (LAST_CR_BUFFER_GETS) to get the result set of 1000 rows (LAST_OUTPUT_ROWS).  That’s and average of 267 LIOs per row returned.  That seems high.  Maybe it’s OK, but one other thing gives me a strong hint that isn’t ideal.  Notice that the query block named MAX_BLOCK is executing 1,310 times.  And this repeated execution is driving the vast majority of the LIOs.  This part of the plan also takes the longest which is often true, but not always.

Rewriting the code to use an analytic to find the max date would look like this:

Using autotrace on this code, this is the plan and statistics:

Wow!  That’s great.  Notice the starts is 1 all the way up and down the plan, the total LIOs is down to an almost unbelievable 238 from 267,478.  And the time of course dropped to .22 seconds from 1.7 seconds. 

A nice thing to do in SQL Developer is put both code blocks in one editor window.  Then do autotrace on both and it’s easy to compare the runs.   Here I’ve done that and after each autotrace finished I did a right click on the tab and renamed each.  Then do a right click on either one lets you compare it to the other (I right clicked on SubQ to get what is shown below).

Below the plan is a boat load of stats collect by the autotrace and doing the compare you can see how these have changed.  Ones that are different are in red, and nearly all of them are in red here. (There are more below this screen shot and some are the same.)  The consistent gets (yet another name for LIOs) dropped way way down.

SQL Developer doesn’t really do anything that you can’t do in something like SQL*Plus.  For example at Hotsos we have a free tool called the Harness that collects all this same information in SQL*Plus.  SQL Developer pulls all this information together for you in a very easy to navigate presentation. This makes it a snap to read and analyze the problem which then helps you make the right decisions. If you're not using SQL Developer you really need to start. 

Also for great info on installing, configuring and using SQL Developer, hop over to Jeff Smith's Blog.  He's the main man for SQL Developer, he with his outstanding team have made it a powerful and indispensable part of SQL development.