SQL语句调优论文.doc

8基于Oracle数据库的SQL语句调优基于Oracle数据库的SQL语句调优摘要：本文主要研究了几种SQL语句的调优方法，提出了具体的SQL调优规则，并对每条规则给出SQL语句示例及其执行计划和定性分析。并以相应的执行时间、处理事务所需I/O量作为重要的参考指标。关键字：Oracle、SQL语句调优1 引言基于数据库系统的性能调整对于整个系统的正常运行起着至关重要的作用。以为对于大多数应用数据库而言，在经过一段时间的运行后，系统会存在一定的性能问题，可能涉及数据库硬件、数据库服务器、应用程序和网络等方面。也就是说数据库的性能最终决定数据库的可用性。1,2 性能调整就是通过优化应用程序、修改系统参数、改变系统配置及硬件调整来改变系统的性能。性能调整包括对硬件配置、操作系统与关系型数据库管理系统配置，以及对访问这些组件的应用的详细分析与优化。系统性能的判定标准依赖于性能衡量的研究，通常认为性能的指标可以通过处理一个事物所需的I/O量、CPU时间量以及响应时间来衡量。性能会随特定环境的不同而变化，并受应用程序、体系结构和资源、服务器以及并发活动的具体情况影响。数据库性能调整是一项涉及多个层面的复杂工作，通过统一规划、系统分析做出相应的调整方法和措施，可以提高数据库的稳定性和可用性，保障系统高效地运行，解决系统瓶颈，节约系统开销，具有良好的应用价值，同时也对理论研究提供了一定的实践方法支持。3,4 当前各种大型的数据库应用系统中，数据量越来越大，数据处理越来越复杂，对数据库安全和性能的要求也越来越高。如何充分利用现有的软硬件资源，获得系统的最大吞吐量及数据处理能力已成为数据库应用系统中的一个重要的研究课题。数据库系统性能的好坏主要通过响应时间和吞吐量来衡量，响应越快，吞吐量越大，系统性能就越好。在很多大型的数据信息采集系统中，在规定时间内采集量大，用户与系统的交互时间短，存在对系统的爆炸性访问，同时存在对数据库海量数据的读写。因此，优化和调整数据库应用系统的性能，使整个数据库应用系统满足以上要求显得非常重要。5,6 2 SQL语句调优规则SQL调整是Oracle调整活动中最花费时间且最具挑战性的操作，它是一个持续进行的过程。每个SQL语句都必须进行单独的调整，而且这些调整必须使用提示或者SQL优化器计划稳定性来实现。另外，SQL调整对Oracle性能有着直接正面的影响。如果Oracle优化器选择了非优化的执行计划，人工调整通常可以使查询速度翻三倍。对于那些每天都要执行上千次的查询来讲，这样做将大大提高数据库的性能，同时还可以延长数据库服务器上硬件的寿命。下面，我们将基于Oracle数据库介绍并分析几种常用的SQL语句优化策略，结合实例说明SQL优化在Oracle中的实现。为了方便描述这里先给出所用到的查询表的结构。 employees:（共200000条记录）AttributeData typeidNUMBER, PRIMARY KEY departmentVARCHAR2(10)salaryNUMBER management:（共10条记录）AttributeData typedepartmentVARCHAR2(10), PRIMARY KEYleaderVARCHAR2(10)下文中用到的符号说明：SymbolExplanationE表employees的大小M表management的大小P页面大小2.1规则一：连接两个表的查询，以较小的表为驱动表依据所建立的employees及management表，进行如下操作：SELECT distINct management.leader FROM employees, managementWHERE employees.department= management.department; CaseAnalysis以employees为驱动表那么只要对employees和management各进行一次全表扫描，需要(E+M)/P)次I/O。以employees.department= management.department为驱动表则需要对employees表进行一次全表扫描，对management表进行200000次全表扫描，需要(E+EM/P)/P)次I/O。2.2规则二：两个表的连接操作，应该以返回行数少的为驱动依据所建立的employees及management表，进行如下操作：SELECT employees.id, management.leader FROM employees, managementWHERE employees.department= management.department AND employees.id=65; CaseAnalysis以employees.id=65为驱动那么只要对employees和management各进行一次全表扫描，需要(E+M)/P)次I/O。以employees.department= management.department为驱动则需要对employees表进行一次全表扫描，对management表进行200000次全表扫描，需要(E+EM/P)/P)次I/O。2.3规则三：用于连接的子句的列应该被索引，在WHERE子句中应该尽量利用索引而不是避开索引依据所建立的employees及management表，进行如下操作：SELECT department FROM employeesWHERE id=78; CaseAnalysis没对employees.department建立索引需要对employees表进行一次全表扫描，需要（E/P）次I/O。对employees.department建立索引对每一个返回的记录需要2次I/O，一次用作索引的读取，一次用作数据块的读取。2.4规则四：两个表如果一个表有索引，另一个表无索引，应该以无索引的表为驱动表依据所建立的employees及management表，进行如下操作：SELECT distINct management.leader FROM employees, managementWHERE employees.department= management.department; CaseAnalysis以employees为驱动表，employees.department上有索引则所需要的I/O为：(M/P)(在索引表employees上的查询代价)。以management为驱动表management.department上没有索引则所需要的I/O为：EM/P2。2.5规则五：对于一个表某个字段的查询大多数都是区域查询，那么可对这个字段进行有序索引依据所建立的employees及management表，进行如下操作：SELECT employees.idFROM employeesWHERE id1000 AND salary1000 AND salary=代替；用表连接替换EXISTS；避免在索引列上使用计算等。可以进一步进行研究。参考文献1 盖国强，冯春培，叶梁，等数据库性能优化M北京：人民邮电出版社，2005：63662 武欣，何畅，罗云峰等SQL Server 2000性能调整技术指南M北京：机械工业出版社，2002：74-943 聂文燕SQL Server数据查询的优化方法J工程技术与管理论坛，2006，8(4)：59614 张晓峰SQL优化方法J航空计算技术，2002，4(32)：4-65 邹承俊，唐晏如何提高基于SQL Server应用程序的性能J电脑知识与技术，2005，6(9)：8-96 ROBERT B，IRENE GDatabase performance optimization AND capacity plannINgJBusINess Communications Review，1997，4(16)：28-327 B Dageville. Automatic SQL Tuning in Oracle 10g. Oracle Corporation, 2004.8 Herodotos Herodotou, Shivnath Babu. Automated SQL tuning through trial and (sometimes) error. Proceedings of the Sec