Oracle的性能优化.pptx
《Oracle的性能优化.pptx》由会员分享,可在线阅读,更多相关《Oracle的性能优化.pptx(71页珍藏版)》请在咨信网上搜索。
1、本章学习目标本章学习目标 本章将介绍优化和调整Oracle数据库系统的一些相关命令和方法。第九章第九章 Oracle的性能优化的性能优化本章内容安排本章内容安排本章内容安排本章内容安排 9.1 9.1 数据库性能优化概述数据库性能优化概述数据库性能优化概述数据库性能优化概述 9.2 SQL 9.2 SQL语句的优化语句的优化语句的优化语句的优化 9.3 Oracle 9.3 Oracle运行环境的优化运行环境的优化运行环境的优化运行环境的优化 9.4 9.4 并发事件处理并发事件处理并发事件处理并发事件处理 9.5 9.5 数据完整性数据完整性数据完整性数据完整性 9.7 9.7 常见问题处理
2、常见问题处理常见问题处理常见问题处理 9.1 9.1 数据库性能优化概述数据库性能优化概述9.1.1 9.1.1 数据库性能优化的内容数据库性能优化的内容数据库性能优化的内容数据库性能优化的内容 9.1.2 9.1.2 不同类型系统的优化不同类型系统的优化不同类型系统的优化不同类型系统的优化(1)调整数据结构的设计。(2)调整应用程序结构设计。(3)调整数据库SQL语句。(4)调整服务器内存分配。(5)调整硬盘I/O,这一步是在信息系统开发之前完成的。(6)调整操作系统参数。9.1.1 数据库性能优化的内容数据库性能优化的内容1 1在线事务处理信息系统(在线事务处理信息系统(OLTPOLTP)
3、这种类型的信息系统一般需要有大量的Insert、Update操作。OLTP系统需要保证数据库的并发性、可靠性和最终用户的速度,这类系统使用的Oracle数据库需要主要考虑下述因素或参数:(1)数据库回滚段是否足够?(2)是否需要建立Oracle数据库索引、聚集、散列?(3)系统全局区(SGA)大小是否足够?(4)SQL语句是否高效?9.1.2 不同类型系统的优化不同类型系统的优化2 2数据仓库系统(数据仓库系统(Data WarehousingData Warehousing)这种信息系统的主要任务是从Oracle的海量数据中进行查询,得到数据之间的某些规律。数据库管理员需要为这种类型的Ora
4、cle数据库着重考虑下述因素或参数:(1)是否采用B*-索引或者bitmap索引?(2)是否采用并行SQL查询以提高查询效率?(3)是否采用PL/SQL函数编写存储过程?(4)是否有必要建立并行数据库,来提高数据库的查询效率。9.2.2 SQL9.2.2 SQL语句优化的具体方法语句优化的具体方法语句优化的具体方法语句优化的具体方法 9.2.1 SQL9.2.1 SQL语句的优化规则语句的优化规则语句的优化规则语句的优化规则 9.2 SQL语句的优化9.2.1 SQL语句的优化规则(1)去掉不必要的大表、全表扫描。不必要的大表、全表扫描会造成不必要的输入输出,而且还会拖垮整个数据库;(2)检查
5、优化索引的使用 这对于提高查询速度来说非常重要;(3)检查子查询,考虑SQL子查询是否可以用简单连接的方式进行重新书写;(4)调整PCTFREE和PCTUSED等存储参数优化插入、更新或者删除等操作;(5)考虑数据库的优化器;(6)考虑数据表的全表扫描和在多个CPU的情况下考虑并行查询。1 1索引的使用索引的使用2.SQL2.SQL语句排序优化语句排序优化3.3.选择联合查询的联合次序选择联合查询的联合次序 9.2.2 SQL语句优化的具体方法4.SQL4.SQL子查询的调整子查询的调整 1、索引的使用索引的使用(1)尽量使用索引 是全表扫描还是索引范围扫描主要考虑SQL的查询速度问题。试比较
6、下面两条SQL语句:语句A:SELECT dname,deptno FROM dept WHERE deptno NOT IN(SELECT deptno FROM emp);语句B:SELECT dname,deptno FROM dept WHERE NOT EXISTS(SELECT deptno FROM emp WHERE dept.deptno=emp.deptno);(2)索引不起作用的情况存在数据类型隐形转换列上有数学运算使用不等于()运算使用substr字符串函数%通配符在第一个字符字符串连接(|)(3)函数的索引例如,日期类型是经常用到的,而且在SQL语句中会使用to_ch
7、ar函数以查询具体的的范围日期。如:select*from staff_member where TO_CHAR(birth_day,YYYY)=2003;可以建立基于函数的索引如:CREATE INDEX Ind_emp_birth ON staff_member(to_char(birth_day,YYYY);2.SQL2.SQL语句排序优化语句排序优化排序发生的情况如下:lSQL中包含group by 子句lSQL 中包含order by 子句lSQL 中包含 distinct 子句lSQL 中包含 minus 或 union操作3 3选择联合查询的联合次序选择联合查询的联合次序 联合查
8、询中如涉及到多个表的字段关联及查询,其SQL查询语句联合次序的不同写法,会导致语句对各表具体操作的步骤有不同的次序,所以虽然执行结果相同,但执行效率却不同。SQLSQL子查询的调整子查询的调整(1)关联子查询和非关联子查询 非关联查询的开销非关联查询时子查询只会执行一次,而且结果是排序好的,并保存在一个Oracle的临时段中,其中的每一个记录在返回时都会被父查询所引用。在子查询返回大量的记录的情况下,将这些结果集排序,以及将临时数据段进行排序会增加大量的系统开销。关联查询的开销对返回到父查询的记录来说,子查询会每行执行一次。因此,必须保证任何可能的时候子查询用到索引。(2)在子查询中慎重使用I
9、N或者NOT IN语句 在子查询中慎重使用IN或者NOT IN语句,使用where(NOT)exists的效果要好的多。带IN的关联子查询是多余的,因为IN子句和子查询中相关的操作的功能是一样的。为非关联子查询指定EXISTS子句是不适当的,因为这样会产生笛卡尔乘积。尽量不要使用NOT IN子句。(3)慎重使用视图的联合查询 慎重使用视图的联合查询,尤其是比较复杂的视图之间的联合查询。一般对视图的查询最好都分解为对数据表的直接查询效果要好一些。可以在参数文件中设置SHARED_POOL_RESERVED_SIZE参数,这个参数在SGA共享池中保留一个连续的内存空间,连续的内存空间有益于存放大的
10、SQL程序包。9.3.2 9.3.2 物理物理物理物理I/OI/O的调整的调整的调整的调整 9.3.3 CPU9.3.3 CPU的优化调整的优化调整的优化调整的优化调整 9.3.4 9.3.4 网络配置的优化网络配置的优化网络配置的优化网络配置的优化 9.3.1 9.3.1 内存结构的调整内存结构的调整内存结构的调整内存结构的调整 9.3 Oracle运行环境的优化 9.3.5 Oracle9.3.5 Oracle碎片整理碎片整理碎片整理碎片整理 9.3.6 Oracle9.3.6 Oracle系统参数的调整系统参数的调整系统参数的调整系统参数的调整 内存参数的调整主要是指Oracle数据库的
11、系统全局区(SGA)的调整。SGA主要由三部分构成:共享池、数据缓冲区、日志缓冲区。1 1共享池共享池 共享池由两部分构成:共享SQL区和数据字典缓冲区。共享SQL区是存放用户SQL命令的区域,数据字典缓冲区存放数据库运行的动态信息。9.3.1 内存结构的调整(1)数据库管理员通过执行下述语句,来查看共享SQL区的使用率。select(sum(pins-reloads)/sum(pins)Lib Cache from v$librarycache;共享SQL区的使用率应该在90以上,否则需要增加共享池的大小。(2)数据库管理员可以执行下述语句,查看数据字典缓冲区的使用率。select(sum(
12、-getmisses-usage-fixed)/sum(gets)Row Cache from v$rowcache;数据字典缓冲区的使用率也应该在90以上,否则需要增加共享池的大小。2 2数据缓冲区数据缓冲区 数据库管理员可以通过下述语句,来查看数据库数据缓冲区的使用情况。SELECT name,FROM v$sysstat WHERE name IN(db block gets,consistent gets,physical reads);根据查询出来的结果可以计算出数据缓冲区的使用命中率:数据缓冲区的使用命中率1(physical reads/(db block gets+consis
13、tent gets)这个命中率应该在90以上,否则需要增加数据缓冲区的大小。3 3日志缓冲区日志缓冲区 数据库管理员可以通过执行下述语句,查看日志缓冲区的使用情况。select name,value from v$sysstat where name in(redo entries,redo log space requests);根据查询出的结果可以计算出日志缓冲区的申请失败率:申请失败率requests/entries申请失败率应该接近于0,否则说明日志缓冲区开设太小,需要增加Oracle数据库的日志缓冲区。(1)在磁盘上建立数据文件前首先运行磁盘碎片整理程序 为了安全地整理磁盘碎片,需关
14、闭打开数据文件的实例,并且停止服务。如果有足够的连续磁盘空间建立数据文件,那么就很容易避免数据文件产生碎片。(2)不要使用磁盘压缩 Oracle数据文件不支持磁盘压缩。(3)不要使用磁盘加密 加密象磁盘压缩一样增加了一个处理层,降低磁盘读写速度。如果担心自己的数据可能泄密,可以使用dbms_obfuscation包和label security选择性地加密数据的敏感部分。9.3.2 物理I/O的调整(5)使用RAID RAID的使用应注意:选择硬件RAID超过软件RAID;日志文件不要放在RAID 5卷上,因为RAID 5读性能高而写性能差;把日志文件和归档日志放在与控制文件和数据文件分离的磁
15、盘控制系统上。(6)分离页面交换文件到多个磁盘物理卷 跨越至少两个磁盘建立两个页面文件。可以建立四个页面文件并在性能上受益,确保所有页面文件的大小之和至少是物理内存的两倍。1 1查看查看CPUCPU的使用情况的使用情况 使用操作命令可以看到CPU的使用情况,一般UNIX操作系统的服务器,可以使用sar u命令查看CPU的使用率;NT操作系统的服务器,可以使用NT的性能管理器来查看CPU的使用率。出现CPU资源不足的情况是很多的:SQL语句的重解析、低效率的SQL语句、锁冲突都会引起CPU资源不足。9.3.3 CPU的优化调整2 2查看查看SQLSQL语句的解析情况语句的解析情况(1)数据库管理
16、员可以执行下述语句来查看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由此可以得到用户SQL语句平均解析等待时间:用户SQL语句平均解析等待时间waite_time/parse_count(2)数据库管理员还可以通过下述语句,查看低效率的
17、SQL语句:SELECT BUFFER_GETS,EXECUTIONS,SQL_TEXT FROM V$SQLAREA;优化这些低效率的SQL语句也有助于提高CPU的利用率。3 3查看查看OracleOracle数据库的冲突情况数据库的冲突情况 数据库管理员可以通过v$system_event数据字典中的“latch free”统计项查看Oracle数据库的冲突情况,如果没有冲突的话,latch free查询出来没有结果。如果冲突太大的话,数据库管理员可以降低spin_count参数值,来消除高的CPU使用率。4 4CPUCPU的优化调整方法的优化调整方法 一些优化CPU使用和配置的具体方法有
18、:(1)取消屏幕保护。(2)把系统配置为应用服务器。(3)监视系统中消耗中断的硬件。(4)保持最小的安全审计记录。(5)在专用服务器上运行Oracle。(6)禁止非必须的服务。网络配置是性能调整的一项很重要的内容,而且很容易隐藏性能瓶颈。(1)配置网卡使用最快速度和有效模式(2)删除不需要的网络协议(3)优化网络协议绑定顺序(4)为Oracle禁止或优化文件共享9.3.4 网络配置的优化1碎片是如何产生的2碎片对系统的影响(1)导致系统性能减弱(2)浪费大量的表空间9.3.5 Oracle碎片整理3 3自由范围的碎片计算自由范围的碎片计算 用fsfifree space fragmentati
19、on index(自由空间碎片索引)值来直观体现:fsfi=100*sqrt(max(extent)/sum(extents)*1/sqrt(sqrt(count(extents)4 4自由范围的碎片整理自由范围的碎片整理 可以将表空间的缺省存储参数pctincrease改为非0。一般将其设为1,如:alter tablespace temp default storage(pctincrease 1);这样smon便会将自由范围自动合并,达到碎片整理的目的。也可以采用如下语句,通过手工合并自由范围来达到碎片整理的目的。alter tablespace temp coalesce;5 5段的碎
20、片整理段的碎片整理 段由范围组成,在有些情况下,有必要对段的碎片进行整理。要查看段的有关信息,可查看数据字典DBA_segments,范围的信息可查看数据字典DBA_extents。如果段的碎片过多,将其数据压缩到一个范围的最简单方法便是用正确的存储参数将这个段重建,然后将旧表中的数据插入到新表,同时删除旧表。这个过程可以用import/export(输入/输出)工具来完成。export()命令有一个(压缩)标志,这个标志在读表时会引发export确定该表所分配的物理空间量,它会向输出转储文件写入一个新的初始化存储参数,等于全部所分配空间。若这个表关闭,则使用import()工具重新生成。这样
21、,它的数据会放入一个新的、较大的初始段中。例如: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;这种方法可用于整个数据库。另外,应该定期shutdown database,从而清理momery碎片。9.3.6 Oracle系统参数的调整 1Shared Pool and Library Cach
22、e Performance Tuning(共享(共享池和池和Library Cache)共享池调整的技巧主要有:(1)刷共享池刷(Flush)共享池可以使小块的内存合并为大块的内存。当共享池的碎片过多时,能够暂时恢复性能。刷共享池可以使用语句:alter system flush shared_pool;(2)绑定变量2Buffer Cache Performance Tuning(数据库缓存调整)(数据库缓存调整)从缓存调整的角度看,应力求避免以下的问题:(1)“缓存的最近最少使用(LRN)链”(cache buffers LRU chain)的加锁竞争;(2)“平均写队列”(Average
23、 Write Queue)长度过大;(3)过多时间花在等待“写完毕等待上”(write complete waits);(4)过多时间花在等待“缓冲释放等待”上(free buffer waits)。3Latch Contention(加锁或插销竞争)(加锁或插销竞争)插销加锁是SGA中保护共享数据结构的低层的串行化机制。插销latch是一类可以非常快的获得和释放的锁。插销锁的实现是依赖于操作系统的,尤其在关于一个进程是否会等待一个锁,和等多久方面。有如下的锁(插销)需要调整:(1)Redo Copy/Allocation Latch:重写日志的复制/分配插销(2)Shared Pool La
24、tch:共享池的插销(3)Library Cache Latch:Library Cache插销4Redo Log Buffer Performance Tuning(重写日志缓冲的调整)(重写日志缓冲的调整)LGWR 将重写日志缓冲中的重写项写到重写日志文件中。一旦LGWR将这些项复制到重写日志文件中,用户进程就可以重写这些项。统计项目“redo log space requests”反映了用户进程等待重写日志缓冲中空间的时间的数字。(1)设置重写日志大小的提示:“redo log space requests”的值应该接近0。(2)设定合适的重写日志的大小,建议每15-30分钟进行一次重写
25、日志的切换。5Query Performance Tuning(查询效率的调整)(查询效率的调整)如果查询运行得很慢,请考虑以下这些方面:(1)希望这个查询运行的有多快以及有理由这样要求吗?(2)优化模式OPTIMIZER_MODE 设为何值?(3)查询涉及的索引都是有效的吗?(4)在数据库中有没有其他的长时间运行的查询(大查询)。(5)表和索引上有统计信息吗?(6)统计信息是被计算出来的还是被估计出来的?对于查询的性能调整有两个主要的调试工具:TKPROF和AUTOTRACE。6Temporary Tablespace Performance Tuning(临时表空间的(临时表空间的调整)调
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 性能 优化
1、咨信平台为文档C2C交易模式,即用户上传的文档直接被用户下载,收益归上传人(含作者)所有;本站仅是提供信息存储空间和展示预览,仅对用户上传内容的表现方式做保护处理,对上载内容不做任何修改或编辑。所展示的作品文档包括内容和图片全部来源于网络用户和作者上传投稿,我们不确定上传用户享有完全著作权,根据《信息网络传播权保护条例》,如果侵犯了您的版权、权益或隐私,请联系我们,核实后会尽快下架及时删除,并可随时和客服了解处理情况,尊重保护知识产权我们共同努力。
2、文档的总页数、文档格式和文档大小以系统显示为准(内容中显示的页数不一定正确),网站客服只以系统显示的页数、文件格式、文档大小作为仲裁依据,平台无法对文档的真实性、完整性、权威性、准确性、专业性及其观点立场做任何保证或承诺,下载前须认真查看,确认无误后再购买,务必慎重购买;若有违法违纪将进行移交司法处理,若涉侵权平台将进行基本处罚并下架。
3、本站所有内容均由用户上传,付费前请自行鉴别,如您付费,意味着您已接受本站规则且自行承担风险,本站不进行额外附加服务,虚拟产品一经售出概不退款(未进行购买下载可退充值款),文档一经付费(服务费)、不意味着购买了该文档的版权,仅供个人/单位学习、研究之用,不得用于商业用途,未经授权,严禁复制、发行、汇编、翻译或者网络传播等,侵权必究。
4、如你看到网页展示的文档有www.zixin.com.cn水印,是因预览和防盗链等技术需要对页面进行转换压缩成图而已,我们并不对上传的文档进行任何编辑或修改,文档下载后都不会有水印标识(原文档上传前个别存留的除外),下载后原文更清晰;试题试卷类文档,如果标题没有明确说明有答案则都视为没有答案,请知晓;PPT和DOC文档可被视为“模板”,允许上传人保留章节、目录结构的情况下删减部份的内容;PDF文档不管是原文档转换或图片扫描而得,本站不作要求视为允许,下载前自行私信或留言给上传者【w****g】。
5、本文档所展示的图片、画像、字体、音乐的版权可能需版权方额外授权,请谨慎使用;网站提供的党政主题相关内容(国旗、国徽、党徽--等)目的在于配合国家政策宣传,仅限个人学习分享使用,禁止用于任何广告和商用目的。
6、文档遇到问题,请及时私信或留言给本站上传会员【w****g】,需本站解决可联系【 微信客服】、【 QQ客服】,若有其他问题请点击或扫码反馈【 服务填表】;文档侵犯商业秘密、侵犯著作权、侵犯人身权等,请点击“【 版权申诉】”(推荐),意见反馈和侵权处理邮箱:1219186828@qq.com;也可以拔打客服电话:4008-655-100;投诉/维权电话:4009-655-100。