【作者】 黄肖寒；

【导师】 蒋品群；

【作者基本信息】 广西师范大学 ， 电路与系统， 2012， 硕士

【摘要】 数据库应用系统在运行一段时间后,随着用户访问量的增加,各种各样的性能问题就会出现。数据库性能的调整与优化涉及到很多方面,数据库管理员通过找出系统性能下降的瓶颈,认真分析瓶颈出现的原因,并采取一系列优化措施进行合理的调整与优化,减少用户查询的等待时间,提高了数据库的运行效率。不仅使系统的响应速率和响应速度令用户满意,而且还可以降低系统的开发成本,为企业带来了经济效益。和美国、英国等发达国家相比,我国在数据库的应用、优化等方面还比较薄弱,还没有形成一套针对应用系统的优化的统一、全面的解决方案。关于基于Oracle数据库的应用系统的优化也不多,而且有的优化技术的讲解较为抽象。基于此,本论文首先介绍了课题的来源、研究背景及意义,给出了用电信息查询与管理系统的系统框架、功能及组成；其次在进行充分调研和需求分析的基础上,采用了PowerDesigner建模工具,对系统的数据库进行了详细建模,完成了数据库的物理设计,并利用datafactory工具生成了十万级以上的测试数据,并成功插入到数据库的表中,为数据库的性能优化提供了测试数据的来源；然后通过响应时间、数据缓冲区命中率等指标,得到了数据库的性能情况,再通过一系列优化方案,对oracle数据库进行性能调整与优化；最后对论文的工作进行了总结和展望。主要完成了如下工作：(1)用电信息查询与管理系统的系统框架、功能的需求分析通过调研、阅读相关的文献,严格按照国家电网科下发的关于电力用户用电信息采集系统功能规范[1]的要求,明确了系统设计的目标,给出了系统的功能需求、性能需求以及系统配置的情况。其中,为系统设计了9个模块：自动抄表管理模块、设备检测管理模块、停供电管理模块、档案管理模块、权限管理模块、价格管理模块、报表管理模块、电量收费管理模块、历史数据查询模块。(2)数据库的建模及十万级测试数据的生成首先,在完成数据需求分析的基础上,通过实体—联系方法,生成了概念数据模型(简称CDM),实现了数据库的概念设计；其次,通过PowerDesigner建模工具将CDM转换为物理数据模型(简称PDM),完成了数据库的物理设计；然后,根据实际的需求,采用了Oracle数据库作为数据库管理信息系统,用物理数据模型生成了Oracle数据库的结构化查询语言(简称SQL)脚本,生成目标数据库；最后,通过数据生成工具datafactory,快速的为数据库中的表生成了十万级以上的测试数据。(3)基于用电信息查询与管理系统的数据库性能调整与优化在完成数据库的设计、为数据库的表生成了测试数据后,利用查询语句来测试数据库的性能,通过调整内存参数、改善SQL语句、添加索引以及设置分区表的优化技术,对数据库进行调整与优化。与优化前相比,查询的响应时间、逻辑读的次数以及数据库缓冲区命中率等性能指标得到了改善,提高了数据库的性能。更多还原

【Abstract】 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.更多还原