本文共 5107 字，大约阅读时间需要 17 分钟。
1、asm盘的占用
2、表空间占用
3、top10 sql(一段时间内执行最多)
4、top10 sql(一段时间内逻辑读最多)
5、top10 sql(一段时间内物理读最多)
6、top10 sql(一段时间内平均执行时间最长）
7、db time(一段时间内的dbtime/分钟）
8、redo切换次数(一段时间内的redo切换)
9、rman备份信息
10、业务用户top10 表
11、业务用户top10 索引
12、dataguard的归档gap
13、无效对象
14、等待事件
15、datafile 是否关闭自动扩展
su - grid
sqlplus / as sysasm
set lines 300
col name for a10
col state for a10
select name,state,OFFLINE_DISKS,round(total_mb/1024) total_gb,round(free_mb/1024) unsafe_free_gb,round(usable_file_mb/1024) safe_free_gb from v$asm_diskgroup;
su - oracle sqlplus / as sysdba set linesize 1000 col tablespace_name format a20 ; select t1.tablespace_name tablespace_name,t1.flag type,trunc(t1.bytes-nvl(t2.bytes,0),2) used_GB, trunc(t1.maxbytes,2) sum_GB,round(100*(t1.bytes-nvl(t2.bytes,0))/t1.maxbytes,2)||'%' used_pct, 100-round(100*(t1.bytes-nvl(t2.bytes,0))/t1.maxbytes,2)||'%' free_pct from ( SELECT tablespace_name,sum(d1.bytes)/1024/1024/1024bytes,'NORMAL' FLAG, sum(decode(d1.autoextensible,'NO',d1.bytes,d1.maxbytes))/1024/1024/1024 maxbytes FROM dba_data_files d1 GROUP BY tablespace_name UNION all SELECT tablespace_name,sum(d2.bytes)/1024/1024/1024 bytes,'TEMP' FLAG, sum(decode(d2.autoextensible,'NO',d2.bytes,d2.maxbytes))/1024/1024/1024 maxbytes FROM dba_temp_files d2 GROUP BY tablespace_name ) t1,( SELECT tablespace_name,sum(f.bytes)/1024/1024/1024 bytes FROM dba_free_space f GROUP BY tablespace_name ) t2 where t1.tablespace_name = t2.tablespace_name(+) ORDER by t1.flag,t1.tablespace_name ;
create view vw_maxsnap as select max(snap_id) max_snap from dba_hist_snapshot;
create view vw_sql_stat as select * from (select sql_id , sum(executions_delta) execs , sum(buffer_gets_delta) gets , sum(disk_reads_delta) reads , sum(elapsed_time_delta)/1000000 elapsed_s from dba_hist_sqlstat a,dba_hist_snapshot t,vw_maxsnap temp where a.snap_id=t.snap_id and a.snap_id =temp.max_snap group by sql_id);
select a.sql_id,a.execs,substr(regexp_replace(b.sql_text,'(\s)+',' '),1,100) from (select sql_id,execs from (select sql_id,execs from vw_sql_stat order by execs desc) where rownum<=10) a,dba_hist_sqltext b where a.sql_id=b.sql_id;
select a.sql_id,a.gets,substr(regexp_replace(b.sql_text,'(\s)+',' '),1,100) from (select sql_id,gets from (select sql_id,gets from vw_sql_stat order by gets desc) where rownum<=10) a,dba_hist_sqltext b where a.sql_id=b.sql_id;
select a.sql_id,a.reads,substr(regexp_replace(b.sql_text,'(\s)+',' '),1,100) from (select sql_id,reads from (select sql_id,reads from vw_sql_stat order by reads desc) where rownum<=10) a,dba_hist_sqltext b where a.sql_id=b.sql_id;
select a.sql_id,a.avg_elapsed_s,substr(regexp_replace(b.sql_text,'(\s)+',' '),1,100) from (select sql_id,avg_elapsed_s from (select sql_id,elapsed_s/execs avg_elapsed_s from vw_sql_stat where execs>0 order by avg_elapsed_s desc) where rownum<=10) a,dba_hist_sqltext b where a.sql_id=b.sql_id;
select to_char(t.begin_interval_time,'yyyy-mm-dd hh24:mi:ss') btime,to_char(t.end_interval_time,'yyyy-mm-dd hh24:mi:ss') etime , a.value/1000000/60 dbtime_min from DBA_HIST_SYS_TIME_MODEL a,dba_hist_snapshot t, VW_MAXSNAP temp where a.snap_id=t.snap_id and a.snap_id=temp.max_snap and a.STAT_NAME = 'DB time';
select to_char(trunc(first_time, 'HH'),'HH24') Hour, TRUNC(FIRST_TIME, 'DD') Day, count(SEQUENCE#) LOG_SWITCHES from dba_hist_log a,dba_hist_snapshot t,vw_maxsnap temp where a.snap_id=t.snap_id and a.snap_id=temp.max_snap and to_char(trunc(first_time, 'HH'),'HH24')=to_char(trunc(BEGIN_INTERVAL_TIME, 'HH'),'HH24') group by TRUNC(FIRST_TIME, 'DD'), trunc(first_time, 'HH');
select to_char(START_TIME,'yyyy-mm-dd hh24:mi:ss'),to_char(END_TIME,'yyyy-mm-dd hh24:mi:ss') ,INPUT_TYPE from v$rman_backup_job_details where INPUT_TYPE!='ARCHIVELOG' order by START_TIME;
select * from (select sum(bytes/1024/1024/1024) ,segment_name from dba_segments where owner='CC' and segment_type='TABLE' group by segment_name order by sum(bytes/1024/1024/1024) desc )where rownum<=10;
select * from (select sum(bytes/1024/1024/1024) ,segment_name from dba_segments where owner='CC' and segment_type='INDEX' group by segment_name order by sum(bytes/1024/1024/1024) desc )where rownum<=10; dataguard：
select (a.log_archived - b.log_applied) log_gap from (select max(sequence#) log_archived from v$archived_log where dest_id = 1 and archived = 'YES') a , (select max(sequence#) log_applied from v$archived_log where dest_id = 2 and APPLIED = 'YES') b;
SELECT owner, object_name, object_type, status FROM dba_objects WHERE status <> 'VALID' ORDER BY owner, object_name;
14、等待事件
select event, count(*) count from v$session_wait group by event order by 2 desc;
15、datafile 是否关闭自动扩展
select file_name,tablespace_name,autoextensible from dba_data_files where autoextensible='YES' and tablespace_name not like 'UNDO%' union all select file_name,tablespace_name,autoextensible from dba_temp_files where autoextensible='YES' ;
转载地址：http://mlwmf.baihongyu.com/