oracle

1.查询oracle的awr文件，可以根据小时和天查看，查看SQL ordered by Elapsed Time数据

2.oracle在更改视图的时候无法执行成功，是因为有在占用的sql，这是关掉oracle并行，可以结束大批量在执行的sql   session，这时候可以更改视图

2.根据sql建立索引确定查询条件字段，优化查询sql

3.查看服务器cpu和io使用

3.查看当前执行时间比较长的sql

SELECT s.INST_ID,s.sid,s.serial#,s.sql_id,
        s.machine,
        sl.target,
        sl.OPNAME,
        ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
        ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
        ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
 FROM   gv$session s,
        gv$session_longops sl
 WHERE  s.sid     = sl.sid
 AND    s.INST_ID = sl.INST_ID
 AND    s.serial# = sl.serial# and sl.sofar <> sl.totalwork and sl.totalwork<>0;

找到对应的机器

select sql_text from v$sqltext-- dba_hist_sqltext
 where sql_id='&sqlid' order by piece;  

通过sql id找sql 文本。

5.这个sql也可以用来查跑的特别长的sql，不过只是当前正在运行的

 SELECT s.INST_ID,s.sid,s.serial#,s.sql_id,
        s.machine,
        sl.target,
        sl.OPNAME,
        ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
        ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
        ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
 FROM   gv$session s,
        gv$session_longops sl
 WHERE  s.sid     = sl.sid
 AND    s.INST_ID = sl.INST_ID
 AND    s.serial# = sl.serial# and sl.sofar <> sl.totalwork and sl.totalwork<>0;

6.查看top  sql

os: centos 7.4
db: oracle 11.2.0.4

可以依据 cpu时间，执行时间，执行次数，单次执行时间排序，获取 top sql。

with tmp_sql as (
  select to_char(ss.begin_interval_time,'yyyy-mm-dd hh24:mi:ss') as begin_date,
         ss.dbid,
         ss.snap_id,
         ss.instance_number,
         st.sql_id,
         sum(st.cpu_time_delta) as cpu_time_delta,
         sum(st.elapsed_time_delta) as elapsed_time_delta,
         sum(st.executions_delta) as executions_delta
    from dba_hist_snapshot ss,
         dba_hist_sqlstat st
   where 1=1
     and ss.begin_interval_time between to_date('2019-11-05 00:00:00','yyyy-mm-dd hh24:mi:ss')
                                    and to_date('2019-11-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
     and ss.snap_id = st.snap_id
     and ss.dbid = st.dbid
   group by to_char(ss.begin_interval_time,'yyyy-mm-dd hh24:mi:ss') ,
            ss.dbid,
            ss.snap_id,
            ss.instance_number,
            st.sql_id
   order by to_char(ss.begin_interval_time,'yyyy-mm-dd hh24:mi:ss') ,
            ss.dbid,
            ss.snap_id,
            ss.instance_number,
            7 desc
)
select t0.*,
       case when t0.executions_delta is null or t0.executions_delta = 0 then 0
            else round(nvl(t0.elapsed_time_delta,0)/1000000/t0.executions_delta,2) --microseconds
        end as elapsed_second_per_exec, 
       t1.sql_text --,    
       --(select gsa.sql_text from gv$sqlarea gsa where gsa.inst_id=t0.instance_number and gsa.sql_id = t0.sql_id ) as sql_text,     
       --(select gsa.sql_fulltext from gv$sqlarea gsa where gsa.inst_id=t0.instance_number and gsa.sql_id = t0.sql_id ) as sql_fulltext
  from tmp_sql t0
       left outer join dba_hist_sqltext t1
                       on t0.dbid=t1.dbid and t0.sql_id=t1.sql_id
 where 1=1
;

或者把这段时间的sql汇总起来排序

with tmp_sql as (
  select ss.dbid,
         st.sql_id,
         sum(st.cpu_time_delta) as cpu_time_delta,
         sum(st.elapsed_time_delta) as elapsed_time_delta,
         sum(st.executions_delta) as executions_delta
    from dba_hist_snapshot ss,
         dba_hist_sqlstat st
   where 1=1
     and ss.begin_interval_time between to_date('2019-11-05 00:00:00','yyyy-mm-dd hh24:mi:ss')
                                    and to_date('2019-11-07 00:00:00','yyyy-mm-dd hh24:mi:ss')
     and ss.snap_id = st.snap_id
     and ss.dbid = st.dbid
   group by ss.dbid,
            st.sql_id
   order by ss.dbid,
            5 desc
)
select t0.*,
       case when t0.executions_delta is null or t0.executions_delta = 0 then 0
            else round(nvl(t0.elapsed_time_delta,0)/1000000/t0.executions_delta,2) --microseconds
        end as elapsed_second_per_exec, 
       t1.sql_text --,    
       --(select gsa.sql_text from gv$sqlarea gsa where gsa.inst_id=t0.instance_number and gsa.sql_id = t0.sql_id ) as sql_text,     
       --(select gsa.sql_fulltext from gv$sqlarea gsa where gsa.inst_id=t0.instance_number and gsa.sql_id = t0.sql_id ) as sql_fulltext
  from tmp_sql t0
       left outer join dba_hist_sqltext t1
                       on t0.dbid=t1.dbid and t0.sql_id=t1.sql_id
 where 1=1
;