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: 

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=45035996278193955 and statement_id=1;
NOTICE 3557:  Initiator memory for query: [on pool pool1: 996147 KB, minimum: 746280 KB]
NOTICE 5077:  Total memory required by query: [996147 KB]
  customer_name   | annual_income
------------------+---------------
 Emily G. Vogel   |        999998
 James M. McNulty |        999979
(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. For example:

=> SELECT path_id, path_line::VARCHAR(68), running_time FROM v_monitor.query_plan_profiles 
   WHERE transaction_id=45035996278193955 AND statement_id=1 ORDER BY path_id, path_line_index;
 path_id |                              path_line                               |  running_time
---------+----------------------------------------------------------------------+-----------------
       1 | +-JOIN HASH [Semi] [Cost: 644, Rows: 25K] (PATH ID: 1)               | 00:00:00.009077
       1 | |  Join Cond: (customer_dimension.customer_gender = VAL(2)) AND (cus |
       1 | |  Materialize at Output: customer_dimension.customer_name           |
       1 | |  Execute on: Query Initiator                                       |
       2 | | +-- Outer -> STORAGE ACCESS for customer_dimension [Cost: 149, Row | 00:00:00.008763
       2 | | |      Projection: public.customer_dimension_DBD_1_rep_VMartDesign |
       2 | | |      Materialize: customer_dimension.customer_gender, customer_d |
       2 | | |      Execute on: Query Initiator                                 |
       2 | | |      Runtime Filters: (SIP1(HashJoin): customer_dimension.custom |
       4 | | | +---> GROUPBY HASH (LOCAL RESEGMENT GROUPS) [Cost: 159, Rows: 3] | 00:00:00.006566
       4 | | | |      Aggregates: max(customer_dimension.annual_income)         |
       4 | | | |      Group By: customer_dimension.customer_gender              |
       4 | | | |      Execute on: Query Initiator                               |
       5 | | | | +---> STORAGE ACCESS for customer_dimension [Cost: 149, Rows:  | 00:00:00.006943
       5 | | | | |      Projection: public.customer_dimension_DBD_1_rep_VMartDe |
       5 | | | | |      Materialize: customer_dimension.customer_gender, custom |
       5 | | | | |      Execute on: Query Initiator                             |
(17 rows)