通过ASH快速定导致负载波动的SQL

with t as (select to_char(sample_time,'yyyy-mm-dd hh24:mi') etime,round(count(*)/60) aas   from v$active_session_history where 
sample_time>sysdate-1/24
and session_type='FOREGROUND'
group by to_char(sample_time,'yyyy-mm-dd hh24:mi'))
select etime||rpad('|',aas+1,'-')||aas from t order by etime;
ETIME||RPAD('|',AAS+1,'-')||AAS
-------------------------------------------------------------------
2019-12-20 15:25|--2
2019-12-20 15:26|---3
2019-12-20 15:27|-----5
2019-12-20 15:28|-----------11
2019-12-20 15:29|-----------11
2019-12-20 15:30|-----------------------23  <<<
2019-12-20 15:31|-----------------------23
2019-12-20 15:32|----------------------22
2019-12-20 15:33|-----------------17
2019-12-20 15:34|-------------13
2019-12-20 15:35|------------12
2019-12-20 15:36|-------7
2019-12-20 15:37|-------7
2019-12-20 15:38|-----5
2019-12-20 15:39|-----5
2019-12-20 15:40|-------7
2019-12-20 15:41|-------7
2019-12-20 15:42|---------9
2019-12-20 15:43|----------10
2019-12-20 15:44|--------8
2019-12-20 15:45|--------8
2019-12-20 15:46|-------7
2019-12-20 15:47|------6
2019-12-20 15:48|----4
2019-12-20 15:49|-----5
2019-12-20 15:50|-----5
2019-12-20 15:51|----4
2019-12-20 15:52|----4
2019-12-20 15:53|----4
2019-12-20 15:54|----4
2019-12-20 15:55|------6
2019-12-20 15:56|------6
2019-12-20 15:57|---------9
2019-12-20 15:58|---------------15
2019-12-20 15:59|--------------14
2019-12-20 16:00|--------------------------26 <<<
2019-12-20 16:01|--------------------------26
2019-12-20 16:02|-------------------------25
2019-12-20 16:03|-------------------19
2019-12-20 16:04|--------------14
2019-12-20 16:05|------6
2019-12-20 16:06|--------8
2019-12-20 16:07|--------8
2019-12-20 16:08|---------9
2019-12-20 16:09|------6
2019-12-20 16:10|-------7
2019-12-20 16:11|------6
2019-12-20 16:12|-------7
2019-12-20 16:13|-----5
2019-12-20 16:14|-----5
2019-12-20 16:15|-----5
2019-12-20 16:16|-----5
2019-12-20 16:17|-----5
2019-12-20 16:18|---3
2019-12-20 16:19|----4
2019-12-20 16:20|----4
2019-12-20 16:21|---3
2019-12-20 16:22|---3
2019-12-20 16:23|---3
2019-12-20 16:24|---3
2019-12-20 16:25|---3

61 rows selected.

with t as (select  trunc(sample_time,'mi') etime,decode(sql_plan_hash_value,0,decode(sql_opname,'SELECT',sql_id,sql_opname),sql_plan_hash_value) p,count(*) cnt from v$active_session_history where 
sample_time>sysdate-1/24
and session_type='FOREGROUND'
group by trunc(sample_time,'mi'),decode(sql_plan_hash_value,0,decode(sql_opname,'SELECT',sql_id,sql_opname),sql_plan_hash_value))
,t2 as (select * from (select etime,nvl(p,'no_plan') p,cnt,row_number()over(partition by etime order by cnt desc ) rn  from t) where rn<11)
,t3 as (select p SQL_PLAN,max(cnt)-sum(cnt)/60  x from t2
group by p)
select * from t3 where x>100 order by x desc;
SQL_PLAN                                                              X
---------------------------------------------------------------- ----------
4153993367                                                       427.033333
2447725225                                                       388.766667
1788691278                                                           200.75
dfjnpaj7fxjvt                                                    105.216667
0fb4zxzms59uj                                                    104.766667

with t as (select trunc(sample_time,'mi') etime,round(count(*)/60) aas   from v$active_session_history where 
sample_time>sysdate-1/24 and sql_plan_hash_value=4153993367 --修改
and session_type='FOREGROUND'
group by trunc(sample_time,'mi'))
,t2 as(select trunc(sysdate,'mi')-rownum/1440 etime from dual connect by rownum<61)
select a.etime||rpad('|',nvl(aas,0)+1,'-')||aas from t2 a left join t b on a.etime=b.etime
order by a.etime;

A.ETIME||RPAD('|',NVL(AAS,0)+1,'-')||AAS
-------------------------------------------------------------------
20191220 15:28:00|
20191220 15:29:00|
20191220 15:30:00|-------7
20191220 15:31:00|--------8  <<<<
20191220 15:32:00|--------8
20191220 15:33:00|---3
20191220 15:34:00|
20191220 15:35:00|
20191220 15:36:00|
20191220 15:37:00|
20191220 15:38:00|
20191220 15:39:00|
20191220 15:40:00|
20191220 15:41:00|
20191220 15:42:00|
20191220 15:43:00|
20191220 15:44:00|
20191220 15:45:00|
20191220 15:46:00|
20191220 15:47:00|
20191220 15:48:00|
20191220 15:49:00|
20191220 15:50:00|
20191220 15:51:00|
20191220 15:52:00|
20191220 15:53:00|
20191220 15:54:00|
20191220 15:55:00|
20191220 15:56:00|
20191220 15:57:00|
20191220 15:58:00|
20191220 15:59:00|
20191220 16:00:00|-------7
20191220 16:01:00|--------8  <<<<
20191220 16:02:00|--------8
20191220 16:03:00|---3
20191220 16:04:00|
20191220 16:05:00|
20191220 16:06:00|
20191220 16:07:00|
20191220 16:08:00|
20191220 16:09:00|
20191220 16:10:00|
20191220 16:11:00|
20191220 16:12:00|
20191220 16:13:00|
20191220 16:14:00|
20191220 16:15:00|
20191220 16:16:00|
20191220 16:17:00|
20191220 16:18:00|
20191220 16:19:00|
20191220 16:20:00|
20191220 16:21:00|
20191220 16:22:00|
20191220 16:23:00|
20191220 16:24:00|
20191220 16:25:00|
20191220 16:26:00|
20191220 16:27:00|

60 rows selected.

select sql_id,sql_text from v$sql where plan_hash_value=4153993367 and rownum<2;