How to check vertica stauts (such as cpu, memory status) by using query?

Here is a Histogram of Average CPU Usage Percent by Minute query, used in Vadvisor report:
select node_name ,
sum(v80) CountOfAverageLessThan80,
sum(v95) CountOfAverageBetween80and95,
sum(v99) CountOfAverageBetween95and99,
sum(v100) CountOfAverageGreaterThan95,
listagg(vtime||' ' USING PARAMETERS max_length=2048,on_overflow='TRUNCATE') TimeWindowsWithAverageGreaterThan99 ,
max(action)
from (select node_name , case when average_cpu_usage_percent < 80 then 1 else 0 end v80,
case when average_cpu_usage_percent >=80 and average_cpu_usage_percent < 95 then 1 else 0 end v95,
case when average_cpu_usage_percent >=95 and average_cpu_usage_percent < 99 then 1 else 0 end v99,
case when average_cpu_usage_percent >=99 then 1 else 0 end v100 ,
case when average_cpu_usage_percent >=99 then end_time::varchar else null end vtime,
case when average_cpu_usage_percent >=99 then ' Found an instance of CPU utilization over 99%.' else ' ' end action from system_resource_usage) foo
group by 1;

Here is a query to create a Histogram of Average Resident Memory Usage Percent by minute, from the Vadvisor report:
select node_name ,
sum(v80) CountOfAverageLessThan80,
sum(v95) CountOfAverageBetween80and95,
sum(v100) CountOfAverageGreater95,
listagg(vtime||' ' USING PARAMETERS max_length=2048,on_overflow='TRUNCATE') TimeWindowsWithAverageGreaterThan99,
max(action) from (select node_name ,
case when average_memory_usage_percent < 80 then 1 else 0 end v80,
case when average_memory_usage_percent >=80 and average_memory_usage_percent < 95 then 1 else 0 end v100 ,
case when average_memory_usage_percent >=95 then 1 else 0 end v95 ,
case when average_memory_usage_percent >=95 then end_time::varchar(20) else null end vtime,
case when average_memory_usage_percent >=95 then ' Found an instance of resident memory saturation.' else ' ' end action from system_resource_usage) foo
group by 1;

For CPU util see also: https://www.vertica.com/blog/vsql-visual-storytelling-through-query-language/

If you want to check top 10 queries with high CPU Cycles use the following query (written in the past by Maurizio Felici):
SELECT transaction_id,statement_id, cpu_cycles_us, duration_ms
FROM QUERY_CONSUMPTION
WHERE request_type='QUERY' AND
success AND
( NOW() - end_time) < '6 hour'
ORDER BY 3 DESC,4
LIMIT 10;

To see the top 10 queries with high acquired memory do:
select
qr.transaction_id, qr.statement_id,
ra.pool_name,
sum(qr.request_duration_ms) as request_duration_ms,
sum(qr.memory_acquired_mb) as memory_acquired_mb
from
v_monitor.query_requests qr
left outer join v_monitor.resource_acquisitions ra
on qr.statement_id = ra.statement_id and
qr.transaction_id = ra.transaction_id
where
qr.is_executing is false and
( NOW() - qr.start_timestamp) < '6 hour' and
upper(regexp_substr(qr.request, '\w+')::char(8)) in ('SELECT', 'WITH') and
qr.memory_acquired_mb > 0
group by 1,2,3
order by 5 DESC
limit 10;