2022年Oracle数据库性能优化技术ORACLE教程.docx

2022年Oracle数据库性能优化技术ORACLE教程1. 引言随着网络应用和电子商务的不断发展，各个站点的访问量越来越大，数据库规模也随之不断的扩大，数据库系统的性能问题就越来越突出，因此，如何对数据库进行调优至关重要：如何运用有限的计算机系统资源为更多的用户服务？如何保证用户的响应速度和服务质量？这些问题都属于服务器性能优化的范畴。作为全球第一大数据库厂商，Oracle数据库在国内外获得了诸多胜利应用，据统计，全球93%的上市.COM公司、65家"财宝全球100强"企业不谋而合地采纳Oracle数据库来开展电子商务。我国许多企业、政府单位及电子商务网站也采纳了Oracle作为数据库服务器。Oracle数据库服务器是高度可优化的软件产品，常常性的调整可以优化应用系统的性能，防止出现系统瓶颈。数据库性能优化的基本原则就是：通过尽可能少的磁盘访问获得所须要的数据。要评价数据库的性能，须要在数据库调整前后比较其评价指标：响应时间和吞吐量之间的权衡、数据库的可用性、数据库的命中率以及内存的运用效率，以此来衡量调整措施的效果和指导调整的方向。对Oracle数据库进行性能调整时，应当根据肯定的依次进行，因为系统在前面步骤中进行的调整可以避开后面的一些不必要调整或者代价很大的调整。一般来说可以从两个阶段入手：1、设计阶段：对其逻辑结构和物理结构进行优化设计，使之在满意需求条件的状况下,系统性能达到最佳，系统开销达到最小；2、数据库运行阶段：实行操作系统级、数据库级的一些优化措施来使系统性能最佳； 2. 在系统设计开发阶段调整数据库为了充分利用Oracle数据库的功能特性，在设计信息系统时，数据库设计人员须要依据业务状况（如访问量或客户端数量）和现有资源状况（如数据库服务器的配置）考虑系统结构和数据库的逻辑结构的设计：2.1调整应用程序结构设计。即应用程序采纳的是传统的C/S两层体系结构，还是B/W/D三层体系结构。不同的应用程序体系结构要求的数据库资源是不同的。2.2恰当运用分区、索引及存档功能。假如某种业务的数据量增长特别快，可以考虑存放该业务的数据库表是否运用Oracle数据库的分区功能；对于常常访问的数据库表是否须要建立索引；对于常常访问但是当业务流程完成后不再变动的数据可采纳放入历史档案的方法来实现应用系统中访问尽可能少的数据量。2.3恰当编写访问数据的SQL语句。良好的SQL语句可以被数据库重复运用而削减分析时间；恰当的运用索引可使访问的数据块大大削减从而削减响应时间。应用程序的执行最终将归结为数据库中的SQL语句执行，因此SQL语句的执行效率确定了Oracle数据库的性能。Oracle公司举荐运用Oracle语句优化器（Oracle Optimizer）和行锁管理器（row-level manager）来调整优化SQL语句。2.4调整硬盘I/O这一步是在信息系统开发之前完成的。数据库管理员可以将组成同一个表空间的数据文件放在不同的硬盘上，做到硬盘之间I/O负载均衡。在磁盘比较富有的状况下还应当遵循以下原则： 将表和索引分开； 创建用户表空间，与系统表空间（system）分开磁盘； 创建表和索引时指定不同的表空间； 创建回滚段专用的表空间，防止空间竞争影响事务的完成； 创建临时表空间用于排序操作，尽可能的防止数据库碎片存在于多个表空间中。2.5确定数据块大小和存储参数。由于数据库的块大小在数据库创建以后就不能在修改（除非重建数据库），因此为了削减数据链接和行迁移，又提高磁盘空间的利用率，在设计数据库时要确定合适的数据块大小和存储参数。通常我们是依据样例数据确定数据块大小，而依据业务现状和将来发展趋势确定存储参数。12下一页 3. 在数据库运行阶段调整数据库数据库运行阶段调整数据库包括两个方面：操作系统级的调整；数据库级的调整。3.1 操作系统级的调整实施操作系统级调整的主要目的是削减内存交换，削减分页，使SGA(System Globle Area)可留驻内存。3.1.1削减内存交换内存交换（swapping）可能会造成很大的内存开销，应将它最小化。运行在Solaris Unix操作系统上的Oracle数据库，可利用vmstat或sar吩咐来检查交换，查看到系统级内存和硬盘I/O的运用状况，调整unix数据缓冲池的大小、每个进程所能运用的内存大小等参数。 vmstat吩咐它报告Solaris上的进程、虚拟内存、磁盘、分页和CPU的活动状况。下面吩咐将显示系统每5秒钟做的事情的概要：% vmstat 5 sar吩咐用于监控交换、分页、磁盘和CPU活动。下面吩咐用于每10秒显示10次分页活动的概要：% sar -p 10 10若系统内存交换较多，且须要节约内存，可采纳以下措施：1） 避开运行不必要的系统daemon进程或应用程序进程；2） 在不明显影响数据块命中率的前提下削减数据库缓冲区的数量，以释放一些内存；3） 削减UNIX文件缓冲区的数量（特殊是在运用裸设备时）。3.1.2限制分页少量的内存分页不会太显著地影响系统的性能，因为应用程序不必全部放在内存中。但是分页过多将会造成系统性能下降。为了检测过多的分页，可在快速响应或空闲期间运行测量，并与响应迟缓时的测量进行比较。可通过以下方法来解决： 运用vmstat或sar -p监控分页； 安装更多的内存； 将一些工作移到另一系统中； 配置系统核心运用更少的内存； 保持SGA在单个共享内存段中。3.1.3使SGA(System Globle Area)留驻内存SGA是对数据库数据进行快速访问的一个系统全局区，若SGA本身须要频繁地进行释放、安排，则不行能达到快速访问数据的目的，因此，要求SGA驻留内存。这时，我们可以重新配置UNIX核心，调整一些操作系统参数以达到增加共享内存的目的。3.2 数据库级的调整每一个Oracle实例都是由一组Oracle后台进程和SGA的一个内存区组成的。这组后台进程会自动的读写数据库的数据文件，因此，数据库性能可以被这些因素所影响：SGA各部分的安排是否合理，运用效率是否正常；I/O和锁竞争是否较多。3.2.1 SGA的安排及运用效率安排给每个实例的内存，即SGA的运用效率如何，会大大影响数据库系统的性能。SGA由下列部分组成：共享池、数据块缓冲区、重做日志缓冲区、大池组成。l 共享池（Shared pool）共享池存放库缓存（存储共享SQL和PL/SQL区）和数据字典缓存（数据库对象信息）以及会话期间信息（对于MTS）。由于这些信息是应用程序须要常常访问的，因此这些信息须要保持高的命中率。可以通过以下语句来确认共享池数据的命中率：库缓存：select gethitratio from v$librarycache 应大于90%select sum(reloads)/sum(pins) from v$librarycache 应小于1%数据字典缓存：select sum(getmisses)/sum(gets) from v$rowcache 应小于15%由于程序设计人员的水平参差不齐，可能存在大的匿名块，这会导致SQL不能重用，因此须要找出大的匿名块以转换为存储过程达到重用：select * from v$sqlarea where command_type=47 and length(sql_text)>500而对于一些应用系统特别频繁运用的SQL对象如存储过程、函数、包等，可以通过钉在内存中的方式来防止由于共享池太小被移出：exec dbms_shared_pool.keep(对象名) 数据块缓冲区（Db block buffer）：数据块缓冲区存放用户所常常访问的数据文件的数据块内容以及用户修改的数据内容。数据库把数据文件里的内容读到内存中，下次须要时干脆从内存中读取，从而削减了磁盘的I/O和响应时间。当然，一般只在比较小的数据表（如常用代码表）才缓存到内存中。由于数据快缓冲区中不行能存放全部的数据，因此可运用LRU算法来确定移出哪些数据块，但又尽量保证有较高的数据命中率。查看数据块命中率的SQL语句为：select 1-(phy.value/(cur.value+con.value) from v$sysstat cur,v$sysstat con,v$sysstat phywhere cur.name='db block gets' and con.name='consistent gets'and phy.name='physical gets'假如这个命中率小于0.85，就要考虑为数据块缓冲区安排更多的内存了。 重做日志缓冲区（Log buffer）：重做日志缓冲区存放从用户内存区复制来的每个DML或DDL语句的重做条目。假如这个缓冲区安排太小会导致没有足够的空间来放重做条目而等待。3.2.2 I/O和资源竞争由于有众多的进程要写数据文件，因此须要通过I/O调整来解决I/O瓶颈问题。假如在设计阶段有效地考虑了表空间的合理安排，就能有效地在肯定程度上削减I/O竞争。在数据库运行时，由于数据的动态增长，原来安排给表或索引的空间已经用完，Oracle会自动安排空间给这些数据库对象。而这个动态安排会对系统性能有所影响，所以要求： 避开动态空间管理 表空间的本地化管理，以削减与数据字典表空间的磁盘竞争。在系统设计和试运行阶段数据量相对较小，效率低下的SQL可能并不会影响系统响应时间，但当系统数据量增长到肯定程度时，须要在系统运行时监控并找出是哪些SQL不能有效运用索引或缺少索引，并进行相应调整：建立索引；修改SQL写法。另外，在Oracle中，须要采纳