I am using Managed Service and dived through the docs. I found a lot of mentioning of tables in the information_schema database that holds all kind of information about the cluster, databases, queries etc.
But I wish I could find simple queries I can run to see:
- what is the top 10 most used queries
- what is the top 10 slowest queries
Would be great if I got pointers where this data is, how to query it.
Since you’re on the managed service, feel free to contact support. They are happy to answer all of your questions and can offer more details.
In the Monitoring dropdown, select Workload Monitoring. You’ll be prompted to begin profiling. The information on running queries and behavior can then be seen in the Activities and Nodes tabs. The explanation of what you see on the Activities and Nodes tabs is explained here in the doc: Troubleshooting Poorly Performing Queries.
Finally, if you want to run your own active session history (ASH) report against the information_schema, here’s that query for the top 10 most used queries:
(run_count+success_count+failure_count) AS total_executions,
substr(replace(replace(query_text,’\n’,’’), ’ ‘,’ '),1,30) as query_text,
(elapsed_time_ms/(run_count+success_count+failure_count)) AS avg_elapsed_time_ms,
last_finished_timestamp AS last_run,
(cpu_time_ms/(run_count+success_count+failure_count)) as avg_cpu_time_ms,
(cpu_wait_time_ms/(run_count+success_count+failure_count)) AS avg_cpu_wait_time_ms,
(lock_time_ms/(run_count+success_count+failure_count)) AS avg_lock_time_ms,
(network_time_ms/(run_count+success_count+failure_count)) AS avg_network_time_ms,
(disk_time_ms/(run_count+success_count+failure_count)) AS avg_disk_time_ms,
(round((disk_b/1024/1024),2)/(run_count+success_count+failure_count)) AS avg_io_mb,
(round((network_b/1024/1024),2)/(run_count+success_count+failure_count)) AS avg_network_mb,
round((1000*(memory_bs/1024/1024)/(elapsed_time_ms)),2) AS avg_memory_mb,
(memory_major_faults/(run_count+success_count+failure_count)) AS avg_major_faults
FROM information_schema.mv_activities_cumulative JOIN information_schema.mv_queries
ON mv_queries.activity_name = mv_activities_cumulative.activity_name
WHERE activity_type = ‘Query’
and database_name = ‘s2_dataset_tpch’
and last_finished_timestamp <= now() and last_finished_timestamp >= (now() - interval 10 minute)