使用Oracle位圖索引、函數(shù)索引和綁定變量解決數(shù)據(jù)庫服務(wù)器CPU占用過高的案例20110419

《使用Oracle位圖索引、函數(shù)索引和綁定變量解決數(shù)據(jù)庫服務(wù)器CPU占用過高的案例20110419》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在行業(yè)資料-天天文庫。

1、使用Oracle位圖索引、函數(shù)索引和綁定變量解決數(shù)據(jù)庫服務(wù)器CPU占用過高的案例項(xiàng)目介紹:項(xiàng)目名稱：XX疾病防治數(shù)據(jù)信息管理系統(tǒng)問題表現(xiàn)該系統(tǒng)的人口學(xué)特征統(tǒng)計(jì)的操作是本次測試點(diǎn)中響應(yīng)時(shí)間最慢，CPU占用最高（100％），和并發(fā)用戶數(shù)最少(25)的一個(gè)測試點(diǎn)。50用戶并發(fā)時(shí)全部失敗，QALOAD報(bào)“WWWDO_Http:WWW_ERROR_00100--Genericexception:qlhttp::connection::select_error”并發(fā)用戶數(shù)(個(gè))平均響應(yīng)時(shí)間（秒）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用戶并發(fā)執(zhí)行人口學(xué)特征統(tǒng)計(jì)的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-