orcale数据库性能优化技术的分析与实现

《orcale数据库性能优化技术的分析与实现》由会员分享，可在线阅读，更多相关《orcale数据库性能优化技术的分析与实现（49页珍藏版）》请在装配图网上搜索。

1、题题 目目: : OracleOracle 数据库性能优化技术的分析与实现数据库性能优化技术的分析与实现 二二 OO 七七 年年 五 月月江西财经大学学士学位论文论文独创性声明论文独创性声明本人声明，所呈交的学位论文系在导师指导下本文独立完成的研究成果。文中认为引用他人的成果，均已做出明确标注或得到许可。论文内容未包含法律意义上已属于他人的任何形式的研究成果，也不包含本人已用于其他学位申请的论文或成果。本文如违反上述声明，愿意承担以下责任和后果：1. 交回学校授予的学位证书；2. 学校可在相关媒体上对作者本人的行为进行通报；3. 本文按照学校规定的方式，对因不当取得学位给学校造成的名誉损害，进

2、行公开道歉4. 本人负责因论文成果不实产生的法律纠纷。论文作者签名： 日期： 年 月 日江西财经大学学士学位论文摘 要信息时代，服务于信息存储和处理的数据库规模越来越大，对数据库系统的性能要求也越来越高，作为数据库的管理者如何维护数据库系统，保证其高效稳定的运行已经成为第一要务。本文结合自身使用数据库的经验，基于对性能调优的成本和效益的考虑，选择在数据库设计和维护阶段对目前处于主流数据库市场的 Oracle 10g 进行了优化实验，分析了影响数据库性能的各方面因素，提出了优化的方法和相关优化技术。本研究就内存参数调整、SQL 语句优化、存储结构调整、数据文件分布，以及索引技术、分区技术和聚簇技

3、术的使用等方面进行了实验与分析，为系统设计合理的性能调整和优化方案提供有价值的参考。【关键词】Oracle 10g 数据库；性能优化；命中率；参数；存储结构调整江西财经大学学士学位论文IOracle Database Performance Optimization Technology Analysis and ImplementationXue ZhimingAbstract：In Information Age,the scale of the database serving the information storing and processing become larger an

4、d larger;The Performance requirement of the database system also become higher and higher.As a DBA,how to maintain the database system and guarante its highly effective stable movement already become the first important matter.This article unifies the experience of own using database,based on the pe

5、rformance evolutionary cost and the benefit,I carry on the optimized experiments during the database design and the maintenance stage for Oracle 10g,which takes up most percentage of the maintream database market at present.Has analyzed the various factors influenced the database performance.This re

6、seach carries on the experiments and the analysis on the memory parameters adjustment,the SQL sentences optimization,the memory structures adjustment,the datafilesdistribution,as well as the use of the index technology,the district technology and gathered bunch of technology and so on. These providi

7、ng the valuable reference of the designing reasonable performance adjusted and optimized plan.Keywords：Oracle 10g; Performance optimization; Parameters; The hit probability; Memory structure adjustment江西财经大学学士学位毕业论文目 录1 绪论 .11.1 研究背景.11.2 国内外研究现状.11.3 本文主要研究内容与组织结构.22 ORACLE 10G 数据库体系结构 .32.1 系统全局区

8、SGA .32.2 ORACLE 10G的存储结构 .43 数据库性能调整与优化架构 .53.1 性能调整与优化概述.53.2 数据库系统性能评价指标.63.3 数据库性能优化的主要方面.73.4 优化常用工具.83.5 优化方法.94 优化方案的实现与分析 .94.1 概述.94.1.1 实验数据库结构.94.1.2 实验程序.104.2 SQL 语句优化 .114.3 默认数据库配置下性能分析与相关优化技术.134.3.1 共享池性能分析.144.3.2 高速数据缓冲区性能分析.164.3.3 重做日志缓冲区性能分析.174.3.4 表空间及数据文件 I/O 分析.184.3.5 系统资源

9、使用情况分析.204.4 内存参数调整.254.4.1 调整参数方案.254.4.2 确定调整的目标以及初始化参数.254.4.3 参数调整的方式.264.4.4 方案的实现与分析.274.4.5 内存参数调整总结.294.5 存储结构调整.294.5.1 存储结构方案.294.5.2 合理分布表空间和数据文件.304.5.3 索引技术.324.5.4 分区技术.344.5.5 聚簇技术.364.5.6 存储结构调整总结.395 总结 .39参考文献 .41致 谢 .42江西财经大学学士学位毕业论文01 绪论数据库技术是计算机科学技术发展最快、应用最广泛的领域之一，在信息管理自动化程度日益提高

10、的今天，数据库技术已经成为现代计算机信息系统的基础和核心。随着数据库规模的不断扩大，数据库系统的性能问题也越来越突出，数据库系统能否正常、高效地运行倍受关注，数据库优化技术方法的探索具有非常重要的意义。1.1 研究背景Oracle 公司在数据库领域一直占据着领先地位，几乎所有大型 Internet 站点都运行 Oracle 数据库，在中国几乎所有的电信行业使用的数据库都是采用 Oracle 数据库，中国银行、中国建设银行等很多企业、政府单位及电子商务网站也采用 Oracle 作为数据库服务器11。在这样的社会需求环境下，提出对 Oracle 数据库性能优化技术的分析研究具有广阔的应用的前景。1

11、.2 国内外研究现状在 20 世纪 70 年代，美国的 Tony Daugherty 提出了数据库性能优化的概念，在以后的数据库性能调整和优化技术的发展中，人们认识到数据库的性能调整技术是保障应用系统和数据库稳定、高效运行的一项重要技术22。国外的数据库技术比较成熟，对于数据库优化技术的研究和应用也十分广泛。目前国外的数据库系统所采用的优化技术大多为查询优化技术，而且取得了很好的效果。国内对数据库技术的研究起步较晚，对数据库优化技术的研究和应用相对来讲不如国外成熟。目前，数据库系统的优化主要表现在如下几个方面：数据库设计方面：主要采用规范和反规范设计。查询优化方面：除了索引技术、代数优化、物理

12、优化、代价估算优化、语义优化等3,43,4主要的优化技术外，还有针对用户定义谓词的完全排序优化55、针对 Order by、Group by 和 Distinct 的排序优化66、主内存优化 77、表连接优化技术88等。系统性能测试和维护阶段：主要是调整数据库系统内存分配、参数设置以及回滚段的管理等99。硬件调整方面：绝大多数高性能解决方案都采用共享存储器簇结构，在这种结构中，多个服务器皆连到外部的 RAID 子系统上。江西财经大学学士学位毕业论文1性能优化工具方面：在数据库性能优化工具方面，可以分为数据库产品自带的性能优化与监测工具以及专有的性能优化工具两类。数据库产品自带的性能优化与监测工

13、具有 ORACLE 提供的 Performance Manager 工具、SQL 语言跟踪工具等1010；Sybase 的 Sybase Adaptive Server Enterprise 和查询分析器1111；SQL Server 2000 提供企业管理器、Query Analyzer、SQLProfiler，Index Tuning Wizard 等工具。另一方面是专有的性能优化工具，这些第三方工具不仅包括了数据库自带的性能优化与监测工具的功能，而且绝大多数都提供了数据分析、警告、与其他 RDBMS 产品集成等附加功能。例如，目前在商用数据库领域LECCO Technology Limi

14、ted(灵高科研有限公司)所提供的自动优化产品LECCO SQL Expert，该优化产品所使用的人工智能自动 SQL 优化技术是对外部 SQL 重写器的一种实现1212。1.3 本文主要研究内容与组织结构数据库性能问题是当今诸如管理信息系统等众多数据库应用系统所面临的共同问题，为了解决系统运行过程中遇到的种种瓶颈，对数据库性能调整理论和应用的研究是迫切需要的，具有一定的理论研究价值和现实意义。本文通过建立一个集材料供应、生产和销售的商品信息系统的数据库，在数据库生命周期中设计开发阶段，分析了默认配置下的数据库性能统计信息，先后确定内存参数调整方案和存储结构调整方案，对目前Oracle数据库性

15、能调整的一些优化技术进行分析与实现。各章节将做以下安排：第一章 综述了数据库性能调整的研究现状和发展概况，介绍了论文研究的意义、目的以及主要研究内容与组织结构。第二章 介绍了Oracle 10g数据库的整体结构，重点介绍了系统全局区SGA和它的存储结构。第三章 介绍了数据库性能调整与优化的基本概念，分析了系统性能评价指标和数据库性能优化的主要方面以及常用优化工具和方法。第四章 详细介绍了实验用到的集材料供应、生产和销售的商品信息系统的数据库的结构，以及针对该数据库开发设计阶段应用到的各种性能调整与优化技术，包括优化SQL语句、默认配置下的数据库性能的分析、调整Oracle内存分配、创建合适的索

16、引、调整Oracle表空间、Oracle分区技术以及聚簇的合理使用。第五章总结。对生产环境的优化提出了自己的一些意见。第六章参考文献。第七章致谢江西财经大学学士学位毕业论文02 Oracle 10g 数据库体系结构了解数据库结构有助于人们解决复杂的问题，图2-l描述了Oracle 10g数据库的体系结构：图2-1 数据库体系结构 2.1 系统全局区 SGAOracle共享的内存区SGA，该区存放数据库中所有用户进程对数据库访问的共同信息。SGA主要由三部分组成：数据库缓冲区、共享缓冲池区和REDO缓冲区，图2-2描述了SGA的结构。图2-2 系统全局区 数据高速缓冲区（Data Buffer

17、Cache）：用于存放最近从磁盘读取的数据块，以及存放尚未存入磁盘的修改数据。它的尺寸由初始化参数文件中的 DB_CACHE_SIZE 和 DB_nK_CACHE_SIZE 定义。其中初始化参数DB_CACHE_SIZE 用于定义标准高速缓存的尺寸，DB_nK_CACHE_SIZE(n:2,4,8,16 或 32)用于定义非标准高速缓存的尺寸1。共享池（Shared Pool）：用于存放 SQL、PL/SQL 包和过程，以及锁、数据字典、游标等信息的内存区，其尺寸由初始化参数SHARED_POOL_SIZE 定义1313,主要由库高速缓存（Library Cache）和数据字典高速缓存（Dic

18、tionary Cache）两部分组成。REDO 缓冲区：保存写入到 REDO 日志文件前的所有例程变化的信1王海亮等编著：精通 Oracle 10g 系统管理 ，中国水利水电出版社 2005 年，第 4 页江西财经大学学士学位毕业论文1息，由 LOG_BUFFER 定义它的尺寸2。PGA(Program Global Area)：用于存放服务器进程的数据和控制信息，它是独立于 SGA 的一块内存区域。当用户进程连接到 Oracle Server 时，Oracle Server 会为每个用户进程分配相应的 PGA。初始化参数pga_aggregate_target 用于指定所有服务器进程占用的

19、最大 PGA 内存空间1414。SGA 除了包含共享池，数据高速缓存和重做日志缓冲区之外，还提供了两个可选的内存结构：大缓冲池（Large pool）和 Java 池（Java pool）3。大缓冲池（Large pool）：用于为使用共享服务器、使用 I/O Slaves 提高 I/O 性能（如配置初始化参数 DBWR_IO_SLAVES） 、执行并行查询以及使用 RMAN 在磁带设备上执行备份、转储和恢复操作等这些大内存操作提供相对独立的内存空间。通过分配大缓冲池，可以提高这些大内存操作的性能。大缓冲池的尺寸由初始化参数 LARGE_POOL_SIZE 定义。Java 池（Java poo

20、l）：用于存放 Java 代码，其尺寸由初始化参数JAVA_POOL_SIZE 定义。如果要在 Oracle 数据库中安装 Java VM，必须配置 Java 池，并且其尺寸应该大于 20M。 2.2 Oracle 10g 的存储结构Oracle 10g数据库的存储结构包括：物理结构和逻辑结构。其关系如图2-3所示：图 2-3 数据库存储结构 1表空间与数据文件表现为一对多的关系。Oracle 创建数据文件时将磁盘的操作系统块重新格式成 Oracle 数据块，通常是操作系统块的倍数，并为每个 Oracle 数据块分配唯一的标志。图 2-4 描述了 Oracle 10g 的存储结构4：2王海亮等

21、编著：精通 Oracle 10g 系统管理 ，中国水利水电出版社 2005 年，第 5 页3王海亮等编著：精通 Oracle 10g 系统管理 ，中国水利水电出版社 2005 年，第 6 页江西财经大学学士学位毕业论文2图 2-4 数据库存储结构 2由图 2-3 和图 2-4 可以看出，数据库物理上由各种数据文件组成，通过实例管理数据库。逻辑上： Oracle 数据块是最小处理单位，可存放段（Segment）的多个数据； 段（Segment）：占用表空间的具体对象，由多个区间（Extent）构成； 区间（Extent）：由多个连续的Oracle数据块构成。3 数据库性能调整与优化架构 理解数据

22、库性能调整与优化架构是一个很重要的环节，对它们的理解将有助人们在优化数据库性能的过程中明确什么是性能调整与优化、评价性能的指标有哪些、数据库优化应该从哪些方面进行以及常用优化工具和优化方法有哪些等。3.1 性能调整与优化概述性能调整(performance tuning)是通过优化应用程序、修改系统参数和系统配置(硬件调整)来改变系统性能。性能调整包括硬件配置、操作系统与数据库管理系统配置，以及对访问这些组件的应用的详细分析与优化1515。性能优化是指进行有目的地调整组件以改善性能，使得数据库的吞吐量最大限度地增加，相应的响应时间达到最小化5。数据库性能优化的基本原则是：通过尽可能少的磁盘访问

23、获得所需要的数据。性能调整与优化的过程4周元杰编著：Oracle 10g 系统管理员简明教程 ，人民邮电出版社，2006.3，第 34 页5吴京慧：数据库优化技术分析研究 ，载计算机与现代化2004.12江西财经大学学士学位毕业论文3在某种程度上说是循环的，要达到性能优化的目的，通常需要进行适当的性能调整，然后再查看优化结果，如此反复，直到达到满意的效果为止。3.2 数据库系统性能评价指标通常评价 Oracle 数据库性能的指标主要有系统吞吐量、数据库用户响应时间、数据库命中率、内存使用情况以及所需的磁盘 I/0 量。系统吞吐量吞吐量是指单位时间内数据库完成的 SQL 语句数目，以每秒钟的事务

24、量（tps）表示。为了使系统能够达到它的最高吞吐量，将不得不牺牲系统性能的其他方面，如系统响应时间、容错能力等6。提高系统吞吐量有两种方法：减少服务时间使得在同样的资源环境下做更多的工作；减少总的响应时间使工作做得更快。用户响应时间响应时间是指用户从提交 SQL 语句开始到获得结果集的第一行所需要的时间，是应用做出反应的时间，以毫秒或秒表示。在任何一个系统中，吞吐量和响应时间作为调整目标通常是互相对立的。获得满意的用户响应时间有两个途径：一是减少系统服务时间，即提高数据库的吞吐量；二是减少用户等待时间，即减少用户访问同一数据库资源的冲突率7。 数据库命中率这一比率是高速缓存命中总数除以自 Or

25、acle 实例启动后对高速缓存的查找总数。缓冲区高速缓存的命中率，对性能至关重要。由于从高速缓存中读数据比从磁盘中读数据的开销要小得多，因此一般应使其尽可能高一些。内存使用情况 内存的使用情况主要体现在可共享内存、永久性内存和运行时内存这三者的分配使用上。实现内存合理使用，一般考虑的主要调整目标有两条:使投资得到最大回报。把时间和精力用于解决很有可能产生最大利益的问题上；使争用减到最小。瓶颈的特点在于延迟和等待，尽可能地消除或减少它。磁盘 I/O6 美Edward Whalen,Mitchell Schroeter 著，高艳春，周兆确 唐艳军译：Oracle 性能调整与优化 ，人民邮电出版社，

26、2002.12 第 5 页7 美Edward Whalen,Mitchell Schroeter 著，高艳春，周兆确 唐艳军译：Oracle 性能调整与优化 ，人民邮电出版社，2002.12 第 5 页江西财经大学学士学位毕业论文4数据库中发生的每个动作几乎都将产生某种类型的 I/O 活动，该活动可以是逻辑的（在内存中） ，也可以是物理的（在磁盘上） 。通过降低不必要的I/O 开销可以增加用户任务可获得的吞吐量，缩短用户响应时间8。其中，磁盘 I/O 操作是数据库性能最重要的方面，是计算机最大的开销。因此，通过减少不必要的磁盘 I/O，可以大大提高系统性能。3.3 数据库性能优化的主要方面无论

27、哪一个数据库管理系统都会出现数据库运行效率问题，要使数据库的性能达到最大化，需从操作系统、硬件性能、数据库结构、Oracle 资源配置、Oracle 实例性能和 SQL 语句执行等方面进行综合考虑，这些方面是相互依赖的9。调整与优化数据库设计在开发信息系统之前，首要的任务是数据库设计人员要提供一个合理的数据库设计方案。若数据库结构设计有问题，Oracle 系统本身提供的优化技术不可能使得数据库性能达到一个满意的效果。在设计阶段对数据库设计进行论证和调整优化，将保证后期的开发维护中各种优化技术发挥应有的作用，使得以更小的代价实现系统性能的最大的提升。调整应用程序结构设计不同的应用程序体系结构对数

28、据库资源要求不同。目前，常用的应用程序结构有 C/S 两层体系结构、B/S 三层体系结构和多层体系结构。选择使用哪种体系结构应参考的指标主要包括：应用系统的规模、用户数量、使用的数据库数量、对网络安全性和流量的要求等。优化应用程序SQL语句消耗了70%至90%的数据库资源，而应用程序对数据库的操作最终表现为SQL语句对数据库的操作，通过对劣质SQL语句以及访问数据库应用的方法的调整，可以显著地改善一个系统的性能，对提高数据库内存区的命中率、减少I/O访问、减少对网络带宽占用等有着非常重要的意义。调整数据库内存分配 内存分配是在信息系统运行过程中进行的，通过调整内存，提高缓冲区的命中率，将大大影

29、响系统的性能，最佳的调整时机是在数据库文件的物理调整和磁盘I/O调整之前进行。调整与优化磁盘I/O8何月顺，丁秋林：调整优化 Oracle 9i 数据库的性能 ，载计算机应用与软件2004.69Guo, Min, Guo, Jing.“Research on oracle 10 G database performance optimization”, Wuhan Ligong Daxue Xuebao/Journal of Wuhan University of Technology, v 27, n 10, October, 2005江西财经大学学士学位毕业论文5影响磁盘 I/O 的性能的

30、主要原因有磁盘竞争、I/O 次数过多和数据块空间的分配管理等，为了避免与 I/O 相关的性能瓶颈，监控磁盘 I/O 并对其进行调整非常重要。配置和调整操作系统性能Oracle 数据库服务器的整体性能很大程度上依赖于操作系统的性能，如果操作系统不能提供优越的性能，那么无论怎么调整数据库也不能发挥其应有的性能。实施操作系统级调整的主要目的是减少内存交换，减少分页，使SGA 可留驻内存10。可以从以下几方面着手：为Oracle规划系统资源；调整计算机系统中的内存配置；为 Oracle 数据库服务器设置操作系统进程优先级；对 OS 参数进行设置。配置与调整数据库服务器硬件性能与数据库有关的硬件设计主要

31、包括CPU、内存、磁盘子系统和网络这四个部分11。许多典型的性能问题是由不充足的或配置失当的硬件组件导致的，其中计算机内存和CPU严重影响数据库系统的能力。硬件调整是为要求的工作量提供足够的硬件资源的行动，包括数据库大小估计与容量规划。3.4 优化常用工具Oracle数据库常用的数据库性能优化工具有： 操作系统工具：如Windows系统的任务管理器及Unix操作系统的Vmstat、 Iostat等命令。通过这些工具可以帮助管理员查看系统级内存和硬盘I/O的使用情况，弄清系统瓶颈出现在什么地方。 Oracle Enterprise Manager（OEM）：这是一个图形的用户管理界面，用户可以使

32、用它方便地进行数据库管理而不必记住复杂的Oracle数据库管理的命令。 Oracle动态性能视图和数据字典视图：Oracle在线数据字典能够反映出Oracle的动态运行情况，对于调整数据库性能是很有帮助的。 系统脚本：Oracle数据库系统提供的spcreate.sql，utlbstat.sql和utlestat.sql三个脚本，运行这些脚本可以获取一段时间内数据库的内存、磁盘I/O等的情况，它们的存储位置在%ORACLE_HOME%RDBMSADMIN目录下。10童有奎：浅谈 ORACLE 数据库系统性能优化方案 ，载计算机应用2005.311Oracle 性能调优原则.http:/ SQL

33、语言跟踪工具（SQL Trace Facility）：记录SQL语句的执行情况，管理员可以使用虚拟表来调整实例，并使用SQL语句跟踪文件调整应用程序性能。SQL语言跟踪工具将结果输出成一个操作系统的文件，管理员可以使用KPROF工具查看这些文件。 Explain PlanSQL语言优化命令：使用这个命令可以帮助程序员写出高效的SQL语言。 DBMS_STATS包：使用DBMS_STATS包可以收集表、索引、模式甚至系统层次的统计数据，不仅可以更新统计数据，还可以创建新的统计数据和保存旧的统计数据，并在必要的情况下恢复统计数据。3.5 优化方法在Oracle环境下，数据库性能优化调整原则上按以下

34、几步进行1616：(1)有条理的采用各种监测手段收集数据，发现性能问题。(2)做出令人信服的调整(一次只建议改动一两处)。(3)收集统计信息，得到调整后的性能。(4)重复以上操作直至性能调整满意为止。4 优化方案的实现与分析 明确调整目标之后，要确定优化方案中使用何种优化技术、采用的优化技术对系统性能的调整具体起到怎样的效果，以及它们在什么情况下使用效果更好，通过优化方案的实现与分析，让人们更直观的认识各种优化技术，并且制定出更好的优化方案。4.1 概述在实施优化之前，首先通过一个查询实例进行 SQL 语句的简单优化实验，分析说明在系统优化之前优化应用程序的重要性；然后通过运行系统脚本 spc

35、reate 和 ultbstat/ultestat 获取默认安装下数据库运行查询语句后的性能统计信息；接着详细分析各项统计信息，提出相应的优化目标和为达到预期目标可采用的相关优化技术，确定当前系统存在的问题以及针对存在的问题制定内存参数调整方案。最后，在实施内存参数调整方案之后，紧接着实施存储结构调整方案，方案包括表空间和数据文件的分布、索引、分区以及聚簇技术的使用。 江西财经大学学士学位毕业论文74.1.1 实验数据库结构本实验中设计数据库结构包含九张数据表，描述了一个集材料供应、生产和销售的商品信息系统的数据库。对数据库中建立的表结构进行说明如下：部门信息表(departments)：用于

36、保存生产商拥有的部门相关信息。包括的字段有：DepartmentNo，DepartmentName，UpperDepartmentNo，Manage，Address，Telephone。供应商信息表(provider)：用于保存向生产商提供生产材料的供应商信息。包括的字段有：ProviderID，ProviderName，Contactor，Tel，Fax仓库信息表(warehouse)：用于保存生产商用于存储商品的仓库分布信息。包括的字段有：WarehouseID，WarehouseName，Keeper，Tel，warehousememo。仓库详细信息表(whdetail)：用于保存生产商

37、所拥有的仓库的详细信息。包括的字段有：warehouseID，productID，qty。商品信息表(product)：用于保存生产的所有商品主要信息。包括的字段有：ProductID，ProductName，DepartmentNo，Spec，ProductDate，WarehouseID，ProviderID。商品详细信息表(productdetail)：用于保存商品相关的详细信息。包括的字段有：ProductID，ProductMemo，Productpl。客户信息表(customer)：用于保存商品的消费者信息。包括的字段有：CustomerID，CustomerName，Addres

38、s，ProviderID，ProductID，Tel，custanslist。销售信息表(sellmaster)：用于保存已销售商品的销售记录信息。包括的字段有：sellID，customerID，providerID。销售信息表(selldetail)：用于保存已销售商品的销售详细记录信息。包括的字段有：sellID，qty，price，customername，productname，address，Tel，sellreport。4.1.2 实验程序实验程序一：通过九张表连接，联合查询销售单中商品编号小于 2 的商品并且该商品消费者是所有消费者中编号小于 11 的商品编号、对应的部江西财经

39、大学学士学位毕业论文8门编号、商品消费者编号以及供应商编号，并统计符合条件的消费者人数、销售数量和金额。该程序用于验证索引，聚簇及分区技术使用前后性能变化情况，SQL 语句如下：select d.pid,d.proid,d.scustid,d.custnum,d.wqty,d.sqty,d.price,dep.departmentname deptname/*第 7 层查询*/from departments dep,(select c.pid pid,c.proid proid,c.wid wid,c.wqty wqty,c.deptnodeptno,c.sqty sqty,c.price

40、price,c.scustid/*第 6 层查询*/scustid,count(distinctcust.customername) custnumfrom customer cust,(select b.pid pid,b.proid proid,b.wid wid,sum(wd.qty) wqty, b.deptno deptno ,b.sellqty sqty,b.price price,b.scustid scustid/*第 5 层查询*/from whdetail wd,(select p.productid pid,p.providerid proid,p.warehouseid

41、wid,p.departmentnodeptno,a.salecount sellqty,a.priceprice,a.scustid scustid/*第 4 层查询*/from product p,(select p.productid productid,productmemo,p_s.s_qty alecount,p_s.s_priceprice,p_s.scustid/*第 3 层查询*/from productdetail p,(select distinct productid ,n.qty s_qty,n.price s_price,n.scustid scustid /*第

42、2 层查询*/from product p,(select distinct productname,sd.qty qty,sd.price price,sm.customerid scustid /*第 1 层查询*/ from sellmaster sm,selldetail sd where sm.sellid=sd.sellid) nwhere p.productname=n.productname andproductid2) p_s where p.productid=p_s.productid ) awhere p.productid=a.productid )bwhere b.

43、wid=wd.warehouseidgroup by b.pid,b.proid,b.wid,b.deptno,b.sellqty,b.price,b.scustid) cwhere c.scustid=cust.customerid and customerid11group by c.pid,c.proid,c.wid,c.wqty,c.deptno,c.sqty,c.price,c.scustid) dwhere dep.departmentno=d.deptnoorder by d.pid;实验程序二：按商品名称分组统计销售明细表中各个商品的销售数量和金额，结果按商品名称升序排列。该程

44、序用于验证对比非主键索引的使用后性能的变化，SQL 语句如下：select productname,sum(price) 总价 ,sum(qty) 总销售量, sum(price)/sum(qty) 平均价格from selldetail group by productnameorder by productname;江西财经大学学士学位毕业论文94.2 SQL 语句优化SQL 是目前使用最广泛的数据库语言，SQL 语句一般是由用户编写的，不同的 SQL 语句能得到相同的结果，但性能上却相差很大。低效率的查询通常使用过量的系统资源并需要使用过多的时间来运行。通过调整这些SQL 语句，以及调整

45、访问数据库应用的方法，可以显著地改善一个系统的性能，对提高数据库内存区的命中率、减少 I/O 访问和对网络带宽的占用等有着非常重要的意义。下面给出一个查询实例来说明 SQL 语句的优化过程。该查询主要是实现统计已销售商品，编号小于 65656 的每种商品的消费者人数、销售的商品数量、金额以及每样商品平均的销售价格设置在 iSQL*PLUS 环境下输出执行 SQL 语句的统计信息的命令：Set autot on statistics运行如下未优化的 SQL 语句：select distinct p.productid, sum(sd.qty) s_qty,sum(sd.price) s_pric

46、e,sum(sd.price)/sum(sd.qty) avgprice,sm.customerid from selldetail sd ,product p,sellmaster smwhere p.productid=sd.productid and sm.sellid=sd.sellid and p.productid65656group by p.productid,sm.customeridorder by p.productid,sm.customerid;运行后得到如图 4-1 所示的统计信息：图 4-1 优化前性能统计按照 SQL 优化原则改写中的查询语句：select sd

47、.productid,sum(sd.qty) s_qty,sum(sd.price) s_price,sum(sd.price)/sum(sd.qty)avgprice,sm.customerid from sellmaster sm,selldetail sdwhere sd.sellid=sm.sellid and sd.productid in(select productidfrom productwhere productid65656)group by sd.productid,sm.customerid;江西财经大学学士学位毕业论文10运行改写后的查询语句得到如图 4-2 所示的

48、统计信息：图 4-2 优化后性能统计对比图 4-1 与图 4-2 可以清楚看出，在大表查询中，适当的对 SQL 语句进行优化，将尽可能的降低对数据库系统资源的浪费，便于数据库系统资源投入别的用途中。对比两段 SQL 查询语句可以发现，在对优化前的 SQL语句进行了下面四处改写得到了新的优化查询语句：用 select distinct productid from product where productid65656 改写为p.productid=sd.productid and p.productid=x替代x；SELECT子句中避免使用* ； 在 FROM 子句中包含多个表的情况下，建议

49、选择记录条数最少的表作为驱动表；表之间的连接写在其他WHERE条件之前，将可以过滤掉最大数量记江西财经大学学士学位毕业论文11录的条件写在WHERE子句的末尾；用TRUNCATE代替DELETE；在确保完整性的情况下多用 COMMIT 语句。4.3 默认数据库配置下性能分析与相关优化技术 分析默认配置下的数据库性能统计信息，能帮助人们更全面的了解系统当前的性能，更有针对性的制定调整的方案，以更低的成本达到优化系统的目的。在实验中应用统计工具是系统自带的脚本 spcreate 和ultbstat/ultestat。使用系统脚本 spcreate 生成的 STATSPACK 包，收集实验程序一和实

50、验程序二运行时数据库性能统计信息，得到优化前默认配置下系统实例的性能统计信息如图 4-3 所示：江西财经大学学士学位毕业论文0图 4-3 优化前内存使用性能统计对图 4-3 进行分析得到下面几点结论：库缓存命中率（Library Hit）：81.72%。该命中率低于系统运行时要求命中率保持下限 85%的要求，说明共享池的尺寸不够大，应该对共享池进行调整。数据库高速缓冲区率（Buffer Hit）：15.42%。该命中率高于系统运行时要求数据高速缓冲区命中率保持上限 15%的要求，说明应该调整数据库高速缓冲区尺寸使数据库高速缓冲区命中率保持在 10%到 15%的合理范围内。由于上面两个因素的影响

51、，使得 Execute to Parse(58.18%),sofe parse(74.56%),Parse CPU to Parse Elapsd(68.73%)这些指标也相应变得不太理想。4.3.1 共享池性能分析 通过共享池性能分析，查找由共享池引起的影响数据库缓冲区命中率低下的原因。共享池性能分析的对象包括库缓冲区和数据字典缓冲区两方面。库缓冲区通过图 4-3 的分析后，为确定共享池中库缓冲区运行是否正常，运行脚本 ultbstat/ultestat 得到图 4-4 所示信息：图 4-4 优化前库缓冲区使用性能统计字段说明：江西财经大学学士学位毕业论文1PINS 字段：表示数据库缓冲区中

52、某条 SQL 语句执行的次数。RELOADS 字段：表示一个用户进程试图执行某条已经被清除出库缓冲区的 SQL 语句的次数。INVALIDATION 字段：表示某个 SQL 语句参考的对象发生变化的次数。结果分析：(a)任意一个 namespace 的 RELOADS 的次数大于该语句 PINS 次数的 1%，说明库缓冲区太小。(b)用如下公式计算库缓冲区的整体命中率12：库缓冲区命中率=(SUM(PINS-RELOADS)/SUM(PINS)*100 =16678/20408*100=81.72这个值低于 85%。同样说明了库缓冲区太小。对库缓冲区性能分析得到如下结论：库缓冲区命中率没有达到

53、理想指标，系统需要增加共享池尺寸来给库缓冲区分配更多的内存。相关的优化技术：提高初始化参数 SHARED_POOL_SIZE 的值。数据字典缓冲区通过图 4-3 的分析后，为确定共享池中数据字典缓冲区运行是否正常，查看由 STATSPACK 包运行得到的关于数据字典统计信息如图 4-5 所示：图 4-5 优化前数据字典缓冲区使用性能统计字段说明：GET_REQS：表示用户进程请求数据字典缓冲区中数据对象的总次数。12 美Edward Whalen,Mitchell Schroeter 著，高艳春译：Oracle 性能调整与优化 ，人民邮电出版社，2002.12 第 98 页江西财经大学学士学位

54、毕业论文2GET_MISS：表示用户进程请求不在数据库字典缓冲区中的数据对象的总次数。SCAN_REQ 字段：表示用户进程为获得某个对象信息而扫描数据字典缓冲区并且最终找到该对象的总次数。SCAN_MISS 字段：表示用户进程为获得某个对象扫描数据字典缓冲区但没能在数据字典缓冲区中找到该对象的总次数。MOD_REQS 字段：表示系统对数据字典缓冲区执行INSERT，UPDATE 和 DELETE 操作的总次数。COUNT 字段：表示数据字典缓冲区中入口的总数目。CUR_USAGE 字段：表示包含合法数据的缓冲区入口总数。结果分析：用如下查询语句得到数据字典缓冲区的命中率：SELECT(SUM(

55、GETS-GETMISSES-USAGE-FIXED)/SUM(GETS)FROM V$ROWCACHE; 图 4-6 优化前数据字典缓冲区的命中率 对数据字典缓冲区性能分析得到如下结论：Oracle 推荐数据字典缓冲区的命中率不低于 85%。由图 4-6 得到当前数据字典缓冲区的命中率 83.47%偏低，系统需要增加共享池尺寸来给数据字典缓冲区分配更多的内存。相关的优化技术：提高修改初始化参数 SHARED_POOL_SIZE 的值。4.3.2 高速数据缓冲区性能分析通过数据库高速缓冲区性能分析，查找由数据库高速缓冲区引起的影响数据库缓冲区命中率低下的原因。查看由 STATSPACK 包运行

56、得到的关于数据库高速缓冲区统计信息如图 4-7 所示：江西财经大学学士学位毕业论文3图 4-7 优化前高速数据缓冲区使用性能统计字段说明：Consistent changes 字段：一个用户进程利用回滚段入口执行一致性读取某个块中数据的次数。Consistent gets 字段：SELECT 语句访问某个数据块的次数。Db block gets 字段：INSERT，UPDATE 和 DELETE 语句访问某个数据块的次数。这个值与 Consistent gets 之和就是系统执行逻辑读的次数。Physical reads 字段：为满足 SELECT，INSERT，UPDATE 和 DELETE

57、语句的需要，系统从磁盘读取某个数据块的次数。Dirty buffers inspected 字段：一个用户进程在搜索空闲缓冲区时，忽略的“脏”缓冲区数目。Free buffer inspected 字段：一个用户进程在搜索空闲缓冲区时忽略的缓冲区数量。Hot buffers moved to head of LRU 字段：一个 HOT 缓冲区到达替代缓冲区列表末尾的次数，即需要将该缓冲区移到 LRU 列表头的次数。结果分析：Consistent changes 相对于 Consistent gets 而言，这个数字较低。如果这个数值太大，那么用户的查询可能需要花很长的时间才能完成13；Dirt

58、y buffers inspected 的值偏大，说明要么缓冲区太小，要么是DBWR 进程与其负载不匹配14。这时需要检查数据库缓冲区的命中率是否低于 15%。如果低于 15%，尽可能地提高初始化参数 DB_CACHE_SIZE 和DB_BLOCK_BUFFERS 的值。如果数据库命中率低于 10%，那么需要增加DBWRS 进程的数量。计算数据库高速缓冲区命中率：1-(PHYSICAL READS)/(DB BLOCK GETS+CONSISTENT GETS)=70544/（ 832051+1977） =1-0.8458289=0.1541710（即 15.42%15%）对数据库高速缓冲区性

59、能分析得到如下结论：系统初始化参数 DB_CACHE_SIZE 和 DB_BLOCK_BUFFERS 的值太低。相关的优化技术：提高初始化参数 DB_CACHE_SIZE 和 DB_BLOCK_BUFFERS 的值。13 美Edward Whalen,Mitchell Schroeter 著，高艳春译：Oracle 性能调整与优化 ，人民邮电出版社，2002.12 第 99 页14 美Edward Whalen,Mitchell Schroeter 著，高艳春译：Oracle 性能调整与优化 ，人民邮电出版社，2002.12 第 99 页江西财经大学学士学位毕业论文44.3.3 重做日志缓冲区

60、性能分析通过重做日志缓冲区性能分析，查找由重做日志缓冲区引起的影响数据库缓冲区命中率低下的原因。查看由 STATSPACK 包运行得到的关于重做日志缓冲区统计信息如图 4-8 所示：图 4-8 优化前重做日志缓冲区使用性能统计字段说明：Redo buffer allocation retries 字段：一个用户进程试图在一个不可用的重做日志缓冲区中分配空间的总次数。Redo size 字段：写入重做日志缓冲区的总字节数。将这个值除以系统监控时间即可得到单位时间内对重做日志缓冲区的平均需求。这对确定重做日志文件的尺寸很有帮助。结果分析：Redo buffer allocation retries

61、 值为 0。说明当前系统没有出现竞争使用重做日志缓冲区。对重做日志缓冲区性能分析得到如下结论：当前系统为重做日志缓冲区分配的尺寸基本适应当前的需求，不需要对其做出调整。相关的优化技术：如果 Redo buffer allocation retries 值不为 0，那么说明系统中出现了一次日志转换或检查点或 LGWR 与其负载不匹配15。这时需要检查 Oracle 的警告日志以确定两个最近的日志转换和检查点操作。为了增加两次相临的日志转换或检查点之间的间隔，需要增加重做日志文件的尺寸，或增加初始化参数 LOG_CHECKPOINT_TIMEOUT 或 LOG_CHECKPOINT_INTERVA

62、L 的值。为提高 LGWR 进程的效率，可将重做日志文件放到一个读写数据速度更快的磁盘上或增加初始化参数 LOG_BUFFER 的值。4.3.4 表空间及数据文件 I/O 分析 分析表空间及数据文件 I/O 操作的情况，有利于在进行查询优化时对表空间和数据文件的合理安排提供参考，从而实现 I/O 操作的均匀分布，避免由 I/O 操作引起的磁盘资源不良竞争带来的负面影响。查看由15 美Edward Whalen,Mitchell Schroeter 著，高艳春译：Oracle 性能调整与优化 ，人民邮电出版社，2002.12 第 100 页江西财经大学学士学位毕业论文5STATSPACK 包运行

63、得到的关于表空间及数据文件 I/O 操作统计信息如图 4-9 和图 4-10 所示：图 4-9 优化前表空间使用统计图 4-10 优化前数据文件使用统计字段说明：Reads 字段：表示为满足物理读取数据而从磁盘上读取数据块的总次数。Writes 字段：表示为满足物理写操作而写入磁盘的数据块总数。江西财经大学学士学位毕业论文6结果分析：在该实验中，实验程序一和实验程序二访问的系统数据主要存放在SYSTEM 表空间，而用户数据主要集中在一个表空间 TOOKEN 中，如图 4-10 所示，这两个表空间或数据文件的 I/O(Reads+Writes)相对多了很多，考虑到是在单个磁盘驱动上的操作，所以表

64、空间以及数据文件的分布矛盾并不是很突出。对表空间及数据文件 I/O 操作统计结果分析得到如下结论：表空间及数据文件 I/O 操作正常，但仍然可以进一步采取优化技术对表空间进行调整,提高 I/O 操作的效率。相关的优化技术：合理安排系统中表空间和数据文件中的数据，以使 I/O 操作更加均匀的分布到各个驱动器上；将需要更多 I/O 操作的数据文件或表空间放到速度更快的驱动器上；对系统中需要 I/O 操作特别多的某个表空间或数据文件，添加一个或多个数据文件，并将这些数据文件分布到多个物理磁盘上；如果每个驱动器每秒进行的 I/O 操作数超过了该驱动器的推荐值，那么可能需要增加更多的磁盘驱动器或将部分

65、I/O 操作转移到其他磁盘或磁盘阵列上；增加高速磁盘阵列。4.3.5 系统资源使用情况分析系统资源的分配合理与否，不论对数据库还是操作系统的性能都起到限制作用，通过分析当前系统资源使用情况，及时发现资源不合理分配的情况，从而保障系统性能的充分发挥。以下分别对回滚段、排序区、锁存器、队列和系统等待事件这些方面进行分析并提出各自相关的优化技术。回滚段查看由 STATSPACK 包运行得到的关于回滚段使用情况如图 4-11 所示：江西财经大学学士学位毕业论文7图 4-11 优化前回滚段使用性能统计字段说明：Trans tbl gets 字段：表示用户进程请求回滚段中某个回滚头的总次数。Trans t

66、bl waits 字段：表示用户进程请求回滚段时必须等待回滚头的总次数。Shrinks 字段：表示由于回滚段尺寸超过优化尺寸而导致系统减小回滚段尺寸的总次数。Wraps 字段：表示回滚段被打包的总次数。结果分析：Trans tbl waits 字段的值相对较低，不到 Trans tbl gets 的 5%，不会出现回滚段使用的竞争。对回滚段使用情况分析得到如下结论：回滚段工作正常。相关的优化技术：如果 Trans tbl waits 字段的值大 Trans tbl gets 的 5%16，那么应当增加系统中回滚段的数量。如果 Shrinks 字段的值很大，那么说明系统中回滚段的数量可能太小，这时需要增加回滚段的数量，直到 Shrinks 的字段值接近于0 为止。排序区查看由 STATSPACK 包运行得到的关于排序区使用情况如图 4-12 所示：图 4-12 优化前排序区使用性能统计字段说明：Sorts(disk) 字段：所有不能放入内存而必须写到磁盘中的排队次数。sorts(memory) 字段：全部在内存中完成而不需要任何额外的磁盘 I/O排队操作次数。结果分析：sorts(dis