Script: Finding the Top N Queries for a User (AWR)
In some conditions, I need to find the top N queries for a specific user in the database.
Assuming my customer is running Enterprise Edition and have tuning pack licenses, it is easy enough to pull the data off the Automatic Workload Repository (AWR).
For some reason, a lot of DBAs are not aware that the AWR report is just a report – and you can query the base table yourself to extract more information if you need it.
This is a short script I sometime find very useful – finding the top N queries for a specific user.
This query is sorting the queries by the aggregated time it spent in the database (total, for all runs).These queries are often the best candidates for queries that needs to be optimized or tuned.
select sub.sql_id, txt.sql_text, parsing_schema_name, sub.seconds_since_date, sub.execs_since_date, sub.gets_since_date, round(sub.seconds_since_date / (sub.execs_since_date + 0.01), 3) avg_query_time from ( -- sub to sort before top N filter select sql_id, g.parsing_schema_name, round(sum(elapsed_time_delta) / 1000000) as seconds_since_date, sum(executions_delta) as execs_since_date, sum(buffer_gets_delta) as gets_since_date, row_number() over (order by round(sum(elapsed_time_delta) / 1000000) desc) r from dba_hist_snapshot natural join dba_hist_sqlstat g where begin_interval_time > sysdate - 7 and parsing_schema_name = '&user_name' group by sql_id, g.parsing_schema_name) sub join dba_hist_sqltext txt on sub.sql_id = txt.sql_id where r < &N order by seconds_since_date desc
Leave a ReplyWant to join the discussion?
Feel free to contribute!