Oracle数据库性能优化方案的研究.doc

精品论文大全oracle 数据库性能优化方案的研究童非, 刘泉 武汉理工大学信息工程系，武汉（430070) e-mail：摘要：主要以 oracle 数据库性能优化为出发点，从该数据库结构分析入手，围绕数据库 参数、应用系统、网络负载等方面讨论了 oracle 数据库性能调整的主要几个方面，由此了 解到数据库的合理规划及运行一段时间后如何分析和改进性能，如何维护一个性能满意的数据库等。关键词：oracle 数据库，性能，参数，优化中图分类号：tp392；文献标识码：a1.引 言oracle 数据库系统是美国 oracle 公司（甲骨文）提供的以分布式数据库为核心的 数据库软件。作为全球第一大数据库系统，oracle 在国内外获得了诸多成功应用，我国 电信、金融、证券等关键领域的计算机系统中也正在使用 oracle 数据库。随着网络应用 和电子商务的不断发展，数据库服务器的访问量越来越大，如何使用有限的计算机系统资源 为更多的用户服务? 如何保证用户的响应速度和服务质量? 这些问题都属于 oracle 数据 库性能优化的问题。2.性能优化的概念及其评价oracle 数据库的优化是通过合理分配计算机的资源（如内存、cpu 、磁盘等）、调 整系统运行参数来改进 oracle 性能的过程1。优化策略一般包括 oracle 数据库参数调 整、磁盘 i/o 调整、应用程序 sql 语句分析及设计、网络性能调整等几个方面。分析评价 oracle 数据库性能主要有数据库吞吐量、数据库用户响应时间两项指标。 数据库吞吐量是指单位时间内数据库完成的 sql 语句数目；数据库用户响应时间是指用户 从提交 sql 语句开始到获得结果的那一段时间。3.oracle 的体系结构oracle 的体系结构决定了该数据库系统是如何使用内存、硬件和网络，以及哪个进程或 程序运行在哪台机器上等，了解其体系结构可以帮助我们解决复杂的问题，优化数据库的性 能，设计并开发出更加健壮的产品系统。图 1 即是绝大多数机器环境下的 oracle 体系结构：图1 oracle体系结构- 6 -3.1 内存分为两类：sga 与 pgasga：是用于存储数据库信息的内存区，该信息为数据库进程所共享。它包含 oracle 服务器的数据和控制信息, 它是在 oracle 服务器所驻留的计算机的实际内存中得以分配，如 果实际内存不够再往虚拟内存中写。pga：包含单个服务器进程或单个后台进程的数据和控制信息，与几个进程共享的 sga正相反，pga 是只被一个进程使用的区域，pga 在创建进程时分配在终止进程时回收。3.2 oracle 的系统后台进程oracle数据库有四个基本后台进程，它们分别是：dbwr、smon、pmon、lgwr。 其中dbwr负责将database buffer catch中被修改过的数据块写到database files中；smon、 pmon则分别是oracle数据库对系统和进程进行监控、修复的两个守护进程；lgwr负责将 数据库产生的日志文件定期写到redo log files中。3.3 oracle 数据库的物理结构和逻辑结构oracle数据库的物理结构可划分为：数据文件、日志文件、控制文件和参数文件。数据 文件中存放了所有的数据信息；日志文件存放数据库运行期间产生的日志信息，它被重复覆 盖使用，若不采用归档方式的话，已被覆盖的日志信息将无法恢复；控制文件记录了整个数 据库的关键结构信息，它若被破坏，整个数据库将无法工作和恢复；参数文件中设置了很多 oracle数据库的配置参数，当数据库启动时，会读取这些信息。oracle数据库的逻辑结构则 是由一些数据库对象组成，如：数据库表空间、表、索引、段、视图、存储过程、触发器等。 数据库的逻辑存储结构(表空间等) 决定了数据库的物理空间是如何被使用的。4.oracle 性能优化的主要方法从对oracle数据库体系结构的介绍中，我们可以发现，主要有三个方面将影响数据库性 能：系统、数据库、网络。系统方面的主要问题是cpu时间、内存使用、磁盘i/o等，网络 方面诸如对网络带宽的占用等。我们所说的oracle性能优化，都是在假设服务器硬件、操作 系统和网络带宽均没有引起严重的性能问题的前提下进行的。4.1 数据库参数4.1.1 cpu 参数的调整cpu是服务器的一项重要资源。服务器良好的工作状态是在工作高峰时，cpu的使用率 在90 %以上。如果空闲时间cpu使用率就在90 %以上，说明服务器缺乏cpu资源。对unix操作系统的服务器，可以使用sar-u命令查看cpu的使用率。可以通过查看v$sysstat数据字典中“cpu used by this session”统计项得知oracle数据库使用的cpu时间， 查看“os user level cpu time”统计项得知操作系统用户态下的cpu时间，查看“os system call cpu time”统计项得知操作系统系统态下的cpu时间。还可以通过查看v $sysstat数据字典来获得当前连接oracle数据库各个会话占用的cpu时间，从而得知什么会话耗用服务器cpu比较多。 可以执行下述语句来查看 sql 语句的解析情况：select * from v $sysstat where name in(parse time cpu ,parse time elapsed, parse count (hard) ) ;这里parse time cpu是系统服务时间，parse time elapsed是响应时间，用户等待时间waittime = parse time elapsed - parse time cpu。由此可以得到用户sql语句平均解析等待时间= wait time/ parse count。这个平均等待时间应该接近于0。如果平均解析等待时间过长，可以 通过下述语句select sql - text, parse- calls, executions from v $sqlarea order by parse- calls分析确定解析效率比较低的sql语句，优化对应语句，或者增加参数亦可。数据库管理员应该经常查看cpu的利用率。出现异常要迅速找出原因，及时调整系统参 数或优化sql语句。4.1.2 内存分配的优化技术cpu是服务器的一项重要资源。服务器良好的工作状态是在工作高峰时，cpu的使用率 在90 %以上。如果空闲时间cpu使用率就在90 %以上，说明服务器缺乏cpu资源。图2 oracle内存结构oracle服务器由一个实例（instance）和一个数据库组成。实例分配的内存结构主要由系 统全局区sga和程序全局区pga构成，如图2所示。oracle运行时，用到三部分内存区域： 软件区、系统全局区和程序全局区。sga区和pga区的大小是否合适，对系统的性能有很大 的影响。1)调整共享池大小oracle系统中，共享池由几个cache组成，每个cache保持一个特殊的数据库对象的 信息,其中主要包括library cache（存放共享sql和pl/sql）和data dictionary cache（存放数据字典对象信息）。共享池的大小对数据库系统的性能有较大的影响， library cache和data dictionary cache的大小只能通过shared - pool - size 间接调整，一般是通过下面的方法来判断shared - pool - size参数是否需要调整。检测library cache的大小：select sum (reloads) / sum (pins) * 100 from v $ library cache查询结果：sum (reloads) / sum (pins) * 100 = 0.59195703 此值应该小于1 ,否则要加大shared - pool -size。 检测data dictionary cacheselect sum (gets) “gets”,sum (getmisses) “getmisses”,sum(getmisses) / sum(gets) *100“rate”from v s | rowcache;查询结果：gets=13502532getmisses=765065rate=5. 6660854%此rate 应该小于15 % ,否则要加大shared - pool - size。2)调整数据缓冲区cache sga中的数据缓冲区cache用于存放oracle进程经常存取的表、索引、回滚段和聚族等对象的数据拷贝。用户使用的所有数据都要通过数据高速缓存，高速数据缓冲区越大，oracle 可装入内存的数据就越多，磁盘的i/ o就越少，系统的性能也就越好。数据缓冲区的大小是 通过参数db - block -buffers来调节的。通过计算高速缓冲区命中率，可以知道db -block- buffers是否需要调整。select name, value from v $sysstat where name in (db block gets,consistent gets, physical reads查询结果：name velue db block gets24 372 082consistent gets2. 493e + 09physical reads50 606 953hit ratio = 97. 9 %计算高速缓冲区命中率的公式为hit raito = 1- physical reads/ (db block gets + consistent gets)。对于通常的环境,要求此值大于80 %，当命中率低于标准值时，需要增加db - block- buffers，保证命中率为80%以上。3)调整重做日志缓冲区大小 此参数用来定义内存中重做日志缓冲区大小，虽然它相对sga较小，但当此值设置太小时，lgwr进程会频繁将log buffer中的数据写入磁盘，增加i/ o的次数，影响系统性能。 查v $sysstat表：select name , value from v $sysstatwhere name = redo log space requests;查询结果:namevalueredo log space requests261value值应该接近于零，否则每次将log -buffers增大5 %，再执行上面的查询，直 到value接近零。4)调整 pga oracle对事务的处理要产生一定数目的递归语句。减少不必要的sql语句的语法分析可以提高事务的处理速度，每条sql语句一旦完成了语法分析就可被重复执行，不需重做语法 分析。为避免重复的语法分析，要为pga分配合适的用于处理sql语句的工作空间。context page的内存分配由init .ora中的3个参数决定：open cursor ： 一个进程同时享有的context area的总数。context area ：context area 的初始大小context incr ：决定当信息超出context area时oracle每次追加到一个context area中的增 量。4.1.3 磁盘 i/ o 的调整磁盘的i/ o速度对整个系统性能有重要影响。影响磁盘i/ o的性能的主要原因有磁盘竞 争、i/ o次数过多和数据块空间的分配管理2 。为oracle数据库服务器创建新文件时，不论是表空间所用的数据文件还是数据事务登录 所用的日志文件，都应将文件分散存储到各个可用逻辑或物理磁盘上，减少对数据库的数据 文件及事务日志文件的竞争，从而有效地改善服务器的性能。调整dbwr（数据库写进程）， 增加写带宽，oracle提供以下方法以防止dbwr活动成为瓶颈：使用异步i/ o，异步i/ o允许进程继续处理下一个操作，而不必等待在发出写后，最小 化了空闲时间，因而改善了系统性能。使用iostat查找大磁盘请求队列显示特定磁盘设备上等待服务的i/ o请求有多长。oracle块大小应该匹配磁盘块大小或是磁盘块大小的倍数。4.2 应用系统开发一个优秀的应用系统对提高oracle数据库系统效率有着极其关键的作用，不过高效 的应用系统需要开发人员有深厚的编程经验，并对oracle数据库有较好的理解等，一般在以 下方面需要重视。4.2.1 优化 sql 语句较好的sql语句可以提高sga区的命中率，减少i/ o请求数目，减少对网络带宽的占用 等。优化sql语句主要从两方面着手：应尽量使用索引；用规范的格式和访问数据库对象的 一致顺序书写sql语句，使得相同访问的sql代码完全相同，以提高共享缓冲池的命中率。4.2.2 索引使用的一些原则 在大型应用开发或表较大的情况下,使用索引可以极大减少数据库读写次数，从而提高数据库访问速度，但如何正确使用索引以发挥它的优势需遵循以下原则。（1）在主键(primary key) 的索引方面，不应有超过25 %的列成为主键，而只有很少的 普通列，这会浪费索引空间；（2）在索引的使用效率方面，当选择数据少于全表的20 %，并且表的大小超过oracle 的5个数据块时，使用索引才会有效，否则用于索引的i/ o加上用于数据的i/ o就会大于做一 次全表扫描的i/o ;（3）当指向被删除行的索引所占空间超过总索引空间的20 %时，就应删除并重建索引， 以节省空间，提高性能。4.3 网络负载在完成一定的任务量情况下，应尽量减少客户端与服务器之间传递的数据量，合理地分 配任务处理。（1）采用oracle自身的完整性约束机制，而非让应用程序来检查完整性，可大大减少 客户端/服务器间的网络访问。（2）使用触发器、过程和包。开发人员利用触发器、过程和包把应用逻辑移到数据库 服务器执行以便减少网络i/ o，提高性能。（3）使用显式游标。当select语句使用隐式游标时将增加网络调用，尤其多次执行 相同的sql语句时，显式游标不需传输这些额外的包重新打开游标，从而避免了不必要的网络传输。5. 实际应用在开发的凯盛国际办公自动化系统中, 采用oracle9i数据库管理系统。系统运行的硬件 环境为：pentium iv 2.8g处理器, 512m内存, 操作系统为linux。在正常工作日，系统运行稳 定的状态下, 通过该系统的运行状态来观看系统优化的效果。表 1 oracle 9i 系统优化对照表步骤优化策略缓冲区高速缓冲命中率cpu 成本/s程序运行时间1无任何优化策略49.09%9.2922.452优化 sql63.44%7.5815.013调整 oracle 内存分配96.49%6.036.53从表1中可能看出, 通过采取相应的优化策略,整个系统的性能得到了很大的改进。缓冲区高速缓存命中率在调整内存分配后大大提高, 其中cpu成本也有轻微的减少。6. 结 论优化oracle数据库对提高计算机系统可用性和效率，都有非常重要的意义，也是一件经 常性的行之有效的工作，这里我们只是从数据库参数、应用系统、网络负载等方面做了一些 简略讨论，性能优化需要数据库管理员、开发人员等具有较深的经验和共同合作，并采用正 确的方法，才能达到最终目的。参考文献1 joseph c, johoson. ocp: oracl