Sample views for counter information

The EXECUTION_ENGINE_PROFILES table contains the data for each profiling counter as a row within the table. For example, the execution time (us) counter is in one row, and the rows produced counter is in a second row. Since there are many different profiling counters, many rows of profiling data exist for each operator. Some sample views are installed by default to simplify the viewing of profiling counters.

Running scripts to create the sample views

The following script creates the v_demo schema and places the views in that schema.

/opt/vertica/scripts/demo_eeprof_view.sql

Viewing counter values using the sample views

There is one view for each of the profiling counters to simplify viewing of a single counter value. For example, to view the execution time for all operators, issue the following command from the database:

=> SELECT * FROM v_demo.eeprof_execution_time_us;

To view all counter values available for all profiled queries:

=> SELECT * FROM v_demo.eeprof_counters;

To select all distinct operators available for all profiled queries:

=> SELECT * FROM v_demo.eeprof_operators;

Combining sample views

These views can be combined:

=> SELECT * FROM v_demo.eeprof_execution_time_us
    NATURAL LEFT OUTER JOIN v_demo.eeprof_rows_produced;

To view the execution time and rows produced for a specific transaction and statement_id ranked by execution time on each node:

=> SELECT * FROM v_demo.eeprof_execution_time_us_rank
    WHERE transaction_id=45035996273709699
    AND statement_id=1
    ORDER BY transaction_id, statement_id, node_name, rk;

To view the top five operators by execution time on each node:

=> SELECT * FROM v_demo.eeprof_execution_time_us_rank
    WHERE transaction_id=45035996273709699
    AND statement_id=1 AND rk<=5
    ORDER BY transaction_id, statement_id, node_name, rk;