Oracle复杂视图首次执行正常，同会话二次执行挂起问题求助
这种同会话里复杂视图二次执行挂起的问题，我在Oracle 11g环境里处理过好几次，结合你给出的信息，给你梳理几个实用的排查方向和解决思路：
先明确你的环境信息：
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
操作会话已切换到DB_2017_2018schema，且设置了NLS_DATE_FORMAT = 'YYYY-MM-DD'
1. 先查会话等待事件，定位挂起原因
当第二次执行查询挂起时，立刻开另一个sysdba会话，查这个挂起会话的等待状态：
SELECT s.sid, s.serial#, s.event, s.wait_time, s.seconds_in_wait, s.state FROM v$session s WHERE s.schema_name = 'DB_2017_2018' AND s.status = 'ACTIVE';
重点关注event列：
- 如果是
library cache lock或library cache pin：大概率是共享池里的视图元数据/执行计划出现了异常锁定 - 如果是IO相关等待（比如
db file sequential read）：虽然首次执行正常，但也要检查第二次执行时是否触发了全表扫描或大表遍历 - 如果是
cursor: pin S wait on X：可能是执行计划的并发解析冲突
2. 对比首次和第二次的执行计划
首次执行正常，第二次挂起，很可能是执行计划发生了突变。可以分别获取两次的执行计划对比：
- 首次执行完成后，直接抓取刚执行的游标计划：
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
- 对于第二次执行（如果能提前生成计划），用
EXPLAIN PLAN预先生成：
EXPLAIN PLAN FOR SELECT 1 FROM complex_view WHERE company_id = ...; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
如果发现两次计划差异极大（比如第一次走索引，第二次全表扫描；或者嵌套循环变哈希连接导致资源耗尽），那大概率是绑定变量窥视或者统计信息过时导致的优化器决策错误。
3. 刷新统计信息，排除统计过时问题
Oracle 11g的优化器严重依赖统计信息，过时的统计信息会导致执行计划不稳定。可以先更新视图涉及的所有底层表的统计：
-- 单表更新 EXEC DBMS_STATS.GATHER_TABLE_STATS('DB_2017_2018', '目标表名', CASCADE => TRUE, ESTIMATE_PERCENT => 100); -- 批量更新整个schema的表统计 EXEC DBMS_STATS.GATHER_SCHEMA_STATS('DB_2017_2018', CASCADE => TRUE, ESTIMATE_PERCENT => AUTO);
另外，复杂视图本身没有独立统计，完全依赖底层表，所以确保所有关联表的统计都是最新的很关键。
4. 处理共享池相关异常
如果等待事件指向库缓存问题，可以尝试刷新共享池后再执行第二次查询：
ALTER SYSTEM FLUSH SHARED_POOL;
如果刷新后二次执行正常，说明是共享池里的旧执行计划或元数据出现了异常。这种情况下，可以考虑给这个查询创建SQL Profile或者SQL Plan Baseline，强制优化器使用首次执行的正确计划。
5. 优化视图本身的逻辑
复杂的嵌套视图、多段UNION ALL和大量连接容易让优化器陷入困境，你可以尝试：
- 扁平化嵌套视图：把多层嵌套的视图拆成子查询，减少优化器的解析复杂度
- 给
UNION ALL的每个子查询添加合适的索引：确保每个分支都能高效过滤数据 - 检查外连接的必要性：如果业务允许，把不必要的外连接改成内连接，减少结果集大小
- 给连接条件的列添加索引：尤其是大表的连接列，避免全表连接导致的资源耗尽
6. 考虑Oracle 11.2.0.3的已知BUG
Oracle 11.2.0.3版本存在一些关于复杂视图执行、共享池管理的BUG，比如某些场景下会出现同会话二次执行时的死锁或挂起。如果以上方法都无效，可以考虑升级到11.2.0.4（11g的最终补丁版本），这是解决老版本BUG最彻底的方式。
内容的提问来源于stack exchange，提问作者The Coder
