What the database remembers: sys.completed_queries

Every query that finishes on an Ocient system is recorded — automatically — in the sys.completed_queries system catalog table, along with 72 columns of post-execution detail: a full timing breakdown, rows and bytes returned, temp-disk spill, the optimizer's cost estimate, which nodes participated, peak memory, and the workload-management decisions that governed it.

You can query it as a normal read-only sandbox user — you'll see your own queries (an admin sees everyone's; on this sandbox that's already 7,700+):

SELECT count(*) FROM sys.completed_queries;

The anatomy of one query

Here's a real row from the sandbox — a GROUP BY year over the 119-million-row taxi table — with the timing columns broken out (all times in milliseconds):

The story is right there: 99.9% of the time was execution_time — this is a full scan of 119M rows across a single foundation drive, so it's I/O-bound, not plan-bound. Compare a windowed GROUP BY year over the 61M-row GDELT table that returned in 120 ms total (88 ms execution) because it touched far less data. The table makes that difference visible and measurable, query by query.

To see your own recent queries and where their time went:

SELECT sql,
       total_time,
       execution_time,
       optimization_time,
       rows_returned,
       participating_nodes
FROM sys.completed_queries
ORDER BY timestamp_complete DESC
LIMIT 10;

Other columns worth knowing: bytes_returned, temp_disk_consumed (did the query spill to temp disk?), num_root_operator_instances (how parallel the top of the plan ran), rows_inserted / rows_deleted, cached_query / resultset_cached, and several approx_system_peak_*_mem_bytes memory gauges.

Workload management is recorded here too

Ocient talks a lot about workload management (WLM) — and sys.completed_queries is where you see it applied. Queries run under a service class, and the row records which one and the effective priority it got:

SELECT sql, concurrency_service_class_name,
       initial_priority, final_effective_priority, queue_time
FROM sys.completed_queries
ORDER BY timestamp_complete DESC LIMIT 10;

A service class is a named set of limits and priorities. You can list the ones defined on the system:

SELECT name, scheduling_priority, max_concurrent_queries, max_elapsed_time
FROM sys.service_classes;

On the sandbox that returns classes like ocient_ui (scheduling_priority 50) and retention_policy. The knobs a service class can set include (full list in the docs):

• scheduling_priority — ordering for who runs first (a default query is 1.0).
• max_concurrent_queries — how many can run at once before others queue (and queue_time in completed_queries shows how long they waited).
• max_elapsed_time — kill runaway queries after N seconds.
• max_temp_disk_usage — cap temp-disk spill for the class.
• statement_text — auto-route queries matching a LIKE/REGEX pattern to a class (e.g. send everything touching a big table to a lower-priority class).
• dynamic priority (priority_adjustment_factor / _time) that nudges a query's priority up or down while it runs.

On a real multi-tenant hyperscale cluster this is how you keep an interactive dashboard fast while a terabyte-scale batch job runs underneath it — high-priority class for the dashboard, capped concurrency for the batch. And afterward, sys.completed_queries is the evidence trail: which class each query landed in, what priority it ran at, whether it queued, how long it took, and whether it spilled to disk.

Why it's useful

• Find slow queries — sort by execution_time, see exactly which ones and on which nodes.
• Spot spills — temp_disk_consumed > 0 flags memory-pressured queries.
• Capacity planning — counts and timings over time show how the system is used.
• Verify WLM — confirm queries are landing in the right service class at the right priority, and measure queue_time under contention.

Pair this with the example queries: run a few, then look yourself up in sys.completed_queries and see what the database remembered about them.