动态抽样引发的问题

1.现象

JOB 长时间不能结束，查看原因发现一直卡在这个SQL上，

  UPDATE CLMRPTPRODUCIBLE
     SET RPT_IF = 'P'
   WHERE LASTEVENTTIME BETWEEN V_FROMLASTEVENTTIME AND V_TOLASTEVENTTIME
     AND SITE = V_SITE
     AND RPT_IF  = 'W';

单独拿出这个SQL，查看执行计划，发现执行计划生成非常缓慢，等了5min，仍然hang在那。

因此查看ASH，看看在等待什么。发现下面的SQL，执行时间非常长。

SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1), :"SYS_B_00"), NVL(SUM(C2), :"SYS_B_01") FROM (SELECT :"SYS_B_02" AS C1, :"SYS_B_03" AS C2 FROM ((SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_04") SAMPLE BLOCK (:"SYS_B_05" , :"SYS_B_06") SEED (:"SYS_B_07")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_08") SAMPLE BLOCK (:"SYS_B_09" , :"SYS_B_10") SEED (:"SYS_B_11")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_12") SAMPLE BLOCK (:"SYS_B_13" , :"SYS_B_14") SEED (:"SYS_B_15")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_16") SAMPLE BLOCK (:"SYS_B_17" , :"SYS_B_18") SEED (:"SYS_B_19")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_20") SAMPLE BLOCK (:"SYS_B_21" , :"SYS_B_22") SEED (:"SYS_B_23")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_24") SAMPLE BLOCK (:"SYS_B_25" , :"SYS_B_26") SEED (:"SYS_B_27")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_28") SAMPLE BLOCK (:"SYS_B_29" , :"SYS_B_30") SEED (:"SYS_B_31")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_32") SAMPLE BLOCK (:"SYS_B_33" , :"SYS_B_34") SEED (:"SYS_B_35")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_36") SAMPLE BLOCK (:"SYS_B_37" , :"SYS_B_38") SEED (:"SYS_B_39")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_40") SAMPLE BLOCK (:"SYS_B_41" , :"SYS_B_42") SEED (:"SYS_B_43")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_44") SAMPLE BLOCK (:&qu ot;SYS_B_45" , :"SYS_B_46") SEED (:"SYS_B_47")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_48") SAMPLE BLOCK (:"SYS_B_49" , :"SYS_B_50") SEED (:"SYS_B_51")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_52") SAMPLE BLOCK (:"SYS_B_53" , :"SYS_B_54") SEED (:"SYS_B_55")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_56") SAMPLE BLOCK (:"SYS_B_57" , :"SYS_B_58") SEED (:"SYS_B_59")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PAR TITION(:"SYS_B_60") SAMPLE BLOCK (:"SYS_B_61" , :"SYS_B_62") SEED (:"SYS_B_63")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_64") SAMPLE BLOCK (:"SYS_B_65" , :"SYS_B_66") SEED (:"SYS_B_67")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_68") SAMPLE BLOCK (:"SYS_B_69" , :"SYS_B_70") SEED (:"SYS_B_71")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_72") SAMPLE BLOCK (:"SYS_B_73" , :"SYS_B_74") SEED (:"SYS_B_75")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM &qu ot;MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_76") SAMPLE BLOCK (:"SYS_B_77" , :"SYS_B_78") SEED (:"SYS_B_79")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_80")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_81")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_82")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_83")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALL EL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_84")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_85")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_86")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_87")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_88")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPR D"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_89")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_90")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_91")) UNION ALL (SELECT /*+ NO_PARALLEL("MESPRD"."CLMRPTPRODUCIBLE") FULL("MESPRD"."CLMRPTPRODUCIBLE") NO_PARALLEL_INDEX("MESPRD"."CLMRPTPRODUCIBLE") */ * FROM "MESPRD"."CLMRPTPRODUCIBLE" PARTITION(:"SYS_B_92"))) "CLMRPTPRODUCIBLE") SAMPLESUB

2.原因

上面的SQL，网上查了下，和ORACLE动态抽样有关。

ORACLE9i开始，如果Table没有统计信息，则CBO在生成执行计划时，需要进行动态抽样。

由于CLMRPTPRODUCIBLE 非常大，可能导致了动态抽样时非常慢，从而导致执行计划生成延迟。

注:由于当时没有生成10046 trace，因此无法得知当时动态抽样在等待什么.

3.措施:

收集CLMRPTPRODUCIBLE的统计信息后，问题解决。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15412087/viewspace-1976528/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15412087/viewspace-1976528/