ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 提高存储过程执行效率

提高存储过程执行效率

原创 Linux操作系统 作者:FINDUFO10 时间:2009-03-12 19:45:26 0 删除 编辑
曾经发现这样一种情况,存储过程中的语句提取出来单独执行很快,但在存储过程中执行就很慢。
----*************  存储过程中的语句,变量为 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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-03-11

  • 博文量
    12
  • 访问量
    11440