性能优化：两条SQL索引优化，CPU占用率从40%降至25%

我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。

一、问题背景：不寻常的CPU告警

近日，一位在医院工作的朋友找到我，说他们核心HIS系统的CPU使用率突然攀升至40%，而历史水平一直在20%左右，希望我能帮忙排查。凭借经验，我判断这很可能是一个典型的SQL性能问题。
果不其然，从分析到解决，整个过程不到10分钟。本文将完整复盘我的分析思路，希望能为大家提供一个高CPU消耗场景下的性能优化实战参考。
二、性能诊断

2.1 定位CPU消耗来源

接到问题后，我首先请朋友帮忙执行top命令，获取CPU使用率的详细分解。单纯一个“40%”的指标过于笼统，我们需要深入分析CPU时间的具体去向。

1. 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视图，获取了其执行计划和资源消耗情况。

1. ****************************************************************************************
2. PLAN STAT FROM ASH
3. ****************************************************************************************
4. SQL_ID  f0kfhaa3z2p0f, child number 0
5. -------------------------------------
6. SELECT  ID,JK,ZJ,YWRQ,REQJSON,MESSAGEDRGS,RESPJSON,ISUPLOAD,MARK,CREATED
7. TIME,MODIFIEDTIME,NOTE,JKCODE  FROM HT_HTZZ_HTZPWD  WHERE MARK='1'
8. AND (JKCODE = :1  AND ZJ = :2  AND ISUPLOAD = :3 )
9. Plan hash value: 1313371775
10. ------------------------------------------------------------------------------------
11. | Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
12. ------------------------------------------------------------------------------------
13. |   0 | SELECT STATEMENT  |                |       |       |   117K(100)|          |CPU(2)(0%)                           |
14. |*  1 |  TABLE ACCESS FULL| TB_HMYY_UPLOAD |     1 |  1357 |   117K  (1)| 00:23:35 |CPU(92037)(100%)                     |
15. ------------------------------------------------------------------------------------
16. Predicate Information (identified by operation id):
17. ---------------------------------------------------
18. 1 - filter(("ZJ"=:2 AND "JKCODE"=:1 AND "ISUPLOAD"=:3 AND "MARK"='1'))

复制代码

执行计划一目了然：全表扫描（TABLE ACCESS FULL）。CPU(92037)(100%)指标显示，几乎100%的成本都消耗在CPU上。这完美印证了我们之前的判断。
2.3 量化性能瓶颈

为了进一步确认创建索引的必要性，我们用数据说话。通过分析历史执行记录，我们得到了更精确的性能指标：

1.                                   PLAN            CPU      ELA      IO       ROWS     WRITE    GET      DISK     ROWS
2. END_TI I NAME               HASH VALUE EXEC       PRE EXEC PRE EXEC PER EXEC PRE EXEC PER EXEC PRE EXEC PRE EXEC PRE FETC
3. ------ - --------------- ------------- ---------- -------- -------- -------- -------- -------- -------- -------- --------
4. 16 09  1 HTZZ               1313371775 30         49.92s   50.09s   4.9ms    .97      0        45.13W   13.3     1
5. 16 10  1 HTZZ               1313371775 30         51.45s   51.53s   .07ms    .97      0        45.13W   .17      1
6. 16 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）。

1. COLUMN                                                            NUM      NUM               AVG                     LAST
2. NAME                                     NL      DENSITY        NULLS DISTINCT  BUCK      COL LEN  SAMPLE_SIZE HIST  ANALYZED
3. ---------------------------------------- -- ------------ ------------ -------- ----- ------------ ------------ ----- --------
4. ID(VARCHAR2(64))                         N             0            0  2264030     1          20     2,264,030 NONE  20250711
5. JK(VARCHAR2(128))                        Y             0           10        7     7          30         5,469 FREQU 20250711
6. ZJ(VARCHAR2(128))                        Y             0            0  2076160   254          46         5,469 HEIGH 20250711
7. YWRQ(DATE(7))                            Y             0            0   586496   254           8         5,469 HEIGH 20250711
8. REQJSON(CLOB(4000))                      Y             0            0        0     0         964     2,264,030 NONE  20250711
9. MESSAGEDRGS(NVARCHAR2(4000))             Y             0            0       22     1          18     2,264,030 NONE  20250711
10. RESPJSON(CLOB(4000))                     Y             0            0        0     0         244     2,264,030 NONE  20250711
11. ISUPLOAD(CHAR(1))                        Y             0            0        2     2           2         5,469 FREQU 20250711

复制代码

NUM DISTINCT列显示了每列的唯一值数量。可以看到，ZJ列的唯一值数量（2,076,160）非常接近表的总行数（2,264,030），具有极高的选择性。因此，在ZJ列上创建索引是最佳选择。
三、解决方案：在线创建索引

考虑到这是在线业务系统，为避免影响正常运行，我们采用ONLINE方式创建索引。

1. create index hrip.ind_HT_HTZZ_HTZPWD_1 on hrip.HT_HTZZ_HTZPWD (ZJ)  online parallel 10 tablespace HTZZ;
2. alter index hrip.ind_HT_HTZZ_HTZPWD_1 noparallel;

复制代码

四、总结与反思

索引创建后，效果立竿见影。系统CPU使用率迅速回落至正常水平。

1. 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)

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！