page76
58
2.
The leaf nodes of the tree are the physical objects, in this example,
tables, which are expensive to access (see step 6).
3.
Joins and join filters resolve both the total volume of data selected
and the attributes returned. In this example, there are no filters
to subset the results. The example query joined two source tables
(
part_baseline
and
supplier_baseline
) with an intersection
table (
partsupp_baseline
).
4.
The
ORDER BY
and
LIMIT
clauses are evaluated last and shown as
SortWithLimit
.
ORDER BY
always results in a Sort operation.
5.
The result set limited to 10 records is returned to SnowSight; note
this is a parallel operation.
6.
Here you can see the ordered list of operations from the most
expensive to the least expensive with table access and joins being
the most expensive.
7.
The profile overview shows where the most effort is expended
when executing the query. You should pay particular attention to
local disk I/O and remote disk I/O when performance tuning.
8.
Where “Partitions scanned” is less than “Partitions total,” you
know partition pruning has occurred. In the example query
without filters, you should not be surprised to see these values are
identical.
One further point to note: If you look carefully at the query profile, between the
nodes you will see the number of rows input to each parent node, with proportionally
thicker lines indicating record volume.
Overlaying the query parsing order from Chapter 1 allows you to visualize the query
profile in context. Figure 3-8 overlays the query parsing order onto the query profile.
ChapTer 3
The Query profiler