原创 转载请注明出处

（我的故障处理）

我对今天POMS数据库的故障进行了分析，原因如下：

   我取出了程序中的一个重要的片段，而整个语句基本都是由这个语句拼凑起来我先叫它主SQL，所以如果这个片段能够优化效果会很好，片段如下

  select decode(sum(pf.period_prem),

                            null,

                            0,

                            sum(pf.period_prem))--sum(pf.period_prem)

                from t_product_fee     pf,

                     t_contract_master cm,

                     t_product_life    pl

               where pf.policy_id = cm.policy_id

                 and cm.validate_date <

                     ADD_MONTHS(to_date('200911', 'yyyymm'), -13)--13

                 and cm.validate_date >=

                     ADD_MONTHS(to_date('200911', 'yyyymm'), -(13+12))--14

                 and pf.charge_type = 1 --不为趸交,小缴别暂不考虑and pf.charge_type <> 5

                 and pf.product_id = pl.product_id

                 and pkg_life_calc_basic_comm.f_is_long_product(pl.product_id) = 0

                 and pf.fee_type in (41, 47)

                 and pf.policy_year = 1

                 and pkg_life_calc_basic_comm.f_is_effect(pf.list_id) = '0'

                 and (('3' = '1' and pf.benefit_type <> 41) or

                     ('3' = '2' and pf.benefit_type = 41) or

                     ('3' = '3'))

                 and (('0' ='7' and cm.nb_agency_id in (select agency_id from t_agency start with agency_id=1 connect by parent_id = prior agency_id) or

                       '0' <>'7'))

                 and cm.organ_id in

                     (select organ_id

                          from t_company_organ

                         start with organ_id = 1

                        connect by parent_id = prior organ_id)

                 and cm.sell_way = 0

                 and cm.policy_type = 1;

 我查看了这个语句的执行计划，并未发现明显的问题，然后对这个语句进行了SQL_TRACE，然后打出了报告，发现问题的实质在于这个语句

pkg_life_calc_basic_comm.f_is_effect，pkg_life_calc_basic_comm.f_is_long_product进行调用的PL/SQL，TRACE报告中给出了4个引起性能

问题根源的片段如下：

SELECT COUNT(CM.POLICY_ID)

FROM

 T_PRODUCT_FEE PF, T_CONTRACT_MASTER CM WHERE CM.PREM_STATUS IN (2, 3, 4) AND

  PF.POLICY_ID = CM.POLICY_ID AND PF.LIST_ID = :B1

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute  43136      1.77       1.80          0          0          0           0

Fetch    43136      2.31       2.16          0     258816          0       43136

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total    86273      4.08       3.97          0     258816          0       43136

SELECT COUNT(CP.ITEM_ID)

FROM

 T_PRODUCT_FEE PF, T_CONTRACT_PRODUCT CP WHERE CP.LIABILITY_STATE = 3 AND

  CP.END_CAUSE IN (1, 2, 8, 10, 11,13,14) AND PF.PRODUCT_NUM = CP.PRODUCT_NUM

  AND PF.INSURED_1 = CP.INSURED_1 AND PF.POLICY_ID = CP.POLICY_ID AND

  PF.LIST_ID = :B1

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute  43106      1.51       1.60          0          0          0           0

Fetch    43106      2.29       2.17          0     258604          0       43106

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total    86213      3.80       3.78          0     258604          0       43106

SELECT COUNT(CPL.CHANGE_ID)

FROM

 T_PRODUCT_FEE PF,T_POLICY_CHANGE PC, T_CONTRACT_PRODUCT_LOG CPL WHERE

  PF.POLICY_ID = PC.POLICY_ID AND PC.SERVICE_ID=213 AND PC.CHANGE_STATUS=3

  AND PC.CHANGE_ID=CPL.CHANGE_ID AND PF.PRODUCT_NUM = CPL.PRODUCT_NUM AND

  PF.PRODUCT_ID = CPL.PRODUCT_ID AND PF.POLICY_ID = CPL.POLICY_ID AND

  PF.LIST_ID = :B1

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute  43024      1.56       1.60          0          0          0           0

Fetch    43024     11.96      11.69          0    1518816          0       43024

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total    86049     13.52      13.30          0    1518816          0       43024

SELECT COUNT(PF2.LIST_ID)

FROM

 T_PRODUCT_FEE PF, T_PRODUCT_FEE PF2,T_POLICY_CHANGE PC WHERE PF.PRODUCT_NUM =

   PF2.PRODUCT_NUM AND PF.INSURED_1 = PF2.INSURED_1 AND PF.POLICY_ID =

  PF2.POLICY_ID AND PF2.FEE_TYPE = 42 AND PF2.CHANGE_ID=PC.CHANGE_ID AND

  PC.SERVICE_ID=201 AND PC.CHANGE_STATUS=3 AND PF.LIST_ID = :B1

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute  42962      2.26       2.02          0          0          0           0

Fetch    42962      3.03       2.88          0     307000          0       42962

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total    85925      5.29       4.90          0     307000          0       42962

可以看出每执行一次主语句，这4个语句都会执行40000多次，如果整个SQL语句由5个主SQL的拼凑起来，那查询1个代理人

这4个语句都会执行200000次，如果代理人为1000个那这4个语句就会执行200000000次，但是单重这4个语句的执行计划来看

都没有大的问题都是通过索引进行查询然后通过ROWID进行关联到行数据未出现FULL TABLE SCAN，所以我初步觉得对单个SQL

的调整视乎得不到好的效果。

同时在AWRRPT中也说明了这一点如下：

SQL ordered by Executions

• Total Executions: 31,614,801
• Captured SQL account for 100.0% of Total

在出问题的一段时间内TOP 4 EXECUTIONS 语句就是我刚才给出的4个片段，请注意上图中的前4位。