资产管理系统实施优化方法

资产管理系统实施优化方法参考数据量100W一．财政端部署方式应用服务器与数据库服务器分开部署二．硬件配置要求(服务器端)应用服务器配置：双CUP 内存2G数据库服务器：AIX小型机三．网络带宽要求？建议是在内网上上报数据，不要走外网四•数据库优化(oracle9i)我们发现有时服务器硬件很好，但是Oracle就是很慢，很可能是内存分配不合理造成的。主要是SGA和PGA的分配：SGAshared_pool_size共享池+databuffer缓冲区高速缓存+large_pool_size大型池+java_pool_sizeJava池=SGAPGA并发执行进程数*(sort_area_size+hash_ara_size+2M)=PGA一般的内存分配原则：联机事物处理OLTP：SGA：65%；PGA：15%；OS：20%数据仓库和决策支持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的数据库下，通常oracle只能使用不超过1.7G的内存调整方法：(1)使用命令行调整SGA和缓冲区高速缓存等C:\DocumentsandSettings\zwchenliang>sqlplus"sys/sysassysdba"SQL>select*fromv$sga;NAME VALUE

453852209715200453852209715200209715200667648VariableSizeDatabaseBuffersRedoBuffersSQL>altersystemsetsga_max_size=500Mscope=spfile;系统已更改。SQL>altersystemsetpga_aggregate_target=50Mscope=spfile;系统已更改。SQL>altersystemsetdb_cache_size=300Mscope=spfile;系统已更改。SQL>altersystemsetLARGE_POOL_SIZE=64Mscope=spfile;系统已更改。SQL>shutdownimmediate数据库已经关闭。已经卸载数据库。ORACLE例程已经关闭。SQL>startup529604068bytes454116529604068bytes454116bytes209715200bytes318767104bytes667648bytesTotalSystemGlobalAreaFixedSizeVariableSizeDatabaseBuffersRedoBuffers数据库装载完毕。数据库已经打开。(2)此外，缓冲区高速缓存、共享池、大型池、Java池也可以在控制台调整(3)分析表加快查询速度：analyzeTABLEyqxhz_aid.GD_ASSETCARD_Hcomputestatistics;(4)测试命中率检验调整效果select1-(sum(decode(name,'physicalreads',value,0))/(sum(decode(name,'dbblockgets',value,0))+(sum(decode(name,'consistentgets',value,0)))))"bufferhitratio"fromv$sysstat;一般命中率大于95%,才是比较好的oracle内存配置,否则要加大缓冲区高速缓存(5)另外还可以强制数据库启动的时候就把所有内存页装载，将把所有SGA页装载到内存中,以便使该例程迅速达到最佳性能状态，但这将增加例程启动和用户登录的时间。(慎用，如果前面步骤可以提高效率，则最好不要做此操作)altersystemsetpre_page_sga=truescope=spfile五．数据展现优化1.更改固定资产表头表为分区表注意点：对已经存储了客户数据的数据库要提前做好备份详细的分区操作：1)EXECDBMS_REDEFINITION.CAN_REDEF_TABLE(USER,'gd_assetcard_h',

DBMS_REDEFINITION.CONS_USE_PK);2）创建分区中间表（以BILLTAC为标准进行分区）

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