Oracle维护sql归档

#精确计算表空间大小，消耗系统资源，慎用
SELECT F.TABLESPACE_NAME,
        A.ALL_TOTAL "总空间" ,
        A.ALL_USED "总使用空间" ,
        A.ALL_TOTAL - A.ALL_USED "总剩余空间" ,
        (A.ALL_TOTAL - A.ALL_USED) / A.ALL_TOTAL * 100 "总剩余比例" ,
        A.TOTAL "当前大小" ,
        U.USED "当前使用空间" ,
        F. FREE "当前剩余空间" ,
        (U.USED / A.TOTAL) * 100 "当前使用比例" ,
        (F. FREE / A.TOTAL) * 100 "当前剩余比例"
   FROM ( SELECT TABLESPACE_NAME,
                SUM (BYTES / (1024 * 1024 * 1024)) TOTAL,
                SUM (DECODE(AUTOEXTENSIBLE, 'YES' , MAXBYTES, BYTES) /
                    (1024 * 1024 * 1024)) ALL_TOTAL,
                SUM (USER_BYTES) / (1024 * 1024 * 1024) ALL_USED
           FROM DBA_DATA_FILES
          GROUP BY TABLESPACE_NAME) A,
        ( SELECT TABLESPACE_NAME, SUM (BYTES / (1024 * 1024 * 1024)) USED
           FROM DBA_EXTENTS
          GROUP BY TABLESPACE_NAME) U,
        ( SELECT TABLESPACE_NAME, SUM (BYTES / (1024 * 1024 * 1024)) FREE
           FROM DBA_FREE_SPACE
          GROUP BY TABLESPACE_NAME) F
  WHERE A.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
    AND A.TABLESPACE_NAME = U.TABLESPACE_NAME(+)
  ORDER BY (A.ALL_TOTAL - A.ALL_USED) / A.ALL_TOTAL,F. FREE / A.TOTAL ASC ;

# 检查系统中排行前10的等待事件 ,包括空闲等待事件
select * from (select sid,event,p1,p2,p3,p1text,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where
event not like 'SQL%' and event not like 'rdbms%' order by wait_time desc) where rownum <=10;

TOP SQL
# 逻辑读 TOP 10
select *
  from (select sqt.logicr logical_Reads,
               sqt.exec Executions,
               decode(sqt.exec, 0, to_number(null), (sqt.logicr / sqt.exec)) Reads_per_Exec ,
               (100 * sqt.logicr) /
               (SELECT sum(e.VALUE) - sum(b.value)
                  FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
                 WHERE B.SNAP_ID =7634
                   AND E.SNAP_ID =7637
                   AND B.DBID = 3629726729
                   AND E.DBID = 3629726729
                   AND B.INSTANCE_NUMBER = 1
                   AND E.INSTANCE_NUMBER = 1
                   and e.STAT_NAME = 'session logical reads'
                   and b.stat_name = 'session logical reads') Total_rate,
               nvl((sqt.cput / 1000000), to_number(null)) CPU_Time_s,
               nvl((sqt.elap / 1000000), to_number(null)) Elapsed_Time_s,
               sqt.sql_id,
               decode(sqt.module, null, null, 'Module: ' || sqt.module) SQL_Module,
               nvl(st.sql_text, to_clob('** SQL Text Not Available **')) SQL_Text
          from (select sql_id,
                       max(module) module,
                       sum(buffer_gets_delta) logicr,
                       sum(executions_delta) exec,
                       sum(cpu_time_delta) cput,
                       sum(elapsed_time_delta) elap
                  from dba_hist_sqlstat
                 where dbid = 3629726729
                   and instance_number = 1
                   and 7634 < snap_id
                   and snap_id <= 7637
                 group by sql_id) sqt,
               dba_hist_sqltext st
         where st.sql_id(+) = sqt.sql_id
           and st.dbid(+) = 3629726729
           and (SELECT sum(e.VALUE) - sum(b.value)
                  FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
                 WHERE B.SNAP_ID =7634
                   AND E.SNAP_ID =7637
                   AND B.DBID = 3629726729
                   AND E.DBID = 3629726729
                   AND B.INSTANCE_NUMBER = 1
                   AND E.INSTANCE_NUMBER = 1
                   and e.STAT_NAME = 'session logical reads'
                   and b.stat_name = 'session logical reads') > 0
         order by nvl(sqt.logicr, -1) desc, sqt.sql_id)
 where rownum < 65and(rownum <= 10
                   or Total_rate > 1);
# 物理读 TOP 10

select *
  from (select sqt.dskr Physical_Reads,
               sqt.exec Executions,
               decode(sqt.exec, 0, to_number(null), (sqt.dskr / sqt.exec)) Reads_per_Exec ,
               (100 * sqt.dskr) /
               (SELECT sum(e.VALUE) - sum(b.value)
                  FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
                 WHERE B.SNAP_ID = $P{p_beg_snap}
                   AND E.SNAP_ID = $P{p_end_snap}
                   AND B.DBID = 1273705906
                   AND E.DBID = 1273705906
                   AND B.INSTANCE_NUMBER = 1
                   AND E.INSTANCE_NUMBER = 1
                   and e.STAT_NAME = 'physical reads'
                   and b.stat_name = 'physical reads') Total_rate,
               nvl((sqt.cput / 1000000), to_number(null)) CPU_Time_s,
               nvl((sqt.elap / 1000000), to_number(null)) Elapsed_Time_s,
               sqt.sql_id,
               decode(sqt.module, null, null, 'Module: ' || sqt.module) SQL_Module,
               nvl(st.sql_text, to_clob('** SQL Text Not Available **')) SQL_Text
          from (select sql_id,
                       max(module) module,
                       sum(disk_reads_delta) dskr,
                       sum(executions_delta) exec,
                       sum(cpu_time_delta) cput,
                       sum(elapsed_time_delta) elap
                  from dba_hist_sqlstat
                 where dbid = 1273705906
                   and instance_number = 1
                   and $P{p_beg_snap} < snap_id
                   and snap_id <= $P{p_end_snap}
                 group by sql_id) sqt,
               dba_hist_sqltext st
         where st.sql_id(+) = sqt.sql_id
           and st.dbid(+) = 1273705906
           and (SELECT sum(e.VALUE) - sum(b.value)
                  FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
                 WHERE B.SNAP_ID = $P{p_beg_snap}
                   AND E.SNAP_ID = $P{p_end_snap}
                   AND B.DBID = 1273705906
                   AND E.DBID = 1273705906
                   AND B.INSTANCE_NUMBER = 1
                   AND E.INSTANCE_NUMBER = 1
                   and e.STAT_NAME = 'physical reads'
                   and b.stat_name = 'physical reads') > 0
         order by nvl(sqt.dskr, -1) desc, sqt.sql_id)
 where rownum < 65and(rownum <= 10
                   or Total_rate > 1);
# 消耗CPU TOP 10

select *
  from (select nvl((sqt.elap / 1000000), to_number(null)) Elapsed_Time_s,
               nvl((sqt.cput / 1000000), to_number(null)) CPU_Time_s,
               sqt.exec Executions,
               decode(sqt.exec,
                      0,
                      to_number(null),
                      (sqt.elap / sqt.exec / 1000000)) Elap_per_Exec_s,
               (100 *
               (sqt.elap / (SELECT sum(e.VALUE) - sum(b.value)
                               FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
                              WHERE B.SNAP_ID = 7396
                                AND E.SNAP_ID = 7399
                                AND B.DBID = 1273705906
                                AND E.DBID = 1273705906
                                AND B.INSTANCE_NUMBER = 1
                                AND E.INSTANCE_NUMBER = 1
                                and e.STAT_NAME = 'DB time'
                                and b.stat_name = 'DB time')))/1000 Total_DB_Time_rate,
               sqt.sql_id,
               to_clob(decode(sqt.module,
                              null,
                              null,
                              'Module: ' || sqt.module)) SQL_Module,
               nvl(st.sql_text, to_clob(' ** SQL Text Not Available ** ')) SQL_Text
          from (select sql_id,
                       max(module) module,
                       sum(elapsed_time_delta) elap,
                       sum(cpu_time_delta) cput,
                       sum(executions_delta) exec
                  from dba_hist_sqlstat
                 where dbid = 65972167
                   and instance_number = 1
                   and 7396 < snap_id
                   and snap_id <= 7399
                 group by sql_id) sqt,
               dba_hist_sqltext st
         where st.sql_id(+) = sqt.sql_id
           and st.dbid(+) = 1273705906
order by nvl(sqt.cput, -1) desc, sqt.sql_id)
 where rownum < 65
   and (rownum <= 10 or Total_DB_Time_rate > 1);
# 执行时间 TOP 10
select *
  from (select nvl((sqt.elap / 1000000), to_number(null)) Elapsed_Time_s,
               nvl((sqt.cput / 1000000), to_number(null)) CPU_Time_s,
               sqt.exec Executions,
               decode(sqt.exec,
                      0,
                      to_number(null),
                      (sqt.elap / sqt.exec / 1000000)) Elap_per_Exec_s,
               (100 *
               (sqt.elap / (SELECT sum(e.VALUE) - sum(b.value)
                               FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
                              WHERE B.SNAP_ID = $P{p_beg_snap}
                                AND E.SNAP_ID = $P{p_end_snap}
                                AND B.DBID = 1273705906
                                AND E.DBID = 1273705906
                                AND B.INSTANCE_NUMBER = 1
                                AND E.INSTANCE_NUMBER = 1
                                and e.STAT_NAME = 'DB time'
                                and b.stat_name = 'DB time')))/1000 Total_DB_Time_rate,
               sqt.sql_id,
               to_clob(decode(sqt.module,
                              null,
                              null,
                              'Module: ' || sqt.module)) SQL_Module,
               nvl(st.sql_text, to_clob(' ** SQL Text Not Available ** ')) SQL_Text
          from (select sql_id,
                       max(module) module,
                       sum(elapsed_time_delta) elap,
                       sum(cpu_time_delta) cput,
                       sum(executions_delta) exec
                  from dba_hist_sqlstat
                 where dbid = 1273705906
                   and instance_number = 1
                   and $P{p_beg_snap} < snap_id
                   and snap_id <= $P{p_end_snap}
                 group by sql_id) sqt,
               dba_hist_sqltext st
         where st.sql_id(+) = sqt.sql_id
           and st.dbid(+) = 1273705906
         order by nvl(sqt.elap, -1) desc, sqt.sql_id)
 where rownum < 65
   and (rownum <= 10 or Total_DB_Time_rate > 1);

 

#查看等待事件的详细情况

create or replace procedure WaitHistogram(pFilter varchar2)
is
  vTotalWaitCount integer;
  cursor rec_cur is
  select rpad(substr(event,1,40),42) event,
                         lpad(to_char(wait_time_MILLI,999999999.99),13) wtm,
                         lpad(to_char(wait_count,9999999999.99),13) wct,
                         100*(sum(wait_count) over(order by event,wait_time_milli)) pct_rt
                         from v$event_histogram where event=pFilter
                         order by 1,2;
  c_event varchar2(100);
  c_wtm varchar2(100);
  c_wct varchar2(100);
  c_pct_rt number(20,2);
begin
 select sum(wait_count) into vTotalWaitCount  from v$event_histogram where event=pFilter;
      dbms_output.enable(800000);
      dbms_output.put_line(rpad('event',45)||'Wait time   Wait count Pct_rt');
      open rec_cur;
      fetch rec_cur into c_event,c_wtm,c_wct,c_pct_rt;
      while rec_cur%found loop
          dbms_output.put_line(c_event||' '||c_wtm||' '||c_wct||' '||to_char((c_pct_rt/vTotalWaitCount),0999.99));
      fetch rec_cur into c_event,c_wtm,c_wct,c_pct_rt;
end loop;
close rec_cur;
      end ;
      /  
exec WaitHistogram(pfilter=>'gc buffer busy');

 

 

感谢$无为公子、萧雨、惜分飞的帮助

 

参考至:http://mlxia.iteye.com/blog/741227

 

            http://blog.csdn.net/soulcq/article/details/5418085
            http://www.dbtan.com/2010/05/latch-free.html

 

            http://www.2cto.com/database/201107/96826.html
            http://blog.csdn.net/robinson1988/article/details/4793962
            http://blog.csdn.net/tianlesoftware/article/details/5263238
            http://www.laoxiong.net/dbms_stats_and_analyze_and_global_statistics.html
            http://www.laoxiong.net/wp-content/uploads/2008/12/sosi.sql
            http://www.cnblogs.com/caizhimin816/archive/2012/12/21/2827375.html
            http://oracledoug.com/px.pdf

 

            http://www.linuxeden.com/html/database/20111127/117134.html
            https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1019722.6
            http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel006.htm#VLDBG1513

 

本文原创，转载请注明出处，作者

 

如有错误，欢迎指正

邮箱:[e-mail]