How to get the TOPN queries that are CPU intensive

Hi Mohamed,

System view QUERY_CONSUMPTION should be useful here. Given the retention, you can sort on CPU_CYCLES_US to filter top consumers.

Regards,
Pravesh

select REGEXP_REPLACE(REGEXP_REPLACE(substr(qr.request, 1, 500), '\d'), '\''[\S\s]*?\''', '''?''' ) as query
, max(qr.transaction_id) as last_trans_id
, avg(qr.request_duration_ms)/1000/60 as avg_duration_mins
, avg(ra.thread_count) as avg_threads
, to_char(avg(memory_inuse_kb)/1024, '999,999') as avg_mem_mb
, (sum(qr.request_duration_ms) * count(1))/1000 as total_duration_secs
, max(eee.event_type) as event_type
, trunc(min(qr.start_timestamp), 'MI') as first_run
, trunc(max(qr.start_timestamp), 'MI') as last_run
, (sum(qr.request_duration_ms) * count(1)) * sum(ra.thread_count) as total_cost
, count(1) as num_runs
from v_monitor.query_requests qr
join (select transaction_id, statement_id, event_type
        from v_internal.dc_execution_engine_events
       where node_name = (select min(node_name)
                            from v_catalog.nodes)
     ) eee using (transaction_id, statement_id)
left join (select transaction_id, statement_id, thread_count, memory_inuse_kb, duration_ms
             from v_monitor.resource_acquisitions
            where node_name = (select min(node_name)
                                 from v_catalog.nodes)
          ) ra using (transaction_id, statement_id)
where start_timestamp::date > current_date -1
and not qr.is_executing
and eee.event_type in ('GROUP_BY_SPILLED', 'JOIN_SPILLED')
group by 1
having count(*) > 3
order by total_cost desc ;

Enjoy. Also, you can filter out the "SPILLED" filter condition if you want to, but queries that do that tend to be the worst ones in a given system.