sql语句在过程里加上变量后执行非常慢

Question

这条语句在sql窗口执行很快 INSERT INTO T_ESHOP_MONTH
    SELECT MON,
           'qry_su',
           DECODE(SUM(TOTAL), 0, 0, SUM(SU) / SUM(TOTAL)),
           'www'
      FROM (SELECT /*+parallel(t,10)*/
             TO_CHAR(T.BIZ_BEGINDATE, 'YYYYMM') MON,
             'qry_num' ， COUNT(CASE
               WHEN T.BUSI_RESULT = '0' THEN
                1
             END) SU,
             COUNT(1) TOTAL
              FROM ES_PUB_SYSTEMLOG T
             WHERE T.OPER_TYPE = '0'
               AND T.BIZ_BEGINDATE >= TO_DATE('20120601', 'YYYYMMDD')
               AND T.BIZ_BEGINDATE < TO_DATE('20120701', 'YYYYMMDD')
             GROUP BY TO_CHAR(T.BIZ_BEGINDATE, 'YYYYMM')
            UNION ALL
            SELECT /*+parallel(t,10)*/
             TO_CHAR(T.BIZ_BEGINDATE, 'YYYYMM'),
             'qry_num' ， COUNT(CASE
               WHEN T.BUSI_RESULT = '0' THEN
                1
             END) SU,
             COUNT(1) TOTAL
              FROM ES_PUB_SYSTEMLOGMM T
             WHERE T.OPER_TYPE = '0'
               AND T.BIZ_BEGINDATE >= TO_DATE('20120601', 'YYYYMMDD')
               AND T.BIZ_BEGINDATE < TO_DATE('20120701', 'YYYYMMDD')
             GROUP BY TO_CHAR(T.BIZ_BEGINDATE, 'YYYYMM'))
     GROUP BY MON;
  COMMIT;
当加上变量做条件后就执行很慢  INSERT INTO T_ESHOP_MONTH
    SELECT MON,
           'qry_su',
           DECODE(SUM(TOTAL), 0, 0, SUM(SU) / SUM(TOTAL)),
           'www'
      FROM (SELECT /*+parallel(t,10)*/
             TO_CHAR(T.BIZ_BEGINDATE, 'YYYYMM') MON,
             'qry_num' ， COUNT(CASE
               WHEN T.BUSI_RESULT = '0' THEN
                1
             END) SU,
             COUNT(1) TOTAL
              FROM ES_PUB_SYSTEMLOG T
             WHERE T.OPER_TYPE = '0'
               AND T.BIZ_BEGINDATE >= V_MONTH_DATE--变量
               AND T.BIZ_BEGINDATE < V_NMONTH --变量
             GROUP BY TO_CHAR(T.BIZ_BEGINDATE, 'YYYYMM')
            UNION ALL
            SELECT /*+parallel(t,10)*/
             TO_CHAR(T.BIZ_BEGINDATE, 'YYYYMM'),
             'qry_num' ， COUNT(CASE
               WHEN T.BUSI_RESULT = '0' THEN
                1
             END) SU,
             COUNT(1) TOTAL
              FROM ES_PUB_SYSTEMLOGMM T
             WHERE T.OPER_TYPE = '0'
                AND T.BIZ_BEGINDATE >= V_MONTH_DATE--变量
               AND T.BIZ_BEGINDATE < V_NMONTH --变量
             GROUP BY TO_CHAR(T.BIZ_BEGINDATE, 'YYYYMM'))
     GROUP BY MON;
  COMMIT;
这些表都是分区表，所以按照时间段取得
请高手帮忙解决

Answer 1

--这是用变量的执行计划
ADDRESS HASH_VALUE SQL_ID PLAN_HASH_VALUE CHILD_ADDRESS CHILD_NUMBER TIMESTAMP OPERATION OPTIONS OBJECT_NODE OBJECT# OBJECT_OWNER OBJECT_NAME OBJECT_ALIAS OBJECT_TYPE OPTIMIZER ID PARENT_ID DEPTH POSITION SEARCH_COLUMNS COST CARDINALITY BYTES OTHER_TAG PARTITION_START PARTITION_STOP PARTITION_ID OTHER DISTRIBUTION CPU_COST IO_COST TEMP_SPACE ACCESS_PREDICATES FILTER_PREDICATES PROJECTION TIME QBLOCK_NAME REMARKS OTHER_XML
07000002071DAB90 4077732614 5v7c1tzthuhs6 4012815243 070000027BA936A0 0 2012-7-6 AM 09:21:29 INSERT STATEMENT ALL_ROWS 0 0 1641 0 1641
07000002071DAB90 4077732614 5v7c1tzthuhs6 4012815243 070000027BA936A0 0 2012-7-6 AM 09:21:29 LOAD TABLE CONVENTIONAL 1 0 1 1 0 SEL$1 <other_xml><info type="nodeid/pflags">000014000129</info><info type="nodeid/pflags">000013000129</info><info type="nodeid/pflags">000012000129</info><info type="nodeid/pflags">000009000129</info><info type="nodeid/pflags">000008000129</info><info type="nodeid/pflags">000007000129</info><info type="db_version">11.2.0.2</info><info type="parse_schema"><![CDATA["NGESHOP"]]></info><info type="dynamic_sampling">6</info><info type="plan_hash">4012815243</info><info type="plan_hash_2">3491469992</info><peeked_binds><bind nam=":1" pos="1" dty="12" mxl="7">78700601010101</bind><bind nam=":2" pos="2" dty="12" mxl="7">78700701010101</bind><bind nam=":3" pos="3" dty="12" mxl="7">78700601010101</bind><bind nam=":4" pos="4" dty="12" mxl="7">78700701010101</bind></peeked_binds><outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('11.2.0.2')]]></hint><hint><![CDATA[DB_VERSION('11.2.0.2')]]></hint><hint><![CDATA[OPT_PARAM('optimizer_dynamic_sampling' 6)]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$2")]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$3")]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SET$1")]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint><![CDATA[OUTLINE_LEAF(@"INS$1")]]></hint><hint><![CDATA[FULL(@"INS$1" "T_ESHOP_MONTH"@"INS$1")]]></hint><hint><![CDATA[NO_ACCESS(@"SEL$1" "from$_subquery$_002"@"SEL$1")]]></hint><hint><![CDATA[USE_HASH_AGGREGATION(@"SEL$1")]]></hint><hint><![CDATA[INDEX_RS_ASC(@"SEL$3" "T"@"SEL$3" ("ES_PUB_SYSTEMLOGMM"."BIZ_BEGINDATE" "ES_PUB_SYSTEMLOGMM"."BIZ_DEFINITION_ID"))]]></hint><hint><![CDATA[USE_HASH_AGGREGATION(@"SEL$3")]]></hint><hint><![CDATA[INDEX_RS_ASC(@"SEL$2" "T"@"SEL$2" ("ES_PUB_SYSTEMLOG"."BIZ_BEGINDATE" "ES_PUB_SYSTEMLOG"."GOODS_ID"))]]></hint><hint><![CDATA[USE_HASH_AGGREGATION(@"SEL$2")]]></hint></outline_data></other_xml>
07000002071DAB90 4077732614 5v7c1tzthuhs6 4012815243 070000027BA936A0 0 2012-7-6 AM 09:21:29 HASH GROUP BY 2 1 2 1 0 1641 2 62 18819432274 628 "MON"[VARCHAR2,6], SUM("TOTAL")[22], SUM("SU")[22] 20
07000002071DAB90 4077732614 5v7c1tzthuhs6 4012815243 070000027BA936A0 0 2012-7-6 AM 09:21:29 VIEW from$_subquery$_002@SEL$1 3 2 3 1 0 1640 2 62 18800848444 628 "MON"[VARCHAR2,6], "SU"[NUMBER,22], "TOTAL"[NUMBER,22] 20 SET$1
07000002071DAB90 4077732614 5v7c1tzthuhs6 4012815243 070000027BA936A0 0 2012-7-6 AM 09:21:29 UNION-ALL 4 3 4 1 0 STRDEF[6], STRDEF[7], STRDEF[22], STRDEF[22] SET$1
07000002071DAB90 4077732614 5v7c1tzthuhs6 4012815243 070000027BA936A0 0 2012-7-6 AM 09:21:29 HASH GROUP BY 5 4 5 1 0 458 1 12 2679790978 314 TO_CHAR(INTERNAL_FUNCTION("T"."BIZ_BEGINDATE"),'YYYYMM')[6], COUNT(*)[22], COUNT(CASE  WHEN "T"."BUSI_RESULT"='0' THEN 1 END )[22] 6 SEL$2
07000002071DAB90 4077732614 5v7c1tzthuhs6 4012815243 070000027BA936A0 0 2012-7-6 AM 09:21:29 FILTER 6 5 6 1 0 :1<:2 "T".ROWID[ROWID,10], "T"."BIZ_BEGINDATE"[DATE,7], "T"."BUSI_RESULT"[CHARACTER,1], "T"."OPER_TYPE"[NUMBER,22]
07000002071DAB90 4077732614 5v7c1tzthuhs6 4012815243 070000027BA936A0 0 2012-7-6 AM 09:21:29 PARTITION RANGE MULTI-COLUMN 7 6 7 1 0 314 2758382 33100584 KEY(MULTI-COL) KEY(MULTI-COL) 7 2237147 314 "T".ROWID[ROWID,10], "T"."BIZ_BEGINDATE"[DATE,7], "T"."BUSI_RESULT"[CHARACTER,1], "T"."OPER_TYPE"[NUMBER,22] 4
07000002071DAB90 4077732614 5v7c1tzthuhs6 4012815243 070000027BA936A0 0 2012-7-6 AM 09:21:29 TABLE ACCESS BY LOCAL INDEX ROWID 77311 NGESHOP ES_PUB_SYSTEMLOG T@SEL$2 TABLE 8 7 8 1 0 314 2758382 33100584 KEY(MULTI-COL) KEY(MULTI-COL) 7 2237147 314 "T"."OPER_TYPE"=0 "T".ROWID[ROWID,10], "T"."BIZ_BEGINDATE"[DATE,7], "T"."BUSI_RESULT"[CHARACTER,1], "T"."OPER_TYPE"[NUMBER,22] 4 SEL$2
07000002071DAB90 4077732614 5v7c1tzthuhs6 4012815243 070000027BA936A0 0 2012-7-6 AM 09:21:29 INDEX RANGE SCAN 77396 NGESHOP IDX_SYSTEMLOG_GOODSID T@SEL$2 INDEX 9 8 9 1 1 313 1 KEY(MULTI-COL) KEY(MULTI-COL) 7 2229211 313 "T"."BIZ_BEGINDATE">=:1 AND "T"."BIZ_BEGINDATE"<:2 "T".ROWID[ROWID,10], "T"."BIZ_BEGINDATE"[DATE,7] 4 SEL$2
07000002071DAB90 4077732614 5v7c1tzthuhs6 4012815243 070000027BA936A0 0 2012-7-6 AM 09:21:29 HASH GROUP BY 10 4 5 2 0 1181 1 13 16121057466 314 TO_CHAR(INTERNAL_FUNCTION("T"."BIZ_BEGINDATE"),'YYYYMM')[6], COUNT(*)[22], COUNT(CASE  WHEN "T"."BUSI_RESULT"='0' THEN 1 END )[22] 15 SEL$3
07000002071DAB90 4077732614 5v7c1tzthuhs6 4012815243 070000027BA936A0 0 2012-7-6 AM 09:21:29 FILTER 11 10 6 1 0 :3<:4 "T".ROWID[ROWID,10], "T"."BIZ_BEGINDATE"[DATE,7], "T"."BUSI_RESULT"[CHARACTER,1], "T"."OPER_TYPE"[NUMBER,22]
07000002071DAB90 4077732614 5v7c1tzthuhs6 4012815243 070000027BA936A0 0 2012-7-6 AM 09:21:29 PARTITION RANGE MULTI-COLUMN 12 11 7 1 0 314 14991014 194883182 KEY(MULTI-COL) KEY(MULTI-COL) 12 2237147 314 "T".ROWID[ROWID,10], "T"."BIZ_BEGINDATE"[DATE,7], "T"."BUSI_RESULT"[CHARACTER,1], "T"."OPER_TYPE"[NUMBER,22] 4
07000002071DAB90 4077732614 5v7c1tzthuhs6 4012815243 070000027BA936A0 0 2012-7-6 AM 09:21:29 TABLE ACCESS BY LOCAL INDEX ROWID 77285 NGESHOP ES_PUB_SYSTEMLOGMM T@SEL$3 TABLE 13 12 8 1 0 314 14991014 194883182 KEY(MULTI-COL) KEY(MULTI-COL) 12 2237147 314 "T"."OPER_TYPE"=0 "T".ROWID[ROWID,10], "T"."BIZ_BEGINDATE"[DATE,7], "T"."BUSI_RESULT"[CHARACTER,1], "T"."OPER_TYPE"[NUMBER,22] 4 SEL$3
07000002071DAB90 4077732614 5v7c1tzthuhs6 4012815243 070000027BA936A0 0 2012-7-6 AM 09:21:29 INDEX RANGE SCAN 77450 NGESHOP IDX_SYSTEMLOGMM_BIZID T@SEL$3 INDEX 14 13 9 1 1 313 1 KEY(MULTI-COL) KEY(MULTI-COL) 12 2229211 313 "T"."BIZ_BEGINDATE">=:3 AND "T"."BIZ_BEGINDATE"<:4 "T".ROWID[ROWID,10], "T"."BIZ_BEGINDATE"[DATE,7] 4 SEL$3

Answer 2

to_char(varchar(8),T.BIZ_BEGINDATE,112) between start and end;
你这样测试一下??? 

Answer 3

如果对 T.BIZ_BEGINDATE 做函数处理就不能用分区了

Answer 4

V_MONTH_DATE V_NMONTH 变量是什么类型？

Answer 5

没啥好法，把里面的select部分逐步分解，看执行计划