性能优化：两条SQL索引优化，CPU占用率从40%降至25%
我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！由于博客中有大量代码，通过页面浏览效果更佳。
一、问题背景：不寻常的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_IDf0kfhaa3z2p0f, child number 0
-------------------------------------
SELECTID,JK,ZJ,YWRQ,REQJSON,MESSAGEDRGS,RESPJSON,ISUPLOAD,MARK,CREATED
TIME,MODIFIEDTIME,NOTE,JKCODEFROM HT_HTZZ_HTZPWDWHERE MARK='1'
AND (JKCODE = :1AND ZJ = :2AND 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 ROWS
END_TI I NAME HASH VALUE EXEC PRE EXEC PRE EXEC PER EXEC PRE EXEC PER EXEC PRE EXEC PRE EXEC PRE FETC
------ - --------------- ------------- ---------- -------- -------- -------- -------- -------- -------- -------- --------
16 091 HTZZ 1313371775 30 49.92s 50.09s 4.9ms .97 0 45.13W 13.3 1
16 101 HTZZ 1313371775 30 51.45s 51.53s .07ms .97 0 45.13W .17 1
16 101 HTZZ 1313371775 30 52.28s 53.37s 56.32ms1 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 LAST
NAME NL DENSITY NULLS DISTINCTBUCK COL LENSAMPLE_SIZE HISTANALYZED
---------------------------------------- -- ------------ ------------ -------- ----- ------------ ------------ ----- --------
ID(VARCHAR2(64)) N 0 02264030 1 20 2,264,030 NONE20250711
JK(VARCHAR2(128)) Y 0 10 7 7 30 5,469 FREQU 20250711
ZJ(VARCHAR2(128)) Y 0 02076160 254 46 5,469 HEIGH 20250711
YWRQ(DATE(7)) Y 0 0 586496 254 8 5,469 HEIGH 20250711
REQJSON(CLOB(4000)) Y 0 0 0 0 964 2,264,030 NONE20250711
MESSAGEDRGS(NVARCHAR2(4000)) Y 0 0 22 1 18 2,264,030 NONE20250711
RESPJSON(CLOB(4000)) Y 0 0 0 0 244 2,264,030 NONE20250711
ISUPLOAD(CHAR(1)) Y 0 0 2 2 2 5,469 FREQU 20250711NUM 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数据库为许多未经严格审查的业务提供了缓冲。但技术债终有需要偿还的一天。建立规范的开发、测试和上线流程，才是保障系统长期稳定、高效运行的根本之道。
------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
页:
[1]