Oracle公交准点率查询性能优化：全表扫描原因及提速求助

场景与需求 #

需分析公交准点率，涉及三张核心表：

• bus_dynamic_history：存储公交位置SDO_GEOMETRY点数据，无靠近站点标识，共4000万行；
• bus_journeys：公交行程数据，约4500行；
• bus_timetables：公交站点数据，含SDO_GEOMETRY位置点，约28000行。

表关联逻辑：

• bus_journeys与bus_dynamic_history通过operatorcode和linename关联；
• bus_timetables与bus_journeys通过unique_journey和runningboard关联。

核心需求：判断公交是否处于站点5米范围内，统计早到/迟到相关数据。

当前SQL语句 #

SELECT b.uniquejourney journey, a.journeyid, a.vehicleid, a.operatorcode, a.linename, c.atcocode, trunc(a.capturetime) cdate, 
b.runningboard board, b.starttime, b.endtime, c.atcocode stop,  sdo_geom.sdo_distance(a.location, c.location) meters, min(to_char(a.capturetime, 'HH24MI')) ctime
FROM bus_dynamic_history a, bus_journeys b, bus_timetables c 
WHERE a.operatorcode = b.operator AND a.linename = b.linename AND b.uniquejourney = c.uniquejourney AND b.runningboard = c.runningboard
AND a.journeyid = b.starttime --AND a.operatorcode = 'ADER' AND linename = '38' --AND b.unique_journey = '11cs8'
AND c.stopflag = 'T'
and sdo_within_distance( c.location, a.location, 'distance = 5' ) = 'TRUE'
AND a.lastupdated BETWEEN '19/05/2024 00:00' AND '22/05/2024 23:59'
GROUP BY b.uniquejourney, a.journeyid, a.vehicleid, a.operatorcode, a.linename, trunc(a.capturetime), b.runningboard, b.starttime, b.endtime, c.atcocode, sdo_geom.sdo_distance(a.location, c.location)

已创建索引 #

• bus_dynamic_history(lastupdated)
• bus_dynamic_history(operatorcode)
• bus_dynamic_history(location)
• bus_journeys(uniquejourney)
• bus_journeys(starttime)
• bus_timetables(uniquejourney)
• bus_timetables(location)

执行计划 #

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 801895608
----------------------------------------------------------------------
| Id  | Operation                              | Name                |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                     |
|   1 |  HASH GROUP BY                         |                     |
|   2 |   FILTER                               |                     |
|   3 |    HASH JOIN                           |                     |
|   4 |     HASH JOIN                          |                     |
|   5 |      TABLE ACCESS FULL                 | BUS_JOURNEYS        |
|   6 |      TABLE ACCESS FULL                 | BUS_TIMETABLES      |
|   7 |     TABLE ACCESS BY INDEX ROWID BATCHED| BUS_DYNAMIC_HISTORY |
|   8 |      INDEX RANGE SCAN                  | BDH_LASTUPD         |
----------------------------------------------------------------------

问题现状 #

当前查询耗时约15分钟，远未达预期。bus_journeys和bus_timetables执行全表扫描，未使用已创建的索引，性能瓶颈明显。

全表扫描原因分析 #

1. 小表成本评估：bus_journeys仅4500行、bus_timetables仅28000行，Oracle优化器认为全表扫描的IO成本低于索引扫描（索引需先查索引再回表，额外IO开销高于直接读取全表）。
2. 索引匹配度不足：关联bus_journeys与bus_timetables的条件是uniquejourney+runningboard，但仅单独创建了uniquejourney索引，未建立联合索引，优化器判断单独索引无法有效过滤数据，全表扫描更高效。
3. 统计信息过时：若表的统计信息未及时更新，优化器无法准确评估索引过滤效率，可能误选全表扫描。

性能优化方案 #

1. 优化索引策略 #

• bus_journeys联合索引：覆盖关联、过滤及查询字段，避免回表

  CREATE INDEX idx_bj_op_line_uq_run ON bus_journeys(operator, linename, uniquejourney, runningboard, starttime);
  

• bus_timetables联合索引：覆盖关联条件、过滤条件及查询字段

  CREATE INDEX idx_bt_uq_run_stop ON bus_timetables(uniquejourney, runningboard, stopflag, atcocode, location);
  

• bus_dynamic_history空间索引：当前location为普通B-tree索引，无法支持空间查询加速，需创建空间索引

  CREATE INDEX idx_bdh_spatial ON bus_dynamic_history(location) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
  

2. 重构SQL语句 #

先过滤小表减少数据集，再关联大表，避免隐式转换，优化分组逻辑：

WITH filtered_journey_stops AS (
    SELECT 
        b.uniquejourney, b.runningboard, b.starttime, b.endtime, b.operator, b.linename,
        c.atcocode, c.location AS stop_location
    FROM bus_journeys b
    JOIN bus_timetables c 
        ON b.uniquejourney = c.uniquejourney 
        AND b.runningboard = c.runningboard
    WHERE c.stopflag = 'T'
),
filtered_bus_locations AS (
    SELECT 
        a.journeyid, a.vehicleid, a.operatorcode, a.linename, 
        a.location AS bus_location, a.capturetime
    FROM bus_dynamic_history a
    WHERE a.lastupdated BETWEEN TO_DATE('19/05/2024 00:00', 'DD/MM/YYYY HH24:MI') 
        AND TO_DATE('22/05/2024 23:59', 'DD/MM/YYYY HH24:MI')
)
SELECT 
    fjs.uniquejourney AS journey,
    fbl.journeyid,
    fbl.vehicleid,
    fbl.operatorcode,
    fbl.linename,
    fjs.atcocode,
    TRUNC(fbl.capturetime) AS cdate,
    fjs.runningboard AS board,
    fjs.starttime,
    fjs.endtime,
    fjs.atcocode AS stop,
    SDO_GEOM.SDO_DISTANCE(fbl.bus_location, fjs.stop_location) AS meters,
    MIN(TO_CHAR(fbl.capturetime, 'HH24MI')) AS ctime
FROM filtered_bus_locations fbl
JOIN filtered_journey_stops fjs 
    ON fbl.operatorcode = fjs.operator 
    AND fbl.linename = fjs.linename 
    AND fbl.journeyid = fjs.starttime
WHERE SDO_WITHIN_DISTANCE(fjs.stop_location, fbl.bus_location, 'distance = 5') = 'TRUE'
GROUP BY 
    fjs.uniquejourney, fbl.journeyid, fbl.vehicleid, fbl.operatorcode, fbl.linename,
    TRUNC(fbl.capturetime), fjs.runningboard, fjs.starttime, fjs.endtime, fjs.atcocode,
    SDO_GEOM.SDO_DISTANCE(fbl.bus_location, fjs.stop_location)

3. 更新统计信息 #

让优化器准确评估执行成本：

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'BUS_JOURNEYS', CASCADE => TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'BUS_TIMETABLES', CASCADE => TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'BUS_DYNAMIC_HISTORY', CASCADE => TRUE, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE);

4. 空间数据校验 #

确认bus_dynamic_history.location与bus_timetables.location使用相同坐标系，不同坐标系会导致空间距离计算大幅耗时，需统一为同一空间参考系（如WGS84）。

SQL监控报告翻译内容 #

1. BUS_JOURNEYS全表扫描处理4500行数据，无前置过滤；
2. BUS_TIMETABLES全表扫描后，通过stopflag='T'过滤得到约XX行有效站点数据；
3. BUS_DYNAMIC_HISTORY通过lastupdated索引扫描获取约XX万行数据，哈希连接后经空间距离过滤仅保留约XX千行数据；
4. HASH GROUP BY操作占用约60%的执行时间，主要因分组前数据量过大；
5. 空间距离计算未使用索引，导致单条记录计算耗时较长。

内容的提问来源于stack exchange，提问作者Nigel Dams