Profiling Single Statements
To profile a single statement, prefix it with PROFILE
. You can profile a query (SELECT
) statement, or any DML statement such as INSERT
, UPDATE
, COPY
, and MERGE
. The statement returns with a profile summary:
- Profile identifiers
transaction_id
andstatement_id
- Initiator memory for the query
- Total memory required
For example:
=> PROFILE SELECT customer_name, annual_income FROM public.customer_dimension WHERE (customer_gender, annual_income) IN (SELECT customer_gender, MAX(annual_income) FROM public.customer_dimension GROUP BY customer_gender);NOTICE 4788: Statement is being profiled HINT: Select * from v_monitor.execution_engine_profiles where transaction_id=45035996274760535 and statement_id=1; NOTICE 3557: Initiator memory for query: [on pool general: 2783428 KB, minimum: 2312914 KB] NOTICE 5077: Total memory required by query: [2783428 KB] customer_name | annual_income ------------------+--------------- James M. McNulty | 999979 Emily G. Vogel | 999998 (2 rows)
You can use the profile identifiers transaction_id
and statement_id
to obtain detailed profile information for this query from system tables EXECUTION_ENGINE_PROFILES
and QUERY_PLAN_PROFILES
. You can also use these identifiers to obtain resource consumption data from system table QUERY_CONSUMPTION
.
For example:
=> SELECT path_id, path_line::VARCHAR(68), running_time FROM v_monitor.query_plan_profiles WHERE transaction_id=45035996274760535 AND statement_id=1 ORDER BY path_id, path_line_index; path_id | path_line | running_time ---------+----------------------------------------------------------------------+----------------- 1 | +-JOIN HASH [Semi] [Cost: 631, Rows: 25K (NO STATISTICS)] (PATH ID: | 00:00:00.052478 1 | | Join Cond: (customer_dimension.customer_gender = VAL(2)) AND (cus | 1 | | Materialize at Output: customer_dimension.customer_name | 1 | | Execute on: All Nodes | 2 | | +-- Outer -> STORAGE ACCESS for customer_dimension [Cost: 30, Rows | 00:00:00.051598 2 | | | Projection: public.customer_dimension_b0 | 2 | | | Materialize: customer_dimension.customer_gender, customer_d | 2 | | | Execute on: All Nodes | 2 | | | Runtime Filters: (SIP1(HashJoin): customer_dimension.custom | 4 | | | +---> GROUPBY HASH (GLOBAL RESEGMENT GROUPS) (LOCAL RESEGMENT GR | 00:00:00.050566 4 | | | | Aggregates: max(customer_dimension.annual_income) | 4 | | | | Group By: customer_dimension.customer_gender | 4 | | | | Execute on: All Nodes | 5 | | | | +---> STORAGE ACCESS for customer_dimension [Cost: 30, Rows: 5 | 00:00:00.09234 5 | | | | | Projection: public.customer_dimension_b0 | 5 | | | | | Materialize: customer_dimension.customer_gender, custom | 5 | | | | | Execute on: All Nodes | (17 rows)