检查Oracle数据库性能
在本节主要检查 Oracle 数据库性能情况，包含：检查数据库的等待事件，检查死锁及处理，检查 cpu、I/O 、内存性能，查看是否有僵死进程，检查行链接/迁移，定期做统计分析，检查缓冲区命中率，检查共享池命中率，检查排序区，检查日志缓冲区，总共十个部分。
1. 检查数据库的等待事件
set pages 80
set lines 120
col event for a40
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 等等待事件时，需要对其进行分析，可能存在问题的语句。
4.2. Disk Read 最高的SQL语句的获取
SELECT
SQL_TEXT
FROM
(SELECT
*
FROM
V$SQLAREA
ORDER BY
DISK_READS)
WHERE
ROWNUM <= 5;
4.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;
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)) 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#;
10.检查死锁及处理
查询目前锁对象信息：
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
