How do I monitor a long running CTAS?
bmurrell
Community Edition User ✭
I'm running a Create Table As, but how do I monitor its progress?
I'm looking at counters in v_monitor.execution_engine_profiles and v_internal_events, but they're NULL.
Tagged:
0
Answers
Grab the transaction id and statement id for the CTAS statement and try a query like this?
dbadmin=> SELECT node_name, operator_name, counter_name, TO_CHAR(SUM(counter_value), '999,999,999,999') rows_loaded_so_far FROM execution_engine_profiles WHERE transaction_id IN (?) AND statement_id = ? AND operator_name IN ('StorageUnion', 'Sort') AND counter_name IN ('rows produced', 'rows to process', 'total rows read in sort') GROUP BY node_name, operator_name, counter_name ORDER BY node_name, operator_name, counter_name, SUM(counter_value); node_name | operator_name | counter_name | rows_loaded_so_far ----------------------------+------------------+--------------------------+-------------------- v_somepoc_node0001 | Sort | rows produced | 4,009,983 v_somepoc_node0001 | Sort | total rows read in sort | 4,009,983 v_somepoc_node0001 | StorageUnion | rows produced | 4,009,983 v_somepoc_node0001 | StorageUnion | rows to process | 4,009,983 v_somepoc_node0003 | Sort | rows produced | 4,007,858 v_somepoc_node0003 | Sort | total rows read in sort | 4,007,858 v_somepoc_node0003 | StorageUnion | rows produced | 4,007,858 v_somepoc_node0003 | StorageUnion | rows to process | 4,007,858 (8 rows)For the "StorageUnion" operator, when "rows produced" = "rows to process" then it is done.
For the "Sort" operator, when "rows produced" = "total rows read is sort" then it is done.
Note that Sort occurs after StorageUnion.