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/