Oracle数据库同表不同数据量的查询性能差异问题咨询

背景概述 #

我有一张名为cars的表，包含car_id、car_name、car_model三列，其中car_id为主键。存在两个包含该表的Oracle数据库：

• 库A：cars表有8,700,455条记录
• 库B：cars表有9,036,113条记录（比库A多335,658条）

在两个库中执行相同的查询：

SELECT objs1_defaultNCObjectMapping.car_id id 
FROM cars objs1_defaultNCObjectMapping 
WHERE UPPER(objs1_defaultNCObjectMapping.car_name) LIKE '%STARTWTH100_T0_00000%' ESCAPE '\';

两个库返回结果均为100行，但库B（数据量更大）的执行时间更长。

性能较好（库A，小表）的执行计划 #

Explain plan where performance is good[Smaller Table] 
-------------- 
LEVEL, PLAN, ROWS, COST, PREDICATES 
1 SELECT STATEMENT (OPTIMIZER=ALL_ROWS) 25000000 112744 [NULL] 
2 INDEX (FULL SCAN) "XIF34NC_OBJECTS" 25000000 112744 filter(UPPER("NAME") LIKE '%STARTWTH100\_T0\_00000%' ESCAPE '\' AND UPPER("NAME") IS NOT NULL)

性能较差（库B，大表）的执行计划 #

LEVEL, PLAN, ROWS, COST, PREDICATES 
1 SELECT STATEMENT (OPTIMIZER=ALL_ROWS) 25000000 112875 [NULL] 
2 INDEX (FULL SCAN) "XIF34NC_OBJECTS" 25000000 112875 filter(UPPER("NAME") LIKE '%STARTWTH100\_T0\_00000%' ESCAPE '\' AND UPPER("NAME") IS NOT NULL)

耗时差异原因分析 #

从执行计划来看，两个库都采用了索引全扫描（INDEX FULL SCAN），过滤条件也完全一致，但库B耗时更长，主要原因如下：

1. 数据量带来的IO开销差异
   库B的cars表比库A多33万多条记录，对应的索引物理体积也更大。Oracle执行索引全扫描时，需要读取更多的索引块到内存中，磁盘IO操作总量更高，自然会拉长执行时间。

2. 索引碎片与存储碎片化
   数据量更大的库B，可能经历了更多的插入、更新操作，导致XIF34NC_OBJECTS索引产生较多碎片——索引块空间利用率降低，扫描时需要遍历更多物理块，额外增加了IO耗时。而小表的索引通常更紧凑，存储更规整。

3. 统计信息与执行成本偏差
   虽然执行计划里估算的行数都是2500万，但如果库B的统计信息过时或不准确，Oracle优化器对索引扫描的成本估算会存在偏差。实际执行时，大表的真实IO成本远高于估算值，这也是耗时增加的一个潜在因素。

4. 系统资源竞争
   库B所在服务器可能存在更严重的资源竞争（比如CPU、内存、磁盘IO带宽被其他进程占用），导致查询执行时无法获得足够的资源支持，进而变慢。这是环境层面的常见影响因素。

优化建议 #

针对这个查询的性能问题，可以从以下几个方向入手优化：

• 创建基于函数的索引
  当前查询使用UPPER(car_name)作为过滤条件，索引全扫描需要在扫描过程中实时计算UPPER并匹配like条件。创建一个基于UPPER(car_name)的函数索引，能让Oracle直接通过索引快速定位符合条件的记录，避免全扫描索引：

  CREATE INDEX idx_cars_upper_carname ON cars(UPPER(car_name));
  

  优化后查询会转为索引范围扫描（INDEX RANGE SCAN），大幅减少需要扫描的索引条目数量，无论表大小如何，性能都会显著提升。

• 清理索引碎片
  对库B的XIF34NC_OBJECTS索引进行碎片整理，执行以下命令让索引块更紧凑，减少扫描时的IO次数：

  ALTER INDEX XIF34NC_OBJECTS REBUILD;
  

• 更新统计信息
  确保两个库的统计信息都是最新的，执行以下命令收集准确的统计信息，让Oracle优化器能做出更合理的执行计划选择：

  ANALYZE TABLE cars COMPUTE STATISTICS;
  

  也可以使用DBMS_STATS包进行更精细化的统计信息收集。

• 检查系统资源瓶颈
  确认库B所在服务器的磁盘IO、CPU、内存资源是否充足，比如用iostat查看磁盘利用率，top查看CPU和内存使用情况，必要时调整系统资源分配或升级硬件。

内容的提问来源于stack exchange，提问作者Ankur Adhyapak