Oracle数据库优化及巡检(运维常用)

小李来了！

490人浏览 · 2026-03-14 17:19:43

小李来了！ · 2026-03-14 17:19:43 发布

Oracle 数据库的优化和巡检是运维工作的核心，直接关系到数据库的稳定性、性能和安全性。下面我会从日常巡检和性能优化两个维度，提供运维常用的实操方案和脚本。我使用的是Oracle19C。

一、Oracle 日常巡检

1.数据库基本状态巡检

-- 1. 数据库实例状态 SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS, STARTUP_TIME FROM V$INSTANCE;

-- 2. 数据库版本及补丁 SELECT BANNER FROM V$VERSION;

-- 3. 归档日志模式（关键：生产库建议开启） SELECT NAME, LOG_MODE, OPEN_MODE FROM V$DATABASE;

-- 4. 控制文件、日志文件状态 SELECT STATUS, NAME FROM V$CONTROLFILE; SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE;

注释：①.STATUS 应为 OPEN（实例）、READ WRITE（数据库），否则说明实例异常；

②.归档模式（LOG_MODE）生产库必须为 ARCHIVELOG，否则无法恢复到指定时间点；

③.控制文件 / 日志文件状态无 INVALID，否则文件损坏。

2.表空间巡检（重要）

2.1数据文件检查，查看表空间总容量和使用率，注意看USED_G，如果已使用空间接近文件总大小时就需要我们增加表空间了，否则数据库会报ORA-01654

SELECT 
    b.file_name,
    b.tablespace_name,
    ROUND(b.bytes/1024/1024/1024, 2) AS 文件总大小G,
    b.autoextensible,
    ROUND((b.bytes - SUM(NVL(a.bytes, 0))) / 1024 / 1024/1024, 2) AS used_G,--已使用空间 
    ROUND((b.bytes - SUM(NVL(a.bytes, 0))) / b.bytes * 100, 2) AS used_pct  --使用率 (%)
FROM dba_free_space a, dba_data_files b
WHERE a.file_id(+) = b.file_id 
GROUP BY 
    b.file_name,
    b.tablespace_name,
    b.bytes,
    b.autoextensible
ORDER BY used_G DESC;

2.2为某个表空间增加数据文件

ALTER TABLESPACE WANXU 
ADD DATAFILE '/opt/oracle/oradata/MES_PRI/meshims04.dbf' 
SIZE 10G                -- 初始大小
AUTOEXTEND ON 
NEXT 1G                 -- 每次扩展1 GB
MAXSIZE UNLIMITED;

2.3TEMP表空间检查

SELECT * FROM (
SELECT D.TABLESPACE_NAME,SPACE ||'M' "SUM_SPACE(M)",
BLOCKS "SUM_BLOCKS",
SPACE-NVL(FREE_SPACE, 0)||'M' "USED_SPACE(M)",
ROUND ( (1-NVL(FREE_SPACE, 0)/SPACE) *100,2)||'%'
"USED_RATE(%)",
FREE_SPACE||'M'"FREE_SPACE(M)" FROM (SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES)/(1024 * 1024), 2) SPACE,SUM (BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM(BYTES)/(1024*1024),2) FREE_SPACE FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) UNION ALL SELECT D.TABLESPACE_NAME,SPACE || 'M' "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE || 'M' "USED_SPACE(M)",
ROUND (NVL(USED_SPACE, 0)/SPACE *100,2)||'%' "USED_RATE(%)", NVL(FREE_SPACE,0)||'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES)/(1024*1024),2) SPACE,SUM (BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES_USED)/(1024 *1024),2) USED_SPACE,ROUND (SUM (BYTES_FREE)/(1024 * 1024),2) FREE_SPACE FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) ORDER BY 1)WHERE TABLESPACE_NAME IN ('SYSAUX','SYSTEM','UNDOTBS1','TEMP') ;

这里拿我的数据举列：首先针对SYSAUX和SYSTEM这两个表空间，你们可能会局的我的利用率很高，而且剩余空间也很少，是否有影响？我这里是没影响的，因为我这里设置的这两个空间会自动扩容，最大到32G,这里总共才3个G多所以不用担心，可以使用下面的sql查看你们对应的SYSAUX和SYSTEM表空间是否设置了自动扩容及最大空间。

SELECT
    tablespace_name,
    file_name,
    ROUND(bytes / 1024 / 1024 / 1024, 2) AS current_size_gb,      -- 当前文件大小 (GB)
    autoextensible,                                                -- 是否自动扩展 (YES/NO)
    ROUND(increment_by * 8192 / 1024 / 1024, 2) AS next_extent_mb, -- 每次自动扩展的大小 (MB)
    ROUND(maxbytes / 1024 / 1024 / 1024, 2) AS max_size_gb         -- 最大可扩展到的上限 (GB)
FROM
    dba_data_files
ORDER BY
    tablespace_name, file_name;

同时我们可以看到TEMP使用率是100%，剩余0M,这里在网上找到了一个很好的例子给大家做解释

同样我们可以使用下面sql进行查询，确认是否需要增加TEMP空间

--查看正在使用TEMP的进程
SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.sql_id,
    ROUND(ss.blocks * 8 / 1024, 2) AS temp_mb
FROM v$session s, v$sort_usage ss
WHERE s.saddr = ss.session_addr
ORDER BY temp_mb DESC;
---这个查询出来的结果如果经常接近TEMP总大小就代表需要扩容TEMP了
SELECT 
    MAX(blocks * 8 / 1024 / 1024) AS max_used_gb
FROM v$sort_usage;
---查询TEMP空间是否会自增
SELECT 
    tablespace_name,
    file_name,
    ROUND(bytes / 1024 / 1024 / 1024, 2) AS current_size_gb,
    autoextensible,
    ROUND(increment_by * 8192 / 1024 / 1024, 2) AS next_extent_mb,
    ROUND(maxbytes / 1024 / 1024 / 1024, 2) AS max_size_gb
FROM dba_temp_files
ORDER BY tablespace_name, file_name;
--增加TEMP文件
ALTER TABLESPACE temp
ADD TEMPFILE '/opt/oracle/oradata/MESHMES_PRI/temp02.dbf'
SIZE 2G AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;
 ---增加SYSAUX每次增加的大小 
    ALTER DATABASE TEMPFILE '/opt/oracle/oradata/MESHMES_PRI/temp01.dbf'
AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED;

3. 会话与锁等待巡检

锁等待超过 30 秒需介入，可 kill 阻塞会话（ALTER SYSTEM KILL SESSION 'SID,SERIAL#'）；
长时间运行的 SQL 需分析是否存在执行计划异常、索引缺失

-- 1. 活跃会话数（对比服务器CPU核心数，过高则性能压力大）
SELECT COUNT(*) AS ACTIVE_SESSIONS 
FROM V$SESSION 
WHERE STATUS = 'ACTIVE' AND TYPE = 'USER';

-- 2. 锁等待（阻塞会话，运维重点关注）
SELECT 
    L.SID,
    L.SERIAL#,
    L.USERNAME,
    L.OSUSER,
    L.MACHINE,
    L.PROGRAM,
    L.SQL_ID,
    BLOCKING_SESSION AS BLOCK_SID,
    L.LOCKWAIT,
    L.SECONDS_IN_WAIT
FROM V$SESSION L
WHERE L.BLOCKING_SESSION IS NOT NULL
OR L.LOCKWAIT IS NOT NULL
ORDER BY L.SECONDS_IN_WAIT DESC;

-- 3. 长时间运行的SQL（执行时间超过30分钟）
SELECT 
    S.SQL_ID,
    S.ELAPSED_TIME/1000000 AS ELAPSED_SEC,
    S.EXECUTIONS,
    S.SQL_TEXT
FROM V$SQLAREA S
WHERE S.ELAPSED_TIME/1000000 > 1800
ORDER BY S.ELAPSED_TIME DESC;

4.重做日志与备份巡检

重做日志 1 小时切换超过 4 次，需增大日志文件大小（避免频繁切换消耗 IO）；
备份状态需为COMPLETED，若出现FAILED需立即排查备份失败原因。

-- 1. 重做日志切换频率（正常每15-30分钟切换一次，过频繁则日志文件过小）
SELECT 
    TO_CHAR(FIRST_TIME, 'YYYY-MM-DD HH24') AS HOUR,
    COUNT(*) AS SWITCH_COUNT
FROM V$LOG_HISTORY
WHERE FIRST_TIME > SYSDATE - 1
GROUP BY TO_CHAR(FIRST_TIME, 'YYYY-MM-DD HH24')
ORDER BY HOUR;

-- 2. RMAN备份状态（近7天）
SELECT 
    BS.START_TIME,
    BS.END_TIME,
    BS.STATUS,
    BS.BACKUP_TYPE,
    BS.BYTES/1024/1024/1024 AS BACKUP_SIZE_GB
FROM V$RMAN_BACKUP_JOB_DETAILS BS
WHERE BS.START_TIME > SYSDATE - 7
ORDER BY BS.START_TIME DESC;

二、Oracle性能优化

1.索引优化

查找无效 / 未使用的索引（清理冗余索引）

--- 从未被使用过的索引
SELECT 
    I.OWNER,
    I.TABLE_NAME,
    I.INDEX_NAME,
    I.INDEX_TYPE,
    I.LAST_ANALYZED
FROM DBA_INDEXES I
WHERE I.OWNER = 'MESHIMS'  -- 替换你的业务用户
AND I.LAST_ANALYZED < SYSDATE - 30
ORDER BY I.LAST_ANALYZED;

缺失索引查找

---查找缺失索引（基于全表扫描）
SELECT 
    P.SQL_ID,
    S.SQL_TEXT,
    P.OBJECT_OWNER,
    P.OBJECT_NAME,
    P.OPTIONS,
    P.COST
FROM V$SQL_PLAN P, V$SQL S
WHERE P.SQL_ID = S.SQL_ID
AND P.OPERATION = 'TABLE ACCESS'
AND P.OPTIONS = 'FULL'
AND P.OBJECT_OWNER = 'MESHIMS'  -- 替换你的业务用户
AND P.COST > 1000
ORDER BY P.COST DESC;
--需要加索引的 SQL
SELECT 
    SQL_ID,
    CHILD_NUMBER,
    PLAN_HASH_VALUE,
    SUBSTR(SQL_TEXT, 1, 200) AS SQL_TEXT,
    EXECUTIONS,
    DISK_READS,
    BUFFER_GETS,
    ROWS_PROCESSED,
    ELAPSED_TIME / 1000000 AS ELAPSED_SEC
FROM V$SQL
WHERE PARSING_SCHEMA_NAME = 'MESHIMS'
AND EXECUTIONS > 10
AND DISK_READS > 10000
ORDER BY DISK_READS DESC;

2.内存参数优化（SGA/PGA）

Oracle 的内存参数（SGA、PGA）是性能核心，需根据服务器内存调整：

-- 查看当前内存配置
SELECT 
    NAME,
    VALUE/1024/1024 AS VALUE_MB,
    ISDEFAULT
FROM V$PARAMETER
WHERE NAME IN ('sga_target', 'pga_aggregate_target', 'memory_target');

-- 调整内存参数（示例：SGA设为16G，PGA设为8G）
ALTER SYSTEM SET SGA_TARGET = 16G SCOPE=SPFILE;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 8G SCOPE=SPFILE;
-- 重启实例生效
SHUTDOWN IMMEDIATE;
STARTUP;

优化原则：