1.增长空间
1 select 2 v.host_name as 主机名, 3 d.NAME as 数据库名, 4 TRUNC(sysdate - (v.startup_time)) || '天' || 5 TRUNC(24 * 6 ((sysdate - v.startup_time) - TRUNC(sysdate - v.startup_time))) || '小时' as 运行时间, 7 (CASE 8 WHEN D.LOG_MODE = 'ARCHIVELOG' THEN 9 '是' 10 else 11 '否' 12 end) as 是否归档, 13 g.TB_SIZE as 数据文件大小, 14 g.US_SIZE as 数据使用大小, 15 round(g.US_SIZE / g.TB_SIZE, 4) * 100 as 使用率, 16 round(g.MO_DAY + g.TU_DAY + g.WE_DAY + g.TH_DAY + g.FR_DAY + 17 g.SA_DAY + g.SU_DAY, 18 0) as 总增长, 19 round((g.MO_DAY + g.TU_DAY + g.WE_DAY + g.TH_DAY + g.FR_DAY + 20 g.SA_DAY + g.SU_DAY) / 7, 21 0) as 平均增长 22 --,g.FR_DAY 23 --,g.SA_DAY 24 --,g.SU_DAY 25 --,g.MO_DAY 26 --,g.TU_DAY 27 --,g.WE_DAY 28 --,g.TH_DAY 29 from v$instance v, 30 v$database d, 31 (select value from v$parameter where name = 'cluster_database') c, 32 (with tbs_usage as (select to_char(ds.begin_interval_time, 33 'YYYY-MM-DD HH24') snap_time, 34 row_number() over(ORDER BY to_char(ds.begin_interval_time, 'YYYY-MM-DD HH24')) snap_week, 35 tu.dbid dbid, 36 round(sum(tu.tablespace_size * 37 dt.block_size) / 1024 / 1024) total_size, 38 round(sum(tu.tablespace_usedsize * 39 dt.block_size) / 1024 / 1024) used_size, 40 round(sum(tu.tablespace_usedsize * 41 dt.block_size) / 1024 / 1024) - 42 lag(round(sum(tu.tablespace_usedsize * 43 dt.block_size) / 1024 / 1024)) over(order by to_char(ds.begin_interval_time, 'YYYY-MM-DD HH24')) ince_size 44 from dba_hist_tbspc_space_usage tu, 45 dba_hist_snapshot ds, 46 dba_tablespaces dt, 47 v$tablespace t 48 where tu.snap_id = ds.snap_id 49 and tu.dbid = ds.dbid 50 and tu.tablespace_id = t.ts# 51 and t.name = dt.tablespace_name 52 and ds.instance_number = 1 53 and ds.begin_interval_time >= 54 trunc(sysdate - 7) 55 and ds.begin_interval_time < 56 trunc(sysdate) + 1 / 24 57 and dt.TABLESPACE_NAME not in 58 ('SYSTEM', 59 'SYSAUX', 60 'TEMP', 61 'USERS', 62 'UNDOTBS1', 63 'UNDOTBS2', 64 'GGS') 65 group by to_char(ds.begin_interval_time, 66 'YYYY-MM-DD HH24'), 67 to_char(ds.begin_interval_time - 1, 'D'), 68 tu.dbid 69 having to_char(ds.begin_interval_time, 'YYYY-MM-DD HH24') like('% 00') 70 order by to_char(ds.begin_interval_time, 71 'YYYY-MM-DD HH24')) 72 select sum((case 73 when t.snap_week = 8 then 74 total_size 75 else 76 0 77 end)) TB_SIZE, 78 sum((case 79 when t.snap_week = 8 then 80 used_size 81 else 82 0 83 end)) US_SIZE, 84 sum((case 85 when t.snap_week = 2 then 86 ince_size 87 else 88 0 89 end)) FR_DAY, 90 sum((case 91 when t.snap_week = 3 then 92 ince_size 93 else 94 0 95 end)) SU_DAY, 96 sum((case 97 when t.snap_week = 4 then 98 ince_size 99 else 100 0 101 end)) SA_DAY, 102 sum((case 103 when t.snap_week = 5 then 104 ince_size 105 else 106 0 107 end)) MO_DAY, 108 sum((case 109 when t.snap_week = 6 then 110 ince_size 111 else 112 0 113 end)) TU_DAY, 114 sum((case 115 when t.snap_week = 7 then 116 ince_size 117 else 118 0 119 end)) WE_DAY, 120 sum((case 121 when t.snap_week = 8 then 122 ince_size 123 else 124 0 125 end)) TH_DAY 126 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
1 SELECT * 2 FROM ( SELECT A.INSTANCE_NUMBER, 3 A.SNAP_ID, 4 B.BEGIN_INTERVAL_TIME + 0 BEGIN_TIME, 5 B.END_INTERVAL_TIME + 0 END_TIME, 6 ROUND(VALUE - LAG( VALUE, 1 , '0') 7 OVER(ORDER BY A.INSTANCE_NUMBER, A.SNAP_ID)) "DB TIME" 8 FROM (SELECT B.SNAP_ID, 9 INSTANCE_NUMBER, 10 SUM(VALUE ) / 1000000 / 60 VALUE 11 FROM DBA_HIST_SYS_TIME_MODEL B 12 WHERE B.DBID = (SELECT DBID FROM V$DATABASE) 13 AND UPPER (B.STAT_NAME) IN UPPER(('DB TIME' )) 14 GROUP BY B.SNAP_ID, INSTANCE_NUMBER) A, 15 DBA_HIST_SNAPSHOT B 16 WHERE A.SNAP_ID = B.SNAP_ID 17 AND B.DBID = (SELECT DBID FROM V$DATABASE) 18 AND B.INSTANCE_NUMBER = A.INSTANCE_NUMBER) 19 WHERE BEGIN_TIME >= SYSDATE -7 20 AND BEGIN_TIME< SYSDATE 21 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.查询历史操作
1 select t1.SQL_TEXT, 2 t1.SQL_FULLTEXT, 3 t.sql_opname, 4 t.sql_id, 5 t.machine, 6 t.module, 7 t.machine, 8 t.sql_exec_start 9 from dba_hist_active_sess_history t, v$sqlarea t1 10 where t.sql_id = t1.SQL_ID(+) 11 and t.sql_exec_start > trunc(sysdate) 12 and t.module = 'JDBC Thin Client';