Blame
| 59d057 | feagor | 2025-12-02 12:14:04 | 1 | ## AWR - Top запросов по elapsed time |
| 2 | ||||
| 3e6b7f | feagor | 2025-12-05 15:27:43 | 3 | ```oraclesql= |
| 59d057 | feagor | 2025-12-02 12:14:04 | 4 | with |
| 5 | snaps as ( |
|||
| 6 | select dbid,snap_id |
|||
| 7 | ,begin_interval_time as beg_time |
|||
| 8 | ,end_interval_time as end_time |
|||
| 9 | from dba_hist_snapshot sn |
|||
| 10 | where dbid=975372630--(select distinct dbid from dba_hist_database_instance i where i.db_name='&_awr_db_name') |
|||
| 11 | and end_interval_time between to_date('&_awr_beg_time','dd.mm.yyyy hh24:mi:ss') and to_date('&_awr_end_time','dd.mm.yyyy hh24:mi:ss') |
|||
| 12 | ) |
|||
| 13 | ,sql_ordered as ( |
|||
| 14 | select |
|||
| 15 | dbid as dbid |
|||
| 16 | ,sql_id as sql_id |
|||
| 17 | ,max(module) as module |
|||
| 18 | ,dense_rank()over(order by sum(elapsed_time_delta) desc) as drnk_ela |
|||
| 19 | ,count(distinct nullif(plan_hash_value,0)) as plans |
|||
| 20 | ,count(*) as snaps |
|||
| 21 | ,max(snap_id) as last_snap |
|||
| 22 | ,count(distinct dbid) as dbids |
|||
| 23 | ,sum(executions_delta) as execs |
|||
| 24 | ,sum(disk_reads_delta) as disk_reads |
|||
| 25 | ,sum(buffer_gets_delta) as buf_gets |
|||
| 26 | ,sum(rows_processed_delta) as rows_pr |
|||
| 27 | ,sum(fetches_delta) as fetches |
|||
| 28 | ,sum(elapsed_time_delta/1e6) as ela_time_secs |
|||
| 29 | ,sum(cpu_time_delta/1e6) as cpu_time_secs |
|||
| 30 | ,sum(iowait_delta/1e6) as io_time_secs |
|||
| 31 | ,sum(apwait_delta/1e6) as app_time_secs |
|||
| 32 | ,round(sum(elapsed_time_delta/1e6)/nullif(sum(executions_delta),0),4) as elaexe |
|||
| 33 | ,round(sum(buffer_gets_delta)/nullif(sum(executions_delta),0)) as bufgets_per_exe |
|||
| 34 | from |
|||
| 35 | dba_hist_sqlstat st |
|||
| 36 | natural join snaps |
|||
| 37 | where module not in ('PL/SQL Developer') |
|||
| 38 | --and module = 'csc.exe' |
|||
| 39 | group by dbid,sql_id |
|||
| 40 | order by ela_time_secs desc |
|||
| 41 | ) |
|||
| 42 | ,topsql as ( |
|||
| 43 | select/*+ no_merge */ so.* |
|||
| 44 | from sql_ordered so |
|||
| 45 | where drnk_ela<=&_awr_top_n |
|||
| 46 | ) |
|||
| 47 | select |
|||
| 48 | sql_id |
|||
| 49 | ,module |
|||
| 50 | ,execs |
|||
| 51 | ,fetches |
|||
| 52 | ,drnk_ela |
|||
| 53 | ,ela_time_secs |
|||
| 54 | ,cpu_time_secs |
|||
| 55 | ,io_time_secs |
|||
| 56 | ,app_time_secs |
|||
| 57 | ,elaexe |
|||
| 58 | ,(select to_char(substr(sql_text,1,800)) from dba_hist_sqltext st where st.sql_id = t.sql_id and st.dbid = t.dbid) as stext200 |
|||
| 59 | ,(select sql_text from dba_hist_sqltext st where st.sql_id = t.sql_id and st.dbid = t.dbid) textfull |
|||
| 60 | ,plans |
|||
| 61 | ,snaps |
|||
| 62 | ,last_snap |
|||
| 63 | ,dbids |
|||
| 64 | ,disk_reads |
|||
| 65 | ,buf_gets |
|||
| 66 | ,rows_pr |
|||
| 67 | ,bufgets_per_exe |
|||
| 68 | from topsql t |
|||
| 69 | where rownum>0 |
|||
| 70 | ``` |