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