分区表分区时粒度太大

oracle数据库，下午发生性能问题

 

1、  检查两台服务器cpu、memory、io等性能参数发现CPU使用率接近100%；

2、  检查oracle session信息发现TMID_DEV_USER_DAY、TMID_INCO_USER_DAY、TMID_USE_USER_DAY等多个表上有等待事件，如下：

1  read by other session     package_PR_TADR_CDMA_ALL_DAY-028-201305    966:4233293320CTCNET     dsadm@dss-db31@@12542   05-07 12:48

2  read by other session     package_PR_TADR_CDMA_ALL_DAY-991-201305    823:3701837735CTCNET     dsadm@dss-db31@@20264   05-07 08:48

3  read by other session     package_PR_TADR_CDMA_ALL_DAY-771-201305    990:2304318681CTCNET     dsadm@dss-db31@@15093   05-07 15:39

4  db file sequential read   JDBC Thin Client886:1749825776CTCNET     dsadm@dss-db31@@11831   05-07 10:25

5  db file scattered read     package_PR_TAdR_3gdev_user_day-791-20131080:164352509CTCNET     dsadm@dss-db31@@26302   05-07 15:46

6  db file scattered read     package_PR_TADR_CDMA_ALL_DAY-371-201305    820:4233293320CTCNET     dsadm@dss-db31@@11817   05-07 13:05

7  db file scattered read     package_PR_TADR_CDMA_ALL_DAY-029-201305    1010:1780394967        CTCNET     dsadm@dss-db31@@1971905-07 12:52

8         PX Deq Credit: send blkd       oracle@dss-db41 (PZ99)        908:2432359197CTMB         zhanghuijun@WORKGROUP\DL580-2@1         05-07 15:56

1、  检查这些等待事件对应的sql及其执行计划如下：

UPDATE TADR_CDMA_ALL_DAY T SET T.LY_ONLINE_USER =( SELECT SUM(X.ONLINE_USER) FROM TMID_DEV_USER_DAY X

WHERE (X.DR_PROV_ID=:B1 OR :B1 ='001' ) AND X.STAT_DATE=:B3 AND T.DR_PROV_ID=X.DR_PROV_ID AND

T.REGION_ID=X.REGION_ID) WHERE T.STAT_DATE=:B2 AND (T.DR_PROV_ID=:B1 OR :B1 ='001' )

 

Plan hash value: 3471877290

 

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

| Id  | Operation                | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

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

|   0 | UPDATE STATEMENT         |                   |       |       |  2208K(100)|          |       |       |

|   1 |  UPDATE                  | TADR_CDMA_ALL_DAY |       |       |            |          |       |       |

|   2 |   TABLE ACCESS FULL      | TADR_CDMA_ALL_DAY |    18 |   414 |  1572   (1)| 00:00:23 |       |       |

|   3 |   SORT AGGREGATE         |                   |     1 |    20 |            |          |       |       |

|   4 |    PARTITION RANGE SINGLE|                   |     5 |   100 |   122K  (1)| 00:28:37 |   KEY |   KEY |

|   5 |     TABLE ACCESS FULL    | TMID_DEV_USER_DAY |     5 |   100 |   122K  (1)| 00:28:37 |   KEY |   KEY |

从分析计划来看TMID_DEV_USER_DAY、TMID_INCO_USER_DAY、TMID_USE_USER_DAY等表查询效率较低。

语句如下：

UPDATE TADR_CDMA_ALL_DAY T SET T.LY_TOT_DEV_USER =( SELECT SUM(X.CM_TOT_DEV_USER) FROM
TMID_DEV_USER_DAY X WHERE (X.DR_PROV_ID=:B1 OR :B1 ='001' ) AND X.STAT_DATE=:B3 AND
T.DR_PROV_ID=X.DR_PROV_ID AND T.REGION_ID=X.REGION_ID) WHERE T.STAT_DATE=:B2 AND (T.DR_PROV_ID=:B1 OR
:B1 ='001' )

原因：

1、月初出帐期间，各种任务组合的叠加意外导致CPU的使用过载，加重了性能问题。

2、大表分区相比较数据规模颗粒偏大（比如，有表单分区达到5千万记录），由于oracle优化器本身有动态sample等机制，对大表而言内部的统计信息变化更容易导致对执行计划产生不确定影响的概率，影响SQL执行效率。

解决方法：

1、在表中创建local索引。

2、将分区粒度变小，拆分区。

最后业务恢复正常