表统计信息过旧导致执行慢的sql优化
UPDATE T_PUB_HF_TASKPOOL_IN L
SET L.FETCH_ID = :B1
WHERE L.TASK_GROUP_ID =
(SELECT TASK_GROUP_ID
FROM (SELECT TASK_GROUP_ID
FROM (SELECT /*+ leading(tg,t) index(t,IDX_HF_TASKPOOL_ORGID) */
TASK_GROUP_ID,
T.BATCH_ID,
(SELECT P.PRIORITY
FROM T_PUB_HF_PRIORITY P
WHERE P.SUB_BIZMODEL = T.SUB_BIZMODEL
AND NVL(P.BIZMODEL_TYPE, ‘00‘) =
NVL(T.BIZMODEL_TYPE, ‘00‘)
AND P.STATUS = ‘1‘) PRIORITY,
(SELECT 1
FROM T_PUB_HF_PRIORITY_ORG O
WHERE O.SUB_BIZMODEL = T.SUB_BIZMODEL
AND O.ORG_ID = T.ORG_ID) PRIORITYORG
FROM T_PUB_HF_TASKPOOL_IN T, T_PUB_HF_ATTR_TMR TG
WHERE T.SUB_BIZMODEL = TG.SUB_BIZMODEL
AND TG.ORG_ID = T.ORG_ID
AND TG.TMR_ID = :B3
AND TG.TL_UM_ID = :B2
AND T.FETCH_ID IS NULL
AND T.STATUS IS NULL)
ORDER BY PRIORITY, PRIORITYORG, BATCH_ID)
WHERE ROWNUM = 1)
用户提供不了绑定变量，只能我自己尝试。
select TMR_ID,count(*) from T_PUB_HF_ATTR_TMR group by tmr_id order by count(*) desc;
--有58行被选择，只截取前面10行，其余剩下的基本是45与44.
select TL_UM_ID,count(*) from T_PUB_HF_ATTR_TMR group by TL_UM_ID order by count(*) desc;
代入绑定变量后的值。Buffer get是6168
UPDATE /*+gather_plan_statistics fwy1758*/T_PUB_HF_TASKPOOL_IN L
SET L.FETCH_ID = ‘abcfwy‘
WHERE L.TASK_GROUP_ID =
(SELECT TASK_GROUP_ID
FROM (SELECT TASK_GROUP_ID
FROM (SELECT /*+ leading(tg,t) index(t,IDX_HF_TASKPOOL_ORGID) */
TASK_GROUP_ID,
T.BATCH_ID,
(SELECT P.PRIORITY
FROM T_PUB_HF_PRIORITY P
WHERE P.SUB_BIZMODEL = T.SUB_BIZMODEL
AND NVL(P.BIZMODEL_TYPE, ‘00‘) =
NVL(T.BIZMODEL_TYPE, ‘00‘)
AND P.STATUS = ‘1‘) PRIORITY,
(SELECT 1
FROM T_PUB_HF_PRIORITY_ORG O
WHERE O.SUB_BIZMODEL = T.SUB_BIZMODEL
AND O.ORG_ID = T.ORG_ID) PRIORITYORG
FROM T_PUB_HF_TASKPOOL_IN T, T_PUB_HF_ATTR_TMR TG
WHERE T.SUB_BIZMODEL = TG.SUB_BIZMODEL
AND TG.ORG_ID = T.ORG_ID
AND TG.TMR_ID = ‘LIUYAOYUN692‘
AND TG.TL_UM_ID = ‘SUNYAN032‘
AND T.FETCH_ID IS NULL
AND T.STATUS IS NULL)
ORDER BY PRIORITY, PRIORITYORG, BATCH_ID)
WHERE ROWNUM = 1)
查看详细的执行计划，红圈的返回行数估算是错误的，可以预计，t_pub_hf_taskpool_in表以及其索引的统计信息有误。
经查看，t_pub_hf_taskpool_in表最后统计信息更新日期是2014/3/5 3:53:03，有1500行。而今天3月10日，已经是212095行，可能做过批量加载数据。
在cow库尝试对t_pub_hf_taskpool_in表及其统计信息进行收集，完了后再跑sql。
此时buffer get已经下降到2590，性能提升两倍以上。
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
