Oralce数据库巡检SQL脚本

Oralce数据库巡检SQL脚本

1 检查表空间使用情况

1. SELECT B.TABLESPACE_NAME          TABLESPACE,
2.        A.EXTENT_MANAGEMENT        EXT_MGT,
3.        A.SEGMENT_SPACE_MANAGEMENT SEG_MGT,
4.        A.STATUS,
5.        A.LOGGING,
6.        B.TOTAL,
7.        B.FREE,
8.        B.USED_PCT
9.   FROM DBA_TABLESPACES A,
10.        (SELECT D.TABLESPACE_NAME TABLESPACE_NAME,
11.                ROUND((D.SUMBYTES / 1024 / 1024 / 1024), 2) || 'GB' TOTAL,
12.                ROUND(DECODE(F.SUMBYTES, NULL, 0, F.SUMBYTES) / 1024 / 1024 / 1024,
13.                      2) || 'GB' FREE,
14.                ROUND((D.SUMBYTES - DECODE(F.SUMBYTES, NULL, 0, F.SUMBYTES)) * 100 /
15.                      D.SUMBYTES,
16.                      2) || '%' USED_PCT
17.           FROM (SELECT TABLESPACE_NAME, SUM(BYTES) SUMBYTES
18.                   FROM DBA_FREE_SPACE
19.                  GROUP BY TABLESPACE_NAME) F,
20.                (SELECT TABLESPACE_NAME,     
21.                        SUM(BYTES) SUMBYTES
22.                   FROM DBA_DATA_FILES
23.                  GROUP BY TABLESPACE_NAME) D
24.          WHERE F.TABLESPACE_NAME(+) = D.TABLESPACE_NAME
25.          ORDER BY D.TABLESPACE_NAME) B
26. WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME;

复制代码

2 检查是否有 offline 状态的表空间

1. SELECT FILE_ID AS ID,
2.        RELATIVE_FNO "FNO",
3.        ROUND(BYTES / 1024 / 1024) AS MBYTES,
4.        ROUND(MAXBYTES / 1024 / 1024) MAXMBYTES,
5.        BLOCKS,
6.        MAXBLOCKS,
7.        AUTOEXTENSIBLE "AUTO",
8.        INCREMENT_BY "INC",
9.        ROUND(USER_BYTES / 1024 / 1024) "NOW_MB",
10.        USER_BLOCKS,
11.        STATUS,
12.        ONLINE_STATUS "ONLINE_S"
13.   FROM DBA_DATA_FILES;

复制代码

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

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

复制代码

4 检查锁壅闭

1. SELECT DECODE(REQUEST, 0, '阻塞者:', '等待者:') || SID SID,
2.        ID1,
3.        ID2,
4.        LMODE,
5.        REQUEST,
6.        TYPE
7.   FROM V$LOCK
8. WHERE (ID1, ID2, TYPE) IN
9.        (SELECT ID1, ID2, TYPE FROM V$LOCK WHERE REQUEST > 0)
10. ORDER BY ID1, REQUEST;

复制代码

5 查看是否有僵死历程

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

复制代码

6 检查是否有失效索引

1. SELECT OWNER, A.INDEX_NAME, A.INDEX_TYPE, A.STATUS
2.   FROM DBA_INDEXES A
3. WHERE STATUS = 'UNUSABLE';
5. SELECT A.INDEX_NAME, A.PARTITION_NAME, A.TABLESPACE_NAME, A.STATUS
6.   FROM DBA_IND_PARTITIONS A
7. WHERE STATUS = 'UNUSABLE';

复制代码

7 检查不起作用的约束

1. SELECT OWNER, CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE, STATUS
2.   FROM DBA_CONSTRAINTS
3. WHERE STATUS = 'DISABLE'
4.    AND CONSTRAINT_TYPE = 'P';

复制代码

8 缓冲区命中率

缓冲命中率应大于90%。

1. SELECT (1 - (SUM(DECODE(NAME, 'PHYSICAL READS', VALUE, 0)) /
2.        (SUM(DECODE(NAME, 'DB BLOCK GETS', VALUE, 0)) +
3.        SUM(DECODE(NAME, 'CONSISTENT GETS', VALUE, 0))))) * 100 "HIT RATIO"
4.   FROM V$SYSSTAT;

复制代码

9 数据字典命中率

数据字典命中率应大于 95%。

1. SELECT (1 - (SUM(GETMISSES) / SUM(GETS))) * 100 "HIT RATIO" FROM V$ROWCACHE;

复制代码

10 库缓存命中率

库缓存命中率应大于 95%。

1. SELECT SUM(PINS) / (SUM(PINS) + SUM(RELOADS)) * 100 "HIT RATIO" FROM V$LIBRARYCACHE;

复制代码

11 内存中的排序

假如存在大量的磁盘排序，则表明检查目前体系中消耗大量磁盘的 SQL 是否已经经过调解。

1. SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE '%SORTS%';

复制代码

12 磁盘中的排序

检查使用磁盘排序的会话信息，可以定位实行了大量磁盘排序的会话。

1. SELECT B.NAME, A.SID, A.VALUE
2.   FROM V$SESSTAT A, V$STATNAME B
3. WHERE A.STATISTIC# = B.STATISTIC#
4.    AND B.NAME = 'SORTS (DISK)'
5.    AND A.VALUE > 0
6.    AND ROWNUM < 10
7. ORDER BY A.VALUE DESC;

复制代码

13 临时空间使用率

1. SELECT * FROM V$TEMP_SPACE_HEADER;

复制代码

14 检查ORACLE实例状态

其中“STATUS”表现ORACLE当前的实例状态，必须为“OPEN”；“DATABASE_STATUS”表现ORACLE当前数据库的状态，必须为“ACTIVE”。

1. SELECT INSTANCE_NAME, HOST_NAME, STARTUP_TIME, STATUS, DATABASE_STATUS FROM V$INSTANCE;

复制代码

15 检查ORACLE表空间的状态

输出结果中STATUS应该都为ONLINE。

1. SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;

复制代码

16 检查ORACLE所有数据文件状态

输出结果中“STATUS”应该都为“ONLINE”。

1. SELECT NAME, STATUS FROM V$DATAFILE;

复制代码

输出结果中“STATUS”应该都为“AVAILABLE”。

1. SELECT FILE_NAME, STATUS FROM DBA_DATA_FILES;

复制代码

17 检查所有回滚段状态

输出结果中所有回滚段的“STATUS”应该为“ONLINE”。

1. SELECT SEGMENT_NAME, STATUS FROM DBA_ROLLBACK_SEGS;

复制代码

18 检查一些扩展非常的对象

假如有纪录返回，则这些对象的扩展已经快达到它界说时的最大扩展值，对于这些对象要修改它的存储布局参数。

1. SELECT SEGMENT_NAME,
2.        SEGMENT_TYPE,
3.        TABLESPACE_NAME,
4.        (EXTENTS / MAX_EXTENTS) * 100 PERCENT
5.   FROM SYS.DBA_SEGMENTS
6. WHERE MAX_EXTENTS != 0
7.    AND (EXTENTS / MAX_EXTENTS) * 100 >= 95
8. ORDER BY PERCENT;

复制代码

19 DISK READ最高的SQL语句的获取

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

复制代码

20 性能最差的前10条SQL

1. SELECT *
2.   FROM (SELECT PARSING_USER_ID EXECUTIONS,
3.                SORTS,
4.                COMMAND_TYPE,
5.                DISK_READS,
6.                SQL_TEXT
7.           FROM V$SQLAREA
8.          ORDER BY DISK_READS DESC)
9. WHERE ROWNUM < 10;

复制代码

21 检查运行好久的SQL

1. SELECT USERNAME,
2.        SID,
3.        OPNAME,
4.        ROUND(SOFAR * 100 / TOTALWORK, 0) || '%' AS PROGRESS,
5.        TIME_REMAINING,
6.        SQL_TEXT
7.   FROM V$SESSION_LONGOPS, V$SQL
8. WHERE TIME_REMAINING <> 0
9.    AND SQL_ADDRESS = ADDRESS
10.    AND SQL_HASH_VALUE = HASH_VALUE;

复制代码

22 检查碎片水平高的表

1. SELECT SEGMENT_NAME TABLE_NAME, COUNT(*) EXTENTS
2.   FROM DBA_SEGMENTS
3. WHERE OWNER NOT IN ('SYS', 'SYSTEM')
4. GROUP BY SEGMENT_NAME
5. HAVING COUNT(*) = (SELECT MAX(COUNT(*))
6.                      FROM DBA_SEGMENTS
7.                     GROUP BY SEGMENT_NAME);

复制代码

23 检查死锁及处置处罚

1. SELECT SID,
2.        SERIAL#,
3.        USERNAME,
4.        SCHEMANAME,
5.        OSUSER,
6.        MACHINE,
7.        TERMINAL,
8.        PROGRAM,
9.        OWNER,
10.        OBJECT_NAME,
11.        OBJECT_TYPE,
12.        O.OBJECT_ID
13.   FROM DBA_OBJECTS O, V$LOCKED_OBJECT L, V$SESSION S
14. WHERE O.OBJECT_ID = L.OBJECT_ID
15.    AND S.SID = L.SESSION_ID;

复制代码

24 失效的触发器

1. SELECT OWNER, TRIGGER_NAME, TABLE_NAME, STATUS
2.   FROM DBA_TRIGGERS
3. WHERE STATUS = 'DISABLED';

复制代码

25 失败的JOB

1. SELECT JOB, WHAT, LAST_DATE, NEXT_DATE, FAILURES, BROKEN
2.   FROM DBA_JOBS
3. WHERE SCHEMA_USER = 'USER';

复制代码

原文链接：https://mp.weixin.qq.com/s?__biz=MzkxNzI1OTE3Mw==&mid=2247493675&idx=1&sn=2d8caece9f669925cda2f364ebfdb9c3&chksm=c141fee1f63677f7bb22b74db64d0849830e7b11adce56472bc33afab5b022c223666c624fbf#rd