Oralce數(shù)據(jù)庫健康和性能巡檢監(jiān)控的25個SQL腳本

更新時間：2025年05月17日 10:54:36 作者：Superman超哥

Oracle日常檢查項的SQL腳本：涵蓋表空間狀態(tài)、日志、鎖、緩存命中率、SQL性能、死鎖處理等,確保數(shù)據(jù)庫穩(wěn)定運行,關(guān)鍵指標(biāo)如緩沖區(qū)、數(shù)據(jù)字典命中率需達(dá)標(biāo),及時處理失效索引、僵死進程及擴展異常對象

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 狀態(tài)的表空間

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 的及狀態(tài)不正常

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 緩沖區(qū)命中率

緩沖命中率應(yīng)大于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 數(shù)據(jù)字典命中率

數(shù)據(jù)字典命中率應(yīng)大于 95%。

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

10 庫緩存命中率

庫緩存命中率應(yīng)大于 95%。

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

11 內(nèi)存中的排序

如果存在大量的磁盤排序，則表明檢查目前系統(tǒng)中消耗大量磁盤的 SQL 是否已經(jīng)經(jīng)過調(diào)整。

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

12 磁盤中的排序

檢查使用磁盤排序的會話信息，可以定位執(zhí)行了大量磁盤排序的會話。

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

13 臨時空間使用率

SELECT * FROM V$TEMP_SPACE_HEADER;

14 檢查ORACLE實例狀態(tài)

其中“STATUS”表示ORACLE當(dāng)前的實例狀態(tài)，必須為“OPEN”；“DATABASE_STATUS”表示ORACLE當(dāng)前數(shù)據(jù)庫的狀態(tài)，必須為“ACTIVE”。

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

15 檢查ORACLE表空間的狀態(tài)

輸出結(jié)果中STATUS應(yīng)該都為ONLINE。

SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;

16 檢查ORACLE所有數(shù)據(jù)文件狀態(tài)

輸出結(jié)果中“STATUS”應(yīng)該都為“ONLINE”。

SELECT NAME, STATUS FROM V$DATAFILE;

輸出結(jié)果中“STATUS”應(yīng)該都為“AVAILABLE”。

SELECT FILE_NAME, STATUS FROM DBA_DATA_FILES;

17 檢查所有回滾段狀態(tài)

輸出結(jié)果中所有回滾段的“STATUS”應(yīng)該為“ONLINE”。

SELECT SEGMENT_NAME, STATUS FROM DBA_ROLLBACK_SEGS;

18 檢查一些擴展異常的對象

如果有記錄返回，則這些對象的擴展已經(jīng)快達(dá)到它定義時的最大擴展值，對于這些對象要修改它的存儲結(jié)構(gòu)參數(shù)。

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 失效的觸發(fā)器

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

到此這篇關(guān)于Oralce數(shù)據(jù)庫健康和性能巡檢監(jiān)控的SQL腳本的文章就介紹到這了,更多相關(guān)Oralce數(shù)據(jù)庫監(jiān)控的SQL腳本內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: