Oracle性能优化 - 系统篇(精品)

单击此处编辑母版标题样式,第一级,第二级,第三级,第四级,第五级,*,Oracle性能优化,学习目标,本课程将介绍优化和调整,Oracle,数据库系统的一些相关命令和方法。,Oracle,性能调整分成，对于,Oralce,系统内部环境的调整，以及对于程序、,SQL,等相关模式对象包括表、视图、序列、同义词、索引、触发器、存储过程等的调整。,本课程的侧重点是“对于,Oralce,系统的内部调整” 。,本课程内容安排,1.,数据库性能优化概述。,2. Oracle,运行环境的优化,1,.,数据库性能优化概述,1.1.,数据库性能优化的内容,1.2 不同类型系统的优化,（,1,）调整数据结构的设计。,（,2,）调整应用程序结构设计。,（,3,）调整数据库,SQL,语句。,（,4,）调整服务器内存分配。,（,5,）调整硬盘,I/O,，这一步是在信息系统开发之前完成的。,（,6,）调整操作系统参数。,本课程着重讲述其中的,4,、,5,、,6,，三个部分。,1.1 数据库性能优化的内容,1,联机事务处理信息系统（,OLTP,）,这种类型的信息系统一般需要有大量的,Insert,、,Update,操作。,OLTP,系统需要保证数据库的并发性、可靠性和最终用户的速度，这类系统使用的,Oracle,数据库需要主要考虑下述因素或参数：,（,1,）数据库回滚段是否足够？,（,2,）是否需要建立,Oracle,数据库索引？,（,3,）系统全局区（,SGA,）大小是否足够？,（,4,）,SQL,语句是否高效？,1.2 不同类型系统的优化,2,数据仓库系统（,Data Warehousing,）,这种信息系统的主要任务是从,Oracle,的海量数据中进行查询，得到数据之间的某些规律。数据库管理员需要为这种类型的,Oracle,数据库着重考虑下述因素或参数：,（,1,）是否采用,B*-,索引或者,bitmap,索引？,（,2,）是否采用并行,SQL,查询以提高查询效率？,（,3,）是否采用,PL/SQL,函数编写存储过程？,2.,Oracle运行环境的优化,2.1.,内存结构的调整,2,.2,.,物理I/O的调整,2.3,逻辑存储结构的优化,2.4.,CPU的优化调整,2.5.,网络配置的优化,2.6. Oracle,碎片整理,2.7. Oracle,系统参数的调整,内存结构的调整主要是指,Oracle,数据库的系统全局区（,SGA,）的调整。,SGA,主要由三部分构成：共享池、数据缓冲区、日志缓冲区。,1,共享池,共享池由两部分构成：共享,SQL,区和数据字典缓冲区。共享,SQL,区是存放用户,SQL,命令的区域，数据字典缓冲区存放数据库运行的动态信息。,2,.1.,内存结构的调整,（,1,）数据库管理员通过执行下述语句，来查看共享,SQL,区的,使用率。,select (sum(pins-reloads)/sum(pins) Lib Cache,from v$librarycache;,共享SQL区的,命中率,应该在90以上，否则需要增加共享池的大小,。,（2）数据库管理员可以执行下述语句，查看数据字典缓冲区的使用率。,select (sum(gets-getmisses-usage-fixed)/sum(gets) Data dictionary cache from v$rowcache;,数据字典缓冲区的,命中率,也应该在90以上，否则需要增加共享池的大小。,（,3,） 调整共享池的大小是通过修改,shared_pool_size,参数的大小。,2,数据缓冲区,数据库管理员可以通过下述语句，来查看数据库数据缓冲区的使用情况。,SELECT name,value FROM v$sysstat WHERE name,IN (db block gets,consistent gets,physical reads);,根据查询出来的结果可以计算出数据缓冲区的使用命中率：,数据缓冲区的使用命中率,1 ( physical reads/(db block gets + consistent gets),这个命中率应该在,90,以上，否则需要增加数据缓冲区的大小。,调整数据缓冲区的大小是通过修改,db_cache_size,参数的大小。,3日志缓冲区,数据库管理员可以通过执行下述语句，查看日志缓冲区的使用情况。,SELECT name, value FROM v$sysstat WHERE name,IN (redo entries,redo log space requests);,根据查询出的结果可以计算出日志缓冲区的申请失败率：,申请失败率,redo log space requests /,redo entries,申请失败率应该接近于0，否则说明日志缓冲区开设太小，需要增加Oracle数据库的日志缓冲区。,调整数据缓冲区的大小是通过修改,log_buffer,参数的大小。,4,开辟,KEEP,池，将使用频率高的小表和其对应的索引直接放入,KEEP,池，完全避免硬盘的读取。,首先设置设置,DB_KEEP_CACHE_SIZE,参数，从内存中开辟,KEEP,池。,调整相关表和索引的,STORAGE,参数，将其放入,KEEP,池：,ALTER TABLE TABLE_Name STORAGE ( BUFFER_POOL KEEP);,ALTER INDEX INDEX_Name STORAGE ( BUFFER_POOL KEEP);,5.,Sessions,Sessions,参数指定了一个,Instance,中能够同时存在的,sessions,数量，或者说，就是能同时登陆到数据库的并发用户数。通常，我们设定这个数字时需要考虑我们可能会有多少个同时连接到数据库的并发用户，并加上后台进程的进程数，最后乘与,1.1.,。,比如说，估计系统中可能会同时有,100,个用户连接到数据库，那么，你的,session,最少应该为,(100 + 10 ) * 1.1 = 121,当数据库连接的并发用户已经达到这个值时，又有新,session,连进来，就会报错,00018, 00000, maximum number of sessions exceeded,6.,Processes,和,Sessions,是类似的是,processes,这个参数。,Processes,参数指定了,Instance,在,OS,层面所能同时运行的进程数。基于和,sessions,设定同样的考虑，我们在设定,processes,时，也应考虑我们可能会有多少个同时连接到数据库的并发用户，并加上后台进程的进程数。,当,Oracle,需要启动新的,process,而又已经达到,processes,参数时，就会报错：,00020, 00000, maximum number of processes (%s) exceeded,7.,Open_links,指定在一次会话中同时打开的与远程数据库的连接的最大数量。该值应等于或超过一个引用多个数据库的单个,SQL,语句中引用的数据库的数量,这样才能打开所有数据库以便执行该语句。,在,Oracle,中可以定义远程数据库连接，使远程数据库对于用户来说是透明的，在一条,SQL,中可以同时对多个远程数据库进行操作。,8.,OPEN_CURSORS,指定一个会话一次最多可以拥有的游标数。缺省值为,50,。遗憾的是，此缺省值通常对,WebLogic Server,这样的系统来说过小。,要将,OPEN_CURSORS,的值设置得足够大，以避免应用程序用尽所有打开的游标。即便会话打开的游标数未达,OPEN_CURSORS,指定的数量（即设置的值高于实际需要的值），也不会增加系统开销。,切记！,ORACLE,优化不是基于命中率，而是基于系统需要,（,1,）在磁盘上建立数据文件前首先运行磁盘碎片整理程序,为了安全地整理磁盘碎片，需关闭打开数据文件的实例，并且停止服务。如果有足够的连续磁盘空间建立数据文件，那么就很容易避免数据文件产生碎片。,（,2,）不要使用磁盘压缩,Oracle,数据文件不支持磁盘压缩。,（,3,）不要使用磁盘加密,加密象磁盘压缩一样增加了一个处理层，降低磁盘读写速度。,（,4,） 使用,RAID,2,.2.,物理I/O的调整,表空间的优化,回滚段的优化,重做日志组的优化,2,.3.,逻辑存储结构的优化,表空间最大的性能问题在于：,（,1,）数据插入过程中，表空间的扩展；需要防止表空间的扩展频率过高；,（,2,）表空间的自我管理开销；,1.,表空间的优化,表空间优化的一般方法：,（,1,）不同用途的数据分离其表空间：系统表空间、临时表空间、数据表空间、索引表空间；,（,2,）对于海量数据的表以及相关的索引，为减少其扩展的频率，为其定义专门的大的表空间；,在系统中，针对表的数据定义了,2,种表空间：,针对海量数据的表 每次自动扩展的大小为,100M,针对普通数据量的表 每次自动扩展的大小为,10M,（,3,）采用本地表空间管理，不要选择数据字典管理，以节省对于字典的争用。,从,Oracle9,开始，,Oracle,提供了对于回滚段的自动管理，减轻了,DBA,的工作。,打开回滚段的自动化管理：,alter system set undo_management=AUTO SCOPE=spfile;,减小回滚数据在回滚段中保存的时间：一般,undo_retention,参数都会采用默认的值,10800,即回滚数据在回滚段中保存,3,个小时,;,如果你的,db,事务非常频繁,undo_retention,就需要适当地调少：,alter system set undo_retention = 3600 scope = spfile;,2.,回滚段的优化,重做日志组由大小相同的一组文件构成，一个时间只有一组,logfile group,是工作状态,(CURRENT),，其他都是非工作状态（,INACTIVE,）, redo logfile,满了后会自动切换到下一个,logfile group,如果数据库是归档方式同时写到归档日志文件。采用以下方式可以查看重做日志组的状态。,select * from v$log;,重做日志组优化的重点是，通过调整重做日志组的大小，防止切换的频率过高，切换太频繁对于系统的性能影响很大。一般来说以每,10,分钟切换一次为宜。,3.,重做日志组的优化,重做日志组建立后不可以更改大小，要想更改大小，只能以新大小建立日志组，再删除旧日志组。系统由于数据处理非常频繁，删除掉了缺省,100M,大小的三个重做日志组，新建了三个,512M,的日志组。必须要按照以下的步骤：,(1),新建,3,个,512M,的日志组；,(2),查看重做日志组的状态：,select * from v$log;,(3),对日志文件进行手工切换，直到要删除的三个缺省重做日志的状态都变成,INACTIVE,：,alter system switch logfile;,(4),删除三个,INACTIVE,状态的重做日志组。,1,查看,CPU,的使用情况,使用操作命令可以看到,CPU,的使用情况，一般,UNIX,操作系统的服务器，可以使用,sar u,命令查看,CPU,的使用率；,Windows,操作系统的服务器，可以使用,Windows,的任务管理器来查看,CPU,的使用率。,出现,CPU,资源不足的情况是很多的：,SQL,语句的重解析、低效率的,SQL,语句、锁冲突都会引起,CPU,资源不足。,2,.4.,CPU,的优化调整,2查看SQL语句的解析情况,（1）数据库管理员可以执行下述语句来查看SQL语句的解析情况：,SELECT * FROM V$SYSSTAT WHERE NAME IN (parse_time_cpu,parse_time_elapsed,parse_count_ hard);,这里：,parse_time_cpu：是系统服务时间。,parse_time_elapsed：是响应时间。,而用户等待时间为：,waite_time = parse_time_elapsed parse_time_cpu,（2）数据库管理员还可以通过下述语句，查看低效率的SQL语句：,SELECT BUFFER_GETS,EXECUTIONS,SQL_TEXT,FROM V$SQLAREA;,优化这些低效率的SQL,语句，是性能调整的重点，能极大的提高,CPU的利用率。,3,CPU,的优化调整其他方法,一些优化,CPU,使用和配置的具体方法有：,（,1,）取消屏幕保护。,（,2,）监视系统中消耗中断的硬件。,（,3,）保持最小的安全审计记录。,（,4,）在专用服务器上运行,Oracle,。,（,5,）禁止非必须的服务。,网络配置是性能调整的一项很重要的内容，而且很容易隐藏性能瓶颈。,（,1,）配置网卡使用最快速度和有效模式,（,2,）删除不需要的网络协议,（,3,）优化网络协议绑定顺序,（,4,）禁止或优化文件共享,2,.5.,网络配置的优化,Oracle,的区在不断,Insert,、,Update,、,Delete,过程中会产生碎片，这些碎片类似于硬盘的碎片，会导致系统性能的下降。,2,.6.,Oracle碎片整理,1.,空闲块,碎片整理,可以将表空间的缺省存储参数pctincrease改为非0。一般将其设为1，如：,alter tablespace,tablespace_name,default storage(pctincrease 1);,这样smon便会将,空闲块碎片,自动合并，达到碎片整理的目的。,也可以采用如下语句，通过手工合并自由范围来达到碎片整理的目的。,alter tablespace,tablespace_name,coalesce;,2,段的碎片整理,段由区组成，在有些情况下，有必要对段的碎片进行整理。要查看段的有关信息，可查看数据字典,DBA_segments,，区的信息可查看数据字典,DBA_extents,。如果段的碎片过多， 将其数据压缩到一个范围的最简单方法便是用正确的存储参数将这个段重建，然后将旧表中的数据插入到新表，同时删除旧表。这个过程可以用,import/export,（输入,/,输出）工具来完成。,export,命令有一个“压缩”选项，这个选项在读表时会计算该表所分配的物理空间量，它会向输出文件写入一个新的初始化存储参数，等于全部所分配空间。使用,import,工具重新生成。这样，它的数据会放入一个新的、较大的初始段中。,例如：,exp user/password file=exp.dmp compress=y grants=y indexes=y tables=(table1,table2);,若输出成功，则从库中删除已输出的表，然后从输出转储文件中输入表：,imp user/password file=exp.dmp commit=y buffer=64000 full=y;,这种方法可用于整个数据库。,3.,内存碎片的整理,Oracle,的内存也会在使用的过程中产生碎片，当不影响系统运行的前提下，定期,shutdown database,，从而清理,momery,碎片。,1.,共享池的调整,：,（1）刷共享池,刷共享池可以使小块的内存合并为大块的内存。当共享池的碎片过多时，能够暂时恢复性能。刷共享池可以使用语句：,alter system flush shared_pool;,（2）绑定变量,使用绑定变量能极大的提高,SQL,语句的重新率，在,SQL,的课程中，会详细讲解绑定变量的用法。,2,.7.,Oracle系统参数的调整,2,Buffer Cache Performance Tuning,（数据库缓存调整）,从缓存调整的角度看，应力求避免以下的问题：,（,1,）“缓存的最近最少使用（,LRN,）链”（,cache buffers LRU chain,）的加锁竞争；,（,2,）“平均写队列”（,Average Write Queue,）长度过大；,（,3,）过多时间花在等待“写完毕等待上”（,write complete waits,）；,（,4,）过多时间花在等待“缓冲释放等待”上（,free buffer waits,）。,3,Latch Contention,（加锁或插销竞争）,插销加锁是,SGA,中保护共享数据结构的低层的串行化机制。插销,latch,是一类可以非常快的获得和释放的锁。插销锁的实现是依赖于操作系统的，尤其在关于一个进程是否会等待一个锁，和等多久方面。,有如下的锁（插销）需要调整：,（,1,）,Redo Copy/Allocation Latch,：重写日志的复制,/,分配插销,（,2,）,Shared Pool Latch,：共享池的插销,（,3,）,Library Cache Latch,：,Library Cache,插销,4,Redo Log Buffer Performance Tuning,（重写日志缓冲的调整）,LGWR,将重写日志缓冲中的重写项写到重写日志文件中。一旦,LGWR,将这些项复制到重写日志文件中，用户进程就可以重写这些项。统计项目“,redo log space requests”,反映了用户进程等待重写日志缓冲中空间的时间的数字。,（,1,）设置重写日志大小的提示：,“,redo log space requests”,的值应该接近,0,。,（,2,）设定合适的重写日志的大小，建议每,15-30,分钟进行一次重写日志的切换。,5,Temporary Tablespace Performance Tuning,（临时表空间的调整）,临时表空间的调整的技巧如下：,如果即使在稳定的状态下也存在很多的排序扩展锁（,Sort Extent Pool latch,）的竞争，应该通过修改临时表空间的,DEFAULT STORAGE,子句的,NEXT,值来增大扩展块的大小。如果存在很多的排序扩展锁（,Sort Extent Pool latch,）的竞争并且这种等待是由于过多的并发的排序造成的，应该增大,SORT_AREA_SIZE,参数的大小，以使更多的排序能保存在内存中。,