Oracle 夜间统计收集作业运行后非常慢的查询

我们有一个从文件加载数据并对加载的数据运行验证查询的应用程序。我们观察到每晚统计作业运行后加载的第一个文件的非常缓慢的验证查询 (并清除了收集了新统计信息的表的所有计划)。如果第一个文件是相对较大的文件，这将导致处理中的巨大延迟。比较每晚stats作业运行后同一小测试文件的第一次和第二次运行的sql跟踪，(较慢) 第一次运行中的计划通常显示并行执行，而第二次运行中的计划则不显示并行执行。

问题: 我们如何管理这种情况，以便在stats工作后的第一次运行中获得更好的计划选择？

一些相关的参数设置如下所示。请注意，parallel_degree_policy设置为自适应，其想法是为优化器提供尽可能多的灵活性。

所讨论的表格和索引的程度为1。

我们在单节点和2节点RAC系统上都观察到了类似的行为。

SQL> show parameter optimizer;
名称类型值
-
优化 _ 自适应 _ 特征布尔真
优化 _ 自适应 _ 报告 _ 仅布尔FALSE
优化 _ 捕获 _ sql_plan_baselines布尔FALSE
优化 _ 动态 _ 采样整数2
优化 _ 功能 _ 启用字符串12.1.0.2
优化 _ 索引 _ 缓存整数0
优化 _ 索引 _ 成本 _ 调整整数100
优化 _ 记忆 _ 感知布尔TRUE
优化模式字符串全行
优化 _ 安全 _ 视图 _ 合并布尔值TRUE
优化 _ 使用 _ 不可见 _ 索引布尔FALSE
优化 _ 使用 _ pending _ 统计布尔FALSE
优化 _ 使用 _ sql_plan_baselines布尔真
SQL> 显示参数并行;
名称类型值
-
快速启动并行回滚字符串低
并行 _ 自适应 _ 多用户布尔真
平行 _ 自动 _ 调整布尔FALSE
Parallel_degre_level整数100
Parallel_degre_limit字符串CPU
Parallel_degre_policy字符串自适应
并行执行消息大小整数16384
parallel_force_local布尔FALSE
parallel_instance_group字符串
parallel_io_cap_enabled布尔FALSE
parallel_max_servers整数666
平行 _ 最小 _ 百分比整数0
平行 _ 最小 _ 服务器整数96
并行 _ 分钟 _ 时间 _ 阈值字符串自动
parallel_server布尔值TRUE
并行服务器实例整数2
parallel_servers_target整数384
并行线程/cpu整数2
恢复 _ 并行性整数0

正如文档所说的关于将parallel_degre_policy设置为自适应:

This value enables automatic degree of parallelism, statement queuing and in-memory parallel execution, similar to the AUTO value. In addition, performance feedback is enabled. Performance feedback helps to improve the degree of parallelism automatically chosen for repeated SQL statements. After the initial execution of a statement, the degree of parallelism chosen by the optimizer is compared to the degree of parallelism computed based on the actual execution performance. If they vary significantly, then the statement is marked for re-parse and the initial execution performance statistics (for example, CPU-time) are provided as feedback for subsequent executions. The optimizer uses the initial execution performance statistics to better determine a degree of parallelism for subsequent executions.

http://docs.oracle.com/database/121/REFRN/GUID-BF09265F-8545-40D4-BD29-E58D5F02B0E5.htm#REFRN10310

所以你不会得到第二次并行执行，因为优化器已经发现这不是最好的方法!

如果你想停止优化器完全使用并行并坚持一个 “好的” 计划，看看SQL计划管理 (SPM)。使用此功能，查询只能使用存储为基线的已批准计划。所以你的计划以后不会改变

有关此的更多信息，请参见:

http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-mgmt-12c-1963237.pdf
https://blogs.oracle.com/optimizer/entry/sql_plan_management_part_1_of_4_creating_sql_plan_baselines

如果您想了解有关并行执行工作原理以及如何选择学位的更多信息，请阅读:

http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-parallel-execution-fundamentals-133639.pdf
https://blogs.oracle.com/datawarehousing/entry/what_is_auto_dop