oracle常用SQL整理

1.查询AWR报告:

select s.snap_id,
       s.dbid,
       s.instance_number,
       to_char(s.end_interval_time, 'yyyy-mm-dd hh24:mi:ss') cdate
  from dba_hist_snapshot s
 order by snap_id desc;
 
select dbms_workload_repository.awr_report_html(1594936874,
                                                1,
                                                153940,
                                                153941)
  from dual;
  
select dbms_workload_repository.ash_report_html(1594936874,
                                                1,
                                                to_date('2015-07-17 10:00:00',
                                                        'yyyy-mm-dd hh24:mi:ss'),
                                                to_date('2015-07-17 10:05:00',
                                                        'yyyy-mm-dd hh24:mi:ss'))
  from dual;

2.查询ASH报告:

select dbms_workload_repository.ash_report_html(l_dbid     => 2288065565,
                                                l_inst_num => 1,
                                                l_btime    => to_date('2015-12-17 12:00:00',
                                                                      'yyyy - mm - dd hh24 :mi :ss '),
                                                l_etime    => to_date('2015-12-17 12:40:00',
                                                                      'yyyy-mm-dd hh24:mi:ss'))
  from dual;


select output
  from table(dbms_workload_repository.ash_report_text((select dbid
                                                        from v$database),
                                                      1,
                                                      TO_DATE('20130712203500',
                                                              'YYYYMMDDHH24MISS'),
                                                      TO_DATE('20130712204600',
                                                              'YYYYMMDDHH24MISS')));

3.sql_info

select sql_id,
       first_load_time,
       round(cpu_time / executions / 1000, 2) cpu_time_us,
       round(elapsed_time / executions / 1000, 2) elapsed_time_us,
       round(buffer_gets / executions / 1000, 2) buffer_gets,
       executions,
       parsing_user_id
  from v$sql
 where sql_id = '&1';

4.查看具体SQL的IO

select to_char(sample_time, 'yyyy/mm/dd hh24') sample_time,
       sql_id,
       round(sum(h.delta_read_io_bytes / 1024 / 1024 / 1024), 2) read_size_g
  from v$active_session_history h
where h.sample_time >= sysdate - 4 / 24
group by to_char(sample_time, 'yyyy/mm/dd hh24'), sql_id
having round(sum(h.delta_read_io_bytes / 1024 / 1024 / 1024), 2) >= 50
order by 1 asc, 3 desc;

5.查看总的IO

select e.snap_id,
       to_char(e.startup_time, 'yyyy-mm-dd hh24:mi:ss') instance_startup_time,
       to_char(e.end_interval_time, 'yyyy-mm-dd hh24:mi:ss') snapshot_end_time,
       b.value - a.value IO_requests,
       round((d.value - c.value)/1024/1024/1024) IO_GB
  from dba_hist_sysstat  a,
       dba_hist_sysstat  b,
       dba_hist_sysstat  c,
       dba_hist_sysstat  d,
       dba_hist_snapshot e
where a.stat_name = 'physical read total IO requests'
   and b.stat_name = 'physical read total IO requests'
   and c.stat_name = 'physical read total bytes'
   and d.stat_name = 'physical read total bytes'
   and a.snap_id = e.snap_id - 1
   and b.snap_id = e.snap_id
   and c.snap_id = e.snap_id - 1
   and d.snap_id = e.snap_id
   and e.end_interval_time BETWEEN
       TO_DATE('20160511 10:20:00', 'yyyymmdd hh24:mi:ss') AND
       TO_DATE('20160511 21:14:00', 'yyyymmdd hh24:mi:ss')
order by e.begin_interval_time

6.查看SQL当前指标

select a.HASH_VALUE,
       a.sql_text,
       a.PLAN_HASH_VALUE,
       round(a.BUFFER_GETS / a.EXECUTIONS) bg_per_exec,
       round(a.CPU_TIME / 1000 / a.EXECUTIONS) cpu_per_exec,
       round(a.ELAPSED_TIME / 1000 / a.EXECUTIONS) time_per_exec,
       round(a.ROWS_PROCESSED / a.EXECUTIONS) rows_per_exec,
       a.EXECUTIONS,
       a.BUFFER_GETS,
       a.DISK_READS,
       a.CPU_TIME,
       a.ELAPSED_TIME,
       a.ROWS_PROCESSED
  from v$sql a
 where hash_value = '3332437006'
   and cpu_time <> 0
   and buffer_gets <> 0
   and rows_processed <> 0
 order by sql_text;

7.查询sql历史执行信息（次数，时间,HASH_VALUES变化等）

select sn.snap_id,
  s.instance_number,
  to_char(sn.end_interval_time, 'YYYYMMDD HH24:MI:SS'),
  s.plan_hash_value,
  s.executions_delta,
  round(s.elapsed_time_delta / s.executions_delta)/1000 elapsed_time_ms ,
  round(s.BUFFER_GETS_delta / s.executions_delta),
  round(s.CPU_TIME_delta / s.executions_delta)/1000 CPU_TIME_ms
  from dba_hist_snapshot sn, sys.WRH$_SQLSTAT s
where s.snap_id = sn.snap_id
   and s.sql_id = '5t21ty9qhhpw2'
   and s.instance_number = sn.instance_number
   and s.executions_delta > 0
order by sn.end_interval_time desc;

8.查询temp空间历史消耗情况

select *
  from (select t.sample_time,
               s.PARSING_SCHEMA_NAME,
               t.sql_id,
               t.sql_child_number as sql_child,
               round(t.temp_space_allocated / 1024 / 1024 / 1024, 2) || ' G' as temp_used,
               round(t.temp_space_allocated /
                     (select sum(decode(d.autoextensible, 'YES', d.maxbytes, d.bytes))
                        from dba_temp_files d),
                     2) * 100 || ' %' as temp_pct,
               t.program,
               t.module,
               s.SQL_TEXT
          from v$active_session_history t, v$sql s
         where t.sample_time > to_date('&begin_time', 'yyyy-mm-dd hh24:mi:ss')
           and t.sample_time < to_date('&end_time', 'yyyy-mm-dd hh24:mi:ss')
           and t.temp_space_allocated is not null
           and t.sql_id = s.SQL_ID
         order by t.temp_space_allocated desc)
 where rownum < 50
 order by temp_used desc;

9.表空间使用情况

select b.tablespace_name,
       round(b.TOTAL_GB) || 'G' as TOTAL_GB,
       round((b.TOTAL_GB - (B.DISK_GB - nvl(a.FREE_GB,0))) * 100 / b.TOTAL_GB, 2) || '%' FREE_PCT,
       (100 -
       round((b.TOTAL_GB - (B.DISK_GB - nvl(a.FREE_GB,0))) * 100 / b.TOTAL_GB, 2)) || '%' USED_PCT,
       round(nvl(a.FREE_GB,0),2) || 'G' as FILE_FREE_GB,
       round((b.TOTAL_GB - (B.DISK_GB - nvl(a.FREE_GB,0))),2) || 'GB' TOTAL_FREE_GB
  from (select TABLESPACE_NAME, sum(bytes) / 1024 / 1024 / 1024  FREE_GB
          from dba_FREE_space
         group by tablespace_name) a,
       (select tablespace_name,
               SUM(BYTES) / 1024 / 1024 /1024 DISK_GB,
               sum(decode(maxbytes, 0, bytes, maxbytes)) / 1024 / 1024 / 1024 as TOTAL_GB
          FROM DBA_DATA_FILES
         group by tablespace_name) b
 where a.tablespace_name(+) = b.tablespace_name
 order by 1;

10.session UNDO占用情况

select se.SID,
       se.SERIAL#,
       se.STATUS,
       round(tr.USED_UBLK *
             (select value from v$parameter where NAME = 'db_block_size') / 1024 / 1024 / 1024,
             4) || ' G' as undo_size
  from v$session se, v$transaction tr
 where se.TADDR = tr.ADDR(+)
 ORDER BY 4 desc;

11.SPID to sql

select se.username,
       se.sid,
       se.serial#,
       se.osuser,
       se.machine,
       se.program,
       se.logon_time,
       sa.sql_text
  from v$session se, v$sqlarea sa, v$process pr
 where se.SQL_ADDRESS = sa.ADDRESS
   and se.SQL_HASH_VALUE = sa.HASH_VALUE
   and se.PADDR=pr.ADDR
   and pr.spid = '&SPID'; 

11.HOLDER会话查询

select count(0), sql_id, BLOCKING_SESSION
                    from v$session ee
                   where BLOCKING_SESSION in
                         (select BLOCKING_SESSION
                            from (select cc.SID,
                                         cc.SERIAL#,
                                         cc.BLOCKING_SESSION,
                                         cc.EVENT,
                                         cc.SQL_ID,
                                         cc.USERNAME
                                    from v$session cc
                                   where cc.BLOCKING_SESSION is not null
                                     and EVENT = '&1'))
                   group by sql_id, BLOCKING_SESSION
                   order by count(0) desc;


select sql_id,BLOCKING_SESSION,sid from v$session where sid in ( 
select BLOCKING_SESSION from 
(select BLOCKING_SESSION,rownum num from 
(select count(0), sql_id, BLOCKING_SESSION
                    from v$session ee
                   where BLOCKING_SESSION in
                         (select BLOCKING_SESSION
                            from (select cc.SID,
                                         cc.SERIAL#,
                                         cc.BLOCKING_SESSION,
                                         cc.EVENT,
                                         cc.SQL_ID,
                                         cc.USERNAME
                                    from v$session cc
                                   where cc.BLOCKING_SESSION is not null
                                     and EVENT = '&1'))
                   group by sql_id, BLOCKING_SESSION
                   order by count(0) desc)) test
                   where num <=1);

12.隐参查询

select  
  x.ksppinm  name,  
  y.ksppstvl  value,  
  y.ksppstdf  isdefault,  
  decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE')  ismod,  
  decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE')  isadj  
from  
  sys.x$ksppi x,  
  sys.x$ksppcv y  
where  
  x.inst_id = userenv('Instance') and  
  y.inst_id = userenv('Instance') and  
  x.indx = y.indx and  
  x.ksppinm like '%_&par%'  
order by  
  translate(x.ksppinm, ' _', ' ') ;

13.lgwr写的平均响应时间和等待

with t as (
 select s.snap_id, s.instance_number, s.end_interval_time ,
 total_waits - lag(total_waits, 1) over (partition by s.instance_number order by s.snap_id) waits,
 (time_waited_micro - lag(time_waited_micro, 1) over (partition by s.instance_number order by s.snap_id))/1000 twt
 from dba_hist_system_event ev, dba_hist_snapshot s
 where ev.instance_number = s.instance_numbr and ev.snap_id = s.snap_id
 and event_name = 'log file parallel write'
 and s.end_interval_time BETWEEN TO_DATE('20170401 10:00:00', 'yyyymmdd hh24:mi:ss') AND TO_DATE('20170614 11:00:00', 'yyyymmdd hh24:mi:ss') )e
select to_char(end_interval_time,'YYYYMMDD HH24:MI'), instance_number, sum(waits), sum(twt), round(sum(twt)/sum(waits), 2) wt_ms
from t
where (to_char(end_interval_time, 'hh24') between 9 and 12 or to_char(end_interval_time, 'hh24') between 14 and 17)
group by to_char(end_interval_time,'YYYYMMDD HH24:MI'), instance_number
order by 1, instance_number;

14. 单块读的平均响应时间

with t as (
 select s.snap_id, s.instance_number, s.end_interval_time ,
 total_waits - lag(total_waits, 1) over (partition by s.instance_number order by s.snap_id) waits,
 (time_waited_micro - lag(time_waited_micro, 1) over (partition by s.instance_number order by s.snap_id))/1000 twt
 from dba_hist_system_event ev, dba_hist_snapshot s
 where ev.instance_number = s.instance_number and ev.snap_id = s.snap_id
 and event_name = 'db file sequential read'
 and s.end_interval_time BETWEEN TO_DATE('20170401 10:00:00', 'yyyymmdd hh24:mi:ss') AND TO_DATE('20171014 11:00:00', 'yyyymmdd hh24:mi:ss') )
select to_char(end_interval_time,'YYYYMMDD HH24:MI'), instance_number, sum(waits), sum(twt), round(sum(twt)/sum(waits), 2) wt_ms
from t
where (to_char(end_interval_time, 'hh24') between 9 and 12 or to_char(end_interval_time, 'hh24') between 14 and 17)
group by to_char(end_interval_time,'YYYYMMDD HH24:MI'), instance_number
order by 1, instance_number;

15.等待事件监控

SELECT /*+ordered*/
SW.SEQ#,
S.SQL_HASH_VALUE,
S.SQL_ID,
SW.SID || ',' || S.SERIAL# SIDS,
S.USERNAME,
S.OSUSER,
SW.EVENT,
D.EVENT_COUNT,
SW.P1,
SW.P2,
SW.P3,
SW.WAIT_TIME "WAIT",
SW.STATE,
SW.SECONDS_IN_WAIT SEC,
S.STATUS,
S.LAST_CALL_ET, TO_CHAR(S.LOGON_TIME, 'dd/hh24:mi:ss') LOGON_TIME,
ST.SQL_TEXT SQL_TEXT
  FROM (SELECT EVENT, COUNT(1) EVENT_COUNT
          FROM V$SESSION_WAIT
         WHERE EVENT NOT LIKE '%SQL*Net%'
           AND EVENT NOT LIKE 'PX Deq%'
           AND EVENT NOT LIKE 'rdbms ipc message'
           AND EVENT NOT LIKE 'queue messages'
           AND EVENT NOT LIKE 'class slave wait'
           AND EVENT NOT LIKE
               'Streams AQ: waiting for messages in the queue'
         GROUP BY EVENT
        HAVING((EVENT = 'latch free' AND COUNT(1) > 10) OR (EVENT = 'enqueue' AND COUNT(1) > 5) OR (EVENT = 'db file scattered read' AND COUNT(1) > 10) OR (EVENT = 'db file sequential read' AND COUNT(1) > 50) OR (EVENT = 'buffer busy waits' AND COUNT(1) > 20) OR (EVENT NOT IN ('db file sequential read', 'buffer busy waits', 'db file scattered read', 'latch free', 'enqueue') AND COUNT(1) > 10))) D,
       V$SESSION_WAIT SW,
       V$SESSION S,
       V$SQL ST
WHERE D.EVENT = SW.EVENT
   AND SW.SID = S.SID
   AND S.SQL_ADDRESS = ST.ADDRESS(+)
   AND S.SQL_HASH_VALUE = ST.HASH_VALUE(+)
   AND S.USERNAME NOT IN ('SYS', 'SOLIX')
   AND ST.CHILD_NUMBER(+) = 0
   AND SW.WAIT_CLASS <> 'Idle'
ORDER BY SW.EVENT, S.USERNAME

16.清理sharepool某条sql占用

select sql_id,
      sql_text,
      sql_fulltext,
      executions,
      round(sharable_mem / 1024 / 1024, 2) sharmemo_used_mb,
      last_active_time,
      parsing_schema_name,
      s.service,
      'exec  dbms_shared_pool.purge(''' || address || ',' ||
      hash_value || ''',''c'');' remove_sql
 from v$sql s
where s.sharable_mem > 1 * 1024 * 1024
order by s.sharable_mem desc;

17、会话打开游标数

select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'opened cursors current' order by 1 desc;

18.session redo

select sysdate,
       se.username,
       se.sid,
       se.serial#,
       se.status,
       se.machine,
       se.osuser,
       round(st.value / 1024 / 1024) redosize,
       sa.sql_text
  from v$session se, v$sesstat st, v$sqlarea sa
 where se.sid = st.sid
   and st.STATISTIC# =
       (select STATISTIC# from v$statname where NAME = 'redo size')
      --and se.username is not null
   and st.value > 10 * 1024 * 1024
   and se.SQL_ADDRESS = sa.ADDRESS
   and se.SQL_HASH_VALUE = sa.HASH_VALUE
 order by redosize;