OracleSQL性能优化技巧分解

Oracle的性能优化本章学习目标本章将介绍优化和调整Oracle数据库系统的一些相关命令和方法。第九章Oracle的性能优化本章内容安排9.1数据库性能优化概述9.2SQL语句的优化9.3Oracle运行环境的优化9.4并发事件处理9.5数据完整性9.7常见问题处理9.1数据库性能优化概述9.1.1数据库性能优化的内容9.1.2不同类型系统的优化（1）调整数据结构的设计。（2）调整应用程序结构设计。（3）调整数据库SQL语句。（4）调整服务器内存分配。（5）调整硬盘I/O，这一步是在信息系统开发之前完成的。（6）调整操作系统参数。9.1.1数据库性能优化的内容1．在线事务处理信息系统（OLTP）这种类型的信息系统一般需要有大量的Insert、Update操作。OLTP系统需要保证数据库的并发性、可靠性和最终用户的速度，这类系统使用的Oracle数据库需要主要考虑下述因素或参数：（1）数据库回滚段是否足够？（2）是否需要建立Oracle数据库索引、聚集、散列？（3）系统全局区（SGA）大小是否足够？（4）SQL语句是否高效？9.1.2不同类型系统的优化2．数据仓库系统（DataWarehousing）这种信息系统的主要任务是从Oracle的海量数据中进行查询，得到数据之间的某些规律。数据库管理员需要为这种类型的Oracle数据库着重考虑下述因素或参数：（1）是否采用B*-索引或者bitmap索引？（2）是否采用并行SQL查询以提高查询效率？（3）是否采用PL/SQL函数编写存储过程？（4）是否有必要建立并行数据库，来提高数据库的查询效率。9.2.2SQL语句优化的具体方法9.2.1SQL语句的优化规则9.2SQL语句的优化9.2.1SQL语句的优化规则（1）去掉不必要的大表、全表扫描。不必要的大表、全表扫描会造成不必要的输入输出，而且还会拖垮整个数据库；（2）检查优化索引的使用这对于提高查询速度来说非常重要；（3）检查子查询，考虑SQL子查询是否可以用简单连接的方式进行重新书写；（4）调整PCTFREE和PCTUSED等存储参数优化插入、更新或者删除等操作；（5）考虑数据库的优化器；（6）考虑数据表的全表扫描和在多个CPU的情况下考虑并行查询。1．索引的使用2.SQL语句排序优化3.选择联合查询的联合次序9.2.2SQL语句优化的具体方法4.SQL子查询的调整1、索引的使用（1）尽量使用索引是全表扫描还是索引范围扫描主要考虑SQL的查询速度问题。试比较下面两条SQL语句：①语句A：SELECTdname,deptnoFROMdeptWHEREdeptnoNOTIN（SELECTdeptnoFROMemp）;②语句B：SELECTdname,deptnoFROMdeptWHERENOTEXISTS（SELECTdeptnoFROMempWHEREdept.deptno=emp.deptno）;（2）索引不起作用的情况①存在数据类型隐形转换②列上有数学运算③使用不等于（）运算④使用substr字符串函数⑤‘%’通配符在第一个字符⑥字符串连接（||）（3）函数的索引例如，日期类型是经常用到的，而且在SQL语句中会使用to_char函数以查询具体的的范围日期。如：select*fromstaff_memberwhereTO_CHAR（birth_day,’YYYY’）=’2003’;可以建立基于函数的索引如：CREATEINDEXInd_emp_birthONstaff_member（to_char（（birth_day,’YYYY’））;2.SQL语句排序优化排序发生的情况如下：SQL中包含groupby子句SQL中包含orderby子句SQL中包含distinct子句SQL中包含minus或union操作3．选择联合查询的联合次序联合查询中如涉及到多个表的字段关联及查询，其SQL查询语句联合次序的不同写法，会导致语句对各表具体操作的步骤有不同的次序，所以虽然执行结果相同，但执行效率却不同。４．SQL子查询的调整（1）关联子查询和非关联子查询非关联查询的开销——非关联查询时子查询只会执行一次，而且结果是排序好的，并保存在一个Oracle的临时段中，其中的每一个记录在返回时都会被父查询所引用。在子查询返回大量的记录的情况下，将这些结果集排序，以及将临时数据段进行排序会增加大量的系统开销。关联查询的开销——对返回到父查询的记录来说，子查询会每行执行一次。因此，必须保证任何可能的时候子查询用到索引。（2）在子查询中慎重使用IN或者NOTIN语句在子查询中慎重使用IN或者NOTIN语句，使用where（NOT）exists的效果要好的多。①带IN的关联子查询是多余的，因为IN子句和子查询中相关的操作的功能是一样的。②为非关联子查询指定EXISTS子句是不适当的，因为这样会产生笛卡尔乘积。③尽量不要使用NOTIN子句。（3）慎重使用视图的联合查询慎重使用视图的联合查询，尤其是比较复杂的视图之间的联合查询。一般对视图的查询最好都分解为对数据表的直接查询效果要好一些。可以在参数文件中设置SHARED_POOL_RESERVED_SIZE参数，这个参数在SGA共享池中保留一个连续的内存空间，连续的内存空间有益于存放大的SQL程序包。9.3.2物理I/O的调整9.3.3CPU的优化调整9.3.4网络配置的优化9.3.1内存结构的调整9.3Oracle运行环境的优化9.3.5Oracle碎片整理9.3.6Oracle系统参数的调整内存参数的调整主要是指Oracle数据库的系统全局区（SGA）的调整。SGA主要由三部分构成：共享池、数据缓冲区、日志缓冲区。1．共享池共享池由两部分构成：共享SQL区和数据字典缓冲区。共享SQL区是存放用户SQL命令的区域，数据字典缓冲区存放数据库运行的动态信息。9.3.1内存结构的调整（1）数据库管理员通过执行下述语句，来查看共享SQL区的使用率。select（sum（pins-reloads））/sum（pins）LibCachefromv$librarycache;共享SQL区的使用率应该在90％以上，否则需要增加共享池的大小。（2）数据库管理员可以执行下述语句，查看数据字典缓冲区的使用率。select（sum（-getmisses-usage-fixed））/sum（gets）RowCachefromv$rowcache;数据字典缓冲区的使用率也应该在90％以上，否则需要增加共享池的大小。2．数据缓冲区数据库管理员可以通过下述语句，来查看数据库数据缓冲区的使用情况。SELECTname，FROMv$sysstatWHEREnameIN（'dbblockgets','consistentgets','physicalreads'）;根据查询出来的结果可以计算出数据缓冲区的使用命中率：数据缓冲区的使用命中率＝1–(physicalreads/(dbblockgets+consistentgets))这个命中率应该在90％以上，否则需要增加数据缓冲区的大小。3．日志缓冲区数据库管理员可以通过执行下述语句，查看日志缓冲区的使用情况。selectname,valuefromv$sysstatwherenamein('redoentries','redologspacerequests');根据查询出的结果可以计算出日志缓冲区的申请失败率：申请失败率＝requests/entries申请失败率应该接近于0，否则说明日志缓冲区开设太小，需要增加Oracle数据库的日志缓冲区。（1）在磁盘上建立数据文件前首先运行磁盘碎片整理程序为了安全地整理磁盘碎片，需关闭打开数据文件的实例，并且停止服务。如果有足够的连续磁盘空间建立数据文件，那么就很容易避免数据文件产生碎片。（2）不要使用磁盘压缩Oracle数据文件不支持磁盘压缩。（3）不要使用磁盘加密加密象磁盘压缩一样增加了一个处理层，降低磁盘读写速度。如果担心自己的数据可能泄密，可以使用dbms_obfuscation包和labelsecurity选择性地加密数据的敏感部分。9.3.2物理I/O的调整（5）使用RAIDRAID的使用应注意：①选择硬件RAID超过软件RAID；②日志文件不要放在RAID5卷上，因为RAID5读性能高而写性能差；③把日志文件和归档日志放在与控制文件和数据文件分离的磁盘控制系统上。（6）分离页面交换文件到多个磁盘物理卷跨越至少两个磁盘建立两个页面文件。可以建立四个页面文件并在性能上受益，确保所有页面文件的大小之和至少是物理内存的两倍。1．查看CPU的使用情况使用操作命令可以看到CPU的使用情况，一般UNIX操作系统的服务器，可以使用sar–u命令查看CPU的使用率；NT操作系统的服务器，可以使用NT的性能管理器来查看CPU的使用率。出现CPU资源不足的情况是很多的：SQL语句的重解析、低效率的SQL语句、锁冲突都会引起CPU资源不足。9.3.3CPU的优化调整2．查看SQL语句的解析情况（1）数据库管理员可以执行下述语句来查看SQL语句的解析情况：SELECT*FROMV$SYSSTATWHERENAMEIN('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）数据库管理员还可以通过下述语句，查看低效率的SQL语句：SELECTBUFFER_GETS,EXECUTIONS,SQL_TEXTFROMV$SQLAREA;优化这些低效率的SQL语句也有助于提高CPU的利用率。3．查看Oracle数据库的冲突情况数据库管理员可以通过v$system_event数据字典中的“latchfree”统计项查看Oracle数据库的冲突情况，如果没有冲突的话，latchfree查询出来没有结果。如果冲突太大的话，数据库管理员可以降低spin_count参数值，来消除高的CPU使用率。4．CPU的优化调整方法一些优化CPU使用和配置的具体方法有：（1）取消屏幕保护。（2）把系统配置为应用服务器。（3）监视系统中消耗中断的硬件。（4）保持最小的安全审计记录。（5）在专用服务器上运行Oracle。（6）禁止非必须的服务。网络配置是性能调整的一项很重要的内容，而且很容易隐藏性能瓶颈。（1）配置网卡使用最快速度和有效模式（2）删除不需要的网络协议（3）优化网络协议绑定顺序（4）为Oracle禁止或优化文件共享9.3.4网络配置的优化1．碎片是如何产生的2．碎片对系统的影响（1）导致系统性能减弱（2）浪费大量的表空间9.3.5Oracle碎片整理3．自由范围的碎片计算用fsfi——freespacefragmentationindex（自由空间碎片索引）值来直观体现：fsfi=100*sqrt(max(extent)/sum(extents))*1/sqrt(sqrt(count(extents)))4．自由范围的碎片整理可以将表空间的缺省存储参数pctincrease改为非0。一般将其设为1，如：altertablespacetempdefaultstorage(pctincrease1);这样smon便会将自由范围自动合并，达到碎片整理的目的。也可以采用如下语句，通过手工合并自由范围来达到碎片整理的目的。altertablespacetempcoalesce;5．段的碎片整理段由范围组成，在有些情况下，有必要对段的碎片进行整理。要查看段的有关信息，可查看数据字典DBA_segments，范围的信息可查看数据字典DBA_extents。如果段的碎片过多，将其数据压缩到