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.
