SQL报表性能优化求助：执行超1小时，需分析执行计划并优化

我开发的报表执行时间超过1小时，已启用跟踪并分析执行计划，从计划来看gl_code_combinations表疑似存在性能瓶颈，烦请帮忙排查，同时希望获取缩短执行时间的优化建议。相关SQL语句如下：

SELECT gjs.user_je_source_name "JOURNAL_SOURCE",
gjc.user_je_category_name "JOURNAL_CATEGORY",
gl.currency_code,
0 AS reportingdebit,
0 AS reportingcredit,
Sum(Nvl(gl.accounted_dr, 0)) AS ledgerdebit,
Sum(Nvl(gl.accounted_cr, 0)) AS ledgercredit,
gl.code_combination_id,
gc.segment1,
gc.segment2,
gc.segment3,
gc.segment4,
gc.segment5,
gc.segment6,
gc.segment7,
gc.segment8,
gc.segment9,
gh.period_name
FROM gl_je_headers gh,
gl_je_lines gl,
gl_je_categories gjc,
gl_je_sources gjs,
gl_code_combinations gc,
gl_ledgers gl1
WHERE 1 = 1
AND gl1.ledger_id = gh.ledger_id
AND gh.je_header_id = gl.je_header_id
AND gjc.je_category_name = gh.je_category
AND gjs.je_source_name = gh.je_source
AND gl.code_combination_id = gc.code_combination_id
AND gl1.chart_of_accounts_id = gc.chart_of_accounts_id
-- AND gl1.currency_code!='USD'
AND gl1.NAME = 'Ledger1'
AND gh.period_name IN (SELECT period_name
FROM gl_periods a
WHERE 1 = 1
AND period_num BETWEEN 1 AND
(SELECT period_num
FROM gl_periods
WHERE period_name =
:p_accounting_period
AND
period_set_name =
a.period_set_name)
AND period_year IN
(SELECT period_year
FROM gl_periods
WHERE
period_name = :p_accounting_period
AND period_set_name =
a.period_set_name))
GROUP BY gjs.user_je_source_name,
gjc.user_je_category_name,
gl.currency_code,
gl.code_combination_id,
gh.period_name,
gc.segment1,
gc.segment2,
gc.segment3,
gc.segment4,
gc.segment5,
gc.segment6,
gc.segment7,
gc.segment8,
gc.segment9

问题排查与优化建议 #

一、gl_code_combinations表专项排查 #

1. 索引有效性检查
   • 确认该表是否存在code_combination_id的主键/唯一索引，以及code_combination_id + chart_of_accounts_id的联合索引。如果缺失，会导致关联时全表扫描，直接拖慢执行速度。
   • 若执行计划显示该表为全表扫描，立即添加索引：

     CREATE INDEX idx_gcc_code_coa ON gl_code_combinations(code_combination_id, chart_of_accounts_id);
     

2. 统计信息更新
   • 若表数据量庞大且统计信息过期，优化器会生成错误执行计划。执行以下命令更新统计信息：

     EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的数据库用户名', TABNAME => 'GL_CODE_COMBINATIONS', CASCADE => TRUE);
     

二、SQL语句优化 #

1. 重构子查询，减少嵌套
   原SQL中gh.period_name IN的多层嵌套子查询可改用CTE+JOIN方式，避免重复查询gl_periods表：

   WITH target_period AS (
       SELECT period_num, period_year, period_set_name
       FROM gl_periods
       WHERE period_name = :p_accounting_period
   )
   SELECT gjs.user_je_source_name "JOURNAL_SOURCE",
          gjc.user_je_category_name "JOURNAL_CATEGORY",
          gl.currency_code,
          0 AS reportingdebit,
          0 AS reportingcredit,
          Sum(Nvl(gl.accounted_dr, 0)) AS ledgerdebit,
          Sum(Nvl(gl.accounted_cr, 0)) AS ledgercredit,
          gl.code_combination_id,
          gc.segment1,
          gc.segment2,
          gc.segment3,
          gc.segment4,
          gc.segment5,
          gc.segment6,
          gc.segment7,
          gc.segment8,
          gc.segment9,
          gh.period_name
   FROM gl_je_headers gh
   JOIN gl_je_lines gl ON gh.je_header_id = gl.je_header_id
   JOIN gl_ledgers gl1 ON gl1.ledger_id = gh.ledger_id
   JOIN gl_code_combinations gc 
     ON gl.code_combination_id = gc.code_combination_id 
     AND gl1.chart_of_accounts_id = gc.chart_of_accounts_id
   JOIN gl_je_categories gjc ON gjc.je_category_name = gh.je_category
   JOIN gl_je_sources gjs ON gjs.je_source_name = gh.je_source
   JOIN gl_periods a ON gh.period_name = a.period_name
   JOIN target_period tp 
     ON a.period_set_name = tp.period_set_name
     AND a.period_num BETWEEN 1 AND tp.period_num
     AND a.period_year = tp.period_year
   WHERE gl1.NAME = 'Ledger1'
   GROUP BY gjs.user_je_source_name,
            gjc.user_je_category_name,
            gl.currency_code,
            gl.code_combination_id,
            gh.period_name,
            gc.segment1,
            gc.segment2,
            gc.segment3,
            gc.segment4,
            gc.segment5,
            gc.segment6,
            gc.segment7,
            gc.segment8,
            gc.segment9
   

2. 改用显式JOIN语法
   原SQL使用逗号分隔表的旧式连接，替换为显式JOIN后，优化器能更清晰识别关联逻辑，生成更优执行计划。
3. 优化分组效率
   为GROUP BY中的核心字段（如gl.code_combination_id、gh.period_name、gc.segment1-segment9）创建联合索引，加速分组聚合操作。

三、系统层面优化 #

• 分区表改造：若gl_je_lines、gl_je_headers为大表，建议按period_name或ledger_id进行分区，减少扫描的数据范围。
• 临时表缓存：若报表需频繁执行，可将target_period这类固定逻辑的结果存入临时表，避免重复计算。

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