I want to run a query to count how many bricks I have on my table here. Clearly
the more I have, the longer this is gonna take. If I go and buy a whole boatload
more clearly this is gonna take longer! It's the same with SQL queries. The
more rows your query processes the longer it's likely to take. But this
isn't the whole story. You also need to look at how much resource your query
consumes. Let's find out how!
By looking at how many rows each step of the plan
processes. you can get a feel for how much work it does. Fewer rows means less
work, more rows means more work. But this is only part of the picture. You also
need to look at how much resource each step of the plan used. The two key
resources being CPU and disk. While both matter, for most queries IO - disk reads -
dominates. So how do you know how much I/O your query used? you need to look at
how many consistent gets it did! So what are these consistent gets, why do they
matter, and how do they differ to the number of rows? To answer that we need to
look at how the database stores data.
When you insert rows to the database
they're not randomly spread across the disk. Each row goes in a block and a
block is the smallest unit of data access. Every time you fetch a row you're
not really getting just that row. You're asking the database to get the block
it's in along with all the other rows in that block. Then locating the row you're
interested in within this block. It's like if, instead of having my bricks here
just randomly placed across the table,
what I do is I store them all in little boxes. Every time I want to get a brick I
have to pick up the box it's in and then find the brick within it. Every time I
pick up another box that's another consistent get. Side note. Why are they
called consistent gets? Well remember that your queries have to return a read
consistent view of the data. That is how the rows existed at the time your query
started. So all the values are consistent to the same point in time. Some
statements can also do current mode gets. Unlike consistent gets which return
how values existed at some point in the past, current mode gets show values as
they exist right now. Each of these gets - also known as buffer gets or consistent
reads - is an IO operation and is relatively expensive. Worst case the
block you want isn't cached in memory. So the database has to fetch it from disk.
This can be orders of magnitude slower then memory access. But even if the block
you want is cached in memory the database still has to do some work to
ensure you only view data committed at the time your query started. So each of
these gets is relatively expensive. The fewer gets your query does the
faster it's likely to be. For example, say I want to get four bricks out of my
boxes here. I could have all four bricks just in this one box. So I can return
them all with one consistent get. Or I could have to get one brick from each of
the four boxes. So I have to do - in this case - four separate gets to get the four
different bricks. So the second query does more work and is likely to be
slower even though it processes exactly the same number of rows! So it's critical
you add this I/O information to your execution plan. Do this in SQL
Developer by going to the tools menu. Selecting the
preferences, and locating autotrace. Once here, select the last CR and CU buffer
gets. While we're at it also select the disk reads and writes, and elapsed time.
With these enabled then run autotrace for your query and you'll get a plan
that looks a little bit like this. Going down the plan to the first leaf we
can see this query first reads all the rows in the bricks table. This uses 42
gets and took 367 microseconds. These rows then flow up to the hash join. This
then triggers the second join which reads all the rows in from colors in 3 gets.
Then fetches all the data from the pens table in another 42 gets. But what about
the hash joins themselves? How much work did they do?
Well the gets disk and timing figures are all cumulative. So to see the values
for a particular step you need to subtract off the corresponding values
for its direct children. Starting at the top, you can see the figures for the
select operation are the same as those for the view below it. So this step did
basically nothing. Going to the view and subtracting the values for the sort
shows that this also did very little work. Walking down the plan repeating
this process for the gets, disk, and timing figures for each step reveals how
much work they each did. When you get to the joins you need to subtract off the
values for both of its children. For the first one this is the 42 gets for
reading the bricks table and then 45 for the other join. For the second join
subtract from its 45 gets the 3 for reading the colors table and the 42 for
reading pens. So it also did zero gets. In fact all non-leaf operations in this
plan did zero consistent gets! So does that mean they did no work? Clearly
no. The sort operation took well over a second. So what's going on? Converting the
cumulative totals to individual step figures makes it
much easier to see what's going on. You can see that this sort operation did a
huge number of disk reads and writes. The data set is too large for the database
to sort it in memory so it has to start writing it to and reading it from
temporary disk to process it. As you can see from how long this sort takes, these
disk operations are slow; showing that IO is usually a performance killer. But what
about the hash join at the bottom? We already established it does no gets of
its own and there's no other disk reads either. So what's it doing?
This means it's able to process a join entirely in memory and the majority of
its runtime is burning CPU. If your queries are CPU bound like this, this is
generally a good thing because you can scale your application by adding more
CPUs to the database. So to make your SQL faster you need to review the
execution plan and look for the operations that did the most work. That
is process for most rows, use the most consistent gets, temp disk or just took
the longest. But once you've found these operations what can you do about it?
Well the first thing to check is that the query itself only does necessary
work. For example, if your application fetches a thousand rows, but then only
keeps ten of them and throws the other 99% away again that's clearly a lot of
wasted effort. You should have just fetched the ten rows you wanted to start
with. In which case the solution is simple - just improve your where clause!
While you're at it ensure you only select columns you need. A select star
returns all the visible columns in the table. If you only care about what
color your bricks are also returning their weight, shape, price, purchase date,
and general level of cleanliness is clearly a waste! You're transferring a
lot of unnecessary data over their network.
But even when your query only returns values that you require it could
still be doing a lot of unnecessary work. How do you spot this? By looking at the
predicate section of the execution plan. View this in SQL Developer by going
back to the autotrace preferences. Once here, go to the predicates branch
option and select it. With this enabled we'll get the execution plan for this
query. We can see it does a full table scan of the bricks table, filtering the
rows to just those where the color is red. This means the database is
inspecting every single row and asking: Is this red? Is this red? Is this red? Is
this red? Is this red? ...
Is this red? Is this red? Is this red? Is this red? Is this red? Until it's checked
every single one! Clearly this is inefficient. But what can we do about it?
Create new data structures to speed up access! The most common you'll use is an
index. If we create an index on the color column and then run the previous query
again, in the execution plan you can see that the filter has now changed to an
access, which means the database only inspects rows that exactly match your
search criteria. Comparing the two plans you can see both
read 250 rows. But with the index in place the database does substantially
less work. Just 3 I/Os in total, compared to 41 without it. Clearly creating new
data structures like this can make your SQL much faster. So we'll look at the
hows and whys of these in future episodes. In the meantime, while tuning
your SQL you need to look at the execution plan and ask: which are the
operations that do the most I/O or take the longest? And can you change your
SQL so you process less data. But ultimately response time is king.
Customers don't care how many I/O operations you're tuning efforts saved,
they love the fact that the page loads 10 times faster! So above all else
measure how long it takes to run your queries and focus your tuning efforts on
the slowest queries within the customer's process. Thanks for watching! When tuning
SQL what do you look for in the execution plan? Let us know in the
comments. And subscribe to learn more about SQL!