表统计信息过旧导致执行慢的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

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<喎?http://www.2cto.com/kf/ware/vc/" target="_blank" class="keylink">vcD4KPHA+ICAgU0VUIEwuRkVUQ0hfSUQgPSA="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，性能提升两倍以上。