qhd2004

41 积分	0 好友	15 主题

发消息

[RAC Clusterware] rhel5+10204上性能问题，附件是awr+addm

1^#

发表于 2014-3-14 16:23:23 | 查看: 3173| 回复: 7

os：Red Hat Enterprise Linux Server release 5.2 (Tikanga)
db： 10.2.0.4.0 - 64bit

这两个我们这个库出现性能问题，里面有大量insert，可能io会存在性能问题，请各位帮忙分析下。

另：对于io，有什么根据或是相应的数值可以说明是io差或是好

avg-cpu:  %user %nice %system %iowait  %steal %idle
         2.18 0.00 0.75 14.61 0.00 82.46

Device:          tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda             18.81    1188.12       0.00    1200       0
sdb          340.59    68514.85    1821.78    69200    1840
sdc             16.83       15.84       0.99       16       1
sdd             0.00       0.00       0.00       0       0
sde             0.00       0.00       0.00       0       0
sdf             0.00       0.00       0.00       0       0
sdg             0.00       0.00       0.00       0       0
sdh             0.00       0.00       0.00       0       0
sdi             0.00       0.00       0.00       0       0
dm-0          741.58    68182.18    1829.70    68864    1848
dm-2          741.58    68182.18    1829.70    68864    1848
sdj             0.00       0.00       0.00       0       0
sdk             0.00       0.00       0.00       0       0
sdl             0.00       0.00       0.00       0       0
sdm             0.00       0.00       0.00       0       0
dm-1             0.00       0.00       0.00       0       0

在上面的这个输出中，我如何知道68182.18的Blk_read/s已经是极限呢？

awrrpt_addm.rar

35.52 KB, 下载次数: 1028

分享0

收藏0 回复只看该作者道具举报

Maclean Liu(刘相兵

2^#

发表于 2014-3-14 16:36:30

log file parallel write 7,412 0.00 2,258 305 0.36
control file parallel write 1,368 0.00 596 436 0.07

action plan:

set timing on;
alter database add logfile group 20 size 1g;

给出上面的输出

回复只看该作者道具举报

qhd2004

3^#

发表于 2014-3-14 17:00:56

SQL> set timing on;
SQL> ALTER DATABASE ADD LOGFILE GROUP 38 ('/data01/oradata/smslog/redo38_01.log') SIZE 5G;

Database altered.

Elapsed: 00:02:44.19
SQL>

回复只看该作者道具举报

Maclean Liu(刘相兵

4^#

发表于 2014-3-14 17:07:39

5g/164s=30MB/s

假定 Io吞吐量的上限为30M左右

Physical reads: 5,242.93 929.27
Physical writes: 21.11 3.74

你的AWR中每秒的吞吐量大约为40 MB，即IO峰值情况下导致IO响应很慢

回复只看该作者道具举报

Maclean Liu(刘相兵

5^#

发表于 2014-3-14 17:11:07

Physical Reads Executions Reads per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
8,186,762 1 8,186,762.00 43.27 562.78 3461.99 fmt8hr9dntv5m JDBC Thin Client select * from (select * from G...
1,001,154 1 1,001,154.00 5.29 26.64 422.95 3hbm32un274wz JDBC Thin Client select * from (select t.log_id...

select * from (select * from GW_7000_STATEREPORTLOG t where t.log_id>:1 order by t.log_id) where rownum<=500
select * from (select t.log_id, t.inner_service_id, t.msg_id, t.registered_delivery, t.service_id, t.src_id, t.dest_terminal_id, t.report_id from GW_7000_MTLOG t where t.log_id>:1 order by t.log_id) where rownum<=500

2个类似的SQL只要求返回500行，但消耗了大量IO

table scans (long tables) 33 0.01 0.00

主线问题估计很简单就是有大的全表扫描

Segments by Physical Reads

Total Physical Reads: 18,919,861
Captured Segments account for 91.1% of Total

Owner Tablespace Name Object Name Subobject Name Obj. Type Physical Reads %Total
SMSLOGUSR TBS_USMP01 GW_7000_MTLOG P20130711 TABLE PARTITION 2,905,734 15.36
SMSLOGUSR TBS_USMP01 GW_7000_MTLOG P20130712 TABLE PARTITION 2,792,884 14.76
SMSLOGUSR TBS_USMP01 GW_7000_MTLOG P20130709 TABLE PARTITION 2,407,568 12.73
SMSLOGUSR TBS_USMP01 GW_7000_MTLOG P20130708 TABLE PARTITION 680,456 3.60
SMSLOGUSR TBS_USMP01 GW_7000_STATEREPORTLOG P20140309 TABLE PARTITION 677,182 3.58

回复只看该作者道具举报