一、问题背景：不寻常的 CPU 告警
近日，一位在医院工作的朋友找到我，说他们核心 HIS 系统的 CPU 使用率突然攀升至 40%，而历史水平一直在 20%左右，希望我能帮忙排查。凭借经验，我判断这很可能是一个典型的 SQL 性能问题。
果不其然，从分析到解决，整个过程不到 10 分钟。本文将完整复盘我的分析思路，希望能为大家提供一个高 CPU 消耗场景下的性能优化实战参考。
二、性能诊断
2.1 定位 CPU 消耗来源
接到问题后，我首先请朋友帮忙执行top命令，获取 CPU 使用率的详细分解。单纯一个“40%”的指标过于笼统，我们需要深入分析 CPU 时间的具体去向。
Cpu(s): 45.3%us, 2.5%sy, 0.0%ni, 50.8%id, 1.1%wa, 0.0%hi, 0.3%si, 0.0%st
复制代码
我们重点关注以下三项：
us (user space)：用户空间程序占用的 CPU 百分比。在我们的场景中，这主要指向 Oracle 数据库进程。
sy (system space)：内核空间占用的 CPU 百分比，通常为操作系统内核、驱动等消耗。
wa (I/O wait)：CPU 等待 I/O 操作完成的时间百分比。
从top的输出可以看到，用户空间（us）占用了高达 45.3%的 CPU，而系统内核（sy）和 IO 等待（wa）的占比都非常低。这清晰地表明：系统的 IO 性能没有瓶颈，问题根源在于 Oracle 数据库自身消耗了过多的 CPU 资源。
那么，什么情况下 Oracle 会消耗大量 CPU 而 IO 压力不大呢？常见原因包括：
密集的内存运算：如大量的逻辑读（Logical Reads）、复杂的函数或表达式计算、高频的 Mutex/Latch 争用等。
低效的程序代码：如循环嵌套、无谓计算的 PL/SQL 或 Java 存储过程。
特定内部功能：如 Oracle 的 In-Memory (IM) Columnar Store 等。
在当前大内存服务器普及的背景下，这种“高 CPU、低 IO”的性能问题正变得越来越普遍。
2.2 锁定问题 SQL
明确了方向后，我让朋友运行诊断脚本，重点关注处于ON CPU状态的会话及其执行的 SQL。很快，我们就锁定了罪魁祸首，并通过关联v$active_session_history视图，获取了其执行计划和资源消耗情况。
****************************************************************************************PLAN STAT FROM ASH****************************************************************************************SQL_ID f0kfhaa3z2p0f, child number 0-------------------------------------SELECT ID,JK,ZJ,YWRQ,REQJSON,MESSAGEDRGS,RESPJSON,ISUPLOAD,MARK,CREATEDTIME,MODIFIEDTIME,NOTE,JKCODE FROM HT_HTZZ_HTZPWD WHERE MARK='1'AND (JKCODE = :1 AND ZJ = :2 AND ISUPLOAD = :3 )Plan hash value: 1313371775------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 117K(100)| |CPU(2)(0%) ||* 1 | TABLE ACCESS FULL| TB_HMYY_UPLOAD | 1 | 1357 | 117K (1)| 00:23:35 |CPU(92037)(100%) |------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter(("ZJ"=:2 AND "JKCODE"=:1 AND "ISUPLOAD"=:3 AND "MARK"='1'))
复制代码
执行计划一目了然：全表扫描（TABLE ACCESS FULL）。CPU(92037)(100%)指标显示，几乎 100%的成本都消耗在 CPU 上。这完美印证了我们之前的判断。
2.3 量化性能瓶颈
为了进一步确认创建索引的必要性，我们用数据说话。通过分析历史执行记录，我们得到了更精确的性能指标：
PLAN CPU ELA IO ROWS WRITE GET DISK ROWSEND_TI I NAME HASH VALUE EXEC PRE EXEC PRE EXEC PER EXEC PRE EXEC PER EXEC PRE EXEC PRE EXEC PRE FETC------ - --------------- ------------- ---------- -------- -------- -------- -------- -------- -------- -------- --------16 09 1 HTZZ 1313371775 30 49.92s 50.09s 4.9ms .97 0 45.13W 13.3 116 10 1 HTZZ 1313371775 30 51.45s 51.53s .07ms .97 0 45.13W .17 116 10 1 HTZZ 1313371775 30 52.28s 53.37s 56.32ms 1 0 45.13W 138.93 1
复制代码
关键数据解读：
ROWS PRE EXEC (每次执行返回行数)：接近 1。说明该查询非常高效，每次只返回极少数记录。
GET PRE EXEC (每次执行逻辑读)：高达 45 万。为了找出 1 行数据，却扫描了 45 万个数据块，这是典型的低效查询。
CPU PRE EXEC (每次执行 CPU 耗时)：约 50 秒。巨大的 CPU 消耗完全源于海量的逻辑读。
数据不会说谎，全表扫描导致了“高逻辑读、低返回行”的性能灾难，创建索引势在必行。
2.4 选择索引列
那么，应该在哪一列上创建索引呢？WHERE子句涉及JKCODE, ZJ, ISUPLOAD, MARK四列。我们通过查询数据字典来分析这些列的选择性（selectivity）。
COLUMN NUM NUM AVG LASTNAME NL DENSITY NULLS DISTINCT BUCK COL LEN SAMPLE_SIZE HIST ANALYZED---------------------------------------- -- ------------ ------------ -------- ----- ------------ ------------ ----- --------ID(VARCHAR2(64)) N 0 0 2264030 1 20 2,264,030 NONE 20250711JK(VARCHAR2(128)) Y 0 10 7 7 30 5,469 FREQU 20250711ZJ(VARCHAR2(128)) Y 0 0 2076160 254 46 5,469 HEIGH 20250711YWRQ(DATE(7)) Y 0 0 586496 254 8 5,469 HEIGH 20250711REQJSON(CLOB(4000)) Y 0 0 0 0 964 2,264,030 NONE 20250711MESSAGEDRGS(NVARCHAR2(4000)) Y 0 0 22 1 18 2,264,030 NONE 20250711RESPJSON(CLOB(4000)) Y 0 0 0 0 244 2,264,030 NONE 20250711ISUPLOAD(CHAR(1)) Y 0 0 2 2 2 5,469 FREQU 20250711
复制代码
NUM DISTINCT列显示了每列的唯一值数量。可以看到，ZJ列的唯一值数量（2,076,160）非常接近表的总行数（2,264,030），具有极高的选择性。因此，在ZJ列上创建索引是最佳选择。
三、解决方案：在线创建索引
考虑到这是在线业务系统，为避免影响正常运行，我们采用ONLINE方式创建索引。
create index hrip.ind_HT_HTZZ_HTZPWD_1 on hrip.HT_HTZZ_HTZPWD (ZJ) online parallel 10 tablespace HTZZ;alter index hrip.ind_HT_HTZZ_HTZPWD_1 noparallel;
复制代码
四、总结与反思
索引创建后，效果立竿见影。系统 CPU 使用率迅速回落至正常水平。
Cpu(s): 27.1%us, 2.7%sy, 0.0%ni, 68.8%id, 1.2%wa, 0.0%hi, 0.2%si, 0.0%st
复制代码
经了解，这两条问题 SQL 都源于一个新上线的业务模块。这次“小事故”也暴露了一个普遍存在于许多企业的典型问题：业务上线前缺乏充分的性能测试和 SQL 审核。
这个案例虽然简单，但其反映的问题却值得我们深思。在此，我提出几点建议，希望能引起开发者、DBA 和项目管理者的重视：
建立 SQL 审核制度：任何新功能或 SQL 变更上线前，都应由 DBA 或资深开发人员进行审核（Code Review）。重点关注查询是否使用了合适的索引、是否存在潜在的全表扫描、以及连接逻辑是否最优。
性能测试左移：不要把性能测试推到上线前的最后一环。开发人员在开发阶段就应该关注 SQL 性能，利用EXPLAIN PLAN分析执行计划，并在开发库中进行小规模的压力测试。
强化“数据导向”的优化思维：性能优化不能仅凭感觉。要善于利用数据库提供的性能视图（如 AWR, ASH）和诊断工具，用数据定位瓶颈，用数据验证优化效果。
培养开发人员的数据库意识：开发人员是 SQL 的生产者，他们的代码质量直接决定了数据库的健康状况。企业应定期组织培训，提升开发团队的数据库基础知识，让他们理解索引原理、执行计划、事务隔离等核心概念。
幸运的是，强大的硬件和稳健的 Oracle 数据库为许多未经严格审查的业务提供了缓冲。但技术债终有需要偿还的一天。建立规范的开发、测试和上线流程，才是保障系统长期稳定、高效运行的根本之道。
文章转载自：认真就输
原文链接：https://www.cnblogs.com/www-htz-pw/p/18996832/xing-neng-you-hua-liang-tiaosql-suo-yin-you-huacpu
体验地址：http://www.jnpfsoft.com/?from=001YH
评论