Oracle月末计息汇总慢查询性能优化技术求助

针对单大型investment_product_cd对应300万日活客户、月末汇总耗时2.5小时的问题，结合表结构与数据特征，给出以下优化方向：

一、分区/子分区策略调整 #

• 为大型CD单独配置列表子分区：当前按investment_product_cd做40个哈希子分区，大型CD的哈希分布可能不均，导致单分区数据量过大。将10-15个大型CD从哈希子分区中剥离，单独设置列表子分区，查询时可直接定位到目标CD的专属子分区，避免扫描无关哈希分区的数据。
• 增加accrual_period_id子分区维度：将表调整为position_ts主分区 + investment_product_cd列表子分区 + accrual_period_id列表子分区的复合结构。月末汇总时，可精准过滤出目标CD+当月accrual_period_id的子分区，大幅减少扫描范围。

二、索引优化 #

• 创建覆盖型分区本地索引：针对大型CD，创建包含sub_account_id, money_group_id, accrual_period_id, close_out_ind及应计金额字段（如accrual_amt）的分区本地索引。确保查询所需字段全部在索引中，实现索引覆盖扫描，避免回表操作。索引需按investment_product_cd和accrual_period_id做过滤，只保留目标周期的索引数据。
• 清理冗余索引：检查现有索引是否包含非必要字段，减少索引维护开销与存储空间占用，确保核心查询的索引效率。

三、查询语句与并行度优化 #

• 强化过滤条件精准性：在查询中明确指定investment_product_cd（目标大型CD）、当月accrual_period_id、position_ts的当月时间范围，以及close_out_ind的取值，引导数据库精准定位目标分区，避免扫描额外数据。
• 优化并行度设置：全并行hint易引发资源竞争，可根据服务器CPU核心数指定合理并行度（如/*+ PARALLEL(32) */），同时为该查询设置更高的资源优先级。
• 分阶段聚合：先按日汇总客户应计金额，再按月度聚合每日结果，降低单步聚合的数据量：

  WITH daily_accrual AS (
      SELECT sub_account_id, money_group_id, SUM(accrual_amt) AS daily_total
      FROM AET
      WHERE investment_product_cd = 'TARGET_CD'
        AND accrual_period_id = '202405'
        AND position_ts BETWEEN TO_DATE('2024-05-01', 'YYYY-MM-DD') AND TO_DATE('2024-05-31', 'YYYY-MM-DD')
        AND close_out_ind = 'N'
      GROUP BY sub_account_id, money_group_id, TRUNC(position_ts)
  )
  SELECT sub_account_id, money_group_id, SUM(daily_total) AS monthly_total
  FROM daily_accrual
  GROUP BY sub_account_id, money_group_id;
  

四、数据预处理与存储优化 #

• 预计算每日客户汇总：每日数据加载完成后，自动计算当日每个sub_account_id+money_group_id的应计金额，存储到按investment_product_cd、accrual_period_id分区的每日汇总表。月末汇总时直接从该表聚合，无需扫描原始3000万/日的交易数据。
• 启用数据压缩：对AET表启用OLTP级压缩（如Oracle Advanced Compression），大型CD子分区数据量极大，压缩可显著降低磁盘IO开销。
• 确保归档策略有效：定期检查超期数据归档脚本，保证表中仅保留最近90天数据，避免查询时扫描历史归档数据。

五、执行计划调优 #

• 强制分区裁剪：若数据库未自动过滤无关分区，可添加hint强制分区裁剪（如/*+ NO_EXPAND PARTITION(AET P_202405) */，具体分区名需替换为实际值），确保仅扫描目标分区。
• 检查哈希子分区分布：查询大型CD在哈希子分区中的数据占比，若存在数据集中的子分区，说明哈希函数不合理，需调整子分区数量或改用列表子分区。

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