HIS系统Oracle数据库性能优化实战：AWR诊断、SQL改写与索引重构全复盘

去年接了一个有点典型的活儿——给某市中心医院的HIS系统做数据库性能优化。这个系统上线跑了差不多六年，业务高峰期的卡顿已经严重到门诊护士想摔鼠标、收费窗口排队排到大厅的程度。对于搞数据库的人来说，HIS系统大概是所有OLTP系统里最“拧巴”的一种：既要承担门急诊挂号、收费、医嘱、检验、药房这些高并发短事务，又要兼顾住院全流程的长事务和大量报表查询，数据模型设计还带着十几年前业务逻辑的烙印。这篇文章就把这次优化从问题诊断、方案设计到落地实操的完整过程做个复盘，把踩过的坑和关键技巧都摊开讲，给做医疗信息化、Oracle数据库运维或者正在被系统卡顿折磨的朋友们一个真实可参考的案例。

1. 项目背景与问题诊断

1.1 HIS系统现状与卡顿表现

这个医院的HIS系统涵盖门急诊、住院、药房药库、检验检查、手术麻醉等二十多个模块，日门诊量峰值在七千到八千人次。数据库用的Oracle 11g R2，跑在两台物理服务器上（RHEL 6.8，32核CPU，256G内存），存储是集中式SAN，主库没有做RAC，就是单实例架构。应用端是典型的C/S架构加少量Web服务，中间层连接池配得比较激进，高峰期活跃会话能冲到五六百。

最明显的故障窗口集中在上午九点半到十一点半，下午两点半到四点半。具体表现就是：挂号收费页面转圈卡死、门诊医生站开医嘱保存等响应、检验报告查询直接超时。数据库主机CPU在高峰期几乎顶着99%，load average飙到60多，磁盘IO队列平均在80以上，log file sync等待非常严重。更要命的是，这个问题不是突然出现的，而是最近两个月逐步恶化，应用代码也没改过，业务量增幅也不至于这么大，明显是数据库内部什么地方积累出了问题。

我接手后的第一反应没有去看SQL，而是先拉着早上高峰期的AWR和ASH报告，把系统的“体检报告”拿到手再说。因为HIS这种7×24小时业务系统，你不可能在生产上瞎试，必须先找准病灶才能动刀。

1.2 AWR与ASH报告里的关键线索

AWR报告是Oracle自带的性能基线报告，我一般习惯保存两个时间点的快照：一个高峰期，一个非高峰期，方便对比。生成方式是执行脚本，使用awrrpt.sql，或者直接用DBMS_WORKLOAD_REPOSITORY包手动打快照：

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); -- 过一小时后再次执行，然后运行 @?/rdbms/admin/awrrpt.sql

生成报告后重点看三块：Top 10 Foreground Events by Total Wait Time、SQL ordered by Elapsed Time、以及Instance Activity。

这次的AWR结果让我很意外，Top事件前三名分别是：

• db file sequential read：占了将近41%的DB Time
• log file sync：占了约22%的DB Time
• latch: cache buffers chains：占了约9%

这三项加起来超过70%。db file sequential read通常是单块读，说明大量索引回表或随机IO；log file sync高是因为提交频繁且redo写入慢；latch争用则指向buffer cache里的热点块竞争。而SQL ordered by Elapsed Time里，前10条SQL的逻辑读加起来占了全库逻辑读的58%，其中两条特别扎眼——一条是门诊收费结算时查药品库存批次的，另一条是住院发药后更新库存余额的。

ASH报告（v$active_session_history）进一步把问题指向了几张核心大表：PRESCRIPTION_DETAIL（处方明细，约2180万行）、DRUG_BATCH（药品批次，约430万行）、PATIENT_VISIT（就诊记录，约860万行）。这三张表在高峰期的会话等待事件高度集中在db file sequential read上，而且执行计划里出现了大量的全表扫描和代价极高的嵌套循环。

1.3 根因三层拆解

拿到这些线索后，我没有急着改SQL，而是把问题拆成了三层来看。

第一层是SQL本身的问题。最典型的是一条药品库存批次查询，这条SQL在高峰期每次执行都要把DRUG_BATCH表全表扫一遍，逻辑读一次就跑出上百万的量级。门诊收费高峰时，药剂科每个发药操作都会触发类似查询，一百多个并发会话同时跑这种SQL，CPU和IO直接被吃满。

第二层是索引设计严重滞后。数据库里相当一部分索引还是系统上线时建的，但六年里业务加了“医保类型”、“就诊卡号”、“发药状态”这些查询条件，旧索引根本覆盖不了新的访问路径。更离谱的是有些大表上的索引因为长时间没有维护，BLEVEL超过了4，聚簇因子高达七八百万，CBO优化器算来算去发现还不如全表扫描来得“便宜”。

第三层是数据库内存参数和日志配置存在明显短板。SHARED_POOL只配了4G，但HIS系统里大量使用字面量SQL，没有全面绑定变量，导致高并发下硬解析和库缓存争用严重；LOG_BUFFER只有4MB，而高峰期每秒事务提交量极大，redo写盘跟不上，log file sync等待自然就上去了。

三层问题叠加，最终表现就是系统整体性卡顿。这不是单一故障，靠重启实例或者清缓存根本解决不了。

1.4 优化目标与硬约束

这次优化不是从零开始做设计，是在一个“跑了好几年、不敢随便大改”的生产系统上做手术，所以目标必须务实：

• 高峰期CPU占用从99%压到50%以下
• 门诊收费核心操作的响应时间从平均5秒压到1秒以内
• 查询类SQL响应控制在2秒以内
• 不能改应用代码包，因为医院信息科的开发资源有限，并且改动客户端程序需要走很长的测试流程
• 停机窗口只有周六凌晨4小时
• 不能换硬件、不能迁移数据库版本

这些约束决定了我后面所有的方案选型都要围绕“低风险、短窗口、可回退”来设计，不能玩花的。

2. 优化方案设计与技术选型

2.1 三条路线的取舍

面对这种“病入膏肓”的生产系统，业内通常有三条路线：

第一条是扩容和迁移。加CPU、加内存、换全闪存储，甚至把Oracle单实例改成RAC。这条路当然有效，但医院这边预算审批周期长，而且单实例改RAC涉及的架构变更和停机时间远超4小时，等于要让全院系统停摆大半天，这不可能接受。

第二条是应用层重构。把C/S架构逐步改成B/S，或者把高频查询改成走缓存中间件。这个方向长期看是对的，但短期内无法落地，而且HIS系统经过多年二次开发，很多存储过程和业务逻辑盘根错节，动应用等于动业务。

第三条就是我最常走的路线：数据库内部优化。通过SQL改写、索引重构、分区调整、参数微调这四个组合拳，在不动架构、不改代码包的前提下把性能榨出来。这条路线的优势是风险可控、见效快，每一步都可以单独回退。缺点是对DBA的水平要求比较高，因为数据库层优化的每一步都可能牵动执行计划的变化，需要持续观察。

最终我选了第三条，但配套加了一个“监控先行”策略：在优化前先建立一套完整的性能基线和监控手段，任何一步改动都必须有数据支撑，而不是拍脑袋。

2.2 分阶段推进：先止血，再治理，后保健

我把整个优化分成三个阶段，每个阶段都有明确的交付物。

第一阶段是“止血”。主要工作包括采集高峰期基线数据、锁定Top SQL、分析执行计划、把最烂的几条SQL改写掉。这个阶段的目标是在不动大量索引的情况下先缓解最主要的性能压力。

第二阶段是“治理”。针对核心大表做索引重建和补充，针对历史数据堆积严重的单表做分区拆分，同时优化关键内存参数。这个阶段需要停机窗口，是做物理结构变更的最佳时机。

第三阶段是“保健”。上线后持续监控一周，对比各项指标，建立定期巡检机制，把优化成果固化下来，防止新SQL把系统打回原形。

这个思路借鉴了“先救火、再排查火灾原因、最后建立防火制度”的逻辑，处理生产事故时特别管用。如果一上来就大动干戈做分区、重建索引，很可能在第一阶段就把自己绕进去，出了问题都不知道是哪一步引起的。

2.3 工具选型与监控方案

Oracle生态里好用的性能分析工具其实不少，但生产环境里我习惯尽量用Oracle自带的工具，因为兼容性和安全性最有保障。

用得最多的有四个：

• AWR/ASH：看全局负载和会话等待
• SQLT（SQL Test Case Builder）：分析单条SQL的执行计划和绑定变量情况
• dbms_xplan：查看实际执行计划
• v$sql / v$sql_plan / v$sqlstats：追踪SQL的累计消耗和计划变化

另外我还会配合10053事件看CBO优化器的成本计算过程，不过这个只建议在测试环境用，生产上开10053 trace会带来额外开销。SQLT这个工具可能很多DBA不常用，但对分析特定SQL非常方便，它可以打包SQL的全部信息，生成一份详细的诊断报告，包括执行计划、统计信息、绑定变量、对象状态等，省去了手工查询几十张视图的麻烦。

监控方面，因为医院没有部署完整的企业级监控平台，我选择用历史AWR对比+自定义SQL巡检脚本的方式来做轻量级监控。关键指标包括：DB Time、Top等待事件、逻辑读总量、活跃会话数、Top SQL的逻辑读变化。这些指标每周拉一次，就能快速判断系统是否在“变坏”。

3. 实操过程与关键步骤

3.1 基线建立：没有数据就没有发言权

所有优化动作之前，第一步是建立基线。我在一个周四的工作日采集了完整的AWR快照，覆盖早上高峰（9:00-11:30）和下午平峰（14:00-15:00），然后整理出下面的关键基线指标：

建立基线这一步千万不要偷懒。原因很简单：如果没有优化前的数据，你做完任何改动都无法量化收益。而且基线数据也是后续和医院信息科、管理层汇报优化效果的最有力依据。我当时把这些数字整理成了一张表，后面每次周报更新一版，效果一目了然。

采集完成后，我用下面这段SQL把所有“嫌疑SQL”按逻辑读排序拉出来：

SELECT sql_id, executions, ROUND(elapsed_time/1000000,2) elapsed_sec, ROUND(cpu_time/1000000,2) cpu_sec, ROUND(buffer_gets/1000,1) buffer_gets_k, ROUND(disk_reads/1000,1) disk_reads_k, ROUND(rows_processed,0) rows_proc FROM v$sqlstats WHERE last_active_time > SYSDATE - 7 ORDER BY buffer_gets DESC FETCH FIRST 20 ROWS ONLY;

拿到SQL_ID后，再用dbms_xplan显示实际执行计划：

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => 'xxxxxxx', format => 'ALLSTATS LAST'));

3.2 核心慢SQL改写实例

这次优化最出效果的SQL改写来自DRUG_BATCH（药品批次表）的一条查询。这条SQL原本是这样写的：

-- 优化前 SELECT b.batch_no, b.batch_qty, b.expire_date FROM drug_batch b WHERE b.drug_code = :drug_code AND b.batch_no NOT IN ( SELECT c.batch_no FROM drug_checkout c WHERE c.drug_code = :drug_code AND c.checkout_time >= TRUNC(SYSDATE) ) AND b.batch_qty > 0 ORDER BY b.expire_date;

问题一眼就能看出来：NOT IN子查询。这里有两个坑：一是如果子查询结果集很大，NOT IN很容易让优化器选择全表扫描过滤；二是如果子查询返回了NULL值，NOT IN的语义会变成结果集为空，逻辑上非常危险。这条SQL在DRUG_BATCH表2180万行数据上跑，每次都要扫全表。

改写方案是换成NOT EXISTS + 关联字段索引+ 当天的时间条件：

-- 优化后 SELECT b.batch_no, b.batch_qty, b.expire_date FROM drug_batch b WHERE b.drug_code = :drug_code AND b.batch_qty > 0 AND NOT EXISTS ( SELECT 1 FROM drug_checkout c WHERE c.batch_no = b.batch_no AND c.drug_code = b.drug_code AND c.checkout_time >= TRUNC(SYSDATE) ) ORDER BY b.expire_date;

同时我给DRUG_CHECKOUT表加了一个复合索引，顺序为(BATCH_NO, DRUG_CODE, CHECKOUT_TIME)，这样子查询里的关联条件可以直接走索引扫描，避免sort和全表扫。

改写后这SQL的逻辑读从平均18万次降到了300多次，执行时间从2.3秒降到30毫秒以内。这个效果立竿见影，因为收费高峰期这条SQL每分钟被执行上百次，优化一处，整个系统压力都松了一口。后来我又用类似的方法改写了另外三条问题SQL，比如把OR改写成UNION ALL、把大事务里的逐行UPDATE改成批量MERGE等。

注意一点，改写SQL前一定要收集真实绑定变量值，小心驱动数据分布。同样的SQL在不同药品编码下数据量差异巨大，有些药品可能只有几条批次记录，有些可能有几万条批次记录。如果只按平均值设计索引，可能造成执行计划不稳定。我的办法是先用绑定变量窥视（bind peeking）了解一下典型值分布，再决定是走索引还是走全表扫描。

3.3 索引体检与重建：老索引拖后腿

SQL改写只能解决部分问题，剩下大量SQL还是要靠索引来支撑。我先把几个核心大表的索引状态拉了一遍：

SELECT table_name, index_name, blevel, leaf_blocks, clustering_factor, last_analyzed, status FROM dba_indexes WHERE table_name IN ('PRESCRIPTION_DETAIL','DRUG_BATCH','PATIENT_VISIT') ORDER BY table_name, index_name;

结果发现三张表的索引普遍存在几个问题：一是部分索引的BLEVEL已经到了4，属于偏高的水平；二是聚簇因子特别大，比如PRESCRIPTION_DETAIL表上的IDX_ORDER_TIME，聚簇因子比表行数还大，意味着每回一次表大概率都伴随一次IO；三是有些索引列顺序不合理，比如WHERE条件里高频使用DRUG_CODE + CHECKOUT_TIME，但现有索引是CHECKOUT_TIME + DRUG_CODE，前导列用不上。

当时做了一个索引重建计划，核心操作如下：

-- 在线重建核心索引，注意业务低峰执行 ALTER INDEX IDX_DRUG_CHECKOUT_TIME REBUILD ONLINE NOLOGGING; ALTER INDEX IDX_PRESC_ORDER_TIME REBUILD ONLINE NOLOGGING; -- 新建补充索引，优先覆盖高频查询 CREATE INDEX IDX_CHECKOUT_BATCH_DRUG ON drug_checkout(batch_no, drug_code, checkout_time) TABLESPACE TBS_IDX NOLOGGING;

这里提醒一下，索引重建时加NOLOGGING可以减少redo生成，缩短执行时间，但意味着重建过程中这部分数据没有日志保护。生产环境上这个操作在停机窗口做问题不大，如果是平时在线做，还是要慎重，因为一旦实例崩溃，索引可能需要重建。

另外索引不是越多越好。每加一个索引，DML操作都要多维护一棵B树，写放大是实打实的。我的原则是：优化热查询优先，写性能让路；能不建就不建，能合并就合并。这次一共新建了6个索引，重建了11个索引，删除2个完全用不上的冗余索引。

3.4 大表分区：向历史数据开刀

PRESCRIPTION_DETAIL表有2180万行，而且还在以每月约80万行的速度增长。大量历史数据囤在主表里，导致统计信息、索引维护、全表扫描的成本都成倍上升。这类典型的OLTP大表，最好的策略是按时间做RANGE分区。

因为不能中断业务，我采用的是在线重定义（DBMS_REDEFINITION）方式来做分区转换。这个方案可以在业务不间断的情况下把普通表转换为分区表，对HIS这种7×24小时系统非常友好。核心步骤是：

1. 创建分区中间表，按CREATE_TIME做RANGE分区，近半年的数据放在独立的“热分区”，历史数据按月分区；
2. 调用DBMS_REDEFINITION.START_REDEF_TABLE，建立映射；
3. 拷贝依赖对象、索引、约束、统计信息；
4. 调用DBMS_REDEFINITION.FINISH_REDEF_TABLE，完成切换。

分区表的核心DDL逻辑类似这样：

CREATE TABLE pres_detail_new ( ... ) PARTITION BY RANGE (create_time) ( PARTITION p_2023_03 VALUES LESS THAN (TO_DATE('2023-04-01','YYYY-MM-DD')), PARTITION p_2023_04 VALUES LESS THAN (TO_DATE('2023-05-01','YYYY-MM-DD')), ... PARTITION p_maxvalue VALUES LESS THAN (MAXVALUE) );

注意一点，分区转换过程中，所有全局索引会失效，必须重建。而且分区表上线后，后续的查询计划如果没走分区裁剪，反而可能变慢。我在转换完成后专门检查了高频SQL的执行计划，确认WHERE条件里的CREATE_TIME都能触发分区裁剪，才放心收工。

这次分区改造最直接的效果是：原来对PRESCRIPTION_DETAIL全表扫描的统计类SQL，扫描范围从全表2180万行缩小到近三个月的数据（约240万行），性能提升肉眼可见。

3.5 内存与日志参数调整

最后一步是调整数据库实例参数。结合AWR里的log file sync等待和latch争用，我决定调整以下几个参数：

LOG_BUFFER在Oracle里是静态参数，需要重启实例才能生效。于是这些调整统一放到了周六凌晨的停机窗口执行。修改前我先用SPFILE做了备份，确保可以瞬间回退：

CREATE PFILE='/u01/app/oracle/admin/orcl/pfile/pfile_backup_before_opt.ora' FROM SPFILE; ALTER SYSTEM SET log_buffer=16M SCOPE=SPFILE; ALTER SYSTEM SET shared_pool_size=8G SCOPE=SPFILE; ALTER SYSTEM SET db_cache_size=10G SCOPE=SPFILE; ALTER SYSTEM SET pga_aggregate_target=6G SCOPE=SPFILE; SHUTDOWN IMMEDIATE; STARTUP;

启动完成后我盯着AWR报告看了整整两个小时，确认没有意外情况才离开。参数调整这种事最怕的就是改完出问题说不清，所以每一次修改都留备份、留记录、留对比。

4. 常见问题与性能验证

4.1 优化效果与上线前后对比

优化后一周，我连续取了七天的高峰AWR报告，跟原来的基线做对比。各项指标变化如下：

门诊窗口的护士说“系统终于像活过来了”。这算是我等了一周才确认的“好消息”。

4.2 上线后执行计划漂移“惊魂”

优化的第二天，系统跑得好好的，但第三天早上高峰时段突然有护士反映开药变慢。我马上查AWR，发现一条之前已经改好的SQL执行计划变了，逻辑读从300次涨回8万多。查了v$sql里的PLAN_HASH_VALUE变化，发现优化器在凌晨统计信息自动收集之后，给这条SQL选择了另外一条执行路径——没有走新建的复合索引，而是去做了全表扫描。

根源在于：统计信息刚更新完，某些表的数据分布发生了变化，CBO基于新的直方图，认为全表扫描比索引扫描“更便宜”。这种情况在数据分布不均匀的HIS系统里非常常见，经典的绑定变量窥视问题。

解决办法是对这条SQL做一个SQL Profile，把执行计划固定住：

DECLARE ret VARCHAR2(100); BEGIN ret := DBMS_SQLTUNE.CREATE_SQL_PROFILE( sql_id => 'xxxxxxxxxxxx', name => 'FIX_PLAN_DRUG_CHECKOUT', force_match => TRUE ); END; /

这样设置之后，优化器会优先使用SQL Profile里绑定的执行计划，不会再因为统计信息抖动而乱跑。从那以后我又检查了其他改写过的SQL，凡是发现执行计划有波动迹象的，统统用SQL Profile绑住。这个教训很重要：生产系统优化光改SQL和加索引远远不够，执行计划的“可重复性”才是稳定性的生命线。

4.3 长期监控与预防体系

优化做完不代表一劳永逸。HIS系统每天都会产生新SQL，新业务、新统计信息、新数据分布，都可能把性能重新拖垮。我给医院信息科留了一套简单的监控思路：

每周固定跑一次AWR报告，重点看DB Time、Top等待事件、Top SQL逻辑读。只要DB Time比上周增加超过20%，或者Top 5等待事件里出现新的类型，就说明有问题要提前介入。这个习惯坚持三个月，基本能把系统的性能波动规律摸清楚。

还有两个小工具技巧特别好用。一是用索引监控来识别无效索引：

ALTER INDEX IDX_xxx MONITORING USAGE; -- 跑一周后查 v$object_usage SELECT index_name, used FROM v$object_usage;

如果某个索引监控两周都没被使用过，就可以考虑drop掉，减少DML的索引维护开销。二是定期检查大表的统计信息新鲜度，尤其是分区表，确保每次大批量数据变更后及时收集统计信息。

4.4 避坑指南速查表

这次项目里踩过的坑、见过的坑，我整理成一张速查表，供同行参考：

这条速查表我建议所有做数据库优化的人都留一份，尤其是医疗行业这种“改了就必须对、错了就要担风险”的环境，预判风险的能力永远比修问题的能力更重要。

这次项目做完之后，我最大的体会是：HIS系统的数据库优化，拼的从来不只是SQL功底，而是对业务的理解、对风险的敬畏、和一步一步用数据说话的耐心。优化过程中，信息科最担心的不是你技术行不行，而是你改完能不能让系统不出乱子。只要你把逻辑讲透、每一步都留了退路，层层验证、稳扎稳打，医院这边其实是愿意全力配合的。最后再分享一个小技巧：每次做这种大型优化前，先在测试环境把同样的SQL和索引策略跑一遍，导出执行计划截图留存，出问题的时候拿出来对比，比翻几百页AWR报告高效得多。这次优化整体耗时三周，真正动手改只花了一个停机窗口，后面系统稳定运行了半年多，没有再出现同等量级的性能问题。