aix单机11.1.0.6+raw存储性能问题

leonhat

描述：

ibm p7 aix6.1
oracle 11.1.0.6
华为盘阵 raw设备存储

目前现象是数据库操作很慢，不论是dml还是select都慢

下面是awr报告，请帮忙分析下，谢谢

gzmz_awr.html (341.93 KB, 下载次数: 770)

2013-2-27 10:04 上传

点击文件名下载附件

leonhat

难道还是顺序读的问题？

钱多多

从报告看，SAAS_BUSINESS这个表空间的读写很频繁，而且整体系统等待事件里，前台是db file sequential read，后台是db file sequential write，全部是数据文件的读写。
SQL的话，有一些耗时挺多的SQL，比如下面这一句

select be01.abe010 as 申请审批编码, be01.aaa006 as 经办人员ID, be01.aab010 as 家庭信息编码, be01.org_oid as 经办机构编码, be01.aad011 as 行政区划编码, be01.business_type as 救助业务编码, ae05.aae050 as 变更信息ID, ae05.aac016 as 低保证号, be01.aaa005 as 当前标准, nvl(ab21.aab014, 'NULL') as 家庭主要致贫原因, nvl(ab21.aab016, 'NULL') as 家庭分类救助类别, nvl(ac22.AAC023, 'NULL') as 残疾等级, nvl(ac22.AAC02D, 'NULL') as 人员分类救助类别, nvl(ac21.aac015, 'NULL') as 家庭关系, nvl(ac21.AAC010, 'NULL') as 文化程度, nvl(ac21.AAC014, 'NULL') as 是否三无, nvl(ac22.AAC022, 'NULL') as 残疾类别, nvl(ac21.AAC018, 'NULL') as 人员状态, nvl(ac21.AAC012, 'NULL') as 劳动能力, nvl(ac21.AAC009, 'NULL') as 婚姻状况, nvl(ac21.AAC013, 'NULL') as 健康状况, nvl(ac21.AAC017, 'NULL') as 户籍性质, nvl(ac21.AAC003, 'NULL') as 性别, nvl(be01.abe012, '01') as 业务办理类型, nvl(ac21.AAC008, 'NULL') as 就业状况, nvl(ac21.AAC006, 'NULL') as 民族, nvl(ac21.AAC011, 'NULL') as 政治面貌, ac21.aac000 as 人员编码, nvl(ac22.aac021, '01') as 是否保障范围, ac22.AAC02C as 年收入, ac21.AAC005 as 出生日期_TEMP, ac21.AAC004 as 身份证号码, 0 as 月总收入, '' as 出生日期, 'NULL' as 身体状况, '14' as 分类施保类别, --默认为C类对象 0 as 当前标准金额, '' as 主键, '522627%' as zoneCode from be01, ae05, ab21, ac21, ac22 where be01.abe010 = ae05.abe010 and be01.business_type = '120' and ae05.aae0 50=ab21.aae050 and be01.abe019 = '99' and ae05.aae050=ac21.aae050 and ae05.aae050=ac22.aae050 and ac21.aac000=ac22.aac000 and ab21.aab010=ac21.aab010 -- and ac22.aac021='01' and be01.aaa007>:1 and be01.aaa007<:2 and be01.aad011 like '522627%'

还有一些和这个业务类似的SQL，跑起来很耗资源，Get和Executions都挺高。

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

能否考虑把一些读的频繁的表分开表空间放？还有就是能否把上面这条SQL的执行计划贴出来看看？

关于你的问题，数据库操作慢，是因为磁盘读写都很高，相比之下CPU还好，内存占用也一般般，不算高但是也不少了。

renjixinchina

数据库负载比较高,
DB CPU 很低  
先优化下 SQL ordered by Gets和SQL ordered by Reads的前三个sql
占的比重很高，但是执行次数不高

leonhat

谢谢钱多多和renjixinchina两位的回复。

我先优化一下sql再看看。