目录

• - A.检查表空间使用情况
• - B.检查是否有 offline 状态的表空间
• - C.在线日志是否存在小于 50M 的及状态不正常
• - D.检查锁阻塞
• - E.查看是否有僵死进程
• - F.检查是否有失效索引
• - G.检查不起作用的约束
• - H.缓冲区命中率
• - I.数据字典命中率
• - J.库缓存命中率
• - K.内存中的排序
• - L.磁盘中的排序
• - M.临时空间使用率
• - N.检查ORACLE实例状态
• - O.检查ORACLE表空间的状态
• - P.检查ORACLE所有数据文件状态
• - R.检查所有回滚段状态
• - S.检查一些扩展异常的对象
• - T.DISK READ最高的SQL语句的获取
• - U.性能最差的前10条SQL
• - V.检查运行很久的SQL
• - W.检查碎片程度高的表
• - X.检查死锁及处理
• - Y.失效的触发器
• - Z.失败的JOB

- A.检查表空间使用情况

SELECT B.TABLESPACE_NAME TABLESPACE, A.EXTENT_MANAGEMENT EXT_MGT, A.SEGMENT_SPACE_MANAGEMENT SEG_MGT, A.STATUS, A.LOGGING, B.TOTAL, B.FREE, B.USED_PCT FROM DBA_TABLESPACES A, (SELECT D.TABLESPACE_NAME TABLESPACE_NAME, ROUND((D.SUMBYTES / 1024 / 1024 / 1024), 2) || ‘GB‘ TOTAL, ROUND(DECODE(F.SUMBYTES, NULL, 0, F.SUMBYTES) / 1024 / 1024 / 1024, 2) || ‘GB‘ FREE, ROUND((D.SUMBYTES - DECODE(F.SUMBYTES, NULL, 0, F.SUMBYTES)) * 100 / D.SUMBYTES, 2) || ‘%‘ USED_PCT FROM (SELECT TABLESPACE_NAME, SUM(BYTES) SUMBYTES FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT TABLESPACE_NAME, SUM(BYTES) SUMBYTES FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D WHERE F.TABLESPACE_NAME(+) = D.TABLESPACE_NAME ORDER BY D.TABLESPACE_NAME) B WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME;

- B.检查是否有 offline 状态的表空间

SELECT FILE_ID AS ID, RELATIVE_FNO "FNO", ROUND(BYTES / 1024 / 1024) AS MBYTES, ROUND(MAXBYTES / 1024 / 1024) MAXMBYTES, BLOCKS, MAXBLOCKS, AUTOEXTENSIBLE "AUTO", INCREMENT_BY "INC", ROUND(USER_BYTES / 1024 / 1024) "NOW_MB", USER_BLOCKS, STATUS, ONLINE_STATUS "ONLINE_S" FROM DBA_DATA_FILES;

- C.在线日志是否存在小于 50M 的及状态不正常

SELECT A.GROUP#, A.STATUS, A.BYTES, B.MEMBER, A.ARCHIVED FROM V$LOG A, V$LOGFILE B WHERE A.GROUP# = B.GROUP#;

- D.检查锁阻塞

SELECT DECODE(REQUEST, 0, ‘阻塞者:‘, ‘等待者:‘) || SID SID, ID1, ID2, LMODE, REQUEST, TYPE FROM V$LOCK WHERE (ID1, ID2, TYPE) IN (SELECT ID1, ID2, TYPE FROM V$LOCK WHERE REQUEST > 0) ORDER BY ID1, REQUEST;

- E.查看是否有僵死进程

SELECT SPID FROM V$PROCESS WHERE ADDR NOT IN (SELECT PADDR FROM V$SESSION);

- F.检查是否有失效索引

SELECT OWNER, A.INDEX_NAME, A.INDEX_TYPE, A.STATUS FROM DBA_INDEXES A WHERE STATUS = ‘UNUSABLE‘;SELECT A.INDEX_NAME, A.PARTITION_NAME, A.TABLESPACE_NAME, A.STATUS FROM DBA_IND_PARTITIONS A WHERE STATUS = ‘UNUSABLE‘;

- G.检查不起作用的约束

SELECT OWNER, CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE, STATUS FROM DBA_CONSTRAINTS WHERE STATUS = ‘DISABLE‘ AND CONSTRAINT_TYPE = ‘P‘;

- H.缓冲区命中率

SELECT (1 - (SUM(DECODE(NAME, ‘PHYSICAL READS‘, VALUE, 0)) / (SUM(DECODE(NAME, ‘DB BLOCK GETS‘, VALUE, 0)) + SUM(DECODE(NAME, ‘CONSISTENT GETS‘, VALUE, 0))))) * 100 "HIT RATIO" FROM V$SYSSTAT; --应大于90%

- I.数据字典命中率

SELECT (1 - (SUM(GETMISSES) / SUM(GETS))) * 100 "HIT RATIO" FROM V$ROWCACHE;--此命中率应大于 95%

- J.库缓存命中率

SELECT SUM(PINS) / (SUM(PINS) + SUM(RELOADS)) * 100 "HIT RATIO" FROM V$LIBRARYCACHE;--此命中率应大于 95%

- K.内存中的排序

SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE ‘%SORTS%‘;--如果存在大量的磁盘排序，则表明检查目前系统中消耗大量磁盘的 SQL 是否已经经过调整。

- L.磁盘中的排序

SELECT B.NAME, A.SID, A.VALUE FROM V$SESSTAT A, V$STATNAME B WHERE A.STATISTIC# = B.STATISTIC# AND B.NAME = ‘SORTS (DISK)‘ AND A.VALUE > 0 AND ROWNUM < 10 ORDER BY A.VALUE DESC; --检查使用磁盘排序的会话信息，可以定位执行了大量磁盘排序的会话

- M.临时空间使用率

SELECT * FROM V$TEMP_SPACE_HEADER;

- N.检查ORACLE实例状态

SELECT INSTANCE_NAME, HOST_NAME, STARTUP_TIME, STATUS, DATABASE_STATUS FROM V$INSTANCE;--其中“STATUS”表示ORACLE当前的实例状态，必须为“OPEN”；“DATABASE_STATUS”表示ORACLE当前数据库的状态，必须为“ACTIVE”

- O.检查ORACLE表空间的状态

SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;--输出结果中STATUS应该都为ONLINE

- P.检查ORACLE所有数据文件状态

SELECT NAME, STATUS FROM V$DATAFILE;--输出结果中“STATUS”应该都为“ONLINE”SELECT FILE_NAME, STATUS FROM DBA_DATA_FILES;--输出结果中“STATUS”应该都为“AVAILABLE”。

- R.检查所有回滚段状态

SELECT SEGMENT_NAME, STATUS FROM DBA_ROLLBACK_SEGS;--输出结果中所有回滚段的“STATUS”应该为“ONLINE”。

- S.检查一些扩展异常的对象

SELECT SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, (EXTENTS / MAX_EXTENTS) * 100 PERCENT FROM SYS.DBA_SEGMENTS WHERE MAX_EXTENTS != 0 AND (EXTENTS / MAX_EXTENTS) * 100 >= 95 ORDER BY PERCENT; --如果有记录返回，则这些对象的扩展已经快达到它定义时的最大扩展值，对于这些对象要修改它的存储结构参数

- T.DISK READ最高的SQL语句的获取

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

- U.性能最差的前10条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;

- V.检查运行很久的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;

- W.检查碎片程度高的表

SELECT SEGMENT_NAME TABLE_NAME, COUNT(*) EXTENTS FROM DBA_SEGMENTS WHERE OWNER NOT IN (‘SYS‘, ‘SYSTEM‘) GROUP BY SEGMENT_NAMEHAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM DBA_SEGMENTS GROUP BY SEGMENT_NAME);

- X.检查死锁及处理

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;

- Y.失效的触发器

SELECT OWNER, TRIGGER_NAME, TABLE_NAME, STATUS FROM DBA_TRIGGERS WHERE STATUS = ‘DISABLED‘;

- Z.失败的JOB

SELECT JOB, WHAT, LAST_DATE, NEXT_DATE, FAILURES, BROKEN FROM DBA_JOBS WHERE SCHEMA_USER = ‘FCR51HOST‘;