1. 项目背景与瓶颈诊断1.1 业务现状与核心痛点接手这个项目的时候第一感觉就是“再不动刀子系统就要出大事了”。市中心医院规模不算小开放床位接近1500张日均门急诊量在6000人次左右住院在院人数常年维持在3000上下。HIS系统承载的业务包括门急诊挂号收费、医生站、护士站、药房药库、检验检查申请与报告回传、手术麻醉计费等几乎全院所有流程都跑在同一个数据库上。数据库用的是Oracle 19c RAC两节点硬件并不差每节点32核、512GB内存后端是全闪存储网络也是万兆内网。可业务高峰期一到整个数据库就像被堵死了一样CPU使用率持续在95%以上活动会话数经常冲到200多慢查询日志每半小时就能刷出上千条门诊收费窗口经常出现事务超时患者明明卡里有余额缴费却要等十几秒医生开完医嘱后保存也要转圈。更麻烦的是这个系统的慢不仅影响门诊连病区的护士执行医嘱、药房发药都跟着卡甚至影响到了检验设备的结果回传。这种场景很多人应该不陌生。HIS系统属于典型的高并发OLTP系统但同时又夹杂着大量报表查询和统计类请求业务特征非常复杂。高峰期并发起来以后数据库的等待事件、锁竞争、SQL执行计划、I/O能力都会暴露问题。这次优化的核心关键词就是“数据库优化”所有动作都围绕HIS系统中最影响业务的关键路径展开。1.2 初步诊断是SQL问题还是架构问题拿到问题后我没有马上去改参数先做了一轮数据库体检。通过Oracle的AWR报告和ASH分析发现Top等待事件集中在三类db file sequential read典型的单块读等待说明有大量SQL在走索引后还要反复回表或者执行计划里出现了低效的索引访问路径。enq: TX - row lock contention行锁等待比较多也就是说有不少业务的事务开得太长持锁时间久导致其他会话被卡住。log file sync日志文件同步等待偏高说明提交频繁但日志缓冲区或磁盘I/O响应慢当然这里因为全闪存储更多是应用频繁提交带来的问题。看到这个组合我心里基本有数了这不是单纯硬件不够而是数据库内部“排队”太多。就好比医院挂号窗口不是窗口太少而是队伍排队效率太低一部分人反复来回跑一部分人占着窗口不走。当时我的判断是问题的核心在于三层一是应用发起的SQL质量差大量全表扫描、嵌套循环、重复子查询二是索引设计不合理导致执行计划走了次优路径三是事务控制不严格长事务和锁等待相互叠加。所以优化方案也分成三层来打先抓TOP SQL再调整索引和统计信息最后优化事务和参数。这比直接加硬件、加CPU核数要有效得多后面我会详细拆解每一步的做法。2. 优化方案设计与技术选型2.1 定位慢查询与TOP SQL优化没有数据支撑就是瞎调。我先从AWR中提取了Top SQL by Elapsed Time同时用Oracle的v$SQL视图做了实时抓取专门看那些逻辑读特别高的语句。HIS系统这类业务中一个常见坏味道就是动态拼接SQL比如前端选了不同查询条件后台拼出不同SQL导致变量值不同每次硬解析再加上统计信息不准确执行计划直接跑偏。我给大家一个简单实用的SQL模板用来定位一段时间内的高负载语句SELECT sql_id, executions, elapsed_time/1000000 AS elapsed_sec, cpu_time/1000000 AS cpu_sec, buffer_gets, disk_reads, sql_text FROM v$sql WHERE elapsed_time/1000000 10 AND parsing_schema_name HIS ORDER BY elapsed_time/1000000 DESC FETCH FIRST 20 ROWS ONLY;通过这轮抓取典型问题很快浮出水面。排名第一的是一条门诊费用汇总查询单次执行逻辑读超过800万次执行时间平均在4.5秒高峰期一并发就是几百个会话同时在跑。还有一条住院医嘱分页查询因为缺失合适的索引每执行一次都要扫描上百万行的历史医嘱表响应时间在2秒到3秒之间徘徊。这些SQL不解决后面再怎么调参数都是白搭。我建议所有做HIS数据库优化的人第一步必须把慢查询清单和业务模块对应起来。不要只看AWR里的平均值要结合ASH看具体是哪个时段、哪个窗口、哪个模块触发的。医院的业务高峰很规律上午8点到11点是第一波下午2点到4点是第二波在这些时间段抓到的SQL才是最有价值的。2.2 索引优化与统计信息更新策略定位到SQL之后下一步就是看执行计划。常见的问题包括核心大表上没有过滤性好的索引导致全表扫描复合索引的字段顺序不对让索引失效索引建了但应用查询条件的写法不匹配比如在索引列上使用了函数隐式转换或者like %xxx%统计信息长期不更新Oracle优化器拿到的是几个月前的行数自然选择错误执行计划。我举一个非常典型的例子某门诊挂号记录表outp_register日增数据量大概在5万行左右总行数超过8000万需要按“就诊卡号 就诊日期”查询患者在某一段时间的挂号记录。原表只有主键索引查询条件里还有一个状态字段。之前执行计划全表扫描单次逻辑读高达几百万。后来我建了这样一个复合索引CREATE INDEX idx_outp_reg_card_date ON outp_register(card_no, visit_date, status) TABLESPACE HIS_INDEX;为什么字段顺序这么排因为在业务中card_no是等值过滤visit_date是范围条件status是后续过滤条件。等值条件的列放最前面范围条件放第二个最后才是辅助过滤列这是复合索引设计的基本原则。加了索引后逻辑读从几百万降到了几千查询响应时间直接降到了100毫秒以内。统计信息方面HIS系统很多大表是每天大量插入的但更新量不稳定单靠每天的任务不够。我当时的策略是对核心大表每周做一次全量统计信息收集对数据量大且变化频繁的表使用DBMS_STATS.GATHER_TABLE_STATS并指定granularity AUTO。重点注意一个参数EXEC DBMS_STATS.GATHER_TABLE_STATS( ownname HIS, tabname OUTP_REGISTER, estimate_percent DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt FOR ALL COLUMNS SIZE AUTO, cascade TRUE, no_invalidate FALSE );no_invalidate FALSE意味着收集后立即让当前执行的游标失效并重新解析确保新计划生效。但如果是在业务高峰期做要特别小心因为这个操作可能导致大批SQL执行计划同时变化引发短时间性能抖动。所以我安排在了周六凌晨的维护窗口来做。2.3 数据库参数与事务锁优化在SQL和索引调优进入正轨后我又对数据库参数做了几项针对性调整。先说明一点Oracle的参数不是越多越好尤其不能照搬“网上参数大全”每个系统都有自己的负载特征。我调整了几个关键参数session_cached_cursors从默认的50提高到300减少游标反复打开关闭带来的开销对HIS这种大量短事务、重复SQL的系统效果明显。open_cursors从300提高到1000避免应用并发高时出现游标耗尽。sga_target保持自动管理但将db_cache_size的最小值加大确保核心业务表的数据块尽可能驻留在buffer cache中减少物理读。log_buffer从默认的16MB调整到32MB配合log file sync等待的下降。事务锁的问题更值得说。enq: TX - row lock contention的根源往往是应用层开启事务后执行了若干条DML但长时间不提交尤其是一些医生站操作开完医嘱后弹窗让医生确认确认期间事务一直开着锁。这时候并发一旦上来后面等待的会话就会堆积。我的处理方式分两步。第一步从数据库层面入手通过监控v$lock找高阻塞会话把持锁超过一定时间的的会话杀掉应急然后推动应用方修改代码逻辑把大事务拆成小事务争取秒级提交。第二步是调整初始化参数_dml_monitoring_enabled那些不靠谱的隐含参数我不建议动我当时只做了标准的锁监控优化。说到底数据库参数能解决性能瓶颈的20%剩下80%要靠SQL和事务。3. 核心优化实操与效果验证3.1 典型慢SQL重构实例这里我挑两个最有代表性的SQL重写案例大家可以直观感受一下优化前后的差距。第一个是前面提到的门诊费用汇总查询。原SQL大致逻辑是这样的做了脱敏处理SELECT a.patient_id, a.visit_id, a.total_amount, (SELECT SUM(fee) FROM outp_fee_detail f WHERE f.patient_id a.patient_id AND f.visit_id a.visit_id) AS detail_fee, ... FROM outp_register a WHERE a.visit_date TRUNC(SYSDATE) - 7 AND a.status 0;问题很明显每条主表记录都要执行一次子查询主表数据量一大子查询的执行次数就是千万级别这种写法叫filter操作很容易把数据库拖垮。我把它重写成了基于outp_fee_detail的聚合关联SELECT r.patient_id, r.visit_id, r.total_amount, d.detail_fee FROM outp_register r LEFT JOIN ( SELECT patient_id, visit_id, SUM(fee) AS detail_fee FROM outp_fee_detail WHERE visit_date TRUNC(SYSDATE) - 7 GROUP BY patient_id, visit_id ) d ON d.patient_id r.patient_id AND d.visit_id r.visit_id WHERE r.visit_date TRUNC(SYSDATE) - 7 AND r.status 0;改完之后原来嵌套循环的逐条过滤变成了哈希连接聚合先做一次再关联一次SQL耗时从平均4.5秒降到了150毫秒左右逻辑读下降了99%。这类重写的本质是让数据库一次扫描完成汇总而不是反复访问表。第二个是住院医嘱分页查询。原SQL是WHERE rownum ?和ORDER BY混用导致排序全表扫描。优化方式是新建了支持排序的索引并把分页改写为标准的OFFSET ... FETCH NEXT写法Oracle 12c支持配合二级索引。改写后耗时从2秒下降到200毫秒。通过这两个案例我想表达一个观点HIS系统慢SQL的重写重点不在“炫技”而在于理顺业务逻辑和数据访问路径。很多报表类SQL是开发人员图省事用子查询拼出来的在数据量小时问题不大数据量破千万后就原形毕露。我们优化的原则是能一次扫描完成的绝不扫两次能提前过滤的绝不放最后过滤。3.2 分区表改造与归档策略HIS里增长最快的表往往就是业务流水表比如门诊费用明细、住院费用明细、医嘱执行记录、检验报告表。这些表每天几十万行增长如果不做分区三五年后即使有索引数据块数量也足够让范围查询变慢。这次我们对几张核心大表做了范围分区按月份分区。以门诊费用明细表为例CREATE TABLE outp_fee_detail_new ( patient_id NUMBER NOT NULL, visit_id NUMBER NOT NULL, item_id NUMBER, fee NUMBER(10,2), create_time DATE NOT NULL, ... ) PARTITION BY RANGE (create_time) INTERVAL(NUMTOYMINTERVAL(1, MONTH)) ( PARTITION p_202301 VALUES LESS THAN (TO_DATE(2023-02-01,YYYY-MM-DD)), PARTITION p_202302 VALUES LESS THAN (TO_DATE(2023-03-01,YYYY-MM-DD)) );INTERVAL分区的好处是未来每个月的数据会自动创建新分区不需要人工维护。改造完成后所有按时间范围做的查询Oracle会自动进行分区裁剪只扫描对应月份的分区扫描量直接缩减到原来的几十分之一。当然分区表改造有个大坑如果原表上的索引是普通全局索引分区裁剪的效果会被削弱因为采用了全局索引后会先访问索引再回表不一定能有效裁剪。所以我在分区后把核心查询涉及的索引全部改成了LOCAL索引同时保留极少数的全局索引用于唯一性约束。这个决策需要在测试环境完整压测验证不能想当然。归档策略上我设置了一个保留规则在线库保留最近24个月的数据超过24个月的流水按月迁移到独立的历史库。迁移使用DBMS_PARALLEL_EXECUTE分批跑凌晨执行不影响业务。很多医院会担心历史数据查询所以我们把历史库做成只读库保留同样的表结构应用侧通过视图或路由切换去查询。既保证了在线库性能又不违背医疗数据长期保存的合规要求。3.3 优化后的压测与监控优化不是改完SQL就结束必须用数据说话。我们在测试环境搭了一套同样的数据库结构导入了线上脱敏数据用并发脚本模拟了300个用户同时操作挂号、收费、开医嘱、查报告。压测结果对比很直观指标优化前优化后CPU使用率高峰95%以上35%-45%门诊收费响应时间P993.8秒480毫秒医嘱保存响应时间P992.6秒320毫秒活动会话数高峰20060左右慢查询数量高峰期半小时120020以内上线后我又部署了监控告警数据库层面用OEM监控等待事件和顶层SQL主机层面用Prometheus Grafana采集CPU、内存、I/O。设置了三层告警阈值CPU超过85%持续5分钟告警活动会话超过150告警慢查询超过50条告警。一旦出现异常DB组能在5分钟内收到通知不用等业务部门反馈。这个过程中我觉得最值的还是把TOP SQL巡检做成日常机制每周跑一次发现新出现的高消耗SQL就找开发负责人确认。数据库优化不是一次性的尤其HIS系统业务部门会不断加需求新功能上线必然伴随新SQL如果缺少持续监控问题迟早会反弹。4. 常见问题与排查技巧实录4.1 优化过程中踩过的坑这轮优化我和团队走了不少弯路有些坑值得单独记录下来。第一个坑刚开始接到高CPU告警时我先改了一大堆参数结果收效甚微。后来才意识到参数调整只是辅助SQL不优化CPU永远降不下来。建议大家在定位到明确的SQL问题之前不要动全局参数尤其是db_block_multiblock_read_count、optimizer_index_cost_adj这些影响执行计划选择的参数改错了一个计划变更就可能让整个系统雪上加霜。第二个坑收集统计信息安排不当。我在周三业务低谷做过一次全量统计信息收集结果有个查药品库存的SQL因为统计信息变化执行计划从大表hash join变成了嵌套循环瞬间把两个节点的CPU打满。之后我就养成了习惯生产环境收集统计信息前先做单SQL执行计划基线分析重要会话全部在线监控一旦发现异常立即回退。统计信息本身不是问题问题是你的变更窗口和回退预案要做好。第三个坑分区表改造后忘了重建索引类型。我们一开始只做了分区没有把相关索引改成local结果发现一个按月份查询的SQL执行时间还是2秒。原因就是全局索引把查询路径带偏了分区裁剪没有生效。排查了很久才找到。对于分区表不仅要看分区键是否正确还要看索引策略是否匹配分区策略。第四个坑也是老生常谈的长事务杀错了。有一次客服反馈说系统特别卡我定位到一条长时间未提交的锁直接把会话杀了。结果杀完之后应用报错护士站的发药单数据丢失了一部分。原因是那条事务包含了一个前端页面打开很久才确认的逻辑杀会话等于回滚了整个操作。所以处理锁等待一定要先跟业务确认能平安度过高峰就先度过去不到万不得已不要直接kill宁可先加监控观察。4.2 给其他医院或厂商的参考建议经过这个项目我整理了五条可以复用的经验供正在做HIS数据库优化的人参考第一建立SQL上线审核机制。HIS系统往往背靠多个厂商一个门诊医生站可能就有三四个版本的软件在跑开发出来的SQL风格差异很大。必须要求所有新增的查询、报表类SQL在发布前经过DBA审核重点看执行计划有没有全表扫描、嵌套循环、笛卡尔积。哪怕只是初步的人工检查也能避免大量问题SQL进入生产环境。第二定期做数据库健康检查建议每季度一次。检查内容不用太复杂AWR比对、慢SQL趋势、锁等待次数、表空间增长趋势、归档日志空间这五项足够发现90%的隐患。不要等到系统卡到没法用才介入。第三重视应用层事务设计。很多锁问题不是数据库造成的是开发人员把事务范围写得过大比如在一次事务里同时更新门诊表、费用表、库存表加上远程调用事务开了几秒不提交。这种问题只能靠代码评审解决。技术上可以用V$SESSION查看LAST_CALL_ET找长时间未提交的会话再反向定位到代码模块。第四做好历史数据分离的规划。HIS数据库的数据增长是持续的靠加存储、分区只是延缓问题长远的做法是建历史库实现在线数据的瘦身。很多医院担心历史库查询麻烦其实可以做应用透明应用连接读写分离查询超过一年用历史库的只读视图代码改动量并不大。第五监控告警一定要做而且要设对阈值。不要等业务人员发现卡顿再查问题那已经是故障级响应。我在这个项目里最满意的不是优化了多少条SQL而是建立了一套能让问题“提前暴露”的机制。根据我的个人体会医院HIS数据库优化跟普通互联网系统有一个明显差异医院的业务连续性要求极高白天任何一秒都不能随便停。你必须在凌晨窗口完成所有变更白天只做监控和应急。很多数据库上的“神操作”在医院环境里根本不敢用稳妥和安全比任何炫技都重要。这轮优化下来高峰期的各项指标都有了很大改善但真正让团队放心的是后续这套监控和审核机制一直坚持运行。如果你也在处理类似系统我的建议是先把业务摸透再用数据定位最后小步快跑地改每一步都要有回退方案。