已生成执行计划对查询执行的作用及跨会话性能差异咨询

原因分析 #

• 会话级PGA工作区未预热：同一会话内复用执行计划时，首次执行已经为查询的排序、哈希连接等操作分配了足够的PGA（程序全局区）工作区，后续执行无需重新申请内存、初始化工作区，直接用内存完成运算，避免了磁盘临时IO。而新会话启动后，PGA工作区需要重新分配，若初始内存不足，就会触发临时表空间的磁盘操作，导致耗时飙升。
• 会话级优化器参数不一致：当前会话的优化器相关参数（比如optimizer_index_cost_adj、optimizer_index_caching）或工作区策略（workarea_size_policy）可能被修改过，这些参数是会话级的，新会话会使用系统默认值。哪怕执行计划哈希值相同，实际执行时的成本计算、资源分配逻辑也会不一样，拖慢效率。
• 共享池字典缓存加载开销：首次执行时，数据库会把查询涉及的表、索引统计信息、元数据加载到共享池的字典缓存里，同一会话后续执行直接复用。新会话执行时，可能得重新从磁盘读取这些元数据，增加了初始耗时。

解决方案 #

• 优化PGA内存配置：
  • 调整系统级PGA_AGGREGATE_TARGET参数，确保有足够PGA内存供所有会话使用，减少执行时对临时表空间的依赖。
  • 针对目标查询，可在会话中设置workarea_size_policy=MANUAL，手动指定sort_area_size、hash_area_size等参数，保证执行时内存充足。
• 统一会话执行环境：
  • 将optimizer_mode、optimizer_index_cost_adj这类关键优化器参数设为系统级默认值，或者在应用连接数据库时统一设置这些参数，确保所有会话的执行环境一致。
  • 用数据库配置文件（Profile）统一管控会话参数，避免不同会话的参数差异。
• 预热共享池与执行环境：
  • 系统启动后或业务低峰期，执行一次目标查询，把元数据加载到共享池，同时预热PGA工作区。
  • 用DBMS_SHARED_POOL.KEEP存储过程把目标SQL的执行计划固定在共享池中，防止被换出。
• 排查执行计划实际运行情况：
  • 用DBMS_XPLAN.DISPLAY_CURSOR查看新会话中执行计划的实际运行统计（比如实际行数、物理读次数），确认是否存在执行计划相同但资源消耗不同的情况，排查是否有统计信息过期或绑定变量值导致的数据分布差异。

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