Partition Table Partitioning with a large granularity

The partition table partition granularity is too large to solve the oracle database. performance problems occur in the afternoon. 1. Check the cpu, memory, io, and other performance parameters of the two servers and find that the CPU usage is close to 100%; 2, check the oracle session information found that the TMID_DEV_USER_DAY, TMID_INCO_USER_DAY, TMID_USE_USER_DAY and other tables have a waiting event, as follows: 1 read by other session package_PR_TADR_CDMA_ALL_DAY-028-201305 966: 4233293320 CTCNET dsadm @ dss-db31 @ 12542 05-07 12: 482 read by other session package_PR_TADR_CDMA_ALL_DAY-991-201305 823: 3701837735 CTCNET dsadm @ dss-db31 @ 20264 05-07 83 read by other session package_PR_TADR_CDMA_ALL_DAY-771-201305 990: 2304318681 CTCNET dsadm @ dss-db31 @ 15093 05-07 15: 394 db file sequential read JDBC Thin Client886: 1749825776 CTCNET dsadm @ dss-db31 @ 11831 05-07 db file scattered read package_PR_TAdR_3gdev_user_day-791-20131080: 164352509 CTCNET dsadm @ dss-db31 @ 26302 05-07 15: 466 db file scattered read package_PR_TADR_CDMA_ALL_DAY-371-201305 82 0: 4233293320 CTCNET dsadm @ dss-db31 @ 11817 05-07 13:057 db file scattered read package_PR_TADR_CDMA_ALL_DAY-029-201305 1010: 1780394967 CTCNET dsadm @ dss-db31 @ 1971905-07 PX Deq Credit: send blkd oracle @ dss-db41 (PZ99) 908: 2432359197 CTMB zhanghuijun @ WORKGROUP \ DL580-2 @ 1 05-07 15: 561 check the SQL and its execution plan for these wait events as follows: UPDATE tadr_cdma _all_day t set t. LY_ONLINE_USER = (select sum (X. ONLINE_USER) FROM TMID_DEV _ USER_DAY XWHERE (X. DR_PROV_ID =: B1 OR: B1 = '001') and x. STAT_DATE =: B3 and t. DR_PROV_ID = X. DR_PROV_ID ANDT. REGION_ID = X. REGION_ID) where t. STAT_DATE =: B2 AND (T. DR_PROV_ID =: B1 OR: B1 = '001') Plan hash value: 3471877290 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Psto P | STATEMENT | 0 | update statement | 2208 K (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 | 122 K (1) | 00:28:37 | KEY | KE Y | 5 | table access full | TMID_DEV_USER_DAY | 5 | 100 | 122 K (1) | 00:28:37 | KEY | from the analysis plan, the query efficiency of tables such as TMID_DEV_USER_DAY, TMID_INCO_USER_DAY, and TMID_USE_USER_DAY is low. The statement is as follows: UPDATE tadr_cdma _all_day t set t. LY_TOT_DEV_USER = (select sum (X. CM_TOT_DEV_USER) FROMTMID_DEV_USER_DAY x where (X. DR_PROV_ID =: B1 OR: B1 = '001') and x. STAT_DATE =: B3 ANDT. 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') cause: 1. During the billing cycle at the beginning of the month, the overlay of various task combinations causes overload of CPU usage and performance problems. 2. The size of large table partitions is larger than that of data (for example, the number of table partitions reaches million records). Because the oracle optimizer itself has a dynamic sample mechanism, for large tables, changes in internal statistics are more likely to lead to the probability of an uncertain impact on the execution plan and affect the SQL Execution efficiency.
Solution: 1. Create a local index in the table. 2. Reduce the partition granularity and split the partition. Finally, the Business resumes normal.