两表嵌套查询执行超20分钟的SQL性能优化咨询

我们执行如下SQL语句耗时达1229.206秒，共返回8310286行结果：

SELECT t_01.object_uid 
FROM HashTable t_01 
WHERE t_01.object_uid IN (SELECT t_02.puid  
                          FROM ObjectTable t_02 
                          WHERE (t_02.arev_category IN (48, 40)))

执行计划哈希值（Plan hash value）：1560846306

------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | E-Rows |E-Bytes| Cost (%CPU)|
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |        |       |   780K(100)|
|   1 |  NESTED LOOPS SEMI                   |                   |   7764K|   244M|   780K  (1)|
|   2 |   INDEX FULL SCAN                    | PIHashTable       |   7764K|   111M|  4073   (1)|
|*  3 |   TABLE ACCESS BY INDEX ROWID BATCHED| ObjectTable       |    290M|  4986M|     1   (0)|
|*  4 |    INDEX RANGE SCAN                  | PIOBJECTTABLE     |      1 |       |     1   (0)|
------------------------------------------------------------------------------------------------

表与索引基础统计 #

• HashTable：共51154个数据块，最近统计信息收集时间为2022/04/19，总行数7764715（约700万行）；索引PIHashTable基于OBJECT_UID字段创建，最近统计信息收集时间2022/04/19，覆盖全表7764715行。
• ObjectTable：共3327616个数据块，最近统计信息收集时间为2022/05/02，总行数290473386（约2.9亿行）；索引PIOBJECTTABLE基于PUID字段创建，最近统计信息收集时间2022/05/02，覆盖全表290473386行。

当前执行计划选择了嵌套循环半连接（NESTED LOOPS SEMI）逻辑：先全量扫描HashTable的PIHashTable索引拿到约776万条object_uid，再对每一条object_uid去ObjectTable走PIOBJECTTABLE索引回表，校验对应记录的arev_category是否符合条件。
优化器对Object表单条索引回表的成本估算严重偏低（执行计划中标注单步成本为1），忽略了700多万次循环回表产生的大量随机IO开销，是SQL执行过慢的核心原因。

• 创建覆盖联合索引避免回表：在ObjectTable上创建(arev_category, puid)顺序的联合索引。该索引可以直接过滤出arev_category IN (48,40)的puid集合，完全不需要回表访问表数据；同时优化器可以选择先扫描这个索引拿到符合条件的puid小结果集，再驱动HashTable做关联，大幅减少循环匹配次数。
• 改用哈希半连接替代嵌套循环：嵌套循环仅适合被驱动表关联查询次数少的场景，针对当前大表关联的场景，可以通过添加hint /*+ HASH_AJ */ 强制走哈希半连接，先将ObjectTable符合条件的puid集合构建为哈希表，再扫描HashTable的object_uid做哈希匹配，完全避免百万次随机回表IO。
• 改写SQL消除子查询估算偏差：将IN子查询改写为显式关联，避免优化器对子查询的行数估算错误，参考写法：

SELECT DISTINCT t_01.object_uid 
FROM HashTable t_01 
INNER JOIN ObjectTable t_02 ON t_01.object_uid = t_02.puid
WHERE t_02.arev_category IN (48, 40)

如果确认ObjectTable中同一个puid最多只有一条记录匹配arev_category IN (48,40)的条件，可以去掉DISTINCT，减少去重排序的开销。

• 修正统计信息偏差：当前执行计划对ObjectTable的成本估算和实际2.9亿行的规模严重不符，需要重新对两张表收集统计信息，尤其要对ObjectTable的arev_category字段收集频率直方图，让优化器准确估算过滤后的结果集行数，自动选择更合理的关联顺序和关联算法。
• 开启并行查询缩短大表扫描时间：如果业务侧允许、数据库服务器资源充足，可以添加并行hint /*+ PARALLEL(t_02 8) */，利用多核CPU并行扫描ObjectTable过滤符合条件的puid，进一步缩短执行时间。

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