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

数据库性能优化方案的研究童非,刘泉武汉理工大学信息工程系，武汉（430070)E-mail：[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体系结构：图1Oracle体系结构内存分为两类：SGA与PGASGA：是用于存储数据库信息的内存区，该信息为数据库进程所共享。它包含Oracle服务器的数据和控制信息,它是在Oracle服务器所驻留的计算机的实际内存中得以分配，如果实际内存不够再往虚拟内存中写。PGA：包含单个服务器进程或单个后台进程的数据和控制信息，与几个进程共享的SGA正相反，PGA是只被一个进程使用的区域，PGA在创建进程时分配在终止进程时回收。3.2Oracle的系统后台进程Oracle数据库有四个基本后台进程，它们分别是：DBWR、SMON、PMON、LGWR。其中DBWR负责将databasebuffercatch中被修改过的数据块写到databasefiles中；SMON、PMON则分别是Oracle数据库对系统和进程进行监控、修复的两个守护进程；LGWR负责将数据库产生的日志文件定期写到redologfiles中。3.3Oracle数据库的物理结构和逻辑结构Oracle数据库的物理结构可划分为：数据文件、日志文件、控制文件和参数文件。数据文件中存放了所有的数据信息；日志文件存放数据库运行期间产生的日志信息，它被重复覆盖使用，若不采用归档方式的话，已被覆盖的日志信息将无法恢复；控制文件记录了整个数据库的关键结构信息，它若被破坏，整个数据库将无法工作和恢复；参数文件中设置了很多Oracle数据库的配置参数，当数据库启动时，会读取这些信息。Oracle数据库的逻辑结构则是由一些数据库对象组成，如：数据库表空间、表、索引、段、视图、存储过程、触发器等。数据库的逻辑存储结构(表空间等)决定了数据库的物理空间是如何被使用的。4.Oracle性能优化的主要方法从对Oracle数据库体系结构的介绍中，我们可以发现，主要有三个方面将影响数据库性能：系统、数据库、网络。系统方面的主要问题是CPU时间、内存使用、磁盘I/O等，网络方面诸如对网络带宽的占用等。我们所说的Oracle性能优化，都是在假设服务器硬件、操作系统和网络带宽均没有引起严重的性能问题的前提下进行的。4.1数据库参数4.1.1CPU参数的调整CPU是服务器的一项重要资源。服务器良好的工作状态是在工作高峰时，CPU的使用率在90%以上。如果空闲时间CPU使用率就在90%以上，说明服务器缺乏CPU资源。对UNIX操作系统的服务器，可以使用sar-u命令查看CPU的使用率。可以通过查看v$sysstat数据字典中“CPUusedbythissession”统计项得知ORACLE数据库使用的CPU时间，查看“OSUserlevelCPUtime”统计项得知操作系统用户态下的CPU时间，查看“OSSystemcallCPUtime”统计项得知操作系统系统态下的CPU时间。还可以通过查看v$sysstat数据字典来获得当前连接ORACLE数据库各个会话占用的CPU时间，从而得知什么会话耗用服务器CPU比较多。可以执行下述语句来查看SQL语句的解析情况：SELECT*FROMV$SYSSTATWHERENAMEIN(parsetimecpu,parsetimeelapsed,parsecount(hard));是系统服务时间，parsetimeelapsed是响应时间，用户等待时间waittime=parsetimeelapsed-parsetimecpu。由此可以得到用户SQL语句平均解析等待时间=waittime/parsecount。这个平均等待时间应该接近于0。如果平均解析等待时间过长，可以通过下述语句SELECTSQL-TEXT,PARSE-CALLS,EXECUTIONSFROMV$SQLAREAORDERBYPARSE-CALLS分析确定解析效率比较低的SQL语句，优化对应语句，或者增加参数亦可。数据库管理员应该经常查看CPU的利用率。出现异常要迅速找出原因，及时调整系统参数或优化SQL语句。4.1.2内存分配的优化技术CPU是服务器的一项重要资源。服务器良好的工作状态是在工作高峰时，CPU的使用率在90%以上。如果空闲时间CPU使用率就在90%以上，说明服务器缺乏CPU资源。图2Oracle内存结构Oracle服务器由一个实例（Instance）和一个数据库组成。实例分配的内存结构主要由系统全局区SGA和程序全局区PGA构成，如图2所示。Oracle运行时，用到三部分内存区域：软件区、系统全局区和程序全局区。SGA区和PGA区的大小是否合适，对系统的性能有很大的影响。1)调整共享池大小Oracle系统中，共享池由几个CACHE组成，每个CACHE保持一个特殊的数据库对象的信息,其中主要包括LIBRARYCACHE（存放共享SQL和PL/SQL）和DATADICTIONARYCACHE（存放数据字典对象信息）。共享池的大小对数据库系统的性能有较大的影响，LIBRARYCACHE和DATADICTIONARYCACHE的大小只能通过SHARED-POOL-SIZE间接调整，一般是通过下面的方法来判断SHARED-POOL-SIZE参数是否需要调整。检测LIBRARYCACHE的大小：SELECTSUM(RELOADS)/SUM(PINS)*100FROMV$LIBRARYCACHE查询结果：SUM(RELOADS)/SUM(PINS)*100=0.59195703此值应该小于1,否则要加大SHARED-POOL-SIZE。检测DATADICTIONARYCACHE(GETS)“Gets”,SUM(GETMISSES)“Getmisses”,SUM(GETMISSES)/SUM(GETS)*100“Rate”FROMVS|ROWCACHE;查询结果：Gets=13502532Getmisses=765065Rate=5.6660854%此Rate应该小于15%,否则要加大SHARED-POOL-SIZE。2)调整数据缓冲区CACHESGA中的数据缓冲区CACHE用于存放Oracle进程经常存取的表、索引、回滚段和聚族等对象的数据拷贝。用户使用的所有数据都要通过数据高速缓存，高速数据缓冲区越大，Oracle可装入内存的数据就越多，磁盘的I/O就越少，系统的性能也就越好。数据缓冲区的大小是通过参数DB-BLOCK-BUFFERS来调节的。通过计算高速缓冲区命中率，可以知道DB-BLOCK-BUFFERS是否需要调整。SELECTNAME,VALUEFROMV$SYSSTATWHERENAMEIN(‘dbblockgets’,‘consistentgets’,‘physicalreads’查询结果：NAMEVELUEDbblockgets24372082Consistentgets2.493E+09Physicalreads50606953HitRatio=97.9%计算高速缓冲区命中率的公式为HitRaito=1-physicalreads/(dbblockgets+consistentgets)。对于通常的环境,要求此值大于80%，当命中率低于标准值时，需要增加DB-BLOCK-BUFFERS，保证命中率为80%以上。3)调整重做日志缓冲区大小此参数用来定义内存中重做日志缓冲区大小，虽然它相对SGA较小，但当此值设置太小时，LGWR进程会频繁将LOGBUFFER中的数据写入磁盘，增加I/O的次数，影响系统性能。查V$SYSSTAT表：SELECTNAME,VALUEFROMV$SYSSTATWHERENAME=‘redologspacerequests’;查询结果:NAMEVALUEredologspacerequests261VALUE值应该接近于零，否则每次将LOG-BUFFERS增大5%，再执行上面的查询，直到VALUE接近零。4)调整PGAOracle对事务的处理要产生一定数目的递归语句。减少不必要的SQL语句的语法分析可以提高事务的处理速度，每条SQL语句一旦完成了语法分析就可被重复执行，不需重做语法分析。为避免重复的语法分析，要为PGA分配合适的用于处理SQL语句的工作空间。Contextpage的内存分配由init.ora中的3个参数决定：OpenCursor：一个进程同时享有的contextarea的总数。Contextarea：Contextarea的初始大小Contextincr：决定当信息超出Contextarea时Oracle每次追加到一个Contextarea中的增量。的调整磁盘的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请求数目