数据库调优说明.doc

Oracle应用系统优化计划一、 性能优化的前提 应用系统方案制定准确，对应用系统运行环境分析合理、正确，在数据库服务器性能、存储空间、网络带宽等方面的配置能够达到系统运行要求。二、 优化工作的阶段可优化目标说明设计阶段业务对象不能建立在系统表空间；索引表空间和业务表空间分开；将LOB类型的字段与其它的类型分开；根据应用系统功能确定是否要采用冗余字段；正确的主键字段的选择，建议采用数字，不推荐使用复合主键；开发测试阶段执行sql使用变量绑定的方式，尽可能的保留在共享内存中，提高sql命中率；多表关联查询时采用有效的连接顺序；尽可能的降低客户端和服务器的网络数据交互，某个业务功能点需要频繁和数据库交互的，建议采用存储过程、临时表实现；根据查询条件建立必要的索引，查询条件中使用oracle函数建立相对应的函数索引,数据值范围较小的采用位图索引多张表关联查询时，有时可采用先查询符合条件对应的表中关键字，然后通过关键字再查询对应表中相关信息；频繁访问，较少更新的数据量较小的表信息可采用缓存的方式；安装阶段操作系统交换区和操作系统内核参数设置Oracle文件和启动参数设置运行阶段实际优化工作最多的阶段，运行阶段优化的真正工作是解决因为实际运行数据库参数设置不当、表、索引统计信息不准确，执行路径不当等导致的性能问题。前三个阶段的优化内容。三、 优化目标及说明1、 总体目标响应时间与吞吐量平衡（OLTP系统把吞吐量定义为性能指标；）临界资源系统吞吐量指在给定的时间内所完成的工作量。有以下两种技术： 以相同的资源来完成更多的工作（减少服务时间）； 通过减少整个响应时间来更快完成工作。2、 具体可优化目标可优化目标说明执行服务器资源及网络确保服务器资源满足Oracle应用需求DBA和系统管理员应用程序优化SQL语句的优化；视图、索引的使用；减少用户调用的网络流量DBA和应用研发员Oracle内存优化对PGA和SGA各区域的参数调整，超过250个配置参数、上千个测量值的监视，重点在共享池（字典缓存、库缓存）、缓冲区缓存、排序区和散列区的调整等。DBA磁盘I/O优化当Oracle由磁盘上的一个数据文件得到一个数据块时，读的进程就必须等待物理I/O操作完成。磁盘操作要比数据缓冲慢10,000倍。因此，如果可以令I/O最小化，或者减少由于磁盘上的文件竞争而带来的瓶颈，就可以大大地改善Oracle数据库的性能。DBA注：目标是否需要调整，由具体的诊断工作决定。四、 优化前的工作、连接数据库的方式服务器主机名和数据库网络服务名SYSTEM或SYS账户密码（或任何具有DBA权限的用户密码）、安装、测试并运行statspack工具，获取数据库性能快照、通过数据库快照生成统计报告，分析当前系统性能瓶颈。五、 当前优化工作的重点1、 外部的性能问题数据库性能和外部的环境的关系密不可分，如果外部环境出现瓶颈，Oracle内部调整是没有帮助的。需要确认的硬件参数： CPU CPU资源的不足令查询变慢。当查询超过了Oracle服务器的CPU性能时，你的数据库性能就受到CPU的限制，确认CPU资源满足Oracle查询需求。 内存可用于Oracle的内存数量也会影响SQL的性能，特别是在数据缓冲和内存排序方面，确认是否存在内存分页。 网络大量的Net8通信令SQL的性能变慢，确认网卡和带宽是否满足当前应用的传输需求。方法： 搜集数据库服务器的统计数据，判断服务器拥有的硬件资源是否满足Oracle的处理需求。可以利用操作系统工具来检测。2、 共享池和数据缓冲区缓存的调整2.1 Library cache的调整 通过监控v$librarycache的信息来判断库缓冲区库缓冲区的命中率并决定它的大小。调整目标：若sum(pins)/sum(reloads) 1调整方式：测试并确定shared_pool_size的合适大小。2.2 Dictionary cache的调整 v$Dictionarycache动态视图提供字典缓存的性能信息。调整目标：sum(getmisses)/sum(gets) 60%70%调整方式：增大db_cache_size的值，热点小表的常驻缓存等。3、 Row-resequencing（行的重新排序）在高容量的OLTP环境中，数据是由一个primary索引得到的，重新排序表格的行就可以令连续块的顺序和它们的primary索引一样，这样就可以在索引驱动的表格查询中，减少物理I/O并且改善响应时间。原则 在应用选择多行的时候有用； 在使用索引范围搜索和应用发出多个查询来得到连续的key时有效； 对于随机的唯一primary-key（主键）的访问将不会由行重新排序中得到好处。方法： 使用Oracle的Create Table As Select (CTAS) 语法来拷贝表格 Oracle自带的表格重新组织工具4、 应用调优4.1 SQL调优SQL 调优是一个复杂的主题，不过有一些基本的规则是调优工作所需要跟从的，这些规则可以改善系统的性能。 SQL调优的原则： 消除不必要的大表全表搜索：在一个有序的表中，如果查询返回少于40%的行，或者在一个无序的表中，返回少于7%的行，那么这个查询都可以调整为使用一个索引来代替全表搜索。 在全表搜索是一个最快的访问方法时，将小表的全表搜索放到缓存中，应该确保有一个专门的数据缓冲用作行缓冲。 确保最优的索引使用。 确保最优的JOIN操作。 子查询中IN或者NOT IN等语句的使用以上这几条规则占据了 SQL 调优任务的 90% ，并且它们也无需完全懂得 Oracle SQL 的内部运作。SQL优化的步骤： 调整sga区，使得sga区的使用最优。 sql语句本身的优化，工具有explain,sql trace等 数据库结构调整 项目结构调整4.2索引的建立和使用利用索引可以提高查询性能，减少磁盘 I/O，优化对数据表的查询，加速SQL语句的执行。 索引建立和使用的原则： 该表常用来在索引列上查询 该表不常更新、插入、删除等操作 在一个有序的表中，查询返回少于40%的行；在一个无序的表中，返回应少于7%的行 索引与表的分离存放。5、 调整Oracle的排序操作当排序不能在分配的空间中完成时，就会使用磁盘排序的方式，即在temp tablespace中进行。磁盘排序的开销是很大的，有几个方面的原因。首先，和内存排序相比较，它们特别慢；而且磁盘排序会消耗临时表空间中的资源。Oracle还必须分配缓冲池块来保持临时表空间中的块。涉及排序的操作： 创建索引； 涉及到索引维护的并行插入 order by或者group by（尽可能对索引字段排序） Distinct union/intersect/minus sort-merge join analyze命令（仅可能使用estamate而不是compute）在专有服务器模式下，排序区包含在PGA中，PGA_AGGREGATE_TARGET参数大小需要连续的调整和监控，以确定最佳值。6、 调整Oracle的竞争每当一个Oracle 进程试图访问一个Oracle结构，但由于该结构正由另一个进程结构使用而未能成功访问到它时，就发生对Oracle资源的争用。常见的有latch、Free List 、lock争用。6.1 Latch（锁存器）Latch是简单的、低层次的序列化技术，用以保护SGA中的共享数据结构，比如并发用户列表和buffer cache里的blocks信息。一个服务器进程或后台进程在开始操作或寻找一个共享数据结构之前必须获得对应的latch，在完成以后释放latch。latch可作为内存性能的指标，不必对latch本身进行优化，如果latch存在竞争，表明SGA的一部分正在经历不正常的资源使用，即内存需要调整。Latch调整 如果竞争主要存在于shared pool和library cache中，可以考虑调整应用 如果进一步的调查显示需要调整shared pool和buffer cache，就进行调整注：v$latch和v$system_event试图提供了有关latch的相关信息。6.2 FreeList：会导致繁忙表上的DML操作性能很差。Oracle虽然可以自动维护表和索引的空间管理，但在设置表和索引的存储参数时，必须考虑平均的行长和数据库的块大小，对于调整拥有高的insert或者update的系统来说，不正确的表设置通常是DML语句执行慢的原因。可以将表置于自动管理的表空间上来优化Freelist争用（9i以上版本默认）6.3 Lock：Lock通常发生在许多用户正在少量的表上执行DML操作的时候发生。一旦lock申请获得，lock会一直保留给该用户，直到lock的用户发布一条commit或rollback为止。会遇到彻底的暂停，产生巨大的