page71
53
Get Query Operator Stats
Snowflake has released a new system function called
GET_QUERY_OPERATOR_ STATS()
that is now in Generally Available (GA) status, which I will discuss later. You can find
further information at
https://docs.snowflake.com/en/sql-reference/functions/
get_query_operator_stats
.
Having identified various access paths to viewing query profiles, let’s create an
example query to work through the query profiler.
Example Query
Using our newly established baseline data set, let’s create and execute an example
query and then investigate the query profile. From the TPC ERD provided in Figure 3-2
and substituting the baseline tables, you will use the PARTSUPP_BASELINE, PART_
BASELINE, and SUPPLIER_BASELINE tables initially.
As before, you can establish the good practice of clearing the cache before testing.
ALTER SESSION SET use_cached_result = FALSE;
Expected Result Count
In line with my earlier recommendation, you should know your expected data volumes,
so let’s do this first, and from Table 3-1 you have identified the table row counts.
Therefore, set your warehouse accordingly.
USE WAREHOUSE IDENTIFIER ( $tpc_warehouse_xl );
Now execute your query.
SELECT count(1)
FROM partsupp_baseline ps,
part_baseline p,
supplier_baseline s
WHERE ps.ps_partkey = p.p_partkey
AND ps.ps_suppkey = s.s_suppkey;
Your query should return a record count of 800,000,000 in less than six seconds. If
you had used an X-Small warehouse, your query runtime would have been around 43
seconds.
ChapTer 3
The Query profiler