ארכיון עבור קטגוריה: Performance

איך מוציאים את 30‏ השליפות הארוכות ביותר של משתמש מסויים מתוך ה-AWR‏?

משתמשים בשליפה הבאה:

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, 3) avg_query_time
  from ( -- sub to sort before rownum
        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
          from dba_hist_snapshot natural
          join dba_hist_sqlstat g
         where begin_interval_time > to_date('2010-01-01', 'YYYY-MM-DD')
           and parsing_schema_name = '&user_name'
         group by sql_id, g.parsing_schema_name
         order by seconds_since_date desc) sub
  join dba_hist_sqltext txt on sub.sql_id = txt.sql_id
 where rownum < &n;