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’);