oracle日常巡检--检查oracle数据库性能

概述

昨天分享了日常巡检的第一部分内容，偷个懒就续一下，哈哈，这里主要介绍第二部分内容：检查oracle数据库性能。

检查oracle数据库性能

这里包含：检查数据库的等待事件，检查死锁及处理，检查cpu、I/O、内存性能，查看是否有僵死进程，检查行链接/迁移，定期做统计分析，检查缓冲区命中率，检查共享池命中率，检查排序区，检查日志缓冲区，总共十个部分。

1、检查数据库的等待事件

set pages 100
set lines 250
col event for a80
select sid, event, p1, p2, p3, WAIT_TIME, SECONDS_IN_WAIT from v$session_wait where event not like 'SQL%' and event not like 'rdbms%';

如果数据库长时间持续出现大量像latch free，enqueue，buffer busy waits，db file sequential read，db file scattered read等等待事件时，需要对其进行分析，可能存在问题的语句。

2、Disk Read最高的SQL语句的获取

col SQL_TEXT for a120;
SELECT SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS) WHERE ROWNUM <= 5;

3、查找前十条性能差的sql

SELECT *
 FROM (SELECT PARSING_USER_ID EXECUTIONS,
 SORTS,
 COMMAND_TYPE,
 DISK_READS,
 SQL_TEXT
 FROM V$SQLAREA
 ORDER BY DISK_READS DESC)
 WHERE ROWNUM < 10;

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;

因为是测试环境，所以这里并没有运行很久的sql。

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

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)) SQL FROM 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、检查碎片程度高的表

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比例

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比例

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#; 

说明：phyrds代表物理读，phywrts代表物理写。

10、检查死锁及处理

10.1、查询目前锁对象信息：

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; 

10.2、oracle级kill掉该session：

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

10.3、操作系统级kill掉session：

#kill -9 pid

如果做数据库日常巡检的话数据库性能这一块也是需要去做个巡检的，前面对processes和sessions这两个好像漏了，大家也可以自己加进去。