尧图网站建设 
1. 先从 DBA 的视角理解Oracle 优化这件事做了这么多年 Oracle 运维我最大的感受是一谈到数据库优化很多人脑袋里蹦出来的第一个词就是加内存、扩磁盘、换 SSD。真到了生产环境你会发现硬件层面的调整往往是一锤子买卖动静大、成本高而且大概率解决不了根本问题。真正每天都在发生、每天都在拖垮系统性能的是 SQL 写得不对、索引没建好、统计信息过期这类看起来不起眼的小事。先看一个我印象很深的例子。有一套业务系统每个月月底跑批要 40 分钟业务方催了无数次DBA 查了内存、查了 IO、查了 RAC 心跳全都没问题。后来我接手之后花了一个下午抓 SQL发现跑批过程中有个 UPDATE 语句走了全表扫描表 600 万行单条更新要扫全表再加上循环调用40 分钟就这么耗出来的。改完索引之后跑批直接降到 9 分钟。全程没有动过任何硬件配置——这就是 Oracle 优化最典型的日常。所以这里先把一个理念掰扯清楚数据库优化是有优先级的顺序大概是这么个逻辑SQL 层优先坏 SQL 是所有性能问题的第一来源。同样的数据量一条 SQL 写得好不好性能差距可以到几十倍甚至上百倍。物理结构层索引是否存在、是否失效、分区是否合理、表是否有严重碎片。这层通常是在 SQL 已经写得不错之后才需要动的东西。内存与配置层SGA、PGA、日志缓冲区、进程数、会话数这些参数。大部分情况下默认值或早期配置已经够用真正需要调的场景其实不多。硬件与架构层加内存、换存储、扩展节点。这层是成本最高的也是最后才考虑的。我知道很多刚入行的运维同学看到一条慢 SQL第一反应就是会不会是锁了是不是数据量太大了这些当然要考虑但更高效的做法是先拿到执行计划看一眼执行计划基本五分钟内就能锁定问题方向。后面我会专门讲怎么看执行计划里的关键信号。再聊聊优化和巡检的关系。在我自己的运维习惯里这两件事从来不是分开的。巡检的本质其实是定期体检而优化的本质是有病治病。体检做得好很多病在早期就被发现了根本轮不到治这一步。Oracle 的很多性能问题不是突然爆发的而是慢慢劣化的统计信息越来越不准、索引碎片越来越多、日志增长越来越快、临时表空间被一点一点撑大。这些特征在巡检的时候全都能看到苗头。所以这篇文章的主线我打算分成几个部分先讲 SQL 优化和索引调整里最值钱的几个实操点再给出一套可以在生产环境直接用的巡检体系然后把我这些年踩过的几个高频坑完整复盘一遍——包括监听日志爆盘、归档日志撑爆磁盘、锁等待排查最后聊一下怎么把手动巡检升级成半自动化的脚本巡检。整体节奏会比较贴近真实干活的状态不会讲太多教科书理论。如果你是刚接触 Oracle 的运维工程师或者已经有几年经验但总觉得巡检和优化没有章法这篇文章应该能给你一套可以直接落地的东西。我把所有涉及的关键 SQL 脚本都贴出来也尽量解释每个检查项背后的原理方便你根据自己的环境改着用。2. 高频场景下的 SQL 优化三板斧执行计划、统计信息、索引策略2.1 执行计划怎么看五个必须关注的关键信号拿到一条慢 SQL我第一步永远是EXPLAIN PLAN FOR或者直接在 PL/SQL Developer、SQL Developer 里按 F5 看执行计划。执行计划不用看得太细先抓住五个最要命的信号第一个信号TABLE ACCESS FULL全表扫描。这是最常见的性能杀手。不是说全表扫描一定不行小表全表扫描完全没问题但如果一张百万级以上的大表频繁出现 FULL十有八九是索引问题或者 SQL 写法问题。第二个信号NESTED LOOPS 的驱动顺序有问题。嵌套循环连接的原则是小表驱动大表如果优化器选错了驱动表代价会成倍上涨。出现这种问题很多时候是因为统计信息不准其次是因为 SQL 里关联条件写得太复杂优化器算错了基数。第三个信号SORT ORDER BY / SORT GROUP BY。排序和哈希分组都是内存敏感的如果 PGA 不够就会 spill 到临时表空间产生大量磁盘读写。看到这类操作先确认能不能通过索引消除排序再看临时表空间是否够用。第四个信号FILTER 操作。执行计划里的 FILTER 如果出现在大结果集上往往意味着 SQL 存在相关子查询每一行都要执行一次子查询。这属于典型的看起来没毛病跑起来要命的写法。第五个信号CARDINALITY 估值严重偏离。执行计划里每个步骤都会有一个预估行数如果预估行数和实际行数差了好几个数量级那后面所有的连接方式、驱动顺序判断全都会跑偏。这种情况优先怀疑统计信息。我实际工作中用的比较多的是DBMS_XPLAN.DISPLAY_AWRSQL或DBMS_XPLAN.DISPLAY_CURSOR能拿到真实执行计划和真实行数比 EXPLAIN PLAN 的预估值靠谱得多。一条 SQL 如果真实环境跑得慢但 EXPLAIN PLAN 看起来挺漂亮大概率是统计信息在说谎。2.2 统计信息过期Oracle 优化器瞎了眼的根源先说一个真实案例。某系统有个报表查询平时跑 1 秒不到某天突然变成 30 多秒。抓执行计划一看明明应该走索引的地方变成了全表扫描优化器对表行数的预估差了整整 50 倍。原因就是表数据量从 10 万涨到了 400 万但统计信息还停留在三个月前。Oracle 的优化器是靠统计信息来估算成本的统计信息一旦过期再好的 SQL 也白搭。也正是因为这样我巡检清单里永远把统计信息检查放在靠前的位置。检查统计信息是否过期的标准姿势是SELECT table_name, num_rows, last_analyzed FROM dba_tables WHERE owner APPS AND last_analyzed SYSDATE - 30 ORDER BY last_analyzed;这条 SQL 会列出 30 天以上没做过统计信息收集的表。正常情况下Oracle 会自动维护统计信息默认开启了auto optimizer stats collection任务但有几个坑是自动任务覆盖不到的大表更新频繁自动任务收集一次耗时太长往往被跳过了。使用了临时表或者中间表数据变化极快统计信息永远跟不上。11g 之后的pending statistics待定统计信息场景验证过没发布等于白收集。手动收集统计信息这个操作不同环境要讲究一下策略。我的建议是按表大小分档处理千万级以上的大表用ESTIMATE_PERCENT DBMS_STATS.AUTO_SAMPLE_SIZE同时加上METHOD_OPT FOR ALL COLUMNS SIZE AUTO让直方图自动适配小表直接COMPUTE STATISTICS。同时不管大表小表都建议加上NO_INVALIDATE FALSE让后续 SQL 能立刻用上新统计信息。2.3 索引的设计与维护索引不是越多越好聊索引之前先泼一盆冷水索引不是银弹建多了还不如不建。每个索引都会拖慢 INSERT/UPDATE/DELETE 的写入性能还会占用大量磁盘空间。运维巡检中我见过太多为了查询快一点把所有可疑的列都建了索引的情况最后系统写性能崩了查性能也没见好多少。那么索引到底该怎么设计我这里分享一套自己总结的粗筛逻辑场景合适的索引策略不推荐的做法单列高频查询条件单列普通索引/B-tree 索引对低基数列如性别、状态建索引多条件组合查询复合索引区分度高列放前面随意排列列顺序大量写入的表最小必要索引控制数量为所有查询场景建索引LIKE 前缀匹配普通索引 全文索引方案对 %xx% 建 B-tree 索引排序/去重场景索引列包含排序字段只建过滤条件的索引有个原则需要记住复合索引的列顺序要遵守等值列在前范围列在后。比如查询条件经常是WHERE status N AND create_date TRUNC(SYSDATE)那复合索引应该建在(status, create_date)上这比反过来建平均性能高很多。还有很多人没注意过函数索引。业务系统中常见的写法是WHERE TRUNC(create_date) TRUNC(SYSDATE)这种写法直接让普通索引失效。改造思路有两个一是改 SQL 改成范围条件create_date TRUNC(SYSDATE) AND create_date TRUNC(SYSDATE) 1二是建函数索引CREATE INDEX idx_t_date_trunc ON t(TRUNC(create_date))。我一般优先改 SQL因为函数索引在运维上会增加不少维护成本。索引维护方面巡检要重点看两个指标索引碎片率和失效索引。-- 查看失效索引 SELECT owner, index_name, table_name, status FROM dba_indexes WHERE status NOT IN (VALID, N/A); -- 估算碎片情况 SELECT owner, index_name, table_name, ROUND((DEL_LF_ROWS / LF_ROWS) * 100, 2) AS frag_ratio FROM dba_indexes WHERE DEL_LF_ROWS 0 AND LF_ROWS 0;碎片率超过 30% 且该索引的查询频率较高一般就该ALTER INDEX ... REBUILD了。另外复合索引如果包含大量空值列还要考虑NVL处理否则 NULL 不进入索引查询 NULL 条件时照样全表扫。2.4 绑定变量与 CURSOR被忽略的硬解析消耗这个点非常非常容易踩尤其是从开发转过来的人。看这条 SQLSELECT * FROM orders WHERE order_id 1001; SELECT * FROM orders WHERE order_id 1002;每次执行时 Oracle 都会做硬解析hard parse生成新的游标消耗 CPU 和共享池内存。如果是几千个用户并发执行这类 SQL共享池会瞬间被塞满随之而来的是大量library cache等待和 CPU 爆高。而写成绑定变量的形式SELECT * FROM orders WHERE order_id :id;SQL 文本完全相同第一次硬解析后后续全部走软解析soft parse代价降低一两个数量级。我巡检系统时最常干的一件事就是从 AWR 里看Load Profile中的Hard Parses指标一旦发现硬解析比例偏高就抓出那些没有用绑定变量的 SQL反馈给开发改。Oracle 中可以用V$SQL来捞相似文本的 SQLSELECT sql_id, SUBSTR(sql_text, 1, 100) AS sql_text, executions, loads FROM v$sql WHERE executions 0 AND loads 10 ORDER BY loads DESC;如果发现大量 SQL 文本只是数字不同、其他完全一样那基本可以确定是绑定变量没用了。这种问题的排查比索引优化还隐蔽但收益极大。我曾在电商类业务系统上优化过一次硬解析比例从 40% 降到 5%系统 CPU 负载直接掉了 30%。3. 巡检项目到底该查哪些内容一套能直接上手的巡检体系3.1 巡检的四个核心维度很多刚做运维的朋友巡检就是连上服务器top看一眼、看看磁盘满没满然后写个一切正常的巡检报告。说实话这种巡检基本等于没做。Oracle 巡检要覆盖四个核心维度可用性、容量、性能、安全。四个维度缺一不可。可用性实例是否活着、监听是否正常、关键数据文件在不在线、备份有没有成功。这是底线数据库都挂了性能和容量都免谈。容量表空间剩余量、归档日志目录空间、数据文件是否达到最大扩展限制、UNDO 表空间大小、临时表空间大小。容量问题是生产事故的头号导火索尤其是磁盘满这种看起来很低级但杀伤力极大的问题。性能AWR 报告关键指标、等待事件、Top SQL、锁等待和阻塞会话。这部分是优化工作的数据来源。安全无效账户、默认口令、异常登录记录、审计日志是否开启、敏感数据权限过大等问题。我自己实际做巡检时会按时间节奏分成日常巡检和深度巡检两个层级。3.2 日常巡检每天五分钟快速检查日常巡检目标很简单确认系统健康状况没有急剧恶化。不需要面面俱到盯着几个关键指标就行。-- 1. 实例状态与会话数 SELECT instance_name, status, DATABASE_STATUS, ACTIVE_SESSIONS FROM v$instance, v$database; -- 2. 表空间使用率 TOP10 SELECT tablespace_name, ROUND((total_blocks * block_size / 1024 / 1024), 2) AS total_mb, ROUND((used_blocks * block_size / 1024 / 1024), 2) AS used_mb, ROUND((used_blocks / total_blocks) * 100, 2) AS used_pct FROM (SELECT tablespace_name, SUM(blocks) AS total_blocks, block_size FROM dba_data_files GROUP BY tablespace_name, block_size) t JOIN (SELECT tablespace_name, SUM(used_blocks) AS used_blocks FROM dba_segments GROUP BY tablespace_name) u USING (tablespace_name) ORDER BY used_pct DESC; -- 3. 最近 15 分钟的等待事件 TOP5 SELECT event, COUNT(1) AS waits, ROUND(SUM(time_waited)/100, 2) AS total_wait_s FROM v$session_wait_history WHERE wait_time 0 GROUP BY event ORDER BY total_wait_s DESC FETCH FIRST 5 ROWS ONLY; -- 4. 活跃会话中是否有阻塞 SELECT blocking_session, sid, serial#, username, event, seconds_in_wait FROM v$session WHERE blocking_session IS NOT NULL AND status ACTIVE;这些检查我一般用一个 shell 脚本包起来每天早上定时跑一遍输出到日志文件里只要没产生新的 WARNING 就不需要人工介入。日常巡检不用贪多更重要的是坚持跑别三天打鱼两天晒网。3.3 深度巡检周粒度或月粒度的全面体检深度巡检就要认真多了一般我按周或者月的节奏执行重点看以下几块一、AWR 报告分析。这是最核心的诊断输入。AWR 报告不需要从头到尾细读抓重点DB Time和Elapsed的比例如果 DB Time 接近 Elapsed 的好几倍说明数据库内部存在严重争用Top 5 Timed Events如果前面是 db file sequential read、db file scattered read优先查 SQL 和索引如果是 log file sync优先查提交频率和应用侧如果是 enq: TX 相关查锁等待SQL ordered by Elapsed Time前 10 条慢 SQL 逐个看执行计划。生成 AWR 报告的姿势-- 生成最近 1 小时快照的 AWR 报告 SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML( LOWER(数据库名), 1, (SELECT MAX(snap_id) - 1 FROM dba_hist_snapshot), (SELECT MAX(snap_id) FROM dba_hist_snapshot) ));脚本化的时候可以直接在系统层面跑awrrpt.sql选好起止快照 ID 就行。二、空间增长趋势分析。巡检不能只看当前剩余量还要看增长趋势。用历史快照数据来做SELECT tablespace_name, MIN(used_mb) AS min_used_mb, MAX(used_mb) AS max_used_mb, ROUND(AVG(used_mb), 2) AS avg_used_mb FROM (SELECT tablespace_name, TO_CHAR(begin_interval_time, YYYY-MM-DD) AS day, ROUND((tablespace_size - tablespace_usedsize) * block_size / 1024 / 1024, 2) / 1024 AS used_mb FROM dba_hist_tbspc_space_usage u JOIN dba_hist_snapshot s ON u.snap_id s.snap_id WHERE u.tablespace_size 0) GROUP BY tablespace_name ORDER BY max_used_mb DESC;如果发现某个表空间连续 4 周每周增长 10% 以上就该前置扩容或者跟业务方确认数据增长是否正常了。这个检查比磁盘还剩下 200G 所以没事靠谱得多。三、日志与告警检查。重点看alert_sid.log中的 ORA- 错误。Oracle 的告警日志是排查历史问题的一手资料很多坑事后复盘都是从这里找到线索的。grep -i ora-\|error\|fail alert_xxx.log然后按错误码归类统计连续出现多次的 ORA-01555快照过旧、ORA-01688表空间无法扩展、ORA-00060死锁都是需要跟进去处理的。四、RMAN 备份完整性验证。日常巡检确认备份成功还不够深度巡检要做的是RESTORE VALIDATE或BACKUP VALIDATE去校验备份文件能否正常还原。做运维久了你会知道备份任务跑成功和备份真正可用是两码事。我见过磁盘坏块导致备份文件损坏的案例如果不做 restore 验证真回滚的那天才知道备份是坏的场面极其恐怖。4. 巡检过程中最常见的三个坑日志爆盘、监听故障、锁等待4.1 监听日志无限增长生产系统被垃圾日志拖垮这是运维圈子里的经典故事了。Oracle 的监听日志listener.log位于$ORACLE_HOME/network/log/默认情况下是无限增长的从不自动轮转。网络连接频繁的系统几天就能生成几个 GB 的日志文件。问题在于日志文件太大之后监听进程写入日志时的性能会下降严重的时候监听器本身会僵死新连接全部失败业务大面积中断。我之前处理过一个 11g RAC 环境某天早上业务方反馈数据库连不上了查监听状态是正常的端口也是通的但客户机就是无法建立连接。最后发现listener.log已膨胀到 18GB监听进程一直在忙着写日志。这属于典型的有监控死角的问题因为主机磁盘还远远没满监控系统不会报警但监听已经被自己的日志拖垮了。这个问题的排查链路大体是这样先确认监听进程是否存活ps -ef | grep tnslsnr再看端口状态netstat -an | grep 1521查看监听日志近期内容发现最后几条记录后系统就卡住了du -sh listener.log定位到日志文件异常大确认问题根因后重启监听服务或在线重命名日志文件。处理方案原来我一直用直接清空文件的方式后来发现不同版本要小心选择11g 及以下直接停监听lsnrctl stop删掉或 mv 掉日志再lsnrctl start12c 及以上支持日志轮转配置设置INBOUND_CONNECT_TIMEOUT_LISTENER和日志数量、大小限制。在listener.ora里加LOG_FILE_NUM_LISTENER10 LOG_FILE_SIZE_LISTENER10240这样每个日志文件最大 10MB保留 10 个。需要特别提醒千万别在生产库上直接 listener.log清空文件如果监听进程一直持有这个文件句柄清空后要继续写入文件会变成不可见但仍占空间反而更麻烦。正确做法是先lsnrctl set log_status off12c或者重启监听再处理日志文件。4.2 归档日志撑爆磁盘ORA-00257 事故复盘说一个高发事故ORA-00257归档日志写满磁盘导致数据库直接 hang 住。归档模式下只要有业务在跑重做日志就会不断写进归档日志一旦磁盘满了数据库会挂起所有事务全部堵住。这个坑之所以高发是因为很多环境把归档放到了单独的小分区总容量没规划好而且没有做归档自动清理策略。复盘一次真实处理过程。某系统接到告警数据库无法连接登录后发现系统卡死alert日志里大量 ORA-00257 报错。处理链路是查看ARCHIVE_LOG_LIST和db_recovery_file_dest_size确认V$RECOVERY_FILE_DEST.SPACE_LIMIT已满检查归档目录gv$archive_dest_status发现最近归档文件大量堆积判断归档长时间没有清理备份没有删旧归档临时扩容DB_RECOVERY_FILE_DEST_SIZE如果底层磁盘仍有空间恢复数据库可用清理过期归档DELETE ARCHIVELOG ALL COMPLETED BEFORE SYSDATE-7;制定定期清理策略同时检查 RMAN 备份策略是否已经删除过期归档。治标更要治本。我这边推荐的做法是使用 RMAN 的备份归档删除策略而不是手动删文件rman target / RMAN CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DEVICE TYPE DISK; RMAN BACKUP ARCHIVELOG ALL DELETE INPUT;这样每次备份完成后已备份的归档日志会被自动删除归档目录基本处于可控状态。另外巡检脚本里一定要加入对快速恢复区使用率的检查和告警阈值建议设在 80%留出缓冲时间。还有一个很容易忽视的问题归档清理和备份如果都集中到半夜执行要注意归档目录在备份完成前会不会瞬间被写满。如果业务日间归档很大备份时间又很长建议多分几次备份窗口或者加大归档目录规划。4.3 锁等待与阻塞会话OEM 上看不到的隐形麻烦锁的问题在 Oracle 运维里比较常见表现为某个会话卡了很久然后一堆会话跟着全部卡住。最常见的是以下几种行锁TX enqueue两个会话更新同一行互相等待表锁TM enqueueDML 遇到了 DDL 或锁表操作死锁ORA-00060会话互相持有对方需要的资源Oracle 自动牺牲其中一个但业务会报错。排查锁等待的完整脚本方案我用下面这套比较多-- 找出被阻塞的会话和阻塞源头 SELECT blocking_sess.sid AS blocking_sid, blocking_sess.username AS blocking_user, blocking_sess.event AS blocking_event, blocked_sess.sid AS blocked_sid, blocked_sess.username AS blocked_user, blocked_sess.event AS blocked_event, blocked_sess.seconds_in_wait FROM v$session blocked_sess JOIN v$session blocking_sess ON blocked_sess.blocking_session blocking_sess.sid WHERE blocked_sess.status ACTIVE AND blocked_sess.blocking_session IS NOT NULL;如果确认是行锁再定位具体是哪个 SQL 在操作哪些对象SELECT s.sid, s.serial#, s.username, q.sql_text, o.object_name, lo.locked_mode FROM v$locked_object lo JOIN v$session s ON lo.session_id s.sid JOIN v$sql q ON s.sql_id q.sql_id JOIN dba_objects o ON lo.object_id o.object_id;处理方式轻量级的是把阻塞的会话 kill 掉。但我想强调一个经验杀会话是灭火不是治病。锁之所以产生根因要么是应用侧事务太长不提交占着锁不放要么是代码里忘了提交/回滚要么是同一个对象被开发人员在线上手动执行了 DDL 刚好碰上业务高峰期。我处理过一个最荒诞的案例某开发人员在生产库上跑了一个UPDATE忘了加WHERE条件直接更新了整张表的几百万行事务一直不提交把整张表锁了两个多小时。杀会话之后业务恢复了但脏数据已经写进去了后续修复数据又是另一场恶战。所以锁等待在巡检层面要做的是两件事一是部署锁监控脚本隔几分钟扫一次V$LOCK发现阻塞持续超过 N 分钟就告警二是从源头减少长事务和开发团队约定事务提交间隔和 DDL 执行窗口。4.4 巡检中容易被忽略但特别致命的几个盲区除了上面三个典型事故我再补充几个日常巡检中非常容易漏掉的点表空间自动扩展关闭。很多表空间创建时AUTOEXTEND OFF数据增长到一定量就直接 ORA-01653/ORA-01688。巡检时用这条查一下SELECT tablespace_name, file_name, ROUND(maxbytes / 1024 / 1024, 2) AS max_mb, ROUND(bytes / 1024 / 1024, 2) AS cur_mb, autoextensible FROM dba_data_files WHERE autoextensible NO;临时表空间过小。大量排序、分组、索引重建操作依赖临时表空间临时表空间一旦不够SQL 直接报 ORA-01652非常容易引发大批量任务失败。很多系统临时表空间从建库开始就没调过遇到一次大规模报表就跑不动。UNDO 表空间增长和 undo retention 不匹配。长查询跑的时间超过了 UNDO 的保留窗口就会报 ORA-01555snapshot too old这在 RAC 环境下尤其麻烦。巡检时关注undoblkn和tuned_undoretention的比值一般持续出现 ORA-01555 就该扩大 UNDO 表空间了。定时任务Job执行时间延长。数据库里的 scheduler job 本来十几分钟跑完某天突然变成 1 小时。这类变化不一定会产生告警但往往是内存、SQL、空间问题恶化的信号巡检时需要把关键 job 的执行历史拉出来对比。5. 把巡检从手动命令升级为半自动脚本5.1 一套可复用的日常巡检 Shell 脚本大厂可能会有成熟的监控平台Zabbix、Prometheus Oracled Exporter 等但很多中小团队并没有这么完善的设施。在我自己接触过的环境里真正稳定跑了好几年的巡检方案反而是几个靠谱的 shell 脚本配合 cron。这里分享一套我常用的巡检脚本骨架#!/bin/bash # oracle_daily_check.sh # 用法: 放到 crontab 每天 08:00 执行输出巡检日志 export ORACLE_SIDorcl export ORACLE_HOME/u01/app/oracle/product/11.2.0/dbhome_1 export PATH$ORACLE_HOME/bin:$PATH LOG_DIR/u01/check_logs LOG_FILE$LOG_DIR/check_$(date %Y%m%d).log mkdir -p $LOG_DIR echo Oracle Daily Check $(date) $LOG_FILE sqlplus -s / as sysdba EOF $LOG_FILE SET PAGESIZE 100 SET LINESIZE 200 PROMPT 1. Instance Status SELECT instance_name, status, database_status FROM v$instance; PROMPT 2. Tablespace Usage SELECT tablespace_name, ROUND((used_blocks / total_blocks) * 100, 2) AS used_pct FROM (SELECT tablespace_name, SUM(blocks) AS total_blocks FROM dba_data_files GROUP BY tablespace_name) t JOIN (SELECT tablespace_name, SUM(used_blocks) AS used_blocks FROM dba_segments GROUP BY tablespace_name) u USING (tablespace_name) ORDER BY used_pct DESC; PROMPT 3. Waiting Events SELECT event, COUNT(1) waits FROM v$session WHERE status ACTIVE AND wait_class ! Idle GROUP BY event ORDER BY waits DESC FETCH FIRST 5 ROWS ONLY; EXIT; EOF # 检查 alert 日志中的 ORA- 错误 ALERT_LOG$ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log if [ -f $ALERT_LOG ]; then ERROR_CNT$(grep -i ORA- $ALERT_LOG | grep $(date %Y-%m-%d) | wc -l) echo Alert log ORA- errors today: $ERROR_CNT $LOG_FILE if [ $ERROR_CNT -gt 0 ]; then grep -i ORA- $ALERT_LOG | tail -20 $LOG_FILE fi fi # 检查归档目录空间 echo 4. Archive Log Dir $LOG_FILE df -h /u01/archive $LOG_FILE这套脚本的定位是每天扫一遍心里有底不追求复杂的逻辑重点是保持稳定运行。脚本跑出来的日志每天存一份后续要排查问题时可以翻历史记录配合grep快速定位变化趋势。我见过很多团队一开始就追求做一个大而全的监控平台结果搞了大半年还在调告警规则。其实先跑起来把最关键的几个检查项自动化之后根据事故复盘逐步补充脚本才是效率最高的路径。5.2 检查结果的二次处理让巡检结果有意义脚本跑出来一堆输出如果只是堆在日志文件里那还不是真正的巡检。数据要变成决策依据至少需要做两层加工。第一层阈值判断。比如表空间使用率超过 85% 报 WARNING超过 92% 报 CRITICAL等待事件里出现enq: TX、library cache lock等关键字直接告警。这个逻辑直接在脚本里用grep和awk就能实现不用引入重型工具。第二层趋势对比。单次巡检结果说明不了太多问题但如果把每天的巡检结果归档成 CSV对比一周内的表空间增幅和日志错误数量就能发现很多早期苗头。这个通过简单的 shell 追加写 CSV 就能实现。比如我做过的这种echo $(date %Y-%m-%d),$TS_PCT_USED,$ARCHIVE_USAGE_PCT,$ACTIVE_SESSIONS /u01/check_logs/daily_trend.csv积累一个月之后用 Excel 或者 Python 画个趋势线表空间什么时候会满就能提前算出来了。这种预判式巡检比事后告警高到不知道哪里去了。5.3 Python 巡检脚本和连接 Oracle 的注意点有些场景下shell 处理不了复杂逻辑比如要跨多个数据库实例汇总巡检结果、要对接内部工单系统、要做定时报告推送。这时候用 Python 写巡检脚本就顺理成章了。Python 连接 Oracle 最常用的是python-oracledb新版官方推荐或者老的cx_Oracle。连接前第一件事确保 Python 环境的位数和 Oracle Client 位数一致32 位和 64 位混用是最常见的坑。另外如果在服务器本机跑直接用dsn cx_Oracle.makedsn(host, port, service_name)比SID方式更稳因为很多 RAC 环境用 service_name 才有负载均衡。import oracledb # 直连模式无需安装 Oracle Client新版 python-oracledb connection oracledb.connect(usersystem, password***, dsnhost:1521/service_name) cursor connection.cursor() cursor.execute(SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) FROM dba_data_files GROUP BY tablespace_name) for row in cursor.fetchall(): print(row)小技巧巡检脚本连接数据库建议用只读权限的巡检专用账号别直接用system或sys。巡检账号只需要CONNECT 查询DBA_*视图的权限。另外所有巡检脚本尽量用SET TIMING ON看每条 SQL 的执行耗时巡检脚本本身也要高效否则可能比业务 SQL 还拖累系统。到了这个阶段你的巡检其实已经具备半自动化能力了定时跑、阈值告警、趋势分析、多实例汇总。再往上是对接统一的监控平台和工单闭环但那些对很多团队来说并不是必需的。能稳定、持续、可追溯地跑下去的巡检脚本比一坨炫酷但没人维护的监控平台实用得多。自己运维久了最大的体会是Oracle 这套东西常用的知识点翻来覆去就那么些但每个细节在不同的业务负载下会有完全不同的表现。巡检脚本的价值不在于帮我发现了这个故障而在于帮你积累了环境的历史基线。有了基线和趋势你才能在故障发生前介入而不是每次都当救火队长。这大概就是运维从被动响应转向主动预防最实在的一步。 Oracle优化与巡检实战：从SQL调优到半自动化脚本
1. 先从 DBA 的视角理解Oracle 优化这件事做了这么多年 Oracle 运维我最大的感受是一谈到数据库优化很多人脑袋里蹦出来的第一个词就是加内存、扩磁盘、换 SSD。真到了生产环境你会发现硬件层面的调整往往是一锤子买卖动静大、成本高而且大概率解决不了根本问题。真正每天都在发生、每天都在拖垮系统性能的是 SQL 写得不对、索引没建好、统计信息过期这类看起来不起眼的小事。先看一个我印象很深的例子。有一套业务系统每个月月底跑批要 40 分钟业务方催了无数次DBA 查了内存、查了 IO、查了 RAC 心跳全都没问题。后来我接手之后花了一个下午抓 SQL发现跑批过程中有个 UPDATE 语句走了全表扫描表 600 万行单条更新要扫全表再加上循环调用40 分钟就这么耗出来的。改完索引之后跑批直接降到 9 分钟。全程没有动过任何硬件配置——这就是 Oracle 优化最典型的日常。所以这里先把一个理念掰扯清楚数据库优化是有优先级的顺序大概是这么个逻辑SQL 层优先坏 SQL 是所有性能问题的第一来源。同样的数据量一条 SQL 写得好不好性能差距可以到几十倍甚至上百倍。物理结构层索引是否存在、是否失效、分区是否合理、表是否有严重碎片。这层通常是在 SQL 已经写得不错之后才需要动的东西。内存与配置层SGA、PGA、日志缓冲区、进程数、会话数这些参数。大部分情况下默认值或早期配置已经够用真正需要调的场景其实不多。硬件与架构层加内存、换存储、扩展节点。这层是成本最高的也是最后才考虑的。我知道很多刚入行的运维同学看到一条慢 SQL第一反应就是会不会是锁了是不是数据量太大了这些当然要考虑但更高效的做法是先拿到执行计划看一眼执行计划基本五分钟内就能锁定问题方向。后面我会专门讲怎么看执行计划里的关键信号。再聊聊优化和巡检的关系。在我自己的运维习惯里这两件事从来不是分开的。巡检的本质其实是定期体检而优化的本质是有病治病。体检做得好很多病在早期就被发现了根本轮不到治这一步。Oracle 的很多性能问题不是突然爆发的而是慢慢劣化的统计信息越来越不准、索引碎片越来越多、日志增长越来越快、临时表空间被一点一点撑大。这些特征在巡检的时候全都能看到苗头。所以这篇文章的主线我打算分成几个部分先讲 SQL 优化和索引调整里最值钱的几个实操点再给出一套可以在生产环境直接用的巡检体系然后把我这些年踩过的几个高频坑完整复盘一遍——包括监听日志爆盘、归档日志撑爆磁盘、锁等待排查最后聊一下怎么把手动巡检升级成半自动化的脚本巡检。整体节奏会比较贴近真实干活的状态不会讲太多教科书理论。如果你是刚接触 Oracle 的运维工程师或者已经有几年经验但总觉得巡检和优化没有章法这篇文章应该能给你一套可以直接落地的东西。我把所有涉及的关键 SQL 脚本都贴出来也尽量解释每个检查项背后的原理方便你根据自己的环境改着用。2. 高频场景下的 SQL 优化三板斧执行计划、统计信息、索引策略2.1 执行计划怎么看五个必须关注的关键信号拿到一条慢 SQL我第一步永远是EXPLAIN PLAN FOR或者直接在 PL/SQL Developer、SQL Developer 里按 F5 看执行计划。执行计划不用看得太细先抓住五个最要命的信号第一个信号TABLE ACCESS FULL全表扫描。这是最常见的性能杀手。不是说全表扫描一定不行小表全表扫描完全没问题但如果一张百万级以上的大表频繁出现 FULL十有八九是索引问题或者 SQL 写法问题。第二个信号NESTED LOOPS 的驱动顺序有问题。嵌套循环连接的原则是小表驱动大表如果优化器选错了驱动表代价会成倍上涨。出现这种问题很多时候是因为统计信息不准其次是因为 SQL 里关联条件写得太复杂优化器算错了基数。第三个信号SORT ORDER BY / SORT GROUP BY。排序和哈希分组都是内存敏感的如果 PGA 不够就会 spill 到临时表空间产生大量磁盘读写。看到这类操作先确认能不能通过索引消除排序再看临时表空间是否够用。第四个信号FILTER 操作。执行计划里的 FILTER 如果出现在大结果集上往往意味着 SQL 存在相关子查询每一行都要执行一次子查询。这属于典型的看起来没毛病跑起来要命的写法。第五个信号CARDINALITY 估值严重偏离。执行计划里每个步骤都会有一个预估行数如果预估行数和实际行数差了好几个数量级那后面所有的连接方式、驱动顺序判断全都会跑偏。这种情况优先怀疑统计信息。我实际工作中用的比较多的是DBMS_XPLAN.DISPLAY_AWRSQL或DBMS_XPLAN.DISPLAY_CURSOR能拿到真实执行计划和真实行数比 EXPLAIN PLAN 的预估值靠谱得多。一条 SQL 如果真实环境跑得慢但 EXPLAIN PLAN 看起来挺漂亮大概率是统计信息在说谎。2.2 统计信息过期Oracle 优化器瞎了眼的根源先说一个真实案例。某系统有个报表查询平时跑 1 秒不到某天突然变成 30 多秒。抓执行计划一看明明应该走索引的地方变成了全表扫描优化器对表行数的预估差了整整 50 倍。原因就是表数据量从 10 万涨到了 400 万但统计信息还停留在三个月前。Oracle 的优化器是靠统计信息来估算成本的统计信息一旦过期再好的 SQL 也白搭。也正是因为这样我巡检清单里永远把统计信息检查放在靠前的位置。检查统计信息是否过期的标准姿势是SELECT table_name, num_rows, last_analyzed FROM dba_tables WHERE owner APPS AND last_analyzed SYSDATE - 30 ORDER BY last_analyzed;这条 SQL 会列出 30 天以上没做过统计信息收集的表。正常情况下Oracle 会自动维护统计信息默认开启了auto optimizer stats collection任务但有几个坑是自动任务覆盖不到的大表更新频繁自动任务收集一次耗时太长往往被跳过了。使用了临时表或者中间表数据变化极快统计信息永远跟不上。11g 之后的pending statistics待定统计信息场景验证过没发布等于白收集。手动收集统计信息这个操作不同环境要讲究一下策略。我的建议是按表大小分档处理千万级以上的大表用ESTIMATE_PERCENT DBMS_STATS.AUTO_SAMPLE_SIZE同时加上METHOD_OPT FOR ALL COLUMNS SIZE AUTO让直方图自动适配小表直接COMPUTE STATISTICS。同时不管大表小表都建议加上NO_INVALIDATE FALSE让后续 SQL 能立刻用上新统计信息。2.3 索引的设计与维护索引不是越多越好聊索引之前先泼一盆冷水索引不是银弹建多了还不如不建。每个索引都会拖慢 INSERT/UPDATE/DELETE 的写入性能还会占用大量磁盘空间。运维巡检中我见过太多为了查询快一点把所有可疑的列都建了索引的情况最后系统写性能崩了查性能也没见好多少。那么索引到底该怎么设计我这里分享一套自己总结的粗筛逻辑场景合适的索引策略不推荐的做法单列高频查询条件单列普通索引/B-tree 索引对低基数列如性别、状态建索引多条件组合查询复合索引区分度高列放前面随意排列列顺序大量写入的表最小必要索引控制数量为所有查询场景建索引LIKE 前缀匹配普通索引 全文索引方案对 %xx% 建 B-tree 索引排序/去重场景索引列包含排序字段只建过滤条件的索引有个原则需要记住复合索引的列顺序要遵守等值列在前范围列在后。比如查询条件经常是WHERE status N AND create_date TRUNC(SYSDATE)那复合索引应该建在(status, create_date)上这比反过来建平均性能高很多。还有很多人没注意过函数索引。业务系统中常见的写法是WHERE TRUNC(create_date) TRUNC(SYSDATE)这种写法直接让普通索引失效。改造思路有两个一是改 SQL 改成范围条件create_date TRUNC(SYSDATE) AND create_date TRUNC(SYSDATE) 1二是建函数索引CREATE INDEX idx_t_date_trunc ON t(TRUNC(create_date))。我一般优先改 SQL因为函数索引在运维上会增加不少维护成本。索引维护方面巡检要重点看两个指标索引碎片率和失效索引。-- 查看失效索引 SELECT owner, index_name, table_name, status FROM dba_indexes WHERE status NOT IN (VALID, N/A); -- 估算碎片情况 SELECT owner, index_name, table_name, ROUND((DEL_LF_ROWS / LF_ROWS) * 100, 2) AS frag_ratio FROM dba_indexes WHERE DEL_LF_ROWS 0 AND LF_ROWS 0;碎片率超过 30% 且该索引的查询频率较高一般就该ALTER INDEX ... REBUILD了。另外复合索引如果包含大量空值列还要考虑NVL处理否则 NULL 不进入索引查询 NULL 条件时照样全表扫。2.4 绑定变量与 CURSOR被忽略的硬解析消耗这个点非常非常容易踩尤其是从开发转过来的人。看这条 SQLSELECT * FROM orders WHERE order_id 1001; SELECT * FROM orders WHERE order_id 1002;每次执行时 Oracle 都会做硬解析hard parse生成新的游标消耗 CPU 和共享池内存。如果是几千个用户并发执行这类 SQL共享池会瞬间被塞满随之而来的是大量library cache等待和 CPU 爆高。而写成绑定变量的形式SELECT * FROM orders WHERE order_id :id;SQL 文本完全相同第一次硬解析后后续全部走软解析soft parse代价降低一两个数量级。我巡检系统时最常干的一件事就是从 AWR 里看Load Profile中的Hard Parses指标一旦发现硬解析比例偏高就抓出那些没有用绑定变量的 SQL反馈给开发改。Oracle 中可以用V$SQL来捞相似文本的 SQLSELECT sql_id, SUBSTR(sql_text, 1, 100) AS sql_text, executions, loads FROM v$sql WHERE executions 0 AND loads 10 ORDER BY loads DESC;如果发现大量 SQL 文本只是数字不同、其他完全一样那基本可以确定是绑定变量没用了。这种问题的排查比索引优化还隐蔽但收益极大。我曾在电商类业务系统上优化过一次硬解析比例从 40% 降到 5%系统 CPU 负载直接掉了 30%。3. 巡检项目到底该查哪些内容一套能直接上手的巡检体系3.1 巡检的四个核心维度很多刚做运维的朋友巡检就是连上服务器top看一眼、看看磁盘满没满然后写个一切正常的巡检报告。说实话这种巡检基本等于没做。Oracle 巡检要覆盖四个核心维度可用性、容量、性能、安全。四个维度缺一不可。可用性实例是否活着、监听是否正常、关键数据文件在不在线、备份有没有成功。这是底线数据库都挂了性能和容量都免谈。容量表空间剩余量、归档日志目录空间、数据文件是否达到最大扩展限制、UNDO 表空间大小、临时表空间大小。容量问题是生产事故的头号导火索尤其是磁盘满这种看起来很低级但杀伤力极大的问题。性能AWR 报告关键指标、等待事件、Top SQL、锁等待和阻塞会话。这部分是优化工作的数据来源。安全无效账户、默认口令、异常登录记录、审计日志是否开启、敏感数据权限过大等问题。我自己实际做巡检时会按时间节奏分成日常巡检和深度巡检两个层级。3.2 日常巡检每天五分钟快速检查日常巡检目标很简单确认系统健康状况没有急剧恶化。不需要面面俱到盯着几个关键指标就行。-- 1. 实例状态与会话数 SELECT instance_name, status, DATABASE_STATUS, ACTIVE_SESSIONS FROM v$instance, v$database; -- 2. 表空间使用率 TOP10 SELECT tablespace_name, ROUND((total_blocks * block_size / 1024 / 1024), 2) AS total_mb, ROUND((used_blocks * block_size / 1024 / 1024), 2) AS used_mb, ROUND((used_blocks / total_blocks) * 100, 2) AS used_pct FROM (SELECT tablespace_name, SUM(blocks) AS total_blocks, block_size FROM dba_data_files GROUP BY tablespace_name, block_size) t JOIN (SELECT tablespace_name, SUM(used_blocks) AS used_blocks FROM dba_segments GROUP BY tablespace_name) u USING (tablespace_name) ORDER BY used_pct DESC; -- 3. 最近 15 分钟的等待事件 TOP5 SELECT event, COUNT(1) AS waits, ROUND(SUM(time_waited)/100, 2) AS total_wait_s FROM v$session_wait_history WHERE wait_time 0 GROUP BY event ORDER BY total_wait_s DESC FETCH FIRST 5 ROWS ONLY; -- 4. 活跃会话中是否有阻塞 SELECT blocking_session, sid, serial#, username, event, seconds_in_wait FROM v$session WHERE blocking_session IS NOT NULL AND status ACTIVE;这些检查我一般用一个 shell 脚本包起来每天早上定时跑一遍输出到日志文件里只要没产生新的 WARNING 就不需要人工介入。日常巡检不用贪多更重要的是坚持跑别三天打鱼两天晒网。3.3 深度巡检周粒度或月粒度的全面体检深度巡检就要认真多了一般我按周或者月的节奏执行重点看以下几块一、AWR 报告分析。这是最核心的诊断输入。AWR 报告不需要从头到尾细读抓重点DB Time和Elapsed的比例如果 DB Time 接近 Elapsed 的好几倍说明数据库内部存在严重争用Top 5 Timed Events如果前面是 db file sequential read、db file scattered read优先查 SQL 和索引如果是 log file sync优先查提交频率和应用侧如果是 enq: TX 相关查锁等待SQL ordered by Elapsed Time前 10 条慢 SQL 逐个看执行计划。生成 AWR 报告的姿势-- 生成最近 1 小时快照的 AWR 报告 SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML( LOWER(数据库名), 1, (SELECT MAX(snap_id) - 1 FROM dba_hist_snapshot), (SELECT MAX(snap_id) FROM dba_hist_snapshot) ));脚本化的时候可以直接在系统层面跑awrrpt.sql选好起止快照 ID 就行。二、空间增长趋势分析。巡检不能只看当前剩余量还要看增长趋势。用历史快照数据来做SELECT tablespace_name, MIN(used_mb) AS min_used_mb, MAX(used_mb) AS max_used_mb, ROUND(AVG(used_mb), 2) AS avg_used_mb FROM (SELECT tablespace_name, TO_CHAR(begin_interval_time, YYYY-MM-DD) AS day, ROUND((tablespace_size - tablespace_usedsize) * block_size / 1024 / 1024, 2) / 1024 AS used_mb FROM dba_hist_tbspc_space_usage u JOIN dba_hist_snapshot s ON u.snap_id s.snap_id WHERE u.tablespace_size 0) GROUP BY tablespace_name ORDER BY max_used_mb DESC;如果发现某个表空间连续 4 周每周增长 10% 以上就该前置扩容或者跟业务方确认数据增长是否正常了。这个检查比磁盘还剩下 200G 所以没事靠谱得多。三、日志与告警检查。重点看alert_sid.log中的 ORA- 错误。Oracle 的告警日志是排查历史问题的一手资料很多坑事后复盘都是从这里找到线索的。grep -i ora-\|error\|fail alert_xxx.log然后按错误码归类统计连续出现多次的 ORA-01555快照过旧、ORA-01688表空间无法扩展、ORA-00060死锁都是需要跟进去处理的。四、RMAN 备份完整性验证。日常巡检确认备份成功还不够深度巡检要做的是RESTORE VALIDATE或BACKUP VALIDATE去校验备份文件能否正常还原。做运维久了你会知道备份任务跑成功和备份真正可用是两码事。我见过磁盘坏块导致备份文件损坏的案例如果不做 restore 验证真回滚的那天才知道备份是坏的场面极其恐怖。4. 巡检过程中最常见的三个坑日志爆盘、监听故障、锁等待4.1 监听日志无限增长生产系统被垃圾日志拖垮这是运维圈子里的经典故事了。Oracle 的监听日志listener.log位于$ORACLE_HOME/network/log/默认情况下是无限增长的从不自动轮转。网络连接频繁的系统几天就能生成几个 GB 的日志文件。问题在于日志文件太大之后监听进程写入日志时的性能会下降严重的时候监听器本身会僵死新连接全部失败业务大面积中断。我之前处理过一个 11g RAC 环境某天早上业务方反馈数据库连不上了查监听状态是正常的端口也是通的但客户机就是无法建立连接。最后发现listener.log已膨胀到 18GB监听进程一直在忙着写日志。这属于典型的有监控死角的问题因为主机磁盘还远远没满监控系统不会报警但监听已经被自己的日志拖垮了。这个问题的排查链路大体是这样先确认监听进程是否存活ps -ef | grep tnslsnr再看端口状态netstat -an | grep 1521查看监听日志近期内容发现最后几条记录后系统就卡住了du -sh listener.log定位到日志文件异常大确认问题根因后重启监听服务或在线重命名日志文件。处理方案原来我一直用直接清空文件的方式后来发现不同版本要小心选择11g 及以下直接停监听lsnrctl stop删掉或 mv 掉日志再lsnrctl start12c 及以上支持日志轮转配置设置INBOUND_CONNECT_TIMEOUT_LISTENER和日志数量、大小限制。在listener.ora里加LOG_FILE_NUM_LISTENER10 LOG_FILE_SIZE_LISTENER10240这样每个日志文件最大 10MB保留 10 个。需要特别提醒千万别在生产库上直接 listener.log清空文件如果监听进程一直持有这个文件句柄清空后要继续写入文件会变成不可见但仍占空间反而更麻烦。正确做法是先lsnrctl set log_status off12c或者重启监听再处理日志文件。4.2 归档日志撑爆磁盘ORA-00257 事故复盘说一个高发事故ORA-00257归档日志写满磁盘导致数据库直接 hang 住。归档模式下只要有业务在跑重做日志就会不断写进归档日志一旦磁盘满了数据库会挂起所有事务全部堵住。这个坑之所以高发是因为很多环境把归档放到了单独的小分区总容量没规划好而且没有做归档自动清理策略。复盘一次真实处理过程。某系统接到告警数据库无法连接登录后发现系统卡死alert日志里大量 ORA-00257 报错。处理链路是查看ARCHIVE_LOG_LIST和db_recovery_file_dest_size确认V$RECOVERY_FILE_DEST.SPACE_LIMIT已满检查归档目录gv$archive_dest_status发现最近归档文件大量堆积判断归档长时间没有清理备份没有删旧归档临时扩容DB_RECOVERY_FILE_DEST_SIZE如果底层磁盘仍有空间恢复数据库可用清理过期归档DELETE ARCHIVELOG ALL COMPLETED BEFORE SYSDATE-7;制定定期清理策略同时检查 RMAN 备份策略是否已经删除过期归档。治标更要治本。我这边推荐的做法是使用 RMAN 的备份归档删除策略而不是手动删文件rman target / RMAN CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DEVICE TYPE DISK; RMAN BACKUP ARCHIVELOG ALL DELETE INPUT;这样每次备份完成后已备份的归档日志会被自动删除归档目录基本处于可控状态。另外巡检脚本里一定要加入对快速恢复区使用率的检查和告警阈值建议设在 80%留出缓冲时间。还有一个很容易忽视的问题归档清理和备份如果都集中到半夜执行要注意归档目录在备份完成前会不会瞬间被写满。如果业务日间归档很大备份时间又很长建议多分几次备份窗口或者加大归档目录规划。4.3 锁等待与阻塞会话OEM 上看不到的隐形麻烦锁的问题在 Oracle 运维里比较常见表现为某个会话卡了很久然后一堆会话跟着全部卡住。最常见的是以下几种行锁TX enqueue两个会话更新同一行互相等待表锁TM enqueueDML 遇到了 DDL 或锁表操作死锁ORA-00060会话互相持有对方需要的资源Oracle 自动牺牲其中一个但业务会报错。排查锁等待的完整脚本方案我用下面这套比较多-- 找出被阻塞的会话和阻塞源头 SELECT blocking_sess.sid AS blocking_sid, blocking_sess.username AS blocking_user, blocking_sess.event AS blocking_event, blocked_sess.sid AS blocked_sid, blocked_sess.username AS blocked_user, blocked_sess.event AS blocked_event, blocked_sess.seconds_in_wait FROM v$session blocked_sess JOIN v$session blocking_sess ON blocked_sess.blocking_session blocking_sess.sid WHERE blocked_sess.status ACTIVE AND blocked_sess.blocking_session IS NOT NULL;如果确认是行锁再定位具体是哪个 SQL 在操作哪些对象SELECT s.sid, s.serial#, s.username, q.sql_text, o.object_name, lo.locked_mode FROM v$locked_object lo JOIN v$session s ON lo.session_id s.sid JOIN v$sql q ON s.sql_id q.sql_id JOIN dba_objects o ON lo.object_id o.object_id;处理方式轻量级的是把阻塞的会话 kill 掉。但我想强调一个经验杀会话是灭火不是治病。锁之所以产生根因要么是应用侧事务太长不提交占着锁不放要么是代码里忘了提交/回滚要么是同一个对象被开发人员在线上手动执行了 DDL 刚好碰上业务高峰期。我处理过一个最荒诞的案例某开发人员在生产库上跑了一个UPDATE忘了加WHERE条件直接更新了整张表的几百万行事务一直不提交把整张表锁了两个多小时。杀会话之后业务恢复了但脏数据已经写进去了后续修复数据又是另一场恶战。所以锁等待在巡检层面要做的是两件事一是部署锁监控脚本隔几分钟扫一次V$LOCK发现阻塞持续超过 N 分钟就告警二是从源头减少长事务和开发团队约定事务提交间隔和 DDL 执行窗口。4.4 巡检中容易被忽略但特别致命的几个盲区除了上面三个典型事故我再补充几个日常巡检中非常容易漏掉的点表空间自动扩展关闭。很多表空间创建时AUTOEXTEND OFF数据增长到一定量就直接 ORA-01653/ORA-01688。巡检时用这条查一下SELECT tablespace_name, file_name, ROUND(maxbytes / 1024 / 1024, 2) AS max_mb, ROUND(bytes / 1024 / 1024, 2) AS cur_mb, autoextensible FROM dba_data_files WHERE autoextensible NO;临时表空间过小。大量排序、分组、索引重建操作依赖临时表空间临时表空间一旦不够SQL 直接报 ORA-01652非常容易引发大批量任务失败。很多系统临时表空间从建库开始就没调过遇到一次大规模报表就跑不动。UNDO 表空间增长和 undo retention 不匹配。长查询跑的时间超过了 UNDO 的保留窗口就会报 ORA-01555snapshot too old这在 RAC 环境下尤其麻烦。巡检时关注undoblkn和tuned_undoretention的比值一般持续出现 ORA-01555 就该扩大 UNDO 表空间了。定时任务Job执行时间延长。数据库里的 scheduler job 本来十几分钟跑完某天突然变成 1 小时。这类变化不一定会产生告警但往往是内存、SQL、空间问题恶化的信号巡检时需要把关键 job 的执行历史拉出来对比。5. 把巡检从手动命令升级为半自动脚本5.1 一套可复用的日常巡检 Shell 脚本大厂可能会有成熟的监控平台Zabbix、Prometheus Oracled Exporter 等但很多中小团队并没有这么完善的设施。在我自己接触过的环境里真正稳定跑了好几年的巡检方案反而是几个靠谱的 shell 脚本配合 cron。这里分享一套我常用的巡检脚本骨架#!/bin/bash # oracle_daily_check.sh # 用法: 放到 crontab 每天 08:00 执行输出巡检日志 export ORACLE_SIDorcl export ORACLE_HOME/u01/app/oracle/product/11.2.0/dbhome_1 export PATH$ORACLE_HOME/bin:$PATH LOG_DIR/u01/check_logs LOG_FILE$LOG_DIR/check_$(date %Y%m%d).log mkdir -p $LOG_DIR echo Oracle Daily Check $(date) $LOG_FILE sqlplus -s / as sysdba EOF $LOG_FILE SET PAGESIZE 100 SET LINESIZE 200 PROMPT 1. Instance Status SELECT instance_name, status, database_status FROM v$instance; PROMPT 2. Tablespace Usage SELECT tablespace_name, ROUND((used_blocks / total_blocks) * 100, 2) AS used_pct FROM (SELECT tablespace_name, SUM(blocks) AS total_blocks FROM dba_data_files GROUP BY tablespace_name) t JOIN (SELECT tablespace_name, SUM(used_blocks) AS used_blocks FROM dba_segments GROUP BY tablespace_name) u USING (tablespace_name) ORDER BY used_pct DESC; PROMPT 3. Waiting Events SELECT event, COUNT(1) waits FROM v$session WHERE status ACTIVE AND wait_class ! Idle GROUP BY event ORDER BY waits DESC FETCH FIRST 5 ROWS ONLY; EXIT; EOF # 检查 alert 日志中的 ORA- 错误 ALERT_LOG$ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log if [ -f $ALERT_LOG ]; then ERROR_CNT$(grep -i ORA- $ALERT_LOG | grep $(date %Y-%m-%d) | wc -l) echo Alert log ORA- errors today: $ERROR_CNT $LOG_FILE if [ $ERROR_CNT -gt 0 ]; then grep -i ORA- $ALERT_LOG | tail -20 $LOG_FILE fi fi # 检查归档目录空间 echo 4. Archive Log Dir $LOG_FILE df -h /u01/archive $LOG_FILE这套脚本的定位是每天扫一遍心里有底不追求复杂的逻辑重点是保持稳定运行。脚本跑出来的日志每天存一份后续要排查问题时可以翻历史记录配合grep快速定位变化趋势。我见过很多团队一开始就追求做一个大而全的监控平台结果搞了大半年还在调告警规则。其实先跑起来把最关键的几个检查项自动化之后根据事故复盘逐步补充脚本才是效率最高的路径。5.2 检查结果的二次处理让巡检结果有意义脚本跑出来一堆输出如果只是堆在日志文件里那还不是真正的巡检。数据要变成决策依据至少需要做两层加工。第一层阈值判断。比如表空间使用率超过 85% 报 WARNING超过 92% 报 CRITICAL等待事件里出现enq: TX、library cache lock等关键字直接告警。这个逻辑直接在脚本里用grep和awk就能实现不用引入重型工具。第二层趋势对比。单次巡检结果说明不了太多问题但如果把每天的巡检结果归档成 CSV对比一周内的表空间增幅和日志错误数量就能发现很多早期苗头。这个通过简单的 shell 追加写 CSV 就能实现。比如我做过的这种echo $(date %Y-%m-%d),$TS_PCT_USED,$ARCHIVE_USAGE_PCT,$ACTIVE_SESSIONS /u01/check_logs/daily_trend.csv积累一个月之后用 Excel 或者 Python 画个趋势线表空间什么时候会满就能提前算出来了。这种预判式巡检比事后告警高到不知道哪里去了。5.3 Python 巡检脚本和连接 Oracle 的注意点有些场景下shell 处理不了复杂逻辑比如要跨多个数据库实例汇总巡检结果、要对接内部工单系统、要做定时报告推送。这时候用 Python 写巡检脚本就顺理成章了。Python 连接 Oracle 最常用的是python-oracledb新版官方推荐或者老的cx_Oracle。连接前第一件事确保 Python 环境的位数和 Oracle Client 位数一致32 位和 64 位混用是最常见的坑。另外如果在服务器本机跑直接用dsn cx_Oracle.makedsn(host, port, service_name)比SID方式更稳因为很多 RAC 环境用 service_name 才有负载均衡。import oracledb # 直连模式无需安装 Oracle Client新版 python-oracledb connection oracledb.connect(usersystem, password***, dsnhost:1521/service_name) cursor connection.cursor() cursor.execute(SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) FROM dba_data_files GROUP BY tablespace_name) for row in cursor.fetchall(): print(row)小技巧巡检脚本连接数据库建议用只读权限的巡检专用账号别直接用system或sys。巡检账号只需要CONNECT 查询DBA_*视图的权限。另外所有巡检脚本尽量用SET TIMING ON看每条 SQL 的执行耗时巡检脚本本身也要高效否则可能比业务 SQL 还拖累系统。到了这个阶段你的巡检其实已经具备半自动化能力了定时跑、阈值告警、趋势分析、多实例汇总。再往上是对接统一的监控平台和工单闭环但那些对很多团队来说并不是必需的。能稳定、持续、可追溯地跑下去的巡检脚本比一坨炫酷但没人维护的监控平台实用得多。自己运维久了最大的体会是Oracle 这套东西常用的知识点翻来覆去就那么些但每个细节在不同的业务负载下会有完全不同的表现。巡检脚本的价值不在于帮我发现了这个故障而在于帮你积累了环境的历史基线。有了基线和趋势你才能在故障发生前介入而不是每次都当救火队长。这大概就是运维从被动响应转向主动预防最实在的一步。