资源描述：
《使用Oracle位图索引、函数索引和绑定变量解决数据库服务器CPU占用过高的案例20110419》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、使用Oracle位图索引、函数索引和绑定变量解决数据库服务器CPU占用过高的案例项目介绍:项目名称：XX疾病防治数据信息管理系统问题表现该系统的人口学特征统计的操作是本次测试点中响应时间最慢，CPU占用最高（100％），和并发用户数最少(25)的一个测试点。50用户并发时全部失败，QALOAD报“WWWDO_Http:WWW_ERROR_00100--Genericexception:qlhttp::connection::select_error”并发用户数(个)平均响应时间（秒）110.1525106
2、.46CPU占用情况100.0090.0080.0070.0060.0050.0040.00%ProcessorTime30.0020.0010.000.000.0030.0060.0090.00120.00150.00180.00210.00240.00270.00300.00330.00ElapsedTime(Second)图150用户并发执行人口学特征统计的CPU占用情况问题分析：捕捉该操作的SQL如下：select'1'asa1,count(d.pid)asc1fromAIDSZH_MST_PAT
3、IENTSdwheretrunc((Months_between(to_date('2007-10-11','yyyy-mm-dd'),d.BIRT_DATE)/12))>=0andtrunc((Months_between(to_date('2007-10-11','yyyy-mm-dd'),d.BIRT_DATE)/12))<20and1=1andd.clin_zonecodelike'%'andd.DROP_OUT='2'unionselect'2'asa1,count(d.pid)asc1from
4、AIDSZH_MST_PATIENTSdwheretrunc((Months_between(to_date('2007-10-11','yyyy-mm-dd'),d.BIRT_DATE)/12))>=20andtrunc((Months_between(to_date('2007-10-11','yyyy-mm-dd'),d.BIRT_DATE)/12))<25and1=1andd.clin_zonecodelike'%'andd.DROP_OUT='2'unionselect'3'asa1,count
5、(d.pid)asc1fromAIDSZH_MST_PATIENTSdwheretrunc((Months_between(to_date('2007-10-11','yyyy-mm-dd'),d.BIRT_DATE)/12))>=25andtrunc((Months_between(to_date('2007-10-11','yyyy-mm-dd'),d.BIRT_DATE)/12))<30and1=1andd.clin_zonecodelike'%'andd.DROP_OUT='2'unionsele
6、ct'4'asa1,count(d.pid)asc1fromAIDSZH_MST_PATIENTSdwheretrunc((Months_between(to_date('2007-10-11','yyyy-mm-dd'),d.BIRT_DATE)/12))>=30andtrunc((Months_between(to_date('2007-10-11','yyyy-mm-dd'),d.BIRT_DATE)/12))<35and1=1andd.clin_zonecodelike'%'andd.DROP_O
7、UT='2'unionselect'5'asa1,count(d.pid)asc1fromAIDSZH_MST_PATIENTSdwheretrunc((Months_between(to_date('2007-10-11','yyyy-mm-dd'),d.BIRT_DATE)/12))>=35andtrunc((Months_between(to_date('2007-10-11','yyyy-mm-dd'),d.BIRT_DATE)/12))<40and1=1andd.clin_zonecodelik
8、e'%'andd.DROP_OUT='2'unionselect'6'asa1,count(d.pid)asc1fromAIDSZH_MST_PATIENTSdwheretrunc((Months_between(to_date('2007-10-11','yyyy-mm-dd'),d.BIRT_DATE)/12))>=40andtrunc((Months_between(to_date('2007-10-11','yyyy-
