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 报告分析:
Event | Event Class | % Event | Avg Active Sessions |
---|---|---|---|
CPU + Wait for CPU | CPU | 24.24 | 3.65 |
latch: cache buffers chains | Concurrency | 21.88 | 3.30 |
direct path read | User I/O | 19.26 | 2.90 |
db file sequential read | User I/O | 15.80 | 2.38 |
read by other session | User I/O | 8.07 | 1.22 |
初步判定sql 低效,或者存在热点块;
Top SQL with Top Events
SQL ID | Planhash | Sampled # of Executions | % Activity | Event | % Event | Top Row Source | % RwSrc | SQL Text |
---|---|---|---|---|---|---|---|---|
36qyyk2nundka | 64104854 | 54 | 44.46 | latch: cache buffers chains | 21.40 | INDEX - UNIQUE SCAN | 10.83 | select o.his_id, o.pattype, p.... |
direct path read | 11.82 | TABLE ACCESS - FULL | 11.82 | |||||
CPU + Wait for CPU | 11.16 | INDEX - UNIQUE SCAN | 5.97 | |||||
1891980347 | 22 | 6.08 | read by other session | 3.79 | INDEX - RANGE SCAN | 3.02 | select o.his_id, o.pattype, p.... | |
CPU + Wait for CPU | 1.03 | INDEX - RANGE SCAN | 0.70 | |||||
71pb2jkbf5rvq | 4051734551 | 69 | 4.05 | CPU + Wait for CPU | 1.80 | HASH JOIN | 1.07 | SELECT "MODALITYID", "HIS_ID" ... |
8bw9pc2vtmg31 | 2815487021 | 39 | 3.02 | read by other session | 1.25 | TABLE ACCESS - FULL | 1.25 | SELECT * FROM VIEWORDERLIST WH... |
109w08fxftuuz | 523695818 | 25 | 2.17 | db file sequential read | 1.44 | TABLE ACCESS - BY INDEX ROWID | 1.29 | select 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
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 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
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 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
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 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;