Oralce数据库巡检SQL脚本 1 检查表空间使用情况 2 检查是否有 offline 状态的表空间 3 在线日志是否存在小于 50M 的及状态不正常 4 检查锁阻塞 5 查看是否有僵死进程 6 检查是否有失效索引 7 检查不起作用的约束 8 缓冲区命中率 9 数据字典命中率 10 库缓存命中率 11 内存中的排序 12 磁盘中的排序 13 临时空间使用率 14 检查ORACLE实例状态 15 检查ORACLE表空间的状态 16 检查ORACLE所有数据文件状态 17 检查所有回滚段状态 18 检查一些扩展异常的对象 19 DISK READ最高的SQL语句的获取 20 性能最差的前10条SQL 21 检查运行很久的SQL 22 检查碎片程度高的表 23 检查死锁及处理 24 失效的触发器 25 失败的JOB

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_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
;

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
.ARCHIVED


  
FROM V$LOG A
, V$LOGFILE B


 
WHERE A
.GROUP# 
= B
.GROUP#
;

4 检查锁阻塞

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
;

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
.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'
;

7 检查不起作用的约束

SELECT OWNER
, CONSTRAINT_NAME
, 
TABLE_NAME
, CONSTRAINT_TYPE
, 
STATUS


  
FROM DBA_CONSTRAINTS


 
WHERE 
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
.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
;

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 PERCENT


  
FROM SYS
.DBA_SEGMENTS


 
WHERE MAX_EXTENTS 
!= 
0


   
AND 
(EXTENTS / MAX_EXTENTS
) 
* 
100 
>= 
95


 
ORDER 
BY PERCENT
;

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

SELECT SQL_TEXT


  
FROM 
(
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_TEXT


          
FROM V$SQLAREA


         
ORDER 
BY DISK_READS 
DESC
)


 
WHERE ROWNUM 
< 
10
;

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

22 检查碎片程度高的表

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
)
;

23 检查死锁及处理

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
;

24 失效的触发器

SELECT OWNER
, TRIGGER_NAME
, 
TABLE_NAME
, 
STATUS


  
FROM DBA_TRIGGERS


 
WHERE 
STATUS 
= 
'DISABLED'
;

25 失败的JOB

SELECT JOB
, WHAT
, LAST_DATE
, NEXT_DATE
, FAILURES
, BROKEN


  
FROM DBA_JOBS


 
WHERE SCHEMA_USER 
= 
'USER'
;

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

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

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

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