Arrrggh! My queries running too slow! Again! I need to make it faster. But how? The great
power of SQL is its declarative nature. You tell the database the result
you want and it figures out the best way to get it. So if the database decides how
to execute your query, how do you go about making it faster? Well you need to
look at how the database accesses your tables and joins Them with this
information you can then see if there's a better route available. To do this you
need to get its execution plan. But what is this execution plan? How do you get
one? And how does it help you make your SQL faster? Let's get a plan!
When you send a query to the database it's the job of the SQL optimizer to figure
out how to execute it. This results in the query's execution plan.
This is the driving directions for your query. But instead of telling you which
roads to take and when to turn, it lists how to access your tables and in which
order. For example, join table 1 to table 2. Then join in table 3. By looking at the
routes the database took you can see if there's a faster path available or if
you can create new access methods to make a shortcut! So how do we get this
plan? Before we answer that be aware that there are two types of plan available:
EXPLAIN and EXECUTION. An explain plan doesn't run the query. So this is
just a prediction about what might happen when you really do. This
prediction can be wrong for a whole host of reasons.
So when performance tuning it's the execution plan that you need. There are
various techniques to do this in Oracle Database including the SQL monitor,
DBMS_Xplan, tracing the query, and various third party tools. But in these
videos I'll focus on using the autotrace option in Oracle SQL Developer.
To use it select your query and hit this button. The
query will run and you'll soon be greeted with a plan like this. OK... so
how the heck do you follow these directions? Where do you even start?
Firstly the plan is a tree. Data flows up it from the leaves to the root. The
tables are the leaves the select statements at the top the root. In
between are a whole host of possible operations. Each of these receives rows
from its children and passes them up to its parent. These fall broadly into three
categories: single child, multi-child, and joins. Single child operations have
exactly one operation below them. Sorting, grouping, and aggregating are good
examples of these. If your query includes a group or order by you'll have one of
these in your plan. Multi-child operations have one or more data sources
below. Them these are relatively rare; you're most likely to bump into these
when unioning tables together. When you do this that database combines the rows
from each table into a single union operation in the plan. So that just
leaves joins. We'll cover the exact details of how these work in a future
episode. For now all you need to know is that every join is the proud parent of
exactly two children. Each of which may themselves be joins, tables or any other
plan operation. In theory the database can join tables in any order. Say I have
four tables: colors, bricks, pens, and cuddly toys and I want to join them all
on color. The database could start by joining colors to bricks and separately
join pens to cuddly toys. Then combine the result of these two intermediate
steps into the final data set. So the last join combines the output of joins.
But this is rare in Oracle Database. Left to its own devices the optimizer will
usually have at least one child of a join be a table or other single child
operation. So when combining these four tables it'll pick the two tables to
start with. Then add in the rows from the third
before finally linking the fourth table's data to give the final query output.
Because we're building up the result set as we go along it's best to start with
the two tables that returned the fewest rows. This minimizes how much data you're
carrying around and the work to join each new table in. So I start by joining
the colors to the cuddly toys. Then add in the bricks. Finishing with the table
that returns the most rows, the pens. OK, so how do you know which table the
optimizer started with and whether this is the one that returns the fewest rows?
Well the first table accessed is the one that's top in the plan. To find this
travel along the plan and down until you find a step with no child. This table
passes its rows up to the join above which then requests values from its
other child to add to the intermediate result set. This process continues back
up the tree. At each point you're adding rows to the intermediate results from
another table. Until finally you get to the last join which passes the data to
the select and onto the client. So in this plan the database started with the
colors. Joined those rows to the cuddly toys. Added in the pens. Before finally
joining in rows from bricks. OK, so we figured out the order of processing. But
what about how many rows we're getting? Find that by going to the preferences,
searching for autotrace, then selecting the last output rows and the last starts
columns. While you're here make sure you check "Fetch all rows" to ensure it gets
all the data. Do that, run autotrace and you can see how many rows flowed out
of each step in the plan. So in this plan you can see that we have three rows
coming out of colors which joined to five rows from cuddly toys, resulting in
three rows. We then add in nine rows from the pens. Which gives a total of seven.
And including the 24 rows from the bricks table. Which is a total output of
56 rows. Easy right? Well maybe. There are plenty of exceptions when it comes to
reading execution plans which don't exactly follow these rules. For example I
said that the topmost table in the plan is the one accessed first. Except when it
isn't! Why would this happen? Scalar sub-queries. If you have a select buried
in the select of your query the database gets all the rows from the main query
first. Then passes the values to the subquery. So it runs these parts last. But in
the plan the scalar subqueries appear at the top! So, even though the plan lists
bricks first, it accesses this table last. It starts with the colors table. Then
runs the subquery once for each value this returns. You can verify this by
looking at the "Last Starts" column. This is three for bricks which matches the
number of rows fetched from colors. So, by viewing the plan you can follow the
driving directions for your SQL. The starting point is normally the topmost
operation with no children. The database then works down the plan, going to the
next sibling of this table and repeats the process, searching down for the next
leaf. OK, so... so what? How does having this plan help you make your SQL
faster? Well to figure that out there's two questions you need to answer. Is this
the best order to join the tables? And is that anything you can do to reduce the
work the query does? We get into details for the second question in future episodes.
For now let's focus on the join order. How do you know if this is right? Well
remember the optimizer prefers to start with the two tables that returned the
fewest rows and finish with the one that returns the most. But how does it know
this? Clearly querying each table to see how many rows it returns isn't going to
work if the tables are huge! So it estimates. If these guesses are
accurate it's likely that the database has found at the best plan available. So
what is it based these estimates on? And what can you do about it if they're
wrong? Well this uses the table statistics;
summary information about the data stored in each. We'll get into details of
these in the next episode! Thanks for watching :)
Got any execution plans you're struggling to understand? Post them in
the comments and we'll see how we can help.
And subscribe for more SQL tips