DB2 LUW 10.5-如何强制DB2存储过程将最新的统计信息用于最优化的计划 [英] DB2 LUW 10.5 - How to force DB2 Stored procedures to use latest stats for most optimized plan

问题描述

我们偶尔会遇到一个问题。运行SQL的存储过程运行非常慢。从命令行运行时，相同的SQL运行速度非常快。似乎存储过程使用不同的路径。对我们来说，解决方法是删除并重新创建该过程，然后选择正确的计划。

We see an issue occasionally. Stored procedure running a SQL runs very slow. Same SQL when run from command line runs very fast. It seems stored procedure uses a different path. The workaround for us is to drop and recreate the procedure, after which it picks up the right plan.

有没有一种方法可以执行存储过程并重新生成指令运行计划，以便每次都能获得最佳计划。

Is there a way to execute a stored procedure with an instruction to regenerate execution plan at run time, so as to get the best plan every time.

推荐答案

您可能不想重新编译计划

You probably don't want to recompile plans every time you call your procedure, as you lose the performance benefit of having the procedure in the first place.

当您执行需要重新编译时，每次调用过程都会失去性能优势。

When you do need to recompile it, which shouldn't happen too frequently in a stable environment, you can use the REBIND_ROUTINE_PACKAGE system stored procedure:

call SYSPROC.REBIND_ROUTINE_PACKAGE('P', 'YOUR_SP', '')

如果确定每次调用该过程都希望重新创建计划，则可以在创建过程时设置 REOPT ALWAYS 绑定选项。 ，使用 ，例如通过在创建过程之前执行调用SYSPROC.SET_ROUTINE_OPTS（'REOPT ALWAYS）

If you do decide that you want the plan to be recreated each time the procedure is called, you can set the REOPT ALWAYS bind option when you create the procedure, using the many ways described in the manual, for example by executing call SYSPROC.SET_ROUTINE_OPTS('REOPT ALWAYS) before creating the procedure

这篇关于DB2 LUW 10.5-如何强制DB2存储过程将最新的统计信息用于最优化的计划的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！