At the start of this year I embarked on a new
adventure. I’m now at Zione Solutions in
a consulting role as a Senior Database Administrator and doing what I’ve
been teaching for the past so many years.
It’s been quite a change, for one I am now using that strange new join
syntax “LEFT OUTER JOIN … ON…” which I had successfully avoided for all these
years. It’s turned out to be harder than
I expected to learn. Also the queries
I’ve been tuning are rather huge. An
average query is about 3-400 lines long, and the plans for these will easily be
1000 lines. These are reporting queries
the classic Data Warehouse type queries.
The smaller tables are millions of rows and 10’s of billions in a table
is not uncommon. Also it’s on Exadata.
I’ve seen firsthand that what I’ve
been teaching works.
One of the most important things you can do when tuning a
query is; let the numbers lead you to the problem. Some folks can look at a query and, without
running it, make a good guesses as to where the problem will likely be. And that can work, however why bother? Run the query and use a tool like SQL Monitor
to see how the plan is executing. In the
SQL Monitor report the activity column is a great way to see which step is taking
the most percentage of time. Now you
can see exactly where a query is spending all it’s time and then pound that
nail down.
Also it’s about starting and staying small with the
rows. Here is where getting predicates
applied at the right time and use of things like the WITH clause can really
help. With Exadata and working with huge
volumes of data this concept is still critical to success; however the use of
indexes is not a solution more often than not.
Why? Not because
indexes are bad, it’s just that index use tends to serialize the access. This tends to cause nested loop joins which
end up being very expensive many times because the plan will hit the inner
table (or index) way too much. This can
be millions of times, even if using a unique index to look up values, doing it
over a million times isn’t efficient. A
fast full index scan can work fine, since it works like a full table scan and
can be in parallel.
Using subquery factors (also called CTE, Common Table Expression)
are very powerful in these queries. These
are those temporary views you define in the WITH clause, typically it’s best to
use the MATERIALIZE hint. This hint forces the optimizer into making the
sub query factor a global temp table like structor, without thie hint the
optimizer might just merge it back into the query. This is of course exactly what you don’t want
to happen most times. Taking a subquery that is executed even just a couple
times and making a sub query factor can be huge. Also sometimes taking that subquery out of
the main query and making it a “stand alone” query can help
parallelization. This can also work
well to get that “start small stay small” thing.
In the team that I’m working in now, it’s speculated that
about 60% of the performance problems are solved with using sub query factors
(CTEs).
Oh and did I mention LAT (Lateral) views? Yea these tend to work very poorly in these
kinds of queries. Seeing one or more in these plans is a known
killer to performance. Making them go
away can be tricky sometimes. A typical
cause of these is having an OR in the ON clause of an outer join. These LAT view might work well with smaller
data sets, but with large ones these can cause a query to stall at that LAT
view for very long periods of time, like hours.
For me it’s been quite a beginning to the year. As the saying goes chance is inevitable, except from vending machines. And this
year has had plenty, but still not from vending machines.
