page274
362
Additionally, I advocate consolidating warehouses of the same size into a single
declaration. I also propose query tags with JSON to differentiate usage as discussed next.
You also want minimal warehouse lag. Suspending and resuming warehouses introduce
latency, and some use cases benefit from keeping warehouses “warm.” Conversely,
starting and stopping warehouses in an ad hoc manner can result in under-utilization
and additional spend, which delivers no value.
Workload Monitoring
Where workloads are consolidated into generic warehouses, consumption metrics are
more difficult to attribute to the consuming source. To maintain the efficiencies gained
by consolidating workloads, query tags should be set before a SQL statement is issued,
and unset, or set to a new value for subsequent SQL statements.
You should set individual query tags for every SQL operation in your system. The
use of query tags provides a very fine grain of traceability back to the source when
investigating performance issues.
i strongly recommend implementing query tags to assist in later investigations.
An individual query tag may contain up to 2,000 characters and can contain JSON.
ALTER SESSION SET query_tag = '{"Team": "Finance", "Query":
"BusinessLineYTD"}';
You can investigate query tag values using the
SHOW
command.
SHOW PARAMETERS LIKE 'query_tag';
Then extract the “value” programmatically.
SELECT "key",
"value"
FROM TABLE ( RESULT_SCAN ( last_query_id()));
Likewise, you can unset a query tag.
ALTER SESSION UNSET query_tag;
ChapTer 10
OpTimizing perfOrmanCe