[20150705]从AWR抽取有问题的sql语句.txt

--闲着没事,写一个脚本从awr数据里面抽取有问题的sql语句,主要我不想看awr报表,而优化80%的问题集中在sql语句,实际上可能更多.
--另外我看报表主要集中在9-11点,下午3-5点这些报表,这样可能漏调一些晚上的一些schedule,job等相关sql语句的优化.

--注意一定要在环境变量中设置
--NLS_DATA_FORMAT='YYYY/MM/DD HH24:MI:SS'

prompt
prompt @deltasqlid begin_time end_time count
prompt for example :  @deltasqlid '2015/06/24' '2015/06/25' 4
prompt

select * from (  SELECT sql_id, event, COUNT (*)
    FROM DBA_HIST_ACTIVE_SESS_HISTORY
   WHERE     sql_id IS NOT NULL
         AND event IS NOT NULL
         AND SAMPLE_TIME BETWEEN '&&1' AND '&&2'
GROUP BY sql_id, event
having count(*)>=&&3
ORDER BY 3 DESC)
union all
  select * from (
  SELECT sql_id, nvl(event,'on cpu'), COUNT (*)
    FROM DBA_HIST_ACTIVE_SESS_HISTORY
   WHERE     sql_id IS NOT NULL
         AND event IS NULL
         AND SAMPLE_TIME BETWEEN '&&1' AND '&&2'
GROUP BY sql_id, event
having count(*)>=&&3
ORDER BY 3 DESC) where rownum<=50;

--说明: event is NULL 我单独分开,实际上也许是重点,对我们的生产系统,因为我们的机器内存很大.基本没有物理读的情况.
--另外后面的count可以理解为时间,应该x10,对应的单位应该是秒.
--因为v$active_session_history是1秒取样1次,而DBA_HIST_ACTIVE_SESS_HISTORY是10秒取样,这样算出来的count x 10,对应的应该是这个语句的
--花在对应event的秒数,不知道我理解是否正确.
--我看了基本符合我在awr报表看到的结果.