10.2.0.4 RAC数据库AWR，烦请帮忙分析

Troy

大家好：

我的一个OLTP数据库，以RAC的方式部署，版本为10.2.0.4，总是有用户反应说系统比较慢。附件里我上传了12月5号的AWR，请大家帮我看下，性能问题在哪里。谢谢！

我看到的几个有问题的地方：

1. Hard Parse：平均每秒32个，Execute to Parse %为35.2，% SQL with executions>1为53%
2. dc_histogram_defs和dc_histogram_data也比较高
3. enq: TX - row lock contention平均等待488ms（逻辑读和物理读平均等待2到3ms）

请大家指点迷津，我这库如果要优化的话从哪几点着手。谢谢

xteitxu

Library Hit %: 81.90 Soft Parse %: 80.30   好低啊

cpasdfx5200

shared pool 加大4400M

UPDATE so_meannc SET ts='2013-12-03 08:58:23', ntotalinvoicenum = :1 WHERE pk_so_meannc = :2

这条语句 要跑 31,642 S ？

545459983

sql 语句select count ( freevalueid ) from gl_freevalue where checkvalue = '0001651000000005XXK1' and checktype in ( '00010000000000000071', '00010000000000000072', '00010000000000000073' ) and freevalueid in ( select distinct assid from gl_detail where gl_detail.dr = 0 and pk_corp = '1135' union all select distinct id from dap_rtvouch_b where dap_rtvouch_b.dr = 0 and pk_corp = '1135' union all select distinct assid from gl_verifydetail where gl_verifydetail.dr = 0 and pk_corp = '1135' union all select distinct assid from gl_modelsetdetail where gl_modelsetdetail.dr = 0 and pk_corp = '1135' union all select distinct dfreevalueid from gl_subrelation where gl_subrelation.dr = 0 and pk_corp = '1135' union all select distinct cfreevalueid from gl_subrelation where gl_subrelation.dr = 0 and pk_corp = '1135' union all select distinct freevalueid from gl_subtoass, gl_subrelation where gl_subtoass.dr = 0 and gl_subrelation.dr = 0 and gl_subtoass.pk_subrelation = gl_subrelation.pk_subrelation and pk_corp = '1135' union all select distinct freevalueid from gl_subtoass, gl_subrelation where gl_subtoass.dr = 0 and gl_subrelation.dr = 0 and gl_subtoass.pk_subrelation = gl_subrelation.pk_subrelation and pk_corp = '1135' )
这个sql 物理读过高105,210,988

xteitxu

刚发现你这快照间隔太长了吧     搞个一个小时的分析

当时库里应该有锁吧？  同时UPDATE  还没执行完
8rk1fzpya5yq6 UPDATE so_meannc SET ts='2013-12-05 08:31:39', ntotalinvoicenum = :1 WHERE pk_so_meannc = :2
8vs7ctx6pd2nf UPDATE so_meannc SET ts='2013-12-03 08:58:23', ntotalinvoicenum = :1 WHERE pk_so_meannc = :2

2y8a6j7b3xz5k UPDATE so_salepreorder_b SET ts='2013-12-04 19:32:18', ntotalpondernum = :1 WHERE pk_saledispose_b = :2
559wc7n9kpb68 update so_saleinvoice set ts='2013-12-05 16:22:23', bfreecustflag = :1, binitflag = :2, btogoldtax = :3,

renjixinchina

enq: TX - row lock contention         189,140         92,330         488         33.2        Application
看下活动进程 被什么sql阻塞

Troy

各位：

感谢，我又上传了一个一小时的AWR。之前上传的可能开发人员在做长时间的数据调整。所以有误导性，这个新上传的时间点内，完全是应用系统的Session。

weiranth

cd2zjpv26y5wh 和 bwq4c63ng4j09  这2条sql 在node1 和node2 上都出现了，分别对应的logical read 和pyhical read 都很高，可能需要优化一下！

lgang403

什么OS，cpu和内存使用率怎么样？

Troy

lgang403 发表于 2013-12-11 16:49
什么OS，cpu和内存使用率怎么样？

操作系统版本为AIX 5.3

xifenfei

1. 64g内存，session数最大多少，oltp为什么给数据库的只有20G左右？
2. top sql大力气优化
3. 恶心的拼接sql

SKYLINE.LIU

1. parse time elapsed        2,064.38        9.69
   
2. hard parse elapsed time        1,952.95        9.17
   
3. 
   
4. parse time elapsed        2,486.83        13.04
   
5. hard parse elapsed time        2,384.27        12.50

复制代码

硬解析占用时间比较多，使用刘大提供的方法《利用FORCE_MATCHING_SIGNATURE捕获非绑定变量SQL》查一下非绑定变量语句看一下