Hi there! Imagine I have three tables: one of cuddly toys, one of bricks, and one of
pens and I want to combine these three tables based on their color. But which
order should I join them? I'd like to start with the two tables that return
the fewest rows. So in this case that's the toys and then the bricks. And then
finish by adding in the pens. But how do I know how many rows I'm going to get
from each? I could go and count how many things there are on each one. But then
I'm running a query to run a query. That doesn't make any sense. Instead from time
to time I'll capture summary information about what's actually stored in each
table: the table statistics. But what are these statistics and how do they help
your SQL run faster? Let's find out!
Table statistics give a summary of the information stored in each table. This
includes details such as the total number of rows and for each column the
highest lowest and number of different values it has. Using this information the
optimizer can then estimate how many rows you're going to get from each table.
If you're selecting all the rows from a table you can use the row count stat to
give a good estimate. But as soon as you add a where clause
you'll only get some fraction of the rows back. The database calculates this
fraction as one divided by the number of distinct values in the column you're
filtering on. This gives the selectivity of your filter; the proportion of rows in
the table the database expects this where clause to return. To estimate how
many rows you'll get it then multiplies the selectivity by the total number of
rows stored in the stats to give the cardinality. So how does this work in
practice? Well let's say I want to find all the blue bricks. The optimizer
estimates the selectivity of this by dividing
by the number of different colors there are - three - to give one third. It then
multiplies this by the total number of bricks - nine - to give an estimated
cardinality of three. The optimizer will then repeat this process for all other
tables in the query and then use these estimates to decide which plan to use.
You can check the accuracy for these estimates by seeing if the estimated
number of rows for an operation, multiplied by the number of times it
started roughly matches the number of rows it really returned. When these
figures are in the same order of magnitude it's likely the optimizer has
found the best plan available. So a key factor in the quality of the plan is the
quality of the statistics. If these are missing, out-of-date or misleading
there's a good chance the optimizer will choose the wrong plan. So what can you do
in these cases? Well for the most part you don't need to worry about missing or
out-of-date - stale - statistics. Modern versions of Oracle
Database have a background process that will gather them for you. But if you just
archived off a whole bunch of rows you may not want to wait for this. In which
case you can gather them manually. But even with fully up-to-date statistics
the optimizer can still get its estimates completely upper wrong. Why?
Correlation and skew.
On my table here I have red cubes, blue cuboids, and green star shape things.
Three different colors, three different shapes. So if I'm searching for a given
color I'll get a third of the bricks back. Same applies if I look for a given
shape. But what happens if I search for a given color and shape? Well that's either
gonna return one third of the bricks too - say I look for the red cubes - or return
nothing at all. Say I look for green cubes. But what does the optimizer think?
If you search for blue cubes it says: there's three different colors. So the
selectivity of color is one third. The same applies for shapes. Then by default
it gets the combined selectivity in the same way you calculate the probability
of independent events: by multiplying the fractions together! To get 1/9. Leading to
an estimated cardinality of one. I'm never gonna get this number of bricks
back when searching by color and shape. I'm either going to get 3 or 0. So what
can we do about this? Create extended statistics! These capture relationships
between columns. By considering a group of columns as one the database can
record how many different values there are across the columns. So with extended
stats on color and shape we can now see that there are only 3 different
combinations and the optimizer can guess correctly again. OK, so that solves the
correlation problem. What about skew? What is this and how do you fix it?
Skew comes in two varieties, range and value.
Let's start with value skew. This happens when there's an uneven spread in your
values. So let's get some different bricks. So now I've got four blue cubes,
eight green, and 14 red cubes. Meaning we've got more red bricks than blue or
green put together. If I'm searching for a given color we can no longer take the
total number of bricks divided by the number of different colors and get an
accurate estimate for how many rows the query's going to return. To fix this we can
store the number of bricks for each color. This gives a frequency histogram.
Each value is stored in what's called a bucket. Provided the stats are up-to-date
this gives us a very accurate estimate of how many rows our query will return
when searching for any of these colors. Also if we search for a color we don't
have - say pink - we can see no buckets store this value and infer we'll get few
rows. Frequency histograms sound great. So why don't we create them on all columns
all the time? Well there's a limit to how many different buckets the database can
create on each column. So if the number of different values is greater than the
number of buckets we can no longer store how many rows have every value. For
example, let's say we add in a yellow brick so we've got four colors. But we're
still stuck with 3 buckets. 4 into 3 doesn't go. So what can we do?
Luckily there's a trick we can use. If the number of yellow bricks is small
such as 1 we can safely ignore it in our histograms and still get reasonable
estimates when searching for yellow values because it's rare. This gives a
top-frequency or top-N histogram. Only the most common values appear in it with
the exact row counts. Any that are really rare don't appear at all. But this only
works if we've got few yellow values. If we
add a whole bunch more we can no longer safely ignore them and still get
reasonable estimates when searching for yellow bricks. So we need to spread four
values over three buckets. Meaning each bucket covers a range of values. We aim
to spread the rows evenly so each bucket has the same number of rows, giving a
height balanced histogram. With this we know how many bricks are in each bucket
and what the maximum value is in each. But not exactly how many rows there are
for any given color. This can lead to overestimating rare values such as
yellow and blue, and under estimating common values such as red. But once again
there's another trick available. The green and red bricks are spread over two
buckets each. Let's move them so they only appear in one bucket each. And
record how many bricks have the same color as the final one for each bucket.
So we get frequency like behavior for the last value in a bucket. But the
buckets can cover as many values as we want. We've combined the benefits of
frequency and height-balanced histograms into a hybrid histogram. This can still
lead to miss estimation, for example we don't know exactly how many blue bricks
there are, but it does help reduce its effect. Uneven distributions in values
like this are common in your data. So histograms make sense here.
But what about values that are guaranteed to be unique? Say your primary
key? Here each value can appear at most once. Which leads some people to ponder
an interesting question: why has the database created a histogram on my
sequence assigned primary key? It's time to discuss range skew. This occurs when
there's an uneven spread between the values. Say we've numbered the bricks
starting at one. Currently the values are all consecutive. But if we now check away
these bricks there's a hole in the numbers. So if we search for values
between 5 and 10 we get no rows back. Adding a histogram
helps identify these missing values. So through a combination of having
up-to-date stats, identifying column correlations by creating extended stats
also called columns groups, and using histograms to identify skew we enable
the optimizer to come up with good estimates. Modern versions of Oracle
Database can gather all of this for you automatically - provided you set your
preferences correctly! If you have a plan where your estimates are way off and
you're getting a bad plan you may want to tweak the histogram and extended
stats settings for each table. You can do this by setting table preferences. But
what if the estimates are bang-on and your query is still too slow? What do you
do now? Well you need to reduce the work your
SQL does. So you need to see how many resources it consumes. How do you do this?
We'll find out in the next episode! Thanks for watching! Do you have any
questions about how to use extended stats or histograms? If so let us know in
the comments. And subscribe for more SQL magic