概述

Oracle DB性能检查有即时观察和长期观察两种方式，即时观察是利用各种SQL查询数据库在当前时间的各个性能指标；长期观察是看各个时期的AWR报告。里面有各种性能指标，按执行时间或资源排列的SQL，各种等待时间的排名。从这里面可以掌握数据库的长期的性能变化。

今天主要讲解即时观察的方式。

1、获取数据库等待事件

select sid,       event,       p1,       p2,       p3,       WAIT_TIME,       SECONDS_IN_WAITfrom v$session_waitwhere event not like 'SQL%'and event not like 'rdbms%';

解析：

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

2、获取磁盘读最高的五条SQL

SELECT SQL_TEXTFROM (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_TEXTFROM V$SQLAREAORDER 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

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最高的进程

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、获取碎片程度高的表

SELECT segment_name table_name,        COUNT(*) extentsFROM dba_segmentsWHERE owner NOT IN ('SYS', 'SYSTEM')GROUP BY segment_nameHAVING 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        PBWFROM V$FILESTAT F, DBA_DATA_FILES DFWHERE F.FILE# = DF.FILE_IDORDER 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.PHYWRTSFROM V$DATAFILE A, V$FILESTAT BWHERE 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;

11、查询僵死进程

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

12、统计缓冲区命中率

SELECT a.VALUE + b.VALUE logical_reads,       c.VALUE phys_reads,       round(100 * (1 - c.value / (a.value + b.value)), 4) hit_ratioFROM v$sysstat a, v$sysstat b, v$sysstat cWHERE a.NAME = 'db block gets'  AND b.NAME = 'consistent gets'  AND c.NAME = 'physical reads'; 

解析：

如果命中率低于90%则需加大数据库参数db_cache_size。

13、统计共享池命中率

select sum(pinhits) / sum(pins) * 100 from v$librarycache; 

解析：

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

14、检查排序区

select name,       valuefrom v$sysstatwhere name like '%sort%';

解析：

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

15、检查日志缓冲区

select name,       valuefrom v$sysstatwhere name in ('redo entries','redo buffer allocation retries');

解析：

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

喜欢的可以点关注，我将分享更多的Oracle实战项目经验，谢谢。