1、CPU接近100% nmon数据 8月5日在9：209：40之间，出现CPU接近100%的情况，特点表现为9：20左右CPU急剧攀升，在9：45左右又快速下降 2、原因分析结果总述 2.1 持续时间与恢复方式 此次CPU攀高时间持续约20分钟，在无人工干预的情况下自动恢复 2.2 原因分析
1、CPU接近100% nmon数据
8月5日在9：20—9：40之间，出现CPU接近100%的情况，特点表现为9：20左右CPU急剧攀升，在9：45左右又快速下降
2、原因分析结果总述
2.1 持续时间与恢复方式
此次CPU攀高时间持续约20分钟，在无人工干预的情况下自动恢复
2.2 原因分析总述：
经过分析，原因为：4条SQL语句ORACLE优化器对LB_T_XXXVIDER视图、LB_T_XXXJECT_PROVIDER表、LA_XXCKAGE表的基数数据评估发生了巨大的差错，导致选择了错误的执行计划，消耗大量的CPU资源
2.3 错误执行计划估算数据与正确执行计划估算数据对比
此处为选择一条最严重的SQL语句为例，其它语句原因相同
错误执行计划基数估算值 | Execution Plan Id | Operation | Name | Rows | Bytes | Cost (%CPU) | 0 | SELECT STATEMENT | | | | 315 (100) | 1 | COUNT STOPKEY | | | | | 2 | VIEW | | 1 | 180 | 315 (2) | 3 | SORT ORDER BY STOPKEY | 来源gaodaimacom搞#^代%!码网 | 1 | 151 | 315 (2) | 4 | HASH UNIQUE | | 1 | 151 | 314 (1) | 5 | FILTER | | | | | 6 | NESTED LOOPS OUTER | | 1 | 151 | 313 (1) | 7 | NESTED LOOPS | | 1 | 86 | 35 (0) | 8 | TABLE ACCESS BY INDEX ROWID | LB_T_XXXJECT_PROVIDER | 1 | 61 | 34 (0) | 9 | INDEX RANGE SCAN | IDX_LB_T_XXXJECT_PROVIDER_003 | 183 | | 3 (0) | 10 | TABLE ACCESS BY INDEX ROWID | LA_XXCKAGE | 1 | 25 | 1 (0) | 11 | INDEX UNIQUE SCAN | PK_LA_XXCKAGE | 1 | | 0 (0) | 12 | VIEW PUSHED PREDICATE | LB_T_XXXVIDER | 1 | 65 | 278 (1) | 13 | MERGE JOIN OUTER | | 1 | 64 | 278 (1) | 14 | TABLE ACCESS BY INDEX ROWID | XXCC_SUPPLIER | 1 | 45 | 146 (0) | 15 | INDEX FULL SCAN | PK_XXCC_SUPPLIER | 1 | | 145 (0) | 16 | SORT JOIN | | 17998 | 333K | 132 (2) | 17 | VIEW | | 17998 | 333K | 131 (1) | 18 | SORT GROUP BY | | 17998 | 544K | 131 (1) | 19 | TABLE ACCESS FULL | XXCC_SUPPLIER_CONTACT | 30058 | 909K | 130 (0) |
|
正确执行计划基数估算值 | Execution Plan Id | Operation | Name | Rows | Bytes | Cost (%CPU) | 0 | SELECT STATEMENT | | | | 64460 (100) | 1 | COUNT STOPKEY | | | | | 2 | VIEW | | 224K | 38M | 64460 (1) | 3 | SORT ORDER BY STOPKEY | | 224K | 28M | 64460 (1) | 4 | HASH UNIQUE | | 224K | 28M | 58849 (1) | 5 | FILTER | | | | | 6 | HASH JOIN OUTER | | 224K | 28M | 53237 (1) | 7 | NESTED LOOPS | | | | | 8 | NESTED LOOPS | | 347 | 29842 | 528 (0) | 9 | TABLE ACCESS BY INDEX ROWID | LB_T_XXXJECT_PROVIDER | 347 | 21167 | 181 (0) | 10 | INDEX RANGE SCAN | IDX_PROJECT_PROVIDER_COMB1 | 182 | | 4 (0) | 11 | INDEX UNIQUE SCAN | PK_LA_XXCKAGE | 1 | | 0 (0) | 12 | TABLE ACCESS BY INDEX ROWID | LA_XXCKAGE | 1 | 25 | 1 (0) | 13 | VIEW | LB_T_XXXVIDER | 9125K | 409M | 52700 (1) | 14 | HASH JOIN OUTER | | 9125K | 556M | 52700 (1) | 15 | TABLE ACCESS FULL | XXCC_SUPPLIER | 26139 | 1148K | 404 (0) | 16 | VIEW | | 6283K | 113M | 52287 (1) | 17 | SORT GROUP BY | | 6283K | 185M | 52287 (1) | 18 | TABLE ACCESS FULL | XXCC_SUPPLIER_CONTACT | 10M | 309M | 148 (9) | - cardinality feedback used for this statement
|
|
