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

论坛 › 期权论坛 › 脚本     
匿名技术用户   2020-12-30 08:25   33   0

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;

回复
分享到 :
0 人收藏
返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

16级独孤
匿名技术用户

积分:7942463
帖子:1588486
精华:0
+ 关注 私信
期权论坛 期权论坛
期权网络科技版权所有
关于我们
联系我们
加入我们
反馈问题
免责声明
积分充值
统一社会信用代码
积分规则
网站地图
爱文库
下属网站
官方
新浪微博
微信公众号
◆
下载
表情包
App下载
期权论坛 期权论坛

QQ咨询|关于我们|Archiver|手机版|小黑屋|( 辽ICP备15012455号-4 ) Powered by 期权论坛 X3.2 © 2001-2016 期权工具网&期权论坛 Inc.

发布
内容

下载期权论坛手机APP