Oralce数据库巡检SQL脚本

Oralce数据库巡检SQL脚本

1 检查表空间使用情况

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_PCTFROM 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_PCTFROM (SELECT TABLESPACE_NAME, SUM(BYTES) SUMBYTESFROM DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) F,(SELECT TABLESPACE_NAME,     SUM(BYTES) SUMBYTESFROM DBA_DATA_FILESGROUP BY TABLESPACE_NAME) DWHERE F.TABLESPACE_NAME(+) = D.TABLESPACE_NAMEORDER BY D.TABLESPACE_NAME) BWHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME;

2 检查是否有 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;

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

SELECT A.GROUP#, A.STATUS, A.BYTES, B.MEMBER, A.ARCHIVEDFROM V$LOG A, V$LOGFILE BWHERE A.GROUP# = B.GROUP#;

4 检查锁阻塞

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

5 查看是否有僵死进程

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

6 检查是否有失效索引

SELECT OWNER, A.INDEX_NAME, A.INDEX_TYPE, A.STATUSFROM DBA_INDEXES AWHERE STATUS = 'UNUSABLE';SELECT A.INDEX_NAME, A.PARTITION_NAME, A.TABLESPACE_NAME, A.STATUSFROM DBA_IND_PARTITIONS AWHERE STATUS = 'UNUSABLE';

7 检查不起作用的约束

SELECT OWNER, CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE, STATUSFROM DBA_CONSTRAINTSWHERE STATUS = 'DISABLE'AND CONSTRAINT_TYPE = 'P';

8 缓冲区命中率

缓冲命中率应大于90%。

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;

9 数据字典命中率

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

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

10 库缓存命中率

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

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

11 内存中的排序

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

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

12 磁盘中的排序

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

SELECT B.NAME, A.SID, A.VALUEFROM V$SESSTAT A, V$STATNAME BWHERE A.STATISTIC# = B.STATISTIC#AND B.NAME = 'SORTS (DISK)'AND A.VALUE > 0AND ROWNUM < 10ORDER BY A.VALUE DESC;

13 临时空间使用率

SELECT * FROM V$TEMP_SPACE_HEADER;

14 检查ORACLE实例状态

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

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

15 检查ORACLE表空间的状态

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

SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;

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

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

SELECT NAME, STATUS FROM V$DATAFILE;

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

SELECT FILE_NAME, STATUS FROM DBA_DATA_FILES;

17 检查所有回滚段状态

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

SELECT SEGMENT_NAME, STATUS FROM DBA_ROLLBACK_SEGS;

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

如果有记录返回，则这些对象的扩展已经快达到它定义时的最大扩展值，对于这些对象要修改它的存储结构参数。

SELECT SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,(EXTENTS / MAX_EXTENTS) * 100 PERCENTFROM SYS.DBA_SEGMENTSWHERE MAX_EXTENTS != 0AND (EXTENTS / MAX_EXTENTS) * 100 >= 95ORDER BY PERCENT;

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

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

20 性能最差的前10条SQL

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

21 检查运行很久的SQL

SELECT USERNAME,SID,OPNAME,ROUND(SOFAR * 100 / TOTALWORK, 0) || '%' AS PROGRESS,TIME_REMAINING,SQL_TEXTFROM V$SESSION_LONGOPS, V$SQLWHERE TIME_REMAINING <> 0AND SQL_ADDRESS = ADDRESSAND SQL_HASH_VALUE = HASH_VALUE;

22 检查碎片程度高的表

SELECT SEGMENT_NAME TABLE_NAME, COUNT(*) EXTENTSFROM DBA_SEGMENTSWHERE OWNER NOT IN ('SYS', 'SYSTEM')GROUP BY SEGMENT_NAME
HAVING COUNT(*) = (SELECT MAX(COUNT(*))FROM DBA_SEGMENTSGROUP BY SEGMENT_NAME);

23 检查死锁及处理

SELECT SID,SERIAL#,USERNAME,SCHEMANAME,OSUSER,MACHINE,TERMINAL,PROGRAM,OWNER,OBJECT_NAME,OBJECT_TYPE,O.OBJECT_IDFROM DBA_OBJECTS O, V$LOCKED_OBJECT L, V$SESSION SWHERE O.OBJECT_ID = L.OBJECT_IDAND S.SID = L.SESSION_ID;

24 失效的触发器

SELECT OWNER, TRIGGER_NAME, TABLE_NAME, STATUSFROM DBA_TRIGGERSWHERE STATUS = 'DISABLED';

25 失败的JOB

SELECT JOB, WHAT, LAST_DATE, NEXT_DATE, FAILURES, BROKENFROM DBA_JOBSWHERE SCHEMA_USER = 'USER';

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

👍 点赞，你的认可是我创作的动力！

⭐️ 收藏，你的青睐是我努力的方向！

✏️ 评论，你的意见是我进步的财富！