存储过程很慢单独执行很快_提高存储过程执行效率

曾经发现这样一种情况，存储过程中的语句提取出来单独执行很快，但在存储过程中执行就很慢。

----*************  存储过程中的语句，变量为 avc_acct_month

SELECT 2101,B.BUNDLE,

COUNT( Distinct CASE WHEN (substr(FAMILY_NUM_CREATE_T,1,6)<=&avc_acct_month AND SUBSTR(nvl(FAMILY_NUM_REMOVE_T,299912),1,6)>&avc_acct_month)

THEN  E1.gsm_nbr END),

COUNT(DISTINCT Case When    TO_CHAR(E1.GSM_INNET_DATE,'YYYYMM') = SUBSTR(E1.FAMILY_NUM_CREATE_T,1,6) AND

SUBSTR(NVL(E1.FAMILY_NUM_REMOVE_T,'299912'),1,6) > &avc_acct_month AND

SUBSTR(E1.FAMILY_NUM_CREATE_T,1,6) <= &avc_acct_month THEN

E1.GSM_NBR END),

COUNT(DISTINCT Case  WHEN TO_CHAR(E1.GSM_INNET_DATE,'YYYYMM') <> SUBSTR(E1.FAMILY_NUM_CREATE_T,1,6) AND

SUBSTR(NVL(E1.FAMILY_NUM_REMOVE_T,'299912'),1,6) > &avc_acct_month AND

SUBSTR(E1.FAMILY_NUM_CREATE_T,1,6) <= &avc_acct_month THEN

E1.GSM_NBR  END),

COUNT(DISTINCT Case  WHEN SUBSTR(NVL(E1.FAMILY_NUM_REMOVE_T,'299912'),1,6) = NVL(TO_CHAR(E1.GSM_REMOVE_DATE,'YYYYMM'),'299912') AND

NVL(TO_CHAR(E1.GSM_REMOVE_DATE,'YYYYMM'),'299912') <= &avc_acct_month THEN

E1.GSM_NBR END),

COUNT(Distinct  CASE WHEN  SUBSTR(E1.FAMILY_NUM_CREATE_T,1,6)=&avc_acct_month

THEN  E1.GSM_NBR END),

COUNT(Distinct  CASE WHEN  SUBSTR(E1.FAMILY_NUM_REMOVE_T,1,6)=&avc_acct_month

THEN  E1.GSM_NBR END),

COUNT( Distinct CASE WHEN (E1.GSM_BRAND = '1' And substr(E1.FAMILY_NUM_CREATE_T,1,6)<=&avc_acct_month AND SUBSTR(nvl(E1.FAMILY_NUM_REMOVE_T,299912),1,6)>&avc_acct_month)

THEN  E1.gsm_nbr END),

COUNT( Distinct CASE WHEN (E1.GSM_BRAND = '1' And TO_CHAR(E1.GSM_INNET_DATE,'YYYYMM')=&avc_acct_month And substr(E1.FAMILY_NUM_CREATE_T,1,6)<=&avc_acct_month AND SUBSTR(nvl(E1.FAMILY_NUM_REMOVE_T,299912),1,6)>&avc_acct_month)

THEN  E1.gsm_nbr END)

FROM EDW_M_PROD_SYNC_RELA_OFFSET_M E1,(SELECT S.SERV_ID,s.subscription_id,COUNT(S.GSM_NBR) BUNDLE FROM EDW_M_PROD_SYNC_RELA_OFFSET_M S

WHERE S.LATN_ID=2101

AND S.ACCT_MONTH=&avc_acct_month

AND SUBSTR(S.SERV_TYPE,1,4)='1110'

AND S.PROD_TYPE='FAMILY_NUM'

AND SUBSTR(S.GSM_NBR, 1, 3) IN

('130', '131', '132', '156', '155', '186')

GROUP BY S.SERV_ID,s.subscription_id) B

Where E1.SUBSCRIPTION_ID=B.SUBSCRIPTION_ID

AND SUBSTR(E1.SERV_TYPE,1,4)='1110'

AND E1.LATN_ID=2101

AND E1.ACCT_MONTH=&avc_acct_month

AND E1.PROD_TYPE='FAMILY_NUM'

AND SUBSTR(E1.GSM_NBR, 1, 3) IN

('130', '131', '132', '156', '155', '186')

AND SUBSTR(E1.SERV_TYPE,1,4)='1110'

GROUP BY B.BUNDLE ;

现象：单独执行的语句已经替换了变量，而存储过程中的变量是在过程中替换的，所以出现相同语句执行时间差异很大，单独执行语句只要几秒中，而存储过程中的语句却需要几小时。

分析方法：把两个语句用explain分析(即F5)，发现已经替换变量的语句随着显示出来的cost较大，代表执行效率低，但时间很快。而带变量的语句虽然cost很小，都用到了索引，代表执行效率高，但时间很慢。

存储过程中的语句的执行计划：

SELECT STATEMENT, GOAL = CHOOSE                        Cost=21        Cardinality=1        Bytes=74

SORT GROUP BY                        Cost=21        Cardinality=1        Bytes=74

VIEW        Object wner=SYS                Cost=13        Cardinality=1        Bytes=74

SORT GROUP BY                        Cost=13        Cardinality=1        Bytes=199

TABLE ACCESS BY GLOBAL INDEX ROWID        Object wner=CMSS        Object name=EDW_M_PROD_SYNC_RELA_OFFSET_M        Cost=2        Cardinality=1        Bytes=78

NESTED LOOPS                        Cost=4        Cardinality=1        Bytes=199

TABLE ACCESS BY GLOBAL INDEX ROWID        Object wner=CMSS        Object name=EDW_M_PROD_SYNC_RELA_OFFSET_M        Cost=2        Cardinality=1        Bytes=121

INDEX RANGE SCAN        Object wner=CMSS        Object name=EDW_PROD_REAL_MON        Cost=1        Cardinality=2594

INDEX RANGE SCAN        Object wner=CMSS        Object name=EDW_PROD_REAL_MON        Cost=1        Cardinality=2594

已替换变量的语句的执行计划：

SELECT STATEMENT, GOAL = CHOOSE                        Cost=784        Cardinality=1        Bytes=74

SORT GROUP BY                        Cost=784        Cardinality=1        Bytes=74

VIEW        Object wner=SYS                Cost=776        Cardinality=1        Bytes=74

SORT GROUP BY                        Cost=776        Cardinality=1        Bytes=199

HASH JOIN                        Cost=767        Cardinality=1        Bytes=199

TABLE ACCESS FULL        Object wner=CMSS        Object name=EDW_M_PROD_SYNC_RELA_OFFSET_M        Cost=383        Cardinality=1        Bytes=121

TABLE ACCESS FULL        Object wner=CMSS        Object name=EDW_M_PROD_SYNC_RELA_OFFSET_M        Cost=383        Cardinality=1        Bytes=78

解决方法：使带变量的语句执行效率与替换变量单独执行的语句相同，再测试程序，发现执行时间很快

因执行时间短的语句用到全表扫描，所以此语句也用全表扫描的优化方法，结果执行时间很快，提高了执行效率。

在其它情况下，可根据执行效率高的语句执行计划来改变相应的hints

SELECT /*+FULL(E1) FULL(B)*/2101,B.BUNDLE,0,0,0,0,0,0,0,0,0,0,

COUNT( Distinct CASE WHEN (substr(FAMILY_NUM_CREATE_T,1,6)<=&avc_acct_month AND SUBSTR(nvl(FAMILY_NUM_REMOVE_T,299912),1,6)>&avc_acct_month)

THEN  E1.gsm_nbr END),

COUNT(DISTINCT Case When    TO_CHAR(E1.GSM_INNET_DATE,'YYYYMM') = SUBSTR(E1.FAMILY_NUM_CREATE_T,1,6) AND

SUBSTR(NVL(E1.FAMILY_NUM_REMOVE_T,'299912'),1,6) > &avc_acct_month AND

SUBSTR(E1.FAMILY_NUM_CREATE_T,1,6) <= &avc_acct_month THEN

E1.GSM_NBR END),

COUNT(DISTINCT Case  WHEN TO_CHAR(E1.GSM_INNET_DATE,'YYYYMM') <> SUBSTR(E1.FAMILY_NUM_CREATE_T,1,6) AND

SUBSTR(NVL(E1.FAMILY_NUM_REMOVE_T,'299912'),1,6) > &avc_acct_month AND

SUBSTR(E1.FAMILY_NUM_CREATE_T,1,6) <= &avc_acct_month THEN

E1.GSM_NBR  END),

COUNT(DISTINCT Case  WHEN SUBSTR(NVL(E1.FAMILY_NUM_REMOVE_T,'299912'),1,6) = NVL(TO_CHAR(E1.GSM_REMOVE_DATE,'YYYYMM'),'299912') AND

NVL(TO_CHAR(E1.GSM_REMOVE_DATE,'YYYYMM'),'299912') <= &avc_acct_month THEN

E1.GSM_NBR END),

COUNT(Distinct  CASE WHEN  SUBSTR(E1.FAMILY_NUM_CREATE_T,1,6)=&avc_acct_month

THEN  E1.GSM_NBR END),

COUNT(Distinct  CASE WHEN  SUBSTR(E1.FAMILY_NUM_REMOVE_T,1,6)=&avc_acct_month

THEN  E1.GSM_NBR END),

COUNT( Distinct CASE WHEN (E1.GSM_BRAND = '1' And substr(E1.FAMILY_NUM_CREATE_T,1,6)<=&avc_acct_month AND SUBSTR(nvl(E1.FAMILY_NUM_REMOVE_T,299912),1,6)>&avc_acct_month)

THEN  E1.gsm_nbr END)

FROM EDW_M_PROD_SYNC_RELA_OFFSET_M E1,(SELECT S.SERV_ID,s.subscription_id,COUNT(S.GSM_NBR) BUNDLE FROM EDW_M_PROD_SYNC_RELA_OFFSET_M S

WHERE S.LATN_ID=2101

AND S.ACCT_MONTH=&avc_acct_month

AND SUBSTR(S.SERV_TYPE,1,4)='1110'

AND S.PROD_TYPE='FAMILY_NUM'

AND SUBSTR(S.GSM_NBR, 1, 3) IN

('130', '131', '132', '156', '155', '186')

GROUP BY S.SERV_ID,s.subscription_id) B

Where E1.SUBSCRIPTION_ID=B.SUBSCRIPTION_ID

AND SUBSTR(E1.SERV_TYPE,1,4)='1110'

AND E1.LATN_ID=2101

AND E1.ACCT_MONTH=&avc_acct_month

AND E1.PROD_TYPE='FAMILY_NUM'

AND SUBSTR(E1.GSM_NBR, 1, 3) IN

('130', '131', '132', '156', '155', '186')

AND SUBSTR(E1.SERV_TYPE,1,4)='1110'

GROUP BY B.BUNDLE

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