Oracle 11g查询视图时执行计划异常问题求助

针对你遇到的xyz_vw视图查询执行计划异常、直接执行定义SQL却能生成高效计划的问题，以下是几种可行的解决方案，既能强制查询视图时使用目标执行计划（计划1），又不会影响后续的视图更新操作：

方案1：使用SQL Profile（推荐，11g官方优先方案） #

SQL Profile可以将指定SQL语句绑定到高效的执行计划上，且仅作用于目标查询语句，不会干扰视图的DML操作（如UPDATE/INSERT）。

操作步骤： #

1. 获取目标查询的SQL_ID
   执行SELECT * FROM xyz_vw后，通过以下语句找到该查询的SQL_ID：

SELECT sql_id, sql_text 
FROM v$sql 
WHERE sql_text LIKE 'SELECT * FROM xyz_vw%' 
  AND sql_text NOT LIKE '%v$sql%';

2. 捕获高效执行计划（计划1）的信息
   直接执行视图的定义SQL（即能生成计划1的SQL），同样获取其SQL_ID，然后导出该SQL的执行计划：

SELECT DBMS_SQLTUNE.EXTRACT_SQL_PROFILE(
  sql_id => '【高效SQL的SQL_ID】',
  profile_type => DBMS_SQLTUNE.PROFILE_TYPE_FROM_CURSOR_CACHE
) FROM dual;

3. 创建SQL Profile绑定到视图查询语句
   用捕获到的计划信息，为SELECT * FROM xyz_vw创建Profile：

DECLARE
  v_profile CLOB;
BEGIN
  v_profile := DBMS_SQLTUNE.EXTRACT_SQL_PROFILE(
    sql_id => '【高效SQL的SQL_ID】',
    profile_type => DBMS_SQLTUNE.PROFILE_TYPE_FROM_CURSOR_CACHE
  );
  
  DBMS_SQLTUNE.CREATE_SQL_PROFILE(
    sql_text => 'SELECT * FROM xyz_vw',
    profile => v_profile,
    name => 'XYZ_VW_QUERY_PROFILE',
    category => 'DEFAULT'
  );
END;
/

4. 验证效果
   重新执行SELECT * FROM xyz_vw，查看执行计划是否变为计划1；同时测试视图更新操作，确认无异常。

注意：创建SQL Profile需要ADMINISTER SQL MANAGEMENT OBJECT权限，操作前需确认权限。

方案2：创建专用查询视图（无需DBA权限） #

如果无法获取DBA权限，可以创建一个仅用于查询的专用视图，在其中嵌入强制生成计划1的hint，原视图保持不变用于更新操作。

操作步骤： #

1. 创建查询专用视图
   将原视图的定义SQL复制出来，添加对应的hint后创建新视图：

CREATE OR REPLACE VIEW xyz_vw_query AS
SELECT /*+ 这里添加计划1对应的hint（如索引提示、连接顺序提示等） */ * FROM (
    -- 原xyz_vw的完整定义SQL
    SELECT col1, col2, ... 
    FROM table1 t1
    JOIN table2 t2 ON t1.id = t2.t1_id
    WHERE ...
);

2. 业务适配
   将业务流程中查询xyz_vw的逻辑替换为查询xyz_vw_query，更新操作仍使用原xyz_vw。

注意：后续原视图xyz_vw的定义若有修改，需同步更新xyz_vw_query，避免数据不一致。

方案3：使用Stored Outlines（兼容旧模式） #

Stored Outlines是Oracle早期的执行计划绑定方案，11g中虽被SQL Profile取代，但仍可使用。

操作步骤： #

1. 开启会话级Outline生成

ALTER SESSION SET CREATE_STORED_OUTLINES = TRUE;

2. 执行高效SQL生成Outline
   直接执行视图的定义SQL（生成计划1的SQL），此时Oracle会自动生成对应的Outline。

3. 绑定Outline到视图查询语句
   关闭会话级Outline生成后，为SELECT * FROM xyz_vw创建Outline并绑定到高效计划：

CREATE OUTLINE xyz_vw_query_outline
FOR SELECT * FROM xyz_vw
USING OUTLINE 【高效SQL生成的Outline名称】;

注意：Stored Outlines的管理灵活性较差，且可能影响同类型SQL的执行计划，建议优先使用方案1或2。

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