oracle DBA 健康检查

sqlplus / as sysdba << !
数据文件的状态
Select file_name from dba_data_files where status='OFFLINE';
无效的数据库对象  
SELECT owner,object_name,object_type FROM dba_objects WHERE status='INVALID';
不起作用的约束
SELECT owner,constraint_name,table_name,constraint_type,status FROM dba_constraints WHERE status = 'DISABLED' AND constraint_type = 'P';
无效的trigger  
SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = 'DISABLED';


自由范围的碎片
column FSFI format 999,99
select tablespace_name,sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))) FSFI from dba_free_space group by tablespace_name order by 1;

Free List竞争
SELECT class,count FROM        v$waitstat WHERE class='free list';  
SELECT sum(value) FROM v$sysstat WHERE name IN ('db block gets','consistent gets');

回退段竞争
SELECT class,count FROM v$waitstat WHERE class IN ('system undo header','system undo block','undo header','undo block');
SELECT sum(value) FROM v$sysstat WHERE name IN ('db block gets','consistent gets');

调度进程的竞争
SELECT network,sum(busy)/sum(busy)+sum(idle) FROM v$dispatcher GROUP BY network;

物理文件的使用频率
SELECT name,phyrds,phywrts FROM        v$datafile df,v$filestat fs WHERE df.file# =fs.file#;

数据字典高速缓存
SELECT (1-(SUM(getmisses)/(SUM(gets)+SUM(getmisses))))*100 FROM v$rowcache;

库高速缓存
SELECT SUM(pins),SUM(reloads) FROM v$librarycache;

锁与等待
SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name, o.owner,o.object_name,o.object_type,s.sid,s.serial# FROM

v$locked_object l,dba_objects o,v$session s WHERE l.object_id=o.object_id AND l.session_id=s.sid ORDER BY o.object_id,xidusn DESC;

SELECT s.USERNAME,s.SID,s.SERIAL#,t.UBAFIL "UBA filenum", t.UBABLK "UBA Block number",t.USED_UBLK "Number os undo Blocks Used",

t.START_TIME,t.STATUS, t.START_SCNB,t.XIDUSN RollID,r.NAME RollName  FROM v$session s,v$transaction t,v$rollname r WHERE s.SADDR=t.SES_ADDR AND

t.XIDUSN=r.usn;

用户锁状态
SELECT /*+ rule */ s.username, decode(l.type,'TM','TABLE LOCK', 'TX','ROW LOCK',NULL) LOCK_LEVEL,

o.owner,o.object_name,o.object_type,s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser FROM v$session s,v$lock l,dba_objects o WHERE l.sid =

s.sid AND l.id1 = o.object_id(+) AND s.username is NOT NULL;

SQL语句的重载率
SELECT SUM(pins) total_pins,SUM(reloads) total_reloads,SUM(reloads)/SUM(pins)*100 libcache_reload_ratio FROM v$librarycache;

数据缓冲命中率
SELECT a.VALUE + b.VALUE logical_reads,c.VALUE phys_reads,round(100*(1-c.value/(a.value+b.value)),4) hit_ratio FROM v$sysstat a,v$sysstat b,v$sysstat c WHERE a.NAME='db block gets' AND b.NAME='consistent gets' AND c.NAME='physical reads';
  更新中……