DB2存储过程优化器没有使用最有效的索引。

我有一个存储过程,它根据p_qds_startTime和p_qds_endTime参数返回数据,这些参数通常被设置为365天的报告期,但是报告期可以设置为1天。存储过程查询的表有很多百万行,并且包含一列包含xml数据。

优化器选择使用针对sys_start的索引,这对于365天的报告期来说效率很低。对于大的报告期,我希望优化器使用与策略状态相关的xml索引,因为只有300,000条记录的策略状态为 “应用”。

策略状态索引的设置是正确的,如果我运行底层查询,将输入参数替换为时间戳,解释计划显示如果报告期超过10天,优化器就会使用这个索引。如果报告期在10天以下,它就会使用sys_start索引,这也是我们想要的。

我的问题是:是否有办法确保优化器检查输入值,并在每次运行时重新评估它将使用的计划?这是一个每天一次的批处理作业,所以这样做的开销并不是真正的问题。

说得通俗一点,批处理作业的运行时间长达10个小时,而当我在本地运行底层查询,打出策略状态索引时,运行时间只需要几分钟。

下面是SP中的底层查询

select 
      policy_number
      ,year_1_commission_amount
from(
select 
      xml_policy.policy_number,
      t002.SYS_START,
      xml_policy.policy_status,
      xml_policy.year_1_commission_amount,
      ROW_NUMBER() over (partition by xml_policy.POLICY_NUMBER order by t002.SYS_START DESC) RN
from 
      DB.t002
      ,xmltable
      (
      '$i/*:AddProtQuoteResponse/*:plan[1]/*:policy[*:policyStatus = "Application"]' passing t002.QDS_XML AS "i"
      columns
      policy_number varchar(30) path '*:policyNumber',
      policy_status varchar(12) path '*:policyStatus',
      year_1_commission_amount decimal(11,2) path ''
      ) as xml_policy
where 
      sys_start > CAST(p_qds_startTime AS TIMESTAMP(12)) 
      and sys_start <= CAST(p_qds_endTime AS TIMESTAMP(12)) 
      and t002.QDS_XML_TYPE_ID = 3 
)
where rn = 1;

解决方案:

尝试将例程包与 REOPT ALWAYS 选项。

CALL SYSPROC.REBIND_ROUTINE_PACKAGE ('SP', 'SP_SCHEMA.SP_SPECIFICNAME', 'REOPT ALWAYS');

其中第2个参数是以下结果:

SELECT RTRIM(ROUTINESCHEMA)||'.'||SPECIFICNAME
--, PARM_COUNT
FROM SYSCAT.ROUTINES R
WHERE ROUTINESCHEMA='SP_SCHEMA' AND ROUTINENAME='SP_NAME';

SP_SCHEMA & SP_NAME–你的程序的模式& 名称。在上面的查询输出中,你可能会有很多行(如果你有很多例程有相同的这对),你必须选择正确的一个相应的具体名称。

给TA打赏
共{{data.count}}人
人已打赏
解决方案

从spring boot应用中获取cassandra cql指标。

2022-5-11 8:00:36

解决方案

如何在运行时跟踪一个布尔值?

2022-5-11 9:00:12

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索