#精确计算表空间大小，消耗系统资源，慎用
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
?
本文原创，转载请注明出处，作者
?
如有错误，欢迎指正
?