oracle 11201 基数反馈导致主机cpu 开销过高处理

来源：互联网发布：java的前景编辑：程序博客网时间：2024/05/24 02:46

dbversion： 11201

osversion： rhel 5 x64

问题分析：

07:10:01 all 35.26 0.00 8.35 0.19 0.00 56.21

07:20:01 all 34.76 0.00 8.31 0.14 0.00 56.80

07:30:01 all 34.31 0.00 8.31 0.38 0.00 57.00

07:40:01 all 34.78 0.00 8.49 0.24 0.00 56.49

07:50:01 all 34.66 0.02 8.90 0.78 0.00 55.64

08:00:01 all 49.26 0.00 13.66 0.66 0.00 36.42

08:10:01 all 53.71 0.00 14.94 1.24 0.00 30.11

08:20:01 all 52.84 0.00 15.26 1.47 0.00 30.43

08:30:01 all 53.40 0.00 14.89 1.23 0.00 30.48

08:40:01 all 54.40 0.00 15.62 1.71 0.00 28.28

08:40:01 CPU %user %nice %system %iowait %steal %idle

08:50:03 all 53.28 0.02 15.76 2.85 0.00 28.09

09:00:01 all 52.55 0.00 16.04 2.46 0.00 28.95

09:10:02 all 52.06 0.00 15.57 4.72 0.00 27.65

09:20:03 all 51.07 0.00 15.43 6.92 0.00 26.59

09:30:01 all 55.17 0.00 15.46 5.69 0.00 23.68

09:40:02 all 57.53 0.00 13.11 11.82 0.00 17.54

09:50:06 all 72.56 0.02 11.16 7.45 0.00 8.81

10:00:09 all 81.26 0.00 10.39 3.40 0.00 4.95

10:10:04 all 80.83 0.00 10.46 5.73 0.00 2.98

10:20:08 all 69.36 0.00 10.60 11.57 0.00 8.47

10:30:21 all 84.88 0.00 9.46 3.10 0.00 2.55

10:40:02 all 75.92 0.00 10.17 8.97 0.00 4.94

10:50:02 all 65.12 0.03 11.28 10.64 0.00 12.94

11:00:07 all 64.72 0.00 10.25 11.40 0.00 13.63

Average: all 42.74 0.00 9.52 1.73 0.00 46.00

基本确定问题发生在9:50 到11:00 左右；

ash 报告分析：

EventEvent Class% EventAvg Active SessionsCPU + Wait for CPUCPU24.243.65latch: cache buffers chainsConcurrency21.883.30direct path readUser I/O19.262.90db file sequential readUser I/O15.802.38read by other sessionUser I/O8.071.22

初步判定sql 低效，或者存在热点块；

Top SQL with Top Events

SQL IDPlanhashSampled # of Executions% ActivityEvent% EventTop Row Source% RwSrcSQL Text36qyyk2nundka641048545444.46latch: cache buffers chains21.40INDEX - UNIQUE SCAN10.83select o.his_id, o.pattype, p.... direct path read11.82TABLE ACCESS - FULL11.82 CPU + Wait for CPU11.16INDEX - UNIQUE SCAN5.97 1891980347226.08read by other session3.79INDEX - RANGE SCAN3.02select o.his_id, o.pattype, p.... CPU + Wait for CPU1.03INDEX - RANGE SCAN0.70 71pb2jkbf5rvq4051734551694.05CPU + Wait for CPU1.80HASH JOIN1.07SELECT "MODALITYID", "HIS_ID" ...8bw9pc2vtmg312815487021393.02read by other session1.25TABLE ACCESS - FULL1.25SELECT * FROM VIEWORDERLIST WH...109w08fxftuuz523695818252.17db file sequential read1.44TABLE ACCESS - BY INDEX ROWID1.29select q.QUEUEID, o.his_id, o....

sql_id ： 36qyyk2nundka 在故障期间存在2个执行计划；

---各个执行计划的效率：

id plan hash last seen elapsed(s) origin note

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

1 398919928 2015-09-10/11:42:27 8.553Cursor Cache original plan

2 1891980347 2015-09-10/11:44:46 14.414Cursor Cache

3 64104854 2015-09-10/11:43:04 71.373 Cursor Cache ---------------------------和 ash 报告分析非常匹配，该执行计划性能异常差！

----执行计划信息：

SQL> /

PLAN_TABLE_OUTPUT

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

SQL_ID36qyyk2nundka

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

selecto.his_id,o.pattype,p.patname,p.hospitalid,p.inpatientid,p.outpati

entid,h.read_flag,h.imagesseq,o.orderdtfrom t_order o ,T_IMAGETOHIS h,

t_patient p where o.patseq = p.patseq and h.orderseq=o.orderseq and

o.orderstatus='1180' and o.deleted='0' and o.his_id is not null and

h.read_flag='Y'and o.orderdt>=to_char(SYSDATE-10,'yyyy-MM-dd

HH:MI:SS') and rownum<=10

Plan hashvalue: 64104854

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

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

| 0 | SELECT STATEMENT | | | | 36 (100)| |

| 1 | COUNT STOPKEY | | | | | |

| 2 | NESTED LOOPS | | | | | |

| 3 | NESTED LOOPS | | 11 | 3157 | 36 (0)| 00:00:01 |

| 4 | NESTED LOOPS | | 11 | 2090 | 33 (0)| 00:00:01 |

| 5 | TABLE ACCESS FULL |T_IMAGETOHIS | 152 | 11704 | 3 (0)| 00:00:01 |

| 6 | TABLE ACCESS BY INDEX ROWID| T_ORDER | 1 | 113 | 1 (0)| 00:00:01 |

| 7 | INDEX UNIQUE SCAN |PK_T_ORDER | 1 | | 1 (0)| 00:00:01 |

| 8 | INDEX UNIQUE SCAN |PK_T_PATSEQ | 1 | | 1 (0)| 00:00:01 |

| 9 | TABLE ACCESS BY INDEX ROWID |T_PATIENT | 1 | 97 | 1 (0)| 00:00:01 |

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

Note

-----

-cardinality feedback used for this statement

SQL_ID36qyyk2nundka

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

selecto.his_id,o.pattype,p.patname,p.hospitalid,p.inpatientid,p.outpati

entid,h.read_flag,h.imagesseq,o.orderdtfrom t_order o ,T_IMAGETOHIS h,

t_patient p where o.patseq = p.patseq and h.orderseq=o.orderseq and

o.orderstatus='1180' and o.deleted='0' and o.his_id is not null and

h.read_flag='Y'and o.orderdt>=to_char(SYSDATE-10,'yyyy-MM-dd

HH:MI:SS') and rownum<=10

Plan hashvalue: 398919928

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

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

| 0 | SELECT STATEMENT | | | | 3 (100)| |

| 1 | COUNT STOPKEY | | | | | |

| 2 | NESTED LOOPS | | | | | |

| 3 | NESTED LOOPS | | 11 | 3157 | 3 (0)| 00:00:01 |

| 4 | NESTED LOOPS | | 2 | 420 | 2 (0)| 00:00:01 |

| 5 | TABLE ACCESS BY INDEX ROWID| T_ORDER | 4107 | 453K| 1 (0)| 00:00:01 |

| 6 | INDEX RANGE SCAN |IX_T_ORDERDT | 5537 | | 1 (0)| 00:00:01 |

| 7 | TABLE ACCESS BY INDEX ROWID| T_PATIENT | 1 | 97 | 1 (0)| 00:00:01 |

| 8 | INDEX UNIQUE SCAN |PK_T_PATSEQ | 1 | | 1 (0)| 00:00:01 |

| 9 | INDEX RANGE SCAN |IX_T_IAMGETOHIS | 8 | | 1 (0)| 00:00:01 |

| 10 | TABLE ACCESS BY INDEX ROWID |T_IMAGETOHIS | 6 | 462 | 1 (0)| 00:00:01 |

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

SQL_ID36qyyk2nundka

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

selecto.his_id,o.pattype,p.patname,p.hospitalid,p.inpatientid,p.outpati

entid,h.read_flag,h.imagesseq,o.orderdtfrom t_order o ,T_IMAGETOHIS h,

t_patient p where o.patseq = p.patseq and h.orderseq=o.orderseq and

o.orderstatus='1180' and o.deleted='0' and o.his_id is not null and

h.read_flag='Y'and o.orderdt>=to_char(SYSDATE-10,'yyyy-MM-dd

HH:MI:SS') and rownum<=10

Plan hashvalue: 1891980347

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

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

| 0 | SELECT STATEMENT | | | | 19 (100)| |

| 1 | COUNT STOPKEY | | | | | |

| 2 | NESTED LOOPS | | | | | |

| 3 | NESTED LOOPS | | 44 | 12628 | 19 (0)| 00:00:01 |

| 4 | NESTED LOOPS | | 1 | 210 | 18 (0)| 00:00:01 |

| 5 | TABLE ACCESS FULL |T_PATIENT | 406K| 37M| 2 (0)| 00:00:01 |

| 6 | TABLE ACCESS BY INDEX ROWID| T_ORDER | 1 | 113 | 1 (0)| 00:00:01 |

| 7 | INDEX RANGE SCAN |IX_T_PATSEQ | 1 | | 1 (0)| 00:00:01 |

| 8 | INDEX RANGE SCAN |IX_T_IAMGETOHIS | 42 | | 1 (0)| 00:00:01 |

| 9 | TABLE ACCESS BY INDEX ROWID |T_IMAGETOHIS | 39 | 3003 | 1 (0)| 00:00:01 |

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

Note

-----

-cardinality feedback used for this statement

处理结果系cardinality feedback bug ，sql 语句第一次执行性能很好，第二就很差 ：

禁用基数反馈：alter system set "_optimizer_use_feedback"=false;

0 0