oracle 日常监控

1.增长空间

select
       v.host_name as 主机名,
       d.NAME as 数据库名,
       TRUNC(sysdate - (v.startup_time)) || '天' ||
       TRUNC(24 *
             ((sysdate - v.startup_time) - TRUNC(sysdate - v.startup_time))) || '小时' as 运行时间,
       (CASE
         WHEN D.LOG_MODE = 'ARCHIVELOG' THEN
          '是'
         else
          '否'
       end) as 是否归档,
       g.TB_SIZE as 数据文件大小,
       g.US_SIZE as 数据使用大小,
       round(g.US_SIZE / g.TB_SIZE, 4) * 100 as 使用率,
       round(g.MO_DAY + g.TU_DAY + g.WE_DAY + g.TH_DAY + g.FR_DAY +
             g.SA_DAY + g.SU_DAY,
             0) as 总增长,
       round((g.MO_DAY + g.TU_DAY + g.WE_DAY + g.TH_DAY + g.FR_DAY +
             g.SA_DAY + g.SU_DAY) / 7,
             0) as 平均增长
--,g.FR_DAY 
--,g.SA_DAY 
--,g.SU_DAY 
--,g.MO_DAY 
--,g.TU_DAY 
--,g.WE_DAY 
--,g.TH_DAY 
  from v$instance v,
       v$database d,
       (select value from v$parameter where name = 'cluster_database') c,
       (with tbs_usage as (select to_char(ds.begin_interval_time,
                                          'YYYY-MM-DD HH24') snap_time,
                                  row_number() over(ORDER BY to_char(ds.begin_interval_time, 'YYYY-MM-DD HH24')) snap_week,
                                  tu.dbid dbid,
                                  round(sum(tu.tablespace_size *
                                            dt.block_size) / 1024 / 1024) total_size,
                                  round(sum(tu.tablespace_usedsize *
                                            dt.block_size) / 1024 / 1024) used_size,
                                  round(sum(tu.tablespace_usedsize *
                                            dt.block_size) / 1024 / 1024) -
                                  lag(round(sum(tu.tablespace_usedsize *
                                                dt.block_size) / 1024 / 1024)) over(order by to_char(ds.begin_interval_time, 'YYYY-MM-DD HH24')) ince_size
                             from dba_hist_tbspc_space_usage tu,
                                  dba_hist_snapshot          ds,
                                  dba_tablespaces            dt,
                                  v$tablespace               t
                            where tu.snap_id = ds.snap_id
                              and tu.dbid = ds.dbid
                              and tu.tablespace_id = t.ts#
                              and t.name = dt.tablespace_name
                              and ds.instance_number = 1
                              and ds.begin_interval_time >=
                                  trunc(sysdate - 7)
                              and ds.begin_interval_time <
                                  trunc(sysdate) + 1 / 24
                              and dt.TABLESPACE_NAME not in
                                  ('SYSTEM',
                                   'SYSAUX',
                                   'TEMP',
                                   'USERS',
                                   'UNDOTBS1',
                                   'UNDOTBS2',
                                   'GGS')
                            group by to_char(ds.begin_interval_time,
                                             'YYYY-MM-DD HH24'),
                                     to_char(ds.begin_interval_time - 1, 'D'),
                                     tu.dbid
                           having to_char(ds.begin_interval_time, 'YYYY-MM-DD HH24') like('% 00')
                            order by to_char(ds.begin_interval_time,
                                             'YYYY-MM-DD HH24'))
         select sum((case
                      when t.snap_week = 8 then
                       total_size
                      else
                       0
                    end)) TB_SIZE,
                sum((case
                      when t.snap_week = 8 then
                       used_size
                      else
                       0
                    end)) US_SIZE,
                sum((case
                      when t.snap_week = 2 then
                       ince_size
                      else
                       0
                    end)) FR_DAY,
                sum((case
                      when t.snap_week = 3 then
                       ince_size
                      else
                       0
                    end)) SU_DAY,
                sum((case
                      when t.snap_week = 4 then
                       ince_size
                      else
                       0
                    end)) SA_DAY,
                sum((case
                      when t.snap_week = 5 then
                       ince_size
                      else
                       0
                    end)) MO_DAY,
                sum((case
                      when t.snap_week = 6 then
                       ince_size
                      else
                       0
                    end)) TU_DAY,
                sum((case
                      when t.snap_week = 7 then
                       ince_size
                      else
                       0
                    end)) WE_DAY,
                sum((case
                      when t.snap_week = 8 then
                       ince_size
                      else
                       0
                    end)) TH_DAY
           from tbs_usage t) g;

 

2.查询tps

with ins_one as
 (select g.instance_number,
         to_char(g.begin_time, 'yyyy-mm-dd') DD,
         round(max(g.maxval), 2) MX,
         round(avg(g.average), 2) AV,
         row_number() over(ORDER BY g.instance_number, to_char(g.begin_time, 'yyyy-mm-dd')) RN
    from DBA_HIST_SYSMETRIC_SUMMARY g
   where g.metric_unit = 'Transactions Per Second'
     and g.begin_time >= trunc(sysdate - 7)
     and g.begin_time < trunc(sysdate)
   group by to_char(g.begin_time, 'yyyy-mm-dd'), g.instance_number
   order by 2)
select
       i.HOST_NAME,
       i.INSTANCE_NAME,
       sum((case
             when o.rn in (1, 8, 15) then
              av
             else
              0
           end)) as "Mon. max tps",
       sum((case
             when o.rn in (1, 8, 15) then
              mx
             else
              0
           end)) as "Mon. min tps",
       sum((case
             when o.rn in (2, 9, 16) then
              av
             else
              0
           end)) as "Tues. max tps",
       sum((case
             when o.rn in (2, 9, 16) then
              mx
             else
              0
           end)) as "Tues. min tps",
       sum((case
             when o.rn in (3, 10, 17) then
              av
             else
              0
           end)) as "Wed. max tps",
       sum((case
             when o.rn in (3, 10, 17) then
              mx
             else
              0
           end)) as "Wed. min tps",
       sum((case
             when o.rn in (4, 11, 18) then
              av
             else
              0
           end)) as "Thur. max tps",
       sum((case
             when o.rn in (4, 11, 18) then
              mx
             else
              0
           end)) as "Thur. min tps",
       sum((case
             when o.rn in (5, 12, 19) then
              av
             else
              0
           end)) as "Fri. max tps",
       sum((case
             when o.rn in (5, 12, 19) then
              mx
             else
              0
           end)) as "Fri. min tps",
       sum((case
             when o.rn in (6, 13, 20) then
              av
             else
              0
           end)) as "Sat. max tps",
       sum((case
             when o.rn in (6, 13, 20) then
              mx
             else
              0
           end)) as "Sat. min tps",
       sum((case
             when o.rn in (7, 14, 21) then
              av
             else
              0
           end)) as "Sun. max tps",
       sum((case
             when o.rn in (7, 14, 21) then
              mx
             else
              0
           end)) as "Sun. min tps"
  from ins_one o, gv$instance i
 where o.instance_number = i.INSTANCE_NUMBER
 group by i.HOST_NAME, i.INSTANCE_NAME;

 

3.查询DB TIME

SELECT *
  FROM ( SELECT A.INSTANCE_NUMBER,
               A.SNAP_ID,
               B.BEGIN_INTERVAL_TIME + 0 BEGIN_TIME,
               B.END_INTERVAL_TIME + 0 END_TIME,
               ROUND(VALUE - LAG( VALUE, 1 , '0')
                     OVER(ORDER BY A.INSTANCE_NUMBER, A.SNAP_ID)) "DB TIME"
          FROM (SELECT B.SNAP_ID,
                       INSTANCE_NUMBER,
                       SUM(VALUE ) / 1000000 / 60 VALUE
                  FROM DBA_HIST_SYS_TIME_MODEL B
                 WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
                   AND UPPER (B.STAT_NAME) IN UPPER(('DB TIME' ))
                 GROUP BY B.SNAP_ID, INSTANCE_NUMBER) A,
               DBA_HIST_SNAPSHOT B
         WHERE A.SNAP_ID = B.SNAP_ID
           AND B.DBID = (SELECT DBID FROM V$DATABASE)
           AND B.INSTANCE_NUMBER = A.INSTANCE_NUMBER)
 WHERE BEGIN_TIME >= SYSDATE -7
 AND BEGIN_TIME< SYSDATE
 ORDER BY BEGIN_TIME;

 

4.查询top SQL-修改

select s.sql_id,
       module 连接方式,
       s.elapsed_time_tot 执行总时间,
       elapsed_tot 执行总次数,
       elapsed_time_per as 每次执行时间,
       cput as CPU时间,
       t1.sql_text,
       nvl(s1.machine, '无信息') 连接客户端机器
  from (select t.sql_id,
               t.dbid,
               max(replace(t.module,' ','_')) as module,
               sum(t.cpu_time_delta / 1000000) cput,
               sum(t.elapsed_time_delta / 1000000) as elapsed_time_tot,
               sum(t.executions_delta) elapsed_tot,
               case when sum(t.executions_delta)= 0 then 0 else  round(sum(t.elapsed_time_delta / 1000000) /
                     sum(t.executions_delta),
                     4)
                     end  as elapsed_time_per
          from dba_hist_sqlstat t, DBA_HIST_SNAPSHOT t2
         where t2.begin_interval_time >= trunc(sysdate - 7)
           and t2.begin_interval_time < trunc(sysdate)
           and t.snap_id = t2.snap_id
         group by t.sql_id, t.dbid) s,
       dba_hist_sqltext t1,
       (select sql_id, dbid, to_char(wm_concat(machine)) as machine
          from (select distinct t.sql_id, t.dbid, t.machine as machine
                  from sys.DBA_HIST_ACTIVE_SESS_HISTORY t
                 where t.sql_exec_start >= trunc(sysdate - 7)
                   and t.sql_exec_start < trunc(sysdate))
         group by sql_id, dbid) s1
 where s.sql_id = t1.sql_id
   and s.dbid = t1.dbid
   and s.dbid = s1.dbid(+)
   and s.sql_id = s1.sql_id(+)
   --and s.elapsed_tot>100
   and module not in ('DBMS_SCHEDULER')
 order by elapsed_time_per desc

注：此sql和awr中top sql保持一致。主要结合执行次数和每次执行时间来判断慢SQL

 

5.查询历史操作

select t1.SQL_TEXT,
       t1.SQL_FULLTEXT,
       t.sql_opname,
       t.sql_id,
       t.machine,
       t.module,
       t.machine,
       t.sql_exec_start
  from dba_hist_active_sess_history t, v$sqlarea t1
 where t.sql_id = t1.SQL_ID(+)
   and t.sql_exec_start > trunc(sysdate)
   and t.module = 'JDBC Thin Client';