分区表分区时粒度太大解决

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

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

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

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

| 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、将分区粒度变小，拆分区。

最后业务恢复正常