資產(chǎn)管理系統(tǒng)實施優(yōu)化方法參考數(shù)據(jù)量100W一．財政端部署方式應用服務器與數(shù)據(jù)庫服務器分開部署二．硬件配置要求(服務器端)應用服務器配置：雙CUP 內(nèi)存2G數(shù)據(jù)庫服務器：AIX小型機三．網(wǎng)絡帶寬要求？建議是在內(nèi)網(wǎng)上上報數(shù)據(jù)，不要走外網(wǎng)四?數(shù)據(jù)庫優(yōu)化(oracle9i)我們發(fā)現(xiàn)有時服務器硬件很好，但是Oracle就是很慢，很可能是內(nèi)存分配不合理造成的。主要是SGA和PGA的分配：SGAshared_pool_size共享池+databuffer緩沖區(qū)高速緩存+large_pool_size大型池+java_pool_sizeJava池=SGAPGA并發(fā)執(zhí)行進程數(shù)*(sort_area_size+hash_ara_size+2M)=PGA一般的內(nèi)存分配原則：聯(lián)機事物處理OLTP：SGA：65%；PGA：15%；OS：20%數(shù)據(jù)倉庫和決策支持DSS：SGA：30%；PGA：50%；OS：20%常用的分配方式：1.SGA：50%；PGA：35%；OS：15%2.SGA：50%；PGA：30%；OS：20%其中SGA中的分配方式：BufferCache：80%；SharePool：15%；Other：5%BufferCache：80%；SharePool：12%；Other：8%例如：2g的windows的平臺，os300m,sag1.2g,pga500m其中shared_pool_size=200M,databuffer=800M注意：在32bit的數(shù)據(jù)庫下，通常oracle只能使用不超過1.7G的內(nèi)存調(diào)整方法：(1)使用命令行調(diào)整SGA和緩沖區(qū)高速緩存等C:\DocumentsandSettings\zwchenliang>sqlplus"sys/sysassysdba"SQL>select*fromv$sga;NAME VALUE

453852209715200453852209715200209715200667648VariableSizeDatabaseBuffersRedoBuffersSQL>altersystemsetsga_max_size=500Mscope=spfile;系統(tǒng)已更改。SQL>altersystemsetpga_aggregate_target=50Mscope=spfile;系統(tǒng)已更改。SQL>altersystemsetdb_cache_size=300Mscope=spfile;系統(tǒng)已更改。SQL>altersystemsetLARGE_POOL_SIZE=64Mscope=spfile;系統(tǒng)已更改。SQL>shutdownimmediate數(shù)據(jù)庫已經(jīng)關閉。已經(jīng)卸載數(shù)據(jù)庫。ORACLE例程已經(jīng)關閉。SQL>startup529604068bytes454116529604068bytes454116bytes209715200bytes318767104bytes667648bytesTotalSystemGlobalAreaFixedSizeVariableSizeDatabaseBuffersRedoBuffers數(shù)據(jù)庫裝載完畢。數(shù)據(jù)庫已經(jīng)打開。(2)此外，緩沖區(qū)高速緩存、共享池、大型池、Java池也可以在控制臺調(diào)整(3)分析表加快查詢速度：analyzeTABLEyqxhz_aid.GD_ASSETCARD_Hcomputestatistics;(4)測試命中率檢驗調(diào)整效果select1-(sum(decode(name,'physicalreads',value,0))/(sum(decode(name,'dbblockgets',value,0))+(sum(decode(name,'consistentgets',value,0)))))"bufferhitratio"fromv$sysstat;一般命中率大于95%,才是比較好的oracle內(nèi)存配置,否則要加大緩沖區(qū)高速緩存(5)另外還可以強制數(shù)據(jù)庫啟動的時候就把所有內(nèi)存頁裝載，將把所有SGA頁裝載到內(nèi)存中,以便使該例程迅速達到最佳性能狀態(tài)，但這將增加例程啟動和用戶登錄的時間。(慎用，如果前面步驟可以提高效率，則最好不要做此操作)altersystemsetpre_page_sga=truescope=spfile五．數(shù)據(jù)展現(xiàn)優(yōu)化1.更改固定資產(chǎn)表頭表為分區(qū)表注意點：對已經(jīng)存儲了客戶數(shù)據(jù)的數(shù)據(jù)庫要提前做好備份詳細的分區(qū)操作：1)EXECDBMS_REDEFINITION.CAN_REDEF_TABLE(USER,'gd_assetcard_h',

DBMS_REDEFINITION.CONS_USE_PK);2）創(chuàng)建分區(qū)中間表（以BILLTAC為標準進行分區(qū)）

createtableGD_ASSETCARD_H_NEW(RWID(RWIDBILLTAGMODELIDBILLCODESTDNAMETYPEIDSRCIDBILLDATEBUILDDATEUSEDATEPLACEIDBMIDSPECQTYUNITQTYDPRCTWAYIDEXPTMONTHUSEDMONTHLOADUNITUSESTATEDPRCTSTATEPERIODREMARKCONFIRMFLAGCONFIRMERCONFIRMDATEISCREATEPZPZCREATERPZCREATEDATEISTRANSFERPZOPERATORRWVRSNORGIDZTYEARCATALOGIDNAMEPLATEMANUFACTORYMANUDATEHOLDUNITNUMBER(10)default0,NUMBER(10)default0,VARCHAR2(50),VARCHAR2(60),NUMBER(10)default'',NUMBER(10)default0,DATE,DATE,DATE,NUMBER(10)default0,NUMBER(10)default0,VARCHAR2(60)default'',VARCHAR2(60)default'',NUMBER,NUMBER(10)default0,NUMBER(10)default0,NUMBER(10)default0,VARCHAR2(60),NUMBER(10)default0,NUMBER(10)default0,NUMBER(10)default0,VARCHAR2(250),NUMBER(1),VARCHAR2(100)default'',DATE,NUMBER(1),VARCHAR2(60)default'',DATE,NUMBER(1),VARCHAR2(100)default'',NUMBER(10)default0,NUMBER(10)default0,NUMBER(10)default0,NUMBER(10)default0,VARCHAR2(60)default'',VARCHAR2(255)default'',DATE,VARCHAR2(100)default'',

ZGFLAGSHRTNAMEPRNTCODESTARTFLAGOBJID"LEVEL"OVERDUETIMEISFUTUREPIPENONUMASSERTATTRIDZGFLAGSHRTNAMEPRNTCODESTARTFLAGOBJID"LEVEL"OVERDUETIMEISFUTUREPIPENONUMASSERTATTRIDVARCHAR2(25)default'',VARCHAR2(60)default'',NUMBER(1)default1,NUMBER(10)default0,NUMBER(10)default(-1),NUMBER(10)default3000,NUMBER(1),NUMBER(10),NUMBER(10),NUMBER(10),USEDWAYDISPMODEISUSEDASSETUSERISUSEDASCHILDNUMBER(10),NUMBER(10),NUMBER(1),VARCHAR2(60),NUMBER(1),USEDWAYBDIDCHUZCJDFDWNUMBER(10),VARCHAR2(60),PINPVARCHAR2(60),USEDSTATEBDIDNUMBER(10),JIAZXSIDKUAIJPZHSHIYRIDBAOXJZRQZHAOPDENGJDYBSSHIFYBYYWEICZZUOLWZCHANQXSIDGUANLDWCHELCDIDCHEJHCHEPHCHANGPXHFADJHBIANZQKIDYONGTFLIDJIANZJGIDJUNGRQQUANSZMQUANSZHFAZSJCHUJDWJIAZXSIDKUAIJPZHSHIYRIDBAOXJZRQZHAOPDENGJDYBSSHIFYBYYWEICZZUOLWZCHANQXSIDGUANLDWCHELCDIDCHEJHCHEPHCHANGPXHFADJHBIANZQKIDYONGTFLIDJIANZJGIDJUNGRQQUANSZMQUANSZHFAZSJCHUJDWNUMBER(10),VARCHAR2(60),NUMBER(10),DATE,BLOB,NUMBER(1),NUMBER(1),NUMBER(1),VARCHAR2(300),NUMBER(10),VARCHAR2(300),NUMBER(10),VARCHAR2(300),VARCHAR2(300),VARCHAR2(300),VARCHAR2(300),NUMBER(10),NUMBER(10),NUMBER(10),DATE,VARCHAR2(60),VARCHAR2(60),DATE,VARCHAR2(300),ZHUCDJJGZHUCDJSJZHUANLHPIZWHSHIYNXQINGCXTZCBHTIAOMBHYULJCSJID1YULRQZD1YULJCSJID2YULZFCZD1DWMCYULZFCZD2YULZFCZD3SHIRYTAIZHCGZZXSIDYULINT1YULDATE1YULBOOL1YLJC1IDYLJC2IDYLJC3IDYLJC4IDYLJC6IDYULBLOB1YULBLOB2YULBLOB3PLACEGUIDPLACECODEPLACENAMEZHUCDJJGZHUCDJSJZHUANLHPIZWHSHIYNXQINGCXTZCBHTIAOMBHYULJCSJID1YULRQZD1YULJCSJID2YULZFCZD1DWMCYULZFCZD2YULZFCZD3SHIRYTAIZHCGZZXSIDYULINT1YULDATE1YULBOOL1YLJC1IDYLJC2IDYLJC3IDYLJC4IDYLJC6IDYULBLOB1YULBLOB2YULBLOB3PLACEGUIDPLACECODEPLACENAMECHUZDWVARCHAR2(300),TOURBDWSYSJDATE,DIH VARCHAR2(300),QUANSXZID NUMBER(10),ZIC NUMBER(1),WENWDJID NUMBER(10),HONGSBJVARCHAR2(100),OLDUSEDWAYBDIDNUMBER(10),OLDUSEDSTATEBDIDNUMBER(10),GUANLRID NUMBER(10),JILDWCZB VARCHAR2(50),DISPDATE DATE,BILLSTDCODE VARCHAR2(60),VARCHAR2(200),DATE,VARCHAR2(100),VARCHAR2(100),NUMBER(10),VARCHAR2(60),VARCHAR2(30),NUMBER(10),DATE,NUMBER(10),VARCHAR2(30),VARCHAR2(60),VARCHAR2(30),VARCHAR2(30),VARCHAR2(100),VARCHAR2(60),NUMBER(10),NUMBER(10),DATE,NUMBER(1),NUMBER(10),NUMBER(10),NUMBER(10),NUMBER(10),NUMBER(10),BLOB,BLOB,BLOB,VARCHAR2(38),VARCHAR2(38),VARCHAR2(38),BMGUIDBMCODEBMNAMEPRICEORGNVALUEDEVALUERMVALUERATERMVALUEDPRCTRATEDPRCTVALUEDPRCTTOTALCURRVALUEEXPTLOADCMPLTLOADORGGUIDACCOUNTVALUESHIYRGUIDSHIYRCODESHIYRNAMEZIYMJCHUJMJCHUZMJJINGYMJXIANZMJQITMJPAIQLJIANZMJSHIYMJDIAXMJHUIYSMJCHEKMJSHITMJPEIDSMJJIFMJTUDMJTUDZZMMJGUANLRGUIDGUANLRBMGUANLRCAIZXJFLYQITJFLYORGCODEVARCHAR2(38),VARCHAR2(38),VARCHAR2(38),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),VARCHAR2(38),NUMBER(16,4),VARCHAR2(38),VARCHAR2(38),VARCHAR2(38),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),VARCHAR2(38),VARCHAR2(38),VARCHAR2(38),NUMBER(16,4),NUMBER(16,4),VARCHAR2(38),ORGNAMEG_IDVARCHAR2(300),VARCHAR2(38)defaultSYS_GUID()notnullPRIMARYKEY,PINGGJZBANGSMJYULFDZD1YULFDZD2YULFDZD3YULFDZD4YULFDZD5YULFDZD6YULFDZD7YULFDZD8YULFDZD9SHIYSRQIZYSWSRQIZCZXJYZJCHUZJZCHUJJZDUIWTZJZDANBJZXIANZJZZIYJZJUNJGUOZWJLQITJZDUIWTZMJDUIBMJISSCRAPPINGGJZBANGSMJYULFDZD1YULFDZD2YULFDZD3YULFDZD4YULFDZD5YULFDZD6YULFDZD7YULFDZD8YULFDZD9SHIYSRQIZYSWSRQIZCZXJYZJCHUZJZCHUJJZDUIWTZJZDANBJZXIANZJZZIYJZJUNJGUOZWJLQITJZDUIWTZMJDUIBMJISSCRAPNUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(16,4),NUMBER(10)default0partitionbyrange(billtag)(partitionpart_tyvalueslessthan(1353),partitionpart_zyvalueslessthan(1357),partitionpart_yqvalueslessthan(1359),partitionpart_jtvalueslessthan(1363),partitionpart_fwvalueslessthan(1364),partitionpart_dzvalueslessthan(1366),partitionpart_wyvalueslessthan(1368),partitionpart_tdvalueslessthan(1370),partition