急 这个存储过程怎么优化？

Question

FOR acct IN (SELECT ucracct_cust_code,
                      ucracct_prem_code,
                      ucracct_cycl_code,
                      ucracct_status_ind
                 FROM ucracct
                WHERE ucracct_cycl_code >= cycle_code_begin
                  AND ucracct_cycl_code <= cycle_code_end
                  AND (account_type = '%' OR
                      ucracct_status_ind = account_type)) --
   LOOP
    IF mod(rows_int, interval_int) = 0 THEN
      FOR chg IN (SELECT uabopen_serv_num,
                         uabopen_srat_code,
                         uabopen_scat_code,
                         uabopen_charge_date,
                         uabopen_ar_trans,
                         uabopen_billed_chg,
                         uabopen_balance,
                         uabopen_budget_variance
                    FROM uabopen
                   WHERE uabopen_prem_code = acct.ucracct_prem_code
                     AND uabopen_cust_code = acct.ucracct_cust_code
                     AND uabopen_bad_debt_status_code IS NULL
UNION ALL   SELECT uwbopen_serv_num,
                         uwbopen_srat_code,
                         uwbopen_scat_code,
                         uwbopen_charge_date,
                         uwbopen_ar_trans,
                         uwbopen_billed_chg,
                         uwbopen_balance,
                         uwbopen_budget_variance
                    FROM uwbopen
                   WHERE uwbopen_prem_code = acct.ucracct_prem_code
                     AND uwbopen_cust_code = acct.ucracct_cust_code
                     AND uwbopen_bad_debt_status_code IS NULL
 ) LOOP
 
adjustments :=0;   
adjustments_copy :=0;
        credits :=0 ;
credits_copy :=0;
payments_applied :=0;
payments_applied_copy :=0;
 
        SELECT NVL(SUM(NVL(uabadje_balance, 0)), 0)
          INTO adjustments
          FROM uabadje
         WHERE uabadje_ORigin_ar_trans = chg.uabopen_ar_trans
               and uabadje_adjm_code <>'BUCA';   --ZD 11/05/2010
   
        SELECT NVL(SUM(NVL(uwbadje_balance, 0)), 0)
          INTO adjustments_copy
          FROM uwbadje
         WHERE uwbadje_ORigin_ar_trans = chg.uabopen_ar_trans
               and uwbadje_adjm_code <>'BUCA';
我不全部发了，大的逻辑就是这些 索引建了，还有什么可以优化的吗？？？
另外存储过程怎么查看它的执行计划，，， 单独的sql语句可以，存储过程是不是要
完成后才能查看的到？？

Answer 1

1、create table t1 as SELECT uabopen_serv_num,
  uabopen_srat_code,
  uabopen_scat_code,
  uabopen_charge_date,
  uabopen_ar_trans,
  uabopen_billed_chg,
  uabopen_balance,
  uabopen_budget_variance
  FROM uabopen；
2、create table t2 as SELECT uwbopen_serv_num,
  uwbopen_srat_code,
  uwbopen_scat_code,
  uwbopen_charge_date,
  uwbopen_ar_trans,
  uwbopen_billed_chg,
  uwbopen_balance,
  uwbopen_budget_variance
  FROM uwbopen;
3、第二个for循环 
  FOR chg IN (Select *  FROM t1
  WHERE uabopen_prem_code = acct.ucracct_prem_code
  AND uabopen_cust_code = acct.ucracct_cust_code
  AND uabopen_bad_debt_status_code IS NULL
   UNION ALL  Select * FROM t2
  WHERE uwbopen_prem_code = acct.ucracct_prem_code
  AND uwbopen_cust_code = acct.ucracct_cust_code
  AND uwbopen_bad_debt_status_code IS NULL
  )