Oracle数据库SQL查询语句优化方法研究毕业论文.docx
《Oracle数据库SQL查询语句优化方法研究毕业论文.docx》由会员分享，可在线阅读，更多相关《Oracle数据库SQL查询语句优化方法研究毕业论文.docx（42页珍藏版）》请在冰点文库上搜索。
Oracle数据库SQL查询语句优化方法研究毕业论文
南京邮电大学通达学院
毕业论文
题目：
Oracle数据库SQL查询语句优化方法研究
摘要
数据库自从成为最常用的数据管理方式以后，逐步向数据结构日渐复杂、数据量日渐庞大、应用场合日渐广泛的方向迅速发展和完善。
数据库与应用软件的紧密结合使用户对数据库性能的需求逐渐提高，尤其是在海量存储的数据库系统中变得愈加明显。
除了软件和硬件条件本身的限制外，数据库中查询语句的执行效率也是影响其性能的关键因素，SQL语句的执行消耗大部分的数据库资源，包括CPU消耗、内存消耗、物理块读取消耗等，因此有必要对SQL语句进行优化调整，对提升数据库性能有重要的意义。
本文分析了Oracle数据库中SQL语句的执行过程和查询机制，研究了SQL语句在Oracle数据库中的处理方式、执行路径和影响执行计划的因素，并作为SQL语句优化规则制定和生效的理论来源。
同时结合对语句进行优化的目标得出具体的优化规则，优化规则的内容包括已经存在的优化规则、研究中最新提出的优化规则和通常认识中错误的优化规则，对优化的语句样本进行分析统计并总结出规则生效或失效的原因。
关键词：
数据库开销；执行计划；SQL优化；优化规则
ABSTRCT
Sincedatabasebecomethemostusefulmanagementwayofdata,ithasdevelopedandperfectedrapidlytothedirectionthatthedatastructureismoreandmorecomplex,theamountofdatabecomeslargerandlargerandtheapplicationbecomeswiderandwider.Thetightrelationshipandfusionwiththeapplicationsystemmakestheusersimprovetherequirementofitperformance,especiallyinthedatabasewithamassmountofdata.Besidesthelimitofsoftwareandhardware,theefficiencyofinquiringstatementbecomesthekeyfactorintheperformance.TherunningSQLstatementconsumesmostoftheresourceofdatabase,soitisnecessarytoadjustitanditmakessensetoimprovetheperformanceofthedatabase.
ThispaperresearchesandanalysestheexecutionprocedureandmechanismofOracledatabase,andlearnsthewayofhandlingtheSQLstatementandthefactorofaffectingtheexecutionplan.Thenthepapermakesitbethetheorysourceoftheoptimizationrule.ThepaperputsforwardoptimizationrulesofSQLstatementbasedontheoptimizationtargetinOracledatabase.Therulescontaintheonesthathasbeenalreadyexist,thenewonesraisedbythispaperandthewrongonesthatknownbypeople,atthesametimeitanalyzesthereasonoftherule.
KeyWords:
Databaseperformance;Executionplan;SQLoptimization;Optimizationrule;Optimizationadvice
第一章绪论
研究背景
当数据库第一次发展成为各种类型数据常用的管理方式之后，它便以数据结构化、可管理性、共享性和数据可扩展性的特点日益发展和完善。
目前的数据库系统可存储的数据结构日渐复杂、数据量日渐庞大，使用户与应用系统和软件的交互逐渐发展为与数据库的交互。
数据库与应用系统和软件的交互越密切，数据库性能问题越显著。
在日常维护工作中，经常会出现系统运行慢的问题，当这个问题与数据相关联时通常归结为数据库的性能问题。
数据库系统本身是一个较为复杂的系统，影响其性能的因素也是多方面的。
部分软件运营商通过提升数据库的硬件平台（CPU和内存等）来达到提升数据库性能的目的，这种方式确实在一定程度上发挥了作用，但在实际应用中仅凭借这个措施无疑会增加系统开销，且性能的提升程度是有限的。
在不断探索中，SQL语句的执行效率逐渐得到重视，实验证明，语句的执行效率在提升系统性能中占有重要地位。
性能优劣不同的语句在得到相同结果的情况下其执行效率有很多区别，这体现在系统资源消耗和响应时间上，在Oracle数据库中，这两个参数可以从执行计划中获得。
所谓SQL优化是指将执行效率较低的SQL语句重写为结果等效但执行效率较高的SQL语句，使优化器选择最有效的执行计划消耗最少的系统资源（CPU代价、I/O交换量、扫描字节数等）和响应时间。
系统资源消耗量和时间值与数据库环境、结构和数据量均有关，因此它们的含义是通过比较确定执行效率的优劣。
SQL语句优化规则的制定和作用取决于执行的数据库环境和数据库本身的结构，优化规则涵盖了执行过程中的表连接方式、语句执行顺序、表扫描方式和循环次数等，应用每一条优化规则都有可能改变该语句在数据库中的执行方式，也就会产生优劣性不同的执行计划。
Oracle为每一条执行过的SQL语句留下统计信息，获取这些信息进行分析总结出优化规则并找到效率低下的语句进行优化和优化验证是本课题研究中对SQL语句进行优化的主要方法。
本课题正是从这个角度出发，分析Oracle数据库中语句的执行原理和过程，并以此为理论依据总结出优化规则，同时对SQL语句优化规则进行验证。
同时以语句解析执行后的统计信息作为数据来源分析系统中存储的SQL语句的执行性能。
国内外研究现状
“数据库性能优化”的概念第一次被提出是在世纪年代，由美国学者TonyDaugherty提出的，从此数据库的性能问题便被人们广泛关注和研究，掀开了数据库性能优化系列新的篇章。
在这之后的几十年里不乏有人尝试对这方面进行研究，在一篇名为《PerformanceTuningBasics》的文章发表之后人们开始关注于“性能调整”。
文中作者将性能调整归结为修改数据库的系统参数、提升数据库系统的硬件配置条件和优化系统组件。
性能优化被提出后最常被使用的手段是不假思索的升级硬件，虽然增加硬件可以在某些场合中改善性能问题，但是当引起性能问题的原因不是硬件资源时这种较为盲目的方法并不能有效的解决问题。
当人们发现仅凭靠硬件升级不能改善性能问题时，便开始注重于分析数据库的参数。
最先的方法叫做RBA（RadioBasedAnalysis，基于比率的分析），这种方法通过相关参数之间的比率判断性能的优劣，例如通过系统逻辑读和物理读的比率判断是否需要改善。
这要求优化者对性能参数比率的概念有深刻的理解，分析的结果也在比率的标准值定义上存在争议，因此并没有被使用很长时间。
ORACLE7之后的版本引用了大量系统参数，在之前的基础上OracleDBA（DatabaseAdministrator,数据库管理员）开始分析系统等待资源的时间。
例如一个大进程中有诸多的子进程，通过获取系统视图中的资源等待时间判断影响性能的子进程并进行处理。
这种方式忽略了执行时间，因此21世纪之后又出现了RTA（ResponseTimeAnalysis，基于响应时间的分析）方式，这种方式结合了等待时间和执行时间，分析系统视图表时获取servicetime，减少后台CPU的占用时间或扩展CPU，同时获取waittime判断系统压力，根据内存占用频率调整参数来减少等待时间。
据不完全统计,当升级硬件和修改网络、操作系统、数据库参数这些方法进行优化后加起来只能提升系统性能的40%左右，其余的60%要靠对应用程序的优化，包括源代码优化和SQL优化。
源代码优化代价较大，涉及到系统设计和开发，且与数据库性能优化关系不大，主要表现在SQL优化。
传统的语句优化方式是依靠经验手工重写SQL。
OracleDBA或程序员通过执行计划分析，依据经验重写SQL，然后将性能和结果进行对比。
第一代优化是获取语句的执行计划然后提取其中关键字，第二代优化只能提出创建索引的建议，而现在有一种较为智能的优化方式，能生成多种有效的SQL形式，LECCOTechnologyLimited公司拥有的产品LECCOSQLExpert是一个典型的实例，但这种优化方式也无法明确语句优化的原理和语句优化的程度。
国内对此项研究相对来说起步略晚，尤其是在日益庞大的数据库规模面前，较为完善的性能研究也偏向于理论化，使实际化研究略显匮乏。
SQL语句优化规则在理论范畴上的研究使得某些规则并不被认可和应用，且不应该忽略Oracle本身的优化工作和建议，应该尽可能的获取Oracle本身的信息来找到低效的语句。
而Oracle本身的信息多种多样，与优化相关的信息主要有语句生成的执行计划信息和语句统计信息，分析和研究这些信息总结出优化规则并以这些信息判断规则的有效性是本文研究SQL语句优化的主要实现原理和方法。
研究的内容及意义
SQL语句优化规则制定的依据就是Oracle数据库的执行机理和SQL语句的执行计划，同时涵盖Oracle数据库中执行语句的统计信息和系统统计信息，这些信息都以文件或系统动态性能视图的方式被存储在数据库系统中，并可以通过Oracle系统命令进行查看。
本文研究了针对Oracle数据库的SQL语句执行原理和SQL语句优化规则，从而获得优化建议。
本文研究的内容包括：
数据库优化SQL语句的原理。
本研究中SQL优化的主要原理是SQL语句执行的过程和查询机制，针对执行过程找出语句执行的瓶颈是本文优化规则制定的主要依据。
数据库优化SQL语句的方法。
本课题中优化的主要方法是依据生成的优化规则对语句进行优化，并获取执行计划进行执行效率的对比，证实优化规则的有效性。
语句优化规则内容。
针对SQL语句的执行机制，对数据库表的访问方式、连接方式、内部循环次数、记录排序和记录返回方式等方面进行了优化规则的制定，规则中分别提出常用的规则和常见的错误的优化规则。
Oracle数据库在整个数据库领域中仍然占有非常重要的地位，它的使用者不计其数，并广泛存在于现行的各种应用系统和应用软件的服务器和客户机平台中。
Oracle数据库的管理工作和执行机制复杂，操作系统的性能、硬件环境的优劣、数据库系统参数的配置、数据库的设计和SQL语句的执行都在一定程度上影响着它的性能，所以SQL语句优化规则的制定对性能提升有着非常重大的意义。
为了改善以往的SQL优化研究停留在理论上的情况，通过具体实验和数据从直观上直接了解优化的程度，对系统中执行过的语句进行优劣判断并从获取的语句中寻求可优化的方案也是数据库性能优化的一个途径。
论文的组织结构
本文的研究内容和组织结构由以下部分组成：
1.绪论。
主要介绍了本课题研究的背景、国内外研究历史和现状、研究的内容和意义，最后说明了论文的组织结构。
2.相关知识技术研究。
主要介绍了本课题研究过程中涉及到的结构化查询语言。
语句优化的原理。
这一部分主要介绍了Oracle数据库中SQL语句的执行过程和查询机制以及影响执行计划的因素。
优化规则的内容和优化方法。
主要介绍了SQL优化规则的具体内容和生效原理，并介绍了几种优化方法和根据执行计划验证的优化规则。
语句优化的实现。
主要介绍了针对制定的优化规则，进行的语句优化的实例。
6.总结与展望。
这部分主要对所做的研究工作做了总结，并对研究过程中的不足做了说明，最后指出下一步的研究方向。
第二章相关知识技术研究
本文中SQL语句优化规则的研究主要涉及到的相关技术有结构化查询语言SQL语句的分析方式、SQL命令写入文件技术、Oracle执行命令的方式、执行计划的获取技术和性能诊断技术。
本章将对不同技术从理论或应用的角度出发进行详细论述说明。
结构化查询语言
Oracle是典型的关系数据库，它执行的SQL（StructuredQueryLanguage）语言是典型的结构化查询语言，是一种通用、功能性强的关系数据库的标准语言。
结构化查询语言有四个部分：
数据库查询语言、数据定义语言、数据操纵语言、数据控制语言。
数据查询语言的动词主要是SELECT，用来从表中获取数据，是使用最多的语言。
数据定义语言的动词主要是CREATE、DROP和ALTER，用以在数据库中创建、删除和修改表等。
数据库操纵语言的动词主要是INSERT、UPDATE和DELETE，用以插入、修改和删除表中的数据。
数据库控制语言的动词是GRANT和REVOKE，用以控制数据库的对象访问权限。
SQL语句的特点有以下几点：
1.综合统一。
SQL逐渐成为一种国际标准，它是一种综合的、功能强又简单易学的语言，可以独立的完成数据库整个生命周期中包含的全部活动。
2.高度非过程化。
SQL语句进行数据库操作时只要明确所做的内容，而无需指明做的方法，因此不必了解存取的路径，操作的具体过程由系统自动完成。
3.操作的方式是面向集合。
不同于非关系数据库模型，SQL语言采用集合的处理方式，除了操作的对象、查找的结果，一次插入操作、删除操作和更新操作的对象都可以为元组的集合。
4.不同使用方式应用同一种语法结构。
它本身是独立的语言，同时也是一种嵌入式语言。
因为它能独立地被应用于联机交互，也能被嵌入高级语言的程序，但其语句结构总是保持一致的。
5.语言简单，容易学习使用。
SQL语言的功能强，但十分简洁，主要因为完成核心功能的动词只用了9个。
影响语句执行效率的主要是数据查询语言，且在数据库操纵语言中也会涉及到查询语言的查询条件，本文中的优化对象是数据库查询语言。
数据库查询语言主要有三个子句：
SELECT、FROM和WHERE，分别说明了查询的列、表和条件。
在每一个子句中包含了不同的语句格式，如排序分组等，正因为语句格式的多样性，才提供了SQL语句优化的可能性。
Oracle命令执行方式
在课题中执行计划的获取和优化建议的获得并不能通过应用客户端执行命令，而是通过将SQL语句嵌入Oracle命令中作为sql文件转交给Oracle内置的工具SQL*Plus来执行的，因此本节讲述在客户端中传统的命令的传递方式、执行方式和返回结果的获取。
在Oracle中，有三种命令形式，分别是SQL语句、PL/SQL块和SQL*Plus命令。
这三种命令形式都可以被Oracle数据库识别并执行，但三者是有区别的。
语句是以数据库为操作对象的标准结构化语言，当输入SQL语句后，SQL*Plus将其保存在内部缓冲区中。
SQL块同样是以数据库中的数据为操作对象，但由于SQL不具备过程控制功能，所以为了能够与其他语言一样具备面向过程的处理功能，在SQL中加入了诸如循环、选择等面向过程的处理功能，由此形成了PL/SQL。
所有对于PL/SQL语句的解析交由PL/SQL引擎执行，其中可编写存储过程、触发器和包等数据库永久对象。
*Plus是Oracle系统为用户提供的创建和管理数据库对象、并与Oracle服务端直接进行交互的工具，与它进行交互的是所有Oracle系统可识别的命令和语句，主要负责格式化查询的结果、编辑和存储执行的SQL命令以及设置环境选项。
从以上论述中可以看出，SQL*Plus是与Oracle数据库服务端交互的直接工具，某些涉及到数据库底层和内核的命令或参数可以通过SQL*Plus通过命令方式来获取，而SQL语句和PL/SQL并不具备执行此类命令的权限。
在本次研究中，我们使用PL/SQLdevelopment这个可视化图形界面软件对参数和系统信息进行统计。
执行计划的获取
对SQL语句的优化从执行计划入手进行分析，优化规则的生成和执行效率也通过执行计划来体现，所以对SQL语句进行优化就要先获取到执行计划，它以数据形式展示了Oracle执行语句的整个过程。
在Oracle中获取执行计划需要有几步准备工作：
1.执行的用户需要有DBA的权限。
执行计划中的部分信息属于系统统计信息，需要赋予查看系统信息的权限即DBA的权限。
2.创建表PLAN_TABLE表。
Oracle默认将执行计划信息写入PLAN_TABLE表，只需执行Oracle安装目录中的db_1\RDBMS\ADMIN\文件即可。
在Oracle数据库中主要有三种方式获取SQL语句的执行计划：
1.使用数据库系统的explainplanfor命令，将SQL语句执行后的执行计划和统计信息放在系统的dbms_xplan包中，调用包的display方法进行显示，这种方式的优点是可以不用真正执行SQL语句而直接获取到执行计划。
这种方式还有一个优点是它可以作为SQL语句直接从客户端传到Oracle服务端执行，此种情况下它与一个通常SQL的语句并无区别。
它的返回值是一个字符串数组型的数据，并可返回前台直接进行数据处理。
它的语法是“explainplanforSQL语句内容；”，获取时使用“select*fromtable;”语句即可返回结果。
2.使用setautotraceon/explain/traceonly/statistics命令，将执行的SQL语句的执行计划和统计信息放在一个追踪文件中进行读取。
命令中的含义分别是获取所有信息/只获取执行计划/只获取统计信息，这种获取方式必须由Oracle系统命令来执行，即必须以DBA的身份执行，并且只能在SQL*Plus中执行。
它较explainplanfor方法的优点是获取的执行计划和统计信息较为全面，包括读取的物理块的数量等等。
3.使用PL/SQLdeveloper软件，在图形化界面下，直接进行操作，如图2-1。
它较前面两种方法的优点就是简单，快捷，获取的信息全面。
为了优化效果易于表达，本课题的研究中对执行计划和统计信息的获取使用的是此种方法。
图中左上角圈出的功能按钮既是执行计划解释按钮。
图2-1执行计划解释功能
获取的执行计划和统计信息的内容包括Oracle执行SQL语句的每条路径的内容、执行路径的顺序和每条路径所消耗的系统资源，同时包括数据块读的块数、物理读的块数和读的字节数等。
它的输出格式是一个数组格式的字符串，以首字符的缩进量表示执行的优先级，缩进量最大的语句被最先执行，其次执行与其同缩进量的语句，合并两者结果集并逐级向上地推执行。
例如，最简单的SQL语句“select*from”的执行计划如图2-2所示：
执行计划的分析方法有一个规则，在Description列值中列举了执行计划的具体内容和先后顺序。
每个执行计划的步骤占一行，且每条步骤的首行缩进代表了执行顺序，缩进最大的语句被率先执行，同缩进量的语句是按照从上到下的顺序执行。
而执行步骤书写的具体内容表示此步采用的执行方法，如图中的“TABLEACCESSFULL”，表示此步采用的是全表扫描，扫描的对象是对象所有者列相应行的CUSTOMERS表。
由此可知图中的执行顺序是先执行对CUSTOMERS表的“TABLEACCESSFULL”，此操作代价的计算值是407，返回了55500行,扫描了个字节。
第二步操作是选择返回列内容。
由以上分析可知，执行计划清晰的展现了SQL语句在Oracle数据库中的执行过程和每步的执行情况，本文中将以此为参考标准对性能做优劣判定。
图2-2执行计划解释
ADDM性能诊断工具
当DBA获得一条有性能问题的SQL语句却又没有足够可用的信息进行优化时，可将其交由Oracle处理，根据系统信息和SQL语句的内容对可能存在的性能问题进行判定并发现可能引起性能问题的原因。
Oracle数据库中的SQL语句优化都是以统计信息为基础并分析的，不同时刻同一数据库的统计信息和所存在的性能问题是不同的。
为了有效评估系统的性能，系统会不定时的动态收集工作量统计信息，而许多的性能指标是在两个快照之间被采集的。
Oracle数据库中的快照是一个表，它含有对一个本地或远程的数据库上的一个或多个表的查询结果，其中表可能是主表，也可能是系统表或视图。
执行快照的过程相当于一次数据采集，采集的对象是系统当前状态、表信息或系统执行过的存储信息等。
数据采集可以由系统定期完成，也可由DBA手动完成，采集快照的步骤如下：
1.执行快照并存储各种性能指标的初始值到系统数据库字典表中；
2.等待足够的时间产生一个有代表性的负载之后再进行另一次快照。
因为数据库引擎并不控制数据库负载，所以需要有时间的等待过程；
3.进行第二次快照。
4.根据两次快照之间的差值，计算出系统统计信息。
在Oracle数据库中存在一个名为自动数据库诊断监视器ADDM（AutomaticDatabaseDiagnosticMonitor）的工具，它是一个不停收集数据库统计信息并定位系统性能瓶颈、分析SQL语句和提供优化建议以提高性能的机器型DBA。
ADDM彻底分析从不同快照中得出的数据并给出必要的建议，它提供的是一个整体的优化方案，主要目的是诊断数据库在某个时期内存在的性能问题。
Oracle优化器中还有一个DBA优化工具叫做STA（SQLTuningAdvisor），它的特点是简单智能，通过调用系统包的函数即可给出所需SQL语句的优化结果和建议，它给出优化建议的形式是建议的内容描述、优化建议可能改变的性能百分比、原语句的执行计划和采取建议后的执行计划。
第三章SQL语句优化的原理
性能优质或劣质的SQL语句最终都是在Oracle数据库中执行，而Oracle的执行过程和方式直接影响了SQL语句的执行效率。
SQL语句优化是通过改变SQL语句的执行计划或书写方式来达到提高SQL语句的执行效率的目的。
所以本课题研究的优化原理是使Oracle改变SQL语句的执行计划，这种改变以Oracle的执行机制和执行过程为依据的，因此SQL语句优化的原理本质上是Oracle数据库执行SQL语句的过程和查询的机制，本章将主要从这两个方面进行详细介绍。
Oracle数据库执行过程
无论在应用程序中数据层使用的是何种框架，最终都是以SQL语句的方式传入数据库中的。
当Oracle客户端接收到一条SQL语句的执行请求后进行以下过程：
（1）客户端把SQL语句发给服务端：
在应用程序中，SQL语句一般是通过客户端传送过来的，并由客户端将其转交给服务端执行；
（2）语句解析：
服务端对接收的SQL语句，无论它是否在数据库中执行过，都会先使用HASH算法得到一个HASH值，并把此HASH值在系统执行过的SQL语句中进行比较，检查高速缓存中是否存在一样的SQL语句。
若存在，则利用匹配项的执行计划直接执行该语句而省略后续工作，此种情况称为软解析；
（3）语法语义检查：
服务端在高速缓存中没有查询到该SQL语句时首先对
升级会员 