25 SQL scripts for Oralce database health and performance inspection monitoring

Oracle's daily check items SQL scripts: cover tablespace status, logs, locks, cache hit rate, SQL performance, deadlock processing, etc., to ensure the stable operation of the database. Key indicators such as buffers and data dictionary hit rate must meet the standards, and timely handle failed indexes, dead processes and extended exception objects.

1 Check table space usage

SELECT B.TABLESPACE_NAME          TABLESPACE,
       A.EXTENT_MANAGEMENT        EXT_MGT,
       A.SEGMENT_SPACE_MANAGEMENT SEG_MGT,
       ,
       ,
       ,
       ,
       B.USED_PCT
  FROM DBA_TABLESPACES A,
       (SELECT D.TABLESPACE_NAME TABLESPACE_NAME,
               ROUND(( / 1024 / 1024 / 1024), 2) || 'GB' TOTAL,
               ROUND(DECODE(, NULL, 0, ) / 1024 / 1024 / 1024,
                     2) || 'GB' FREE,
               ROUND(( - DECODE(, NULL, 0, )) * 100 /
                     ,
                     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 Check whether there is an offline tablespace

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 Whether the online log is less than 50M and the status is abnormal

SELECT #, , , , 
  FROM V$LOG A, V$LOGFILE B
 WHERE # = #;

4 Check for lock blockage

SELECT DECODE(REQUEST, 0, 'Blocker:', 'Waiting:') || SID SID,
       ID1,
       ID2,
       LMODE,
       REQUEST,
       TYPE
  FROM V$LOCK
 WHERE (ID1, ID2, TYPE) IN
       (SELECT ID1, ID2, TYPE FROM V$LOCK WHERE REQUEST &gt; 0)
 ORDER BY ID1, REQUEST;

5 Check if there is a dead process

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

6 Check whether there is a failed index

SELECT OWNER, A.INDEX_NAME, A.INDEX_TYPE, 
  FROM DBA_INDEXES A
 WHERE STATUS = 'UNUSABLE';

SELECT A.INDEX_NAME, A.PARTITION_NAME, A.TABLESPACE_NAME, 
  FROM DBA_IND_PARTITIONS A
 WHERE STATUS = 'UNUSABLE';

7 Check for constraints that do not work

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

8 Buffer hit rate

The buffer hit rate should be greater than 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 Data Dictionary Hit Rate

The data dictionary hit rate should be greater than 95%.

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

10 library cache hit rate

The library cache hit rate should be greater than 95%.

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

11 Sorting in memory

If there is a large amount of disk sorting, it means that checks whether SQL that consumes a large amount of disks in the current system has been adjusted.

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

12 Sort on disk

Check the session information using disk sorting, and you can locate sessions that perform a large number of disk sorting.

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

13 Temporary space usage rate

SELECT * FROM V$TEMP_SPACE_HEADER;

14 Check the status of the ORACLE instance

Where "STATUS" indicates the current instance status of ORACLE and must be "OPEN"; "DATABASE_STATUS" indicates the current status of ORACLE database and must be "ACTIVE".

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

15 Check the status of the ORACLE tablespace

STATUS should be ONLINE in the output result.

SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;

16 Check the status of all data files of ORACLE

The output result should be "ONLINE".

SELECT NAME, STATUS FROM V$DATAFILE;

"STATUS" in the output result should be "AVAILABLE".

SELECT FILE_NAME, STATUS FROM DBA_DATA_FILES;

17 Check all rollback segment status

The "STATUS" of all rollback segments in the output result should be "ONLINE".

SELECT SEGMENT_NAME, STATUS FROM DBA_ROLLBACK_SEGS;

18 Check some objects that extend exceptions

If a record is returned, the extension of these objects is almost at the maximum extension value it defined, and the storage structure parameters of these objects need to be modified.

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's highest SQL statement acquisition

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

20 The top 10 worst performance SQLs

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 Check SQL that has been running for a long time

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 Check the table with high degree of fragmentation

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 Check deadlocks and handle them

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  = L.SESSION_ID;

24 Failed triggers

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

25 Failed JOB

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

This is the article about SQL scripts for Oralce database health and performance inspection and monitoring. For more information about SQL scripts for Oralce database monitoring, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!