How do I monitor a long running CTAS?

bmurrell

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.

Jim_Knicely

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.