BI Publisher报表运行缓慢但SQL Developer执行快速问题排查咨询

问题描述 #

我们有一个调用函数的BI Publisher报表，在SQL Developer中执行select * from table (function(parameters))仅需7分钟，但在BI Publisher中运行时长却高达2小时。

补充背景：

1. 数据来自正在升级的旧版本应用，已导入并升级至新版本模式（schema）；
2. 数据库为受控环境，我们在SQL Developer中仅拥有只读用户权限；
3. BI Publisher数据源用户为函数所在模式的所有者（无密码无法测试），尝试使用SQL Developer的只读用户作为数据源仍耗时2小时。

当前尝试：

• 正追踪函数中所有SQL以获取执行计划，尝试从旧版本捕获并应用到新版本；
• 曾尝试将SQL Developer使用的用户作为报表数据源，但运行时长未改善，与预期不符。

需检查的排查点 #

1. 执行计划与数据库优化器差异 #

• 捕获并对比执行计划：获取BI Publisher执行查询时的实际执行计划，和SQL Developer中的计划做对比。BI Publisher的会话可能因环境变量（如OPTIMIZER_MODE、HASH_AREA_SIZE、NLS参数）不同，导致优化器生成完全不同的执行路径。
• 绑定变量窥探问题：函数内部SQL若未使用绑定变量，或绑定变量的参数值在BI Publisher和SQL Developer中差异较大，可能触发优化器选择低效计划。需检查函数内SQL的参数使用方式。
• 统计信息有效性：升级新版本schema后，确认是否重新收集了表、索引的统计信息。过时的统计信息会导致优化器生成错误的执行计划，不同会话可能读取到不同版本的统计数据。

2. BI Publisher自身执行机制 #

• 报表端额外处理：检查BI Publisher是否对查询结果进行了冗余操作，比如不必要的全局排序、分页计算、模板内复杂聚合或格式转换，这些操作会在BI Publisher应用层消耗大量时间，而非数据库执行阶段。
• 数据源连接配置：查看BI Publisher的数据源连接池设置，包括连接复用策略、最大连接数、超时参数等。不合理的连接池配置可能导致会话资源不足或状态异常。
• 日志分析：查看BI Publisher的执行日志，明确耗时主要集中在数据库查询阶段还是报表渲染阶段，直接定位瓶颈所在。

3. 数据库权限与资源限制 #

• 资源配额差异：受控环境可能对不同客户端的数据库连接设置了CPU、IO等资源限制。确认BI Publisher所用连接是否被限制了资源，而SQL Developer的连接不受此限制。
• 执行上下文权限：即使使用同一只读用户，BI Publisher调用函数时的执行上下文（如函数的AUTHID属性）可能不同，导致函数内部SQL的权限或执行路径变化，影响性能。

4. 网络与数据传输因素 #

• 网络延迟对比：测试BI Publisher服务器与数据库服务器之间的网络延迟，和SQL Developer所在机器到数据库的网络情况做对比。带宽不足、丢包等网络问题会大幅增加数据传输耗时。
• 数据传输量与处理方式：如果函数返回大量数据，BI Publisher在数据序列化、传输过程中的开销可能远大于SQL Developer（比如SQL Developer仅加载部分数据，而BI Publisher需要处理全量数据用于报表生成）。

内容的提问来源于stack exchange，提问作者Anthony Nievera Jr