Oracle 19c SQL全局调优：动态TW_RPT视图名称变化时如何让视图驱动查询

背景 #

Oracle 19.4环境因供应商要求配置了CURSOR_SHARING = FORCE参数，Crystal报表执行耗时长达15分钟，根因为执行计划不合理：200万行的大表PR被选为驱动表，与仅返回个位数行的TW_RPT_前缀动态视图做嵌套循环关联，引发大量重复扫描。手动添加/*+ USE_HASH(TW_RPT_xxxx)*/提示后查询耗时可降至1秒以内，但动态视图名的随机性导致通用优化规则无法直接生效。

核心痛点 #

• 动态生成的TW_RPT_前缀视图名称随机，每个查询对应独立SQL ID，即使指定FORCE选项的SQL Profile也无法复用
• Crystal报表不支持动态注入匹配视图名的Hint，也不支持/*+ USE_HASH(TW_RPT_%) */这类通配符格式的提示
• 所有TW_RPT_前缀视图返回行数极少，必须作为驱动表关联才能得到最优执行计划

可落地优化方案（按优先级排序） #

方案1：SQL翻译框架自动注入Hint（无侵入、适配所有场景） #

利用Oracle自带的DBMS_SQL_TRANSLATOR包配置SQL翻译规则，自动匹配所有带TW_RPT_前缀视图的报表SQL，动态注入对应Hint，完全不需要修改应用、报表代码。
操作步骤：

1. 创建SQL翻译Profile

BEGIN
  DBMS_SQL_TRANSLATOR.CREATE_PROFILE(
    profile_name => 'CRYSTAL_REPORT_PROF',
    description  => '自动为TW_RPT前缀视图的报表SQL添加USE_HASH提示'
  );
END;
/

2. 注册正则匹配的翻译规则，自动提取动态视图名插入Hint

BEGIN
  DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION(
    profile_name    => 'CRYSTAL_REPORT_PROF',
    sql_text        => 'SELECT (#ANY_STRING#) FROM (#ANY_STRING#) INNER JOIN "TRACKWISE_OWNER"."TW_RPT_#(0-9_+)#" "TW_RPT_#(0-9_+)#" ON "PR"."ID"="TW_RPT_#(0-9_+)#"."ID" (#ANY_STRING#)',
    translated_text => 'SELECT /*+ USE_HASH(TW_RPT_#1#) */ \1 FROM \2 INNER JOIN "TRACKWISE_OWNER"."TW_RPT_#1#" "TW_RPT_#1#" ON "PR"."ID"="TW_RPT_#1#"."ID" \3'
  );
END;
/

3. 给报表运行用户启用翻译Profile

ALTER USER [报表执行数据库用户名] SET SQL_TRANSLATION_PROFILE = CRYSTAL_REPORT_PROF;

方案2：DDL触发器自动锁定小视图统计信息（实现最简单） #

所有TW_RPT_前缀视图返回行数都极少，通过DDL触发器在视图创建时自动设置固定的低基数统计信息，引导优化器主动选择该视图作为驱动表，自动生成哈希连接计划。
操作步骤：

CREATE OR REPLACE TRIGGER TRG_SET_TW_RPT_VIEW_STATS
AFTER CREATE ON TRACKWISE_OWNER.SCHEMA
WHEN (OBJ_NAME LIKE 'TW_RPT_%' AND OBJECT_TYPE = 'VIEW')
BEGIN
  DBMS_STATS.SET_TABLE_STATS(
    ownname => 'TRACKWISE_OWNER',
    tabname => :NEW.OBJ_NAME,
    numrows => 10, -- 覆盖所有可能的小结果集场景
    numblks => 1,
    avgrlen => 10,
    force => TRUE
  );
END;
/

方案3：修改视图生成规则固定别名（仅适用于允许调整应用逻辑的场景） #

如果有权限调整应用生成动态视图的逻辑，可要求所有动态生成的TW_RPT_视图在查询中固定别名，比如统一叫TW_RPT_VIEW，之后直接配置通用SQL Patch添加/*+ LEADING(TW_RPT_VIEW) USE_HASH(PR) */提示即可全局生效。 #

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