用电信息查询与管理系统设计与优化
|
摘要
数据库应用系统在运行一段时间后,随着用户访问量的增加,各种各样的性能问题就会出现。数据库性能的调整与优化涉及到很多方面,数据库管理员通过找出系统性能下降的瓶颈,认真分析瓶颈出现的原因,并采取一系列优化措施进行合理的调整与优化,减少用户查询的等待时间,提高了数据库的运行效率。不仅使系统的响应速率和响应速度令用户满意,而且还可以降低系统的开发成本,为企业带来了经济效益。
和美国、英国等发达国家相比,我国在数据库的应用、优化等方面还比较薄弱,还没有形成一套针对应用系统的优化的统一、全面的解决方案。关于基于Oracle数据库的应用系统的优化也不多,而且有的优化技术的讲解较为抽象。基于此,本论文首先介绍了课题的来源、研究背景及意义,给出了用电信息查询与管理系统的系统框架、功能及组成；其次在进行充分调研和需求分析的基础上,采用了PowerDesigner建模工具,对系统的数据库进行了详细建模,完成了数据库的物理设计,并利用datafactory工具生成了十万级以上的测试数据,并成功插入到数据库的表中,为数据库的性能优化提供了测试数据的来源；然后通过响应时间、数据缓冲区命中率等指标,得到了数据库的性能情况,再通过一系列优化方案,对oracle数据库进行性能调整与优化；最后对论文的工作进行了总结和展望。主要完成了如下工作：
(1)用电信息查询与管理系统的系统框架、功能的需求分析
通过调研、阅读相关的文献,严格按照国家电网科下发的关于电力用户用电信息采集系统功能规范[1]的要求,明确了系统设计的目标,给出了系统的功能需求、性能需求以及系统配置的情况。其中,为系统设计了9个模块：自动抄表管理模块、设备检测管理模块、停供电管理模块、档案管理模块、权限管理模块、价格管理模块、报表管理模块、电量收费管理模块、历史数据查询模块。
(2)数据库的建模及十万级测试数据的生成
首先,在完成数据需求分析的基础上,通过实体—联系方法,生成了概念数据模型(简称CDM),实现了数据库的概念设计；其次,通过PowerDesigner建模工具将CDM转换为物理数据模型(简称PDM),完成了数据库的物理设计；然后,根据实际的需求,采用了Oracle数据库作为数据库管理信息系统,用物理数据模型生成了Oracle数据库的结构化查询语言(简称SQL)脚本,生成目标数据库；最后,通过数据生成工具datafactory,快速的为数据库中的表生成了十万级以上的测试数据。
(3)基于用电信息查询与管理系统的数据库性能调整与优化
在完成数据库的设计、为数据库的表生成了测试数据后,利用查询语句来测试数据库的性能,通过调整内存参数、改善SQL语句、添加索引以及设置分区表的优化技术,对数据库进行调整与优化。与优化前相比,查询的响应时间、逻辑读的次数以及数据库缓冲区命中率等性能指标得到了改善,提高了数据库的性能。
Generally speaking, with the increasing of visitor volume, the application system of database will have all kinds of problems after running a few times. As the adjustment and optimization of database relate to many fields, the administrator sometimes tries to find out the bottleneck which leads to drop the system performance, then analysis it for the sake of taking a series of steps to adjust and optimize. Meanwhile, it can also curtail the waiting time. Not only the system response rate and response speed to customer satisfaction, but also can reduce the system development costs, and bring much more economic benefits to enterprise.
Compare with the United States, Britain and other advanced countries, our country is still relatively weak in the database application and optimization, what's more, we are short of a set of application system for the optimization, which wish to be unify and comprehensive. System based on the application of Oracle database on the optimization is not more, and some optimization technologies are explained in a more abstract way. Bases on the source, background and significance of this issue, this paper first produces the frame, function and constitution of the power information query and management system, then base on the foundation of investigate and analysis, the designer accomplished the physical design of database by uses PowerDesigner modeling tool to model the database. On the other hand, the use of datafactory created more than one hundred thousand grade testing data, and established the foundation of optimization for database by inserted these testing data to table successfully. Thirdly, testing the performance of database by testing the index of answer time and hit rates of buffer zone, and adjusting and optimizing the oracle database by a series of optimized proposals. The last step is to summarize the work of this paper. To sum up, what this paper has finished can be showed as follows:
(1).The author has finished the demand analysis for frame and function of power information query and management system. Before writing this paper, the author has investigated and read many relative literatures, and according to the standard of "The Function Cruterion of The power Information Collection System "[1], which was published by the National Grid, the author set specific goals for system design and show out the function demand, performance demand and the configuration. In this system, the designer has designed9modules: automatic meter reading management module, equipment testing management module, stop power supply management module,file management module, rights management module, price management module, statements management module, charge management module, historical data query module.
(2).The author has finished to module and established more than one hundred thousand grade testing data. Firstly, on the foundation of demand analysis, the designer created the data model to achieve conception design of database by the way of from substance to connection. Secondly, changing the Conception Data Model which stands for CDM to Physical Data Model which stands for PDM by the modeling tool which named PowerDesigner and then accomplishing the physical design of database. Thirdly, according to practical demand, using Oracle database as the data bank administration information system, and using physical data model to establish Structured Query Language(SQL) of Oracle database, then the target database can be created. The last is to create more than one hundred thousand grade testing data for table fast by the tool of datafactory.
(3).The author has finished the function adjustment and optimization for database which is based on the power information query and management system. After finished the design of database and created testing data for database, the designer also uses queried sentence to test performance of database, then adjust and optimize database by the ways of adjusting the parameter of internal storage, improving SQL sentence, adding index and using optimized technology to set up subarea list. Comparing to the system before optimize, the new system can be improved on the hand of response speed, times of logical read, hit rates of buffer zone and so on. In a word, the adjustment and optimization have enhanced the performance of database.
和美国、英国等发达国家相比,我国在数据库的应用、优化等方面还比较薄弱,还没有形成一套针对应用系统的优化的统一、全面的解决方案。关于基于Oracle数据库的应用系统的优化也不多,而且有的优化技术的讲解较为抽象。基于此,本论文首先介绍了课题的来源、研究背景及意义,给出了用电信息查询与管理系统的系统框架、功能及组成；其次在进行充分调研和需求分析的基础上,采用了PowerDesigner建模工具,对系统的数据库进行了详细建模,完成了数据库的物理设计,并利用datafactory工具生成了十万级以上的测试数据,并成功插入到数据库的表中,为数据库的性能优化提供了测试数据的来源；然后通过响应时间、数据缓冲区命中率等指标,得到了数据库的性能情况,再通过一系列优化方案,对oracle数据库进行性能调整与优化；最后对论文的工作进行了总结和展望。主要完成了如下工作：
(1)用电信息查询与管理系统的系统框架、功能的需求分析
通过调研、阅读相关的文献,严格按照国家电网科下发的关于电力用户用电信息采集系统功能规范[1]的要求,明确了系统设计的目标,给出了系统的功能需求、性能需求以及系统配置的情况。其中,为系统设计了9个模块：自动抄表管理模块、设备检测管理模块、停供电管理模块、档案管理模块、权限管理模块、价格管理模块、报表管理模块、电量收费管理模块、历史数据查询模块。
(2)数据库的建模及十万级测试数据的生成
首先,在完成数据需求分析的基础上,通过实体—联系方法,生成了概念数据模型(简称CDM),实现了数据库的概念设计；其次,通过PowerDesigner建模工具将CDM转换为物理数据模型(简称PDM),完成了数据库的物理设计；然后,根据实际的需求,采用了Oracle数据库作为数据库管理信息系统,用物理数据模型生成了Oracle数据库的结构化查询语言(简称SQL)脚本,生成目标数据库；最后,通过数据生成工具datafactory,快速的为数据库中的表生成了十万级以上的测试数据。
(3)基于用电信息查询与管理系统的数据库性能调整与优化
在完成数据库的设计、为数据库的表生成了测试数据后,利用查询语句来测试数据库的性能,通过调整内存参数、改善SQL语句、添加索引以及设置分区表的优化技术,对数据库进行调整与优化。与优化前相比,查询的响应时间、逻辑读的次数以及数据库缓冲区命中率等性能指标得到了改善,提高了数据库的性能。
Generally speaking, with the increasing of visitor volume, the application system of database will have all kinds of problems after running a few times. As the adjustment and optimization of database relate to many fields, the administrator sometimes tries to find out the bottleneck which leads to drop the system performance, then analysis it for the sake of taking a series of steps to adjust and optimize. Meanwhile, it can also curtail the waiting time. Not only the system response rate and response speed to customer satisfaction, but also can reduce the system development costs, and bring much more economic benefits to enterprise.
Compare with the United States, Britain and other advanced countries, our country is still relatively weak in the database application and optimization, what's more, we are short of a set of application system for the optimization, which wish to be unify and comprehensive. System based on the application of Oracle database on the optimization is not more, and some optimization technologies are explained in a more abstract way. Bases on the source, background and significance of this issue, this paper first produces the frame, function and constitution of the power information query and management system, then base on the foundation of investigate and analysis, the designer accomplished the physical design of database by uses PowerDesigner modeling tool to model the database. On the other hand, the use of datafactory created more than one hundred thousand grade testing data, and established the foundation of optimization for database by inserted these testing data to table successfully. Thirdly, testing the performance of database by testing the index of answer time and hit rates of buffer zone, and adjusting and optimizing the oracle database by a series of optimized proposals. The last step is to summarize the work of this paper. To sum up, what this paper has finished can be showed as follows:
(1).The author has finished the demand analysis for frame and function of power information query and management system. Before writing this paper, the author has investigated and read many relative literatures, and according to the standard of "The Function Cruterion of The power Information Collection System "[1], which was published by the National Grid, the author set specific goals for system design and show out the function demand, performance demand and the configuration. In this system, the designer has designed9modules: automatic meter reading management module, equipment testing management module, stop power supply management module,file management module, rights management module, price management module, statements management module, charge management module, historical data query module.
(2).The author has finished to module and established more than one hundred thousand grade testing data. Firstly, on the foundation of demand analysis, the designer created the data model to achieve conception design of database by the way of from substance to connection. Secondly, changing the Conception Data Model which stands for CDM to Physical Data Model which stands for PDM by the modeling tool which named PowerDesigner and then accomplishing the physical design of database. Thirdly, according to practical demand, using Oracle database as the data bank administration information system, and using physical data model to establish Structured Query Language(SQL) of Oracle database, then the target database can be created. The last is to create more than one hundred thousand grade testing data for table fast by the tool of datafactory.
(3).The author has finished the function adjustment and optimization for database which is based on the power information query and management system. After finished the design of database and created testing data for database, the designer also uses queried sentence to test performance of database, then adjust and optimize database by the ways of adjusting the parameter of internal storage, improving SQL sentence, adding index and using optimized technology to set up subarea list. Comparing to the system before optimize, the new system can be improved on the hand of response speed, times of logical read, hit rates of buffer zone and so on. In a word, the adjustment and optimization have enhanced the performance of database.
引文
[1]国家电网公司.电力用户用电信息采集系统功能规范,Q/GDW374.1—2009.电力用户用电信息采集系统功能规范,1—9
[2]邹俊.基于Oracle数据库系统性能调整与优化研究：[硕士学位论文].江西财经大学大学,2006：12,48.
[3]宋海平.大型应用系统中数据库性能优化的研究：[硕士学位论文].武汉理工大学,2007：j.
[4]孙树军.基于Oracle数据库的性能调整及优化技术研究硕士论文[D].
[5]姜江.PowerDesigner数据库系统分析设计与应用[M].北京：电子工业出版社,2004：7
[6]冯永玖,董小华,刘妙龙.基于PowerDesigner的公路工程地理信息平台数据库建模[J]工程勘察,2008(7)：44-48.
[7]李炳生.基于PowerDesigner的排水管理信息系统建模[J].地理空间信息,2006,4(1)：46-48
[8]李红,张雪凤,尹征杰.数据库原理与应用[M].高等教育出版社,2003：11,14,135-137.
[9]王志梅,黄河.关系数据库基础与技术[M].国防工业出版社,2005：11,14-15,28
[10]姜江PowerDesigner数据库系统分析设计与应用[M].北京：电子工业出版社,2004：111.
[11]冯凤娟Oracle数据库体系结构和管理[M]].北京：清华大学出版杜,2003：6-11,24.
[12]颤译祝Oracle数据库管理与维护手册[M].北京：人民邮电出版杜,2006：8,13,42.
[13]滕永昌Oracle10g数据库系统管理[M].北京：机械工业出版社,2006.
[14]潘伟Oracle SGA的性能优化[J].中国水运,2009,09(06)：117-118.
[15]魏亚楠,宋义秋. oracle数据库应用系统的性能优化[J].制造业自动化,2010,32(8)：204-206.
[16]陈佳.基于SQL servr数据库优化查询的分析[J].企业信息化,2010(8)：179-180.
[17]荆立夏,韩忠军.SQL优化在Oracle10g调优中的应用[J].煤炭技术,2010,29(6)：198-199.
[18]张天慧.专家精讲：Oracle数据库管理与维护[M].北京：电子工业出版杜,2009：42,145,339,400
[19]赵元杰Oracle10g系统管理员简明教程[M].北京：人民邮电出版杜,2006：255.
[20]赵元杰OracleDatabase 10g实用培训教程[M].北京：人民邮电出版杜,2006：348.
[21]王晓延译.SQL语句查询凡人入门[M].北京：电子工业出版社,2005.
[22]钟平.《基于J2EE的Oracle数据库Web应用研究》：[硕士学位论文].桂林理工大学,2008.
[23]李小黎,刘宗尧Oracle10g数据库管理与应用系统开发[M].北京：人民邮电出版杜,2007.
[24]王海亮,林立新等Oracle10g快速入门[M].北京：中国水利水电出版杜,2005.
[25]王斌.基于Oracle数据库技术的航行情报系统_CNMS性能优化硕士论文[D].
[26]胡晓华.12530网站Oracle数据库性能优化研究硕士论文[D].
[27]曹飚.医院医疗设备管理系统的设计与实施硕士论文[D].
[28]徐国政.晋中学院学生公寓管理信息系统的设计及实现硕士论文[D].
[29]宋光珍,王逢春.电能表自动抄表系统的应用[J].电气时代,2000(1)：37-38.
[30]Tudjarov, Boris Igoshi, Masanori Tateno etc.Datbase design conception andapplications.Proc.of IEEE International Conf Syst Man Cyben, IEEE, USA,1999.
[31]赵韶平,罗海燕,李志等PowerDesigner系统分析与建模[M].清华大学出版社,2004：192-193.
[32]郭正华,杨义,郭吉萍,方平.基于PowerDesigner的焊接企业资源数据库建模[J].2008(3).95-97
[33]白尚旺PowerDesigner软件分析设计技术[M].北京：电子工业出版社,2002.
[34]郑锋,王卫砚,武青等.基于PowerDesigner的社会保险数据库的设计与实现[J].计算技术与自动化,2000(4).64-67
[35]张海藩.软件工程导论[M].北京：清华大学出版社,1990.
[36]RobertB Bellman, Irene Grief. Database Performance Optimization and Business Communications Review,1997 Capacity Planning.
[37]吴伟敏.UML建模工具的比较——ROSE、Visio和PowerDesigner[J]现代计算,2003(3).55-58.
[38]元薇薇.Oracle数据库优化-分区[J].数据库与信息管理,2010(2)：4602-4604.
[39]李学强,罗省贤.基于Oracle系统的数据库性能优化设计[J].通信与广播电视,2006(4)：31-35
[40]巢子杰Oracle数据库优化探究[J].软件导刊,2010(2)：142-144.
[41]钟鸣,石永平等译Oracle性能优化技术内幕[M].北京：机械工业出版社,2002.
[42]赵惠勤,李秀兰Oracle数据库应用系统的优化策略[J].计算机工程与应用,2003(27)：213-215.
[43]苏大威,张乐.基于Oracle数据库开发系统的物理设计优化策略[J].计算机工程,2002(2)：115-125.
[2]邹俊.基于Oracle数据库系统性能调整与优化研究：[硕士学位论文].江西财经大学大学,2006：12,48.
[3]宋海平.大型应用系统中数据库性能优化的研究：[硕士学位论文].武汉理工大学,2007：j.
[4]孙树军.基于Oracle数据库的性能调整及优化技术研究硕士论文[D].
[5]姜江.PowerDesigner数据库系统分析设计与应用[M].北京：电子工业出版社,2004：7
[6]冯永玖,董小华,刘妙龙.基于PowerDesigner的公路工程地理信息平台数据库建模[J]工程勘察,2008(7)：44-48.
[7]李炳生.基于PowerDesigner的排水管理信息系统建模[J].地理空间信息,2006,4(1)：46-48
[8]李红,张雪凤,尹征杰.数据库原理与应用[M].高等教育出版社,2003：11,14,135-137.
[9]王志梅,黄河.关系数据库基础与技术[M].国防工业出版社,2005：11,14-15,28
[10]姜江PowerDesigner数据库系统分析设计与应用[M].北京：电子工业出版社,2004：111.
[11]冯凤娟Oracle数据库体系结构和管理[M]].北京：清华大学出版杜,2003：6-11,24.
[12]颤译祝Oracle数据库管理与维护手册[M].北京：人民邮电出版杜,2006：8,13,42.
[13]滕永昌Oracle10g数据库系统管理[M].北京：机械工业出版社,2006.
[14]潘伟Oracle SGA的性能优化[J].中国水运,2009,09(06)：117-118.
[15]魏亚楠,宋义秋. oracle数据库应用系统的性能优化[J].制造业自动化,2010,32(8)：204-206.
[16]陈佳.基于SQL servr数据库优化查询的分析[J].企业信息化,2010(8)：179-180.
[17]荆立夏,韩忠军.SQL优化在Oracle10g调优中的应用[J].煤炭技术,2010,29(6)：198-199.
[18]张天慧.专家精讲：Oracle数据库管理与维护[M].北京：电子工业出版杜,2009：42,145,339,400
[19]赵元杰Oracle10g系统管理员简明教程[M].北京：人民邮电出版杜,2006：255.
[20]赵元杰OracleDatabase 10g实用培训教程[M].北京：人民邮电出版杜,2006：348.
[21]王晓延译.SQL语句查询凡人入门[M].北京：电子工业出版社,2005.
[22]钟平.《基于J2EE的Oracle数据库Web应用研究》：[硕士学位论文].桂林理工大学,2008.
[23]李小黎,刘宗尧Oracle10g数据库管理与应用系统开发[M].北京：人民邮电出版杜,2007.
[24]王海亮,林立新等Oracle10g快速入门[M].北京：中国水利水电出版杜,2005.
[25]王斌.基于Oracle数据库技术的航行情报系统_CNMS性能优化硕士论文[D].
[26]胡晓华.12530网站Oracle数据库性能优化研究硕士论文[D].
[27]曹飚.医院医疗设备管理系统的设计与实施硕士论文[D].
[28]徐国政.晋中学院学生公寓管理信息系统的设计及实现硕士论文[D].
[29]宋光珍,王逢春.电能表自动抄表系统的应用[J].电气时代,2000(1)：37-38.
[30]Tudjarov, Boris Igoshi, Masanori Tateno etc.Datbase design conception andapplications.Proc.of IEEE International Conf Syst Man Cyben, IEEE, USA,1999.
[31]赵韶平,罗海燕,李志等PowerDesigner系统分析与建模[M].清华大学出版社,2004：192-193.
[32]郭正华,杨义,郭吉萍,方平.基于PowerDesigner的焊接企业资源数据库建模[J].2008(3).95-97
[33]白尚旺PowerDesigner软件分析设计技术[M].北京：电子工业出版社,2002.
[34]郑锋,王卫砚,武青等.基于PowerDesigner的社会保险数据库的设计与实现[J].计算技术与自动化,2000(4).64-67
[35]张海藩.软件工程导论[M].北京：清华大学出版社,1990.
[36]RobertB Bellman, Irene Grief. Database Performance Optimization and Business Communications Review,1997 Capacity Planning.
[37]吴伟敏.UML建模工具的比较——ROSE、Visio和PowerDesigner[J]现代计算,2003(3).55-58.
[38]元薇薇.Oracle数据库优化-分区[J].数据库与信息管理,2010(2)：4602-4604.
[39]李学强,罗省贤.基于Oracle系统的数据库性能优化设计[J].通信与广播电视,2006(4)：31-35
[40]巢子杰Oracle数据库优化探究[J].软件导刊,2010(2)：142-144.
[41]钟鸣,石永平等译Oracle性能优化技术内幕[M].北京：机械工业出版社,2002.
[42]赵惠勤,李秀兰Oracle数据库应用系统的优化策略[J].计算机工程与应用,2003(27)：213-215.
[43]苏大威,张乐.基于Oracle数据库开发系统的物理设计优化策略[J].计算机工程,2002(2)：115-125.