Oracle的性能优化.ppt
《Oracle的性能优化.ppt》由会员分享,可在线阅读,更多相关《Oracle的性能优化.ppt(71页珍藏版)》请在三一文库上搜索。
1、Oracle的性能优化,本章学习目标 本章将介绍优化和调整Oracle数据库系统的一些相关命令和方法。,第九章 Oracle的性能优化,本章内容安排,9.1 数据库性能优化概述,9.2 SQL语句的优化,9.3 Oracle运行环境的优化,9.4 并发事件处理,9.5 数据完整性,9.7 常见问题处理,9.1 数据库性能优化概述,9.1.1 数据库性能优化的内容,9.1.2 不同类型系统的优化,(1)调整数据结构的设计。 (2)调整应用程序结构设计。 (3)调整数据库SQL语句。 (4)调整服务器内存分配。 (5)调整硬盘I/O,这一步是在信息系统开发之前完成的。 (6)调整操作系统参数。,9
2、.1.1 数据库性能优化的内容,1在线事务处理信息系统(OLTP) 这种类型的信息系统一般需要有大量的Insert、Update操作。OLTP系统需要保证数据库的并发性、可靠性和最终用户的速度,这类系统使用的Oracle数据库需要主要考虑下述因素或参数: (1)数据库回滚段是否足够? (2)是否需要建立Oracle数据库索引、聚集、散列? (3)系统全局区(SGA)大小是否足够? (4)SQL语句是否高效?,9.1.2 不同类型系统的优化,2数据仓库系统(Data Warehousing) 这种信息系统的主要任务是从Oracle的海量数据中进行查询,得到数据之间的某些规律。数据库管理员需要为这
3、种类型的Oracle数据库着重考虑下述因素或参数: (1)是否采用B*-索引或者bitmap索引? (2)是否采用并行SQL查询以提高查询效率? (3)是否采用PL/SQL函数编写存储过程? (4)是否有必要建立并行数据库,来提高数据库的查询效率。,9.2.2 SQL语句优化的具体方法,9.2.1 SQL语句的优化规则,9.2 SQL语句的优化,9.2.1 SQL语句的优化规则,(1)去掉不必要的大表、全表扫描。不必要的大表、全表扫描会造成不必要的输入输出,而且还会拖垮整个数据库; (2)检查优化索引的使用 这对于提高查询速度来说非常重要; (3)检查子查询,考虑SQL子查询是否可以用简单连接
4、的方式进行重新书写; (4)调整PCTFREE和PCTUSED等存储参数优化插入、更新或者删除等操作; (5)考虑数据库的优化器; (6)考虑数据表的全表扫描和在多个CPU的情况下考虑并行查询。,1索引的使用,2.SQL语句排序优化,3. 选择联合查询的联合次序,9.2.2 SQL语句优化的具体方法,4.SQL子查询的调整,1、索引的使用 (1)尽量使用索引 是全表扫描还是索引范围扫描主要考虑SQL的查询速度问题。试比较下面两条SQL语句: 语句A: SELECT dname,deptno FROM dept WHERE deptno NOT IN(SELECT deptno FROM emp
5、); 语句B: SELECT dname,deptno FROM dept WHERE NOT EXISTS(SELECT deptno FROM emp WHERE dept.deptno = emp.deptno);,(2)索引不起作用的情况 存在数据类型隐形转换 列上有数学运算 使用不等于()运算 使用substr字符串函数 %通配符在第一个字符 字符串连接(|),(3)函数的索引 例如,日期类型是经常用到的,而且在SQL语句中会使用to_char函数以查询具体的的范围日期。如: select * from staff_member where TO_CHAR(birth_day,YYY
6、Y)=2003; 可以建立基于函数的索引如: CREATE INDEX Ind_emp_birth ON staff_member (to_char(birth_day,YYYY);,2.SQL语句排序优化 排序发生的情况如下: SQL中包含group by 子句 SQL 中包含order by 子句 SQL 中包含 distinct 子句 SQL 中包含 minus 或 union操作,3选择联合查询的联合次序 联合查询中如涉及到多个表的字段关联及查询,其SQL查询语句联合次序的不同写法,会导致语句对各表具体操作的步骤有不同的次序,所以虽然执行结果相同,但执行效率却不同。,SQL子查询的调整
7、 (1)关联子查询和非关联子查询 非关联查询的开销非关联查询时子查询只会执行一次,而且结果是排序好的,并保存在一个Oracle的临时段中,其中的每一个记录在返回时都会被父查询所引用。在子查询返回大量的记录的情况下,将这些结果集排序,以及将临时数据段进行排序会增加大量的系统开销。 关联查询的开销对返回到父查询的记录来说,子查询会每行执行一次。因此,必须保证任何可能的时候子查询用到索引。,(2)在子查询中慎重使用IN或者NOT IN语句 在子查询中慎重使用IN或者NOT IN语句,使用where (NOT)exists的效果要好的多。 带IN的关联子查询是多余的,因为IN子句和子查询中相关的操作的
8、功能是一样的。 为非关联子查询指定EXISTS子句是不适当的,因为这样会产生笛卡尔乘积。 尽量不要使用NOT IN子句。,(3)慎重使用视图的联合查询 慎重使用视图的联合查询,尤其是比较复杂的视图之间的联合查询。一般对视图的查询最好都分解为对数据表的直接查询效果要好一些。 可以在参数文件中设置SHARED_POOL_RESERVED_SIZE参数,这个参数在SGA共享池中保留一个连续的内存空间,连续的内存空间有益于存放大的SQL程序包。,9.3.2 物理I/O的调整,9.3.3 CPU的优化调整,9.3.4 网络配置的优化,9.3.1 内存结构的调整,9.3 Oracle运行环境的优化,9.3
9、.5 Oracle碎片整理,9.3.6 Oracle系统参数的调整,内存参数的调整主要是指Oracle数据库的系统全局区(SGA)的调整。SGA主要由三部分构成:共享池、数据缓冲区、日志缓冲区。 1共享池 共享池由两部分构成:共享SQL区和数据字典缓冲区。共享SQL区是存放用户SQL命令的区域,数据字典缓冲区存放数据库运行的动态信息。,9.3.1 内存结构的调整,(1)数据库管理员通过执行下述语句,来查看共享SQL区的使用率。 select (sum(pins-reloads)/sum(pins) “Lib Cache“ from v$librarycache; 共享SQL区的使用率应该在90
10、以上,否则需要增加共享池的大小。 (2)数据库管理员可以执行下述语句,查看数据字典缓冲区的使用率。 select (sum(-getmisses-usage-fixed)/sum(gets) “Row Cache“ from v$rowcache; 数据字典缓冲区的使用率也应该在90以上,否则需要增加共享池的大小。,2数据缓冲区 数据库管理员可以通过下述语句,来查看数据库数据缓冲区的使用情况。 SELECT name, FROM v$sysstat WHERE name IN (db block gets,consistent gets,physical reads); 根据查询出来的结果可以
11、计算出数据缓冲区的使用命中率: 数据缓冲区的使用命中率1 ( physical reads/(db block gets + consistent gets) 这个命中率应该在90以上,否则需要增加数据缓冲区的大小。,3日志缓冲区 数据库管理员可以通过执行下述语句,查看日志缓冲区的使用情况。 select name,value from v$sysstat where name in (redo entries,redo log space requests); 根据查询出的结果可以计算出日志缓冲区的申请失败率: 申请失败率requests/entries 申请失败率应该接近于0,否则说明日志
12、缓冲区开设太小,需要增加Oracle数据库的日志缓冲区。,(1)在磁盘上建立数据文件前首先运行磁盘碎片整理程序 为了安全地整理磁盘碎片,需关闭打开数据文件的实例,并且停止服务。如果有足够的连续磁盘空间建立数据文件,那么就很容易避免数据文件产生碎片。 (2)不要使用磁盘压缩 Oracle数据文件不支持磁盘压缩。 (3)不要使用磁盘加密 加密象磁盘压缩一样增加了一个处理层,降低磁盘读写速度。如果担心自己的数据可能泄密,可以使用dbms_obfuscation包和label security选择性地加密数据的敏感部分。,9.3.2 物理I/O的调整,(5)使用RAID RAID的使用应注意: 选择硬
13、件RAID超过软件RAID; 日志文件不要放在RAID 5卷上,因为RAID 5读性能高而写性能差; 把日志文件和归档日志放在与控制文件和数据文件分离的磁盘控制系统上。 (6)分离页面交换文件到多个磁盘物理卷 跨越至少两个磁盘建立两个页面文件。可以建立四个页面文件并在性能上受益,确保所有页面文件的大小之和至少是物理内存的两倍。,1查看CPU的使用情况 使用操作命令可以看到CPU的使用情况,一般UNIX操作系统的服务器,可以使用sar u命令查看CPU的使用率;NT操作系统的服务器,可以使用NT的性能管理器来查看CPU的使用率。 出现CPU资源不足的情况是很多的:SQL语句的重解析、低效率的SQ
14、L语句、锁冲突都会引起CPU资源不足。,9.3.3 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 由此可以得到用户SQL语句平均
15、解析等待时间: 用户SQL语句平均解析等待时间waite_time/parse_count,(2)数据库管理员还可以通过下述语句,查看低效率的SQL语句: SELECT BUFFER_GETS,EXECUTIONS,SQL_TEXT FROM V$SQLAREA; 优化这些低效率的SQL语句也有助于提高CPU的利用率。,3查看Oracle数据库的冲突情况 数据库管理员可以通过v$system_event数据字典中的“latch free”统计项查看Oracle数据库的冲突情况,如果没有冲突的话,latch free查询出来没有结果。如果冲突太大的话,数据库管理员可以降低spin_count参数
16、值,来消除高的CPU使用率。,4CPU的优化调整方法 一些优化CPU使用和配置的具体方法有: (1)取消屏幕保护。 (2)把系统配置为应用服务器。 (3)监视系统中消耗中断的硬件。 (4)保持最小的安全审计记录。 (5)在专用服务器上运行Oracle。 (6)禁止非必须的服务。,网络配置是性能调整的一项很重要的内容,而且很容易隐藏性能瓶颈。 (1)配置网卡使用最快速度和有效模式 (2)删除不需要的网络协议 (3)优化网络协议绑定顺序 (4)为Oracle禁止或优化文件共享,9.3.4 网络配置的优化,1碎片是如何产生的 2碎片对系统的影响 (1)导致系统性能减弱 (2)浪费大量的表空间,9.3
17、.5 Oracle碎片整理,3自由范围的碎片计算 用fsfifree space fragmentation index(自由空间碎片索引)值来直观体现: fsfi=100*sqrt(max(extent)/sum(extents)*1/sqrt(sqrt(count(extents),4自由范围的碎片整理 可以将表空间的缺省存储参数pctincrease改为非0。一般将其设为1,如: alter tablespace temp default storage(pctincrease 1); 这样smon便会将自由范围自动合并,达到碎片整理的目的。 也可以采用如下语句,通过手工合并自由范围来达
18、到碎片整理的目的。 alter tablespace temp coalesce;,5段的碎片整理 段由范围组成,在有些情况下,有必要对段的碎片进行整理。要查看段的有关信息,可查看数据字典DBA_segments,范围的信息可查看数据字典DBA_extents。如果段的碎片过多, 将其数据压缩到一个范围的最简单方法便是用正确的存储参数将这个段重建,然后将旧表中的数据插入到新表,同时删除旧表。这个过程可以用import/export(输入/输出)工具来完成。 export()命令有一个(压缩)标志,这个标志在读表时会引发export确定该表所分配的物理空间量,它会向输出转储文件写入一个新的初始化
19、存储参数,等于全部所分配空间。若这个表关闭, 则使用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; 这种方法可用于整个数据库。 另外,应该定期shutdown database,从而清理momery碎片。,
20、9.3.6 Oracle系统参数的调整,1Shared Pool and Library Cache Performance Tuning(共享池和Library Cache) 共享池调整的技巧主要有: (1)刷共享池 刷( Flush)共享池可以使小块的内存合并为大块的内存。当共享池的碎片过多时,能够暂时恢复性能。刷共享池可以使用语句: alter system flush shared_pool; (2)绑定变量,2Buffer Cache Performance Tuning(数据库缓存调整) 从缓存调整的角度看,应力求避免以下的问题: (1)“缓存的最近最少使用(LRN)链”(cach
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 性能 优化
链接地址:https://www.31doc.com/p-4651648.html