Group by spill query coming from management console

norbert_krupa

I was looking through the spill events in my environment (6.1.2) and noticed requests coming from management console that are causing group by spills. How can I fix this?

-[ RECORD 1 ]-----+----------------------------------------------------------------------
event_timestamp   | 2014-04-15 12:04:17.976046-05
node_name         | v_..._node0006
user_id           | 45035996273704962  user_name         | dbadmin  session_id        | ...003-5258:0x1d20c6  request_id        | 0  transaction_id    | 54043195551173322  statement_id      | 1  event_category    | EXECUTION  event_type        | GROUP_BY_PREPASS_FALLBACK  event_description | In-memory prepass disabled due to ineffectiveness.  operator_name     | GroupByHash  path_id           | 2  object_id         |  event_details     | Reduced to 3276 / 3277  suggested_action  |  -[ RECORD 2 ]-----+----------------------------------------------------------------------  event_timestamp   | 2014-04-15 12:04:18.013273-05  node_name         | v_..._node0006  user_id           | 45035996273704962  user_name         | dbadmin  session_id        | ...003-5258:0x1d20c6  request_id        | 0  transaction_id    | 54043195551173322  statement_id      | 1  event_category    | EXECUTION  event_type        | GROUP_BY_SPILLED  event_description | GROUP BY key set did not fit in memory, using external sort grouping.  operator_name     | GroupByHash  path_id           | 2  object_id         |  event_details     |  suggested_action  | Consider a sorted projection.  Increase memory available to the plan.

One of the queries causing the event:

select start_time as x, avg(average_cpu_usage_percent)::numeric(5,2) as y   from v_monitor.cpu_usage   where start_time > sysdate() - interval '1 week'   group by start_time order by start_time

Looking up the client:

dbadmin=> select * from dc_session_starts where session_id = '...003-5258:0x1d20c6';  -[ RECORD 1 ]---------+------------------------------  time                  | 2014-04-15 12:04:05.954864-05  node_name             | v_..._node0003  session_id            | ...003-5258:0x1d20c6  user_id               | 45035996273704962  user_name             | dbadmin  client_hostname       |   client_pid            | 31017  client_label          | MC  client_version        |  ssl_state             | None  authentication_method | Password  is_internal           | f  session_type          | CLIENT

This is happening every 5 minutes for the other resource utilization metrics that are used in the activity graph in MC.