oracal sql语句服务器执行时间超长，本地很快！不知道原因，求oracal大神围观！

Question

贴上sql（这个sql大家不用太用心看，语句不是问题）：

 insert into   pms_consump 
       SELECT   DISTINCT
              DECODE (nvl(trans.transamt,0),
                      0, '0.00',
                      TRUNC (nvl(trans.transamt,0) / 100, 2))
                 AS transamt,
              nvl(trans.transamt,0) AS hiddenTrans,
              DECODE (trans.SEARCH_TRANS_CODE,
                      '0000000200',
                      '消费',
                      '2000000200',
                      '消费撤销',
                      '2000000400',
                      '消费撤销冲正',
                      '0000000400',
                      '消费冲正')
                 AS trans_code,
              DECODE (trans.CANCELFLAG,
                      '0',
                      '正常交易',
                      '1',
                      '冲正交易',
                      '2',
                      '被冲正交易')
                 AS CANCELFLAG,
              DECODE (
                 TRANS.CARDNO,
                 '',
                 '',
                 SUBSTR (TRANS.CARDNO, 0, 4) || '****'
                 || SUBSTR (TRANS.CARDNO,
                            LENGTH (TRANS.CARDNO) - 3,
                            LENGTH (TRANS.CARDNO))
              )
                 AS cardno,
              TRANS.CARDNO AS hidenCardno,
              TO_CHAR (to_date(trans.transdate||trans.transtime,'yyyymmddhh24miss'), 'yyyy-mm-dd hh24:mi:ss') AS SENDDATE,
              trans.pospsn,
              trans.id,
              trans.routeid,
              trans.cancelid,
              trans.search_trans_code as TRANSCODE,
              MERPOS.POSBUSINESSNO,
              merinfo.MERC_NAME,
              MERINFO.MERC_ID,
              MERINFO.seller_no,
              merInfo.mcc_cd,
              merInfo.premiumrate,
              merInfo.BANKNAME,
              POSINFO.SERIALNO,
              pri.channel_code as channelCode ,
              trans.RESPONSECODE AS channelno,
              pri.merchant_id as  merchantId ,
              MERPOS.POSID,
              ai.AGENT_NUMBER,
              ai.AGENT_NAME,
              di.departmentnumber,
              di.departmentname,
              DECODE(nvl(trans.transfee1, 0),
                       0,
                       '0.000',
                TRUNC(nvl(trans.transfee1, 0) / 100, 6)) AS transfee1,
              (transamt-transfee1)/100 as transmoney
       FROM (select * from POSP_TRANS_INFO trans where trans.search_trans_code not in ('9000100800','9000100820','9000200500') 
       and  to_date(trans.transdate,'yyyy/mm/dd')<=trunc(sysdate)-1 
       -- and to_date(trans.transdate,'yyyy/mm/dd') >=add_months(trunc(sysdate,'mm'),-1)
       and trans.transdate is not null 
       and  trans.merchantcode is not null) trans
                       LEFT JOIN
                          PMS_MERCHANT_POS merPos
                       ON trans.POSID = merPos.id
                    LEFT JOIN
                       PMS_POS_INFO posinfo
                    ON POSINFO.ID = MERPOS.POSID
                 LEFT JOIN
                    PMS_MERCHANT_INFO merInfo
                 ON merInfo.id = MERPOS.MERCHANTID
                  left join posp_route_info pri
                  on trans.routeid=pri.id
              LEFT JOIN
                 pms_agent_info ai
              ON merinfo.agent_number = ai.agent_number
              left join
              pms_department_info di
              on di.id=merpos.departmentid
   ORDER BY   senddate ,merc_id, posid; 

以上语句，我本地执行时间17s左右，服务器上跑了几个小时！
之后查询服务器数据库执行这条语句的日志，
几个关键的数据是这样的：
BUFFER_GETS ：26907302
CONCURRENCY_WAIT_TIME：41
OPTIMIZER_COS：495638759
CPU_TIME：70572155
ELAPSED_TIME：70572155
OBJECT_STATUS：VALID

需要其他数据我可以提供！摆脱大家了！

Answer 1

检查下索引有没有建立正确，服务器有没有额外的事务或者触发器？