高效进行Oracle日常巡检：数据库性能与安全检查(2)

4. 等待时间最多的5个系统等待事件的获取

SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQL%' ORDER BY TOTAL_WAITS DESC) WHERE ROWNUM<=5; 

5.  检查运行很久的SQL

COLUMN USERNAME FORMAT A12  
COLUMN OPNAME FORMAT A16  
COLUMN PROGRESS FORMAT A8  
SELECT USERNAME,SID,OPNAME,ROUND(SOFAR*100 / TOTALWORK,0) || '%' AS PROGRESS,TIME_REMAINING,SQL_TEXT FROM V$SESSION_LONGOPS , V$SQL WHERE TIME_REMAINING <> 0 AND SQL_ADDRESS=ADDRESS AND SQL_HASH_VALUE = HASH_VALUE; 

6.  检查消耗CPU最高的进程

SET LINE 240 
SET VERIFY OFF 
COLUMN SID FORMAT 999 
COLUMN PID FORMAT 999  
COLUMN S_# FORMAT 999 
COLUMN USERNAME FORMAT A9 HEADING "ORA USER" 
COLUMN PROGRAM FORMAT A29 
COLUMN SQL FORMAT A60 
COLUMN OSNAME FORMAT A9 HEADING "OS USER" 
SELECT P.PID PID,S.SID SID,P.SPID SPID,S.USERNAME USERNAME,S.OSUSER OSNAME,P.SERIAL# S_#,P.TERMINAL,P.PROGRAM PROGRAM,P.BACKGROUND,S.STATUS,RTRIM(SUBSTR(A.SQL_TEXT, 1, 80)) SQLFROM V$PROCESS P, V$SESSION S,V$SQLAREA A WHERE P.ADDR = S.PADDR AND S.SQL_ADDRESS = A.ADDRESS (+) AND P.SPID LIKE '%&1%'; 

7. 检查碎片程序高的表

SQL> SELECT segment_name table_name,COUNT(*) extents FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name HAVING COUNT(*)=(SELECT MAX(COUNT(*)) FROM dba_segments GROUP BY segment_name); 

8.  检查表空间的 I/O 比例

SQL>SELECT DF.TABLESPACE_NAME NAME,DF.FILE_NAME "FILE",F.PHYRDS PYR, F.PHYBLKRD PBR,F.PHYWRTS PYW, F.PHYBLKWRT PBW FROM V$FILESTAT F, DBA_DATA_FILES DF WHERE F.FILE# = DF.FILE_ID ORDER BY DF.TABLESPACE_NAME; 

9.  检查文件系统的 I/O 比例

SQL>SELECT SUBSTR(A.FILE#,1,2) "#", SUBSTR(A.NAME,1,30) "NAME", A.STATUS,A.BYTES,B.PHYRDS,B.PHYWRTS FROM V$DATAFILE A, V$FILESTAT B WHERE A.FILE# = B.FILE#; 

10. 检查死锁及处理

查询目前锁对象信息：

col sid for 999999 
col username for a10 
col schemaname for a10 
col osuser for a16 
col machine for a16 
col terminal for a20 
col owner for a10 
col object_name for a30 
col object_type for a10 
select sid,serial#,username,SCHEMANAME,osuser,MACHINE, 
terminal,PROGRAM,owner,object_name,object_type,o.object_id  
from dba_objects o,v$locked_object l,v$session s  
where o.object_id=l.object_id and s.sid=l.session_id; 

oracle级kill掉该session：

alter system kill session '&sid,&serial#'; 

操作系统级kill掉session：

#>kill -9 pid 

11.查看是否有僵死进程

select spid from v$process where addr not in (select paddr from v$session); 

有些僵尸进程有阻塞其他业务的正常运行，定期杀掉僵尸进程。

12.  检查缓冲区命令中率

13. 检查共享池命令中率

如低于95%，则需要调整应用程序使用绑定变量，或者调整数据库参数shared pool的大小。

14.检查排序区

如果disk/(memoty+row)的比例过高，则需要调整sortareasize(workareasizepolicy=false)或pgaaggregatetarget(workareasizepolicy=true)。

15. 检查日志缓中区

如果redo buffer allocation retries/redo entries 超过1% ，则需要增大log_buffer。

三、检查数据库cpu、I/O、内存性能

（编辑：ASP站长网）