Oracle性能优化.ppt

1、Oracle性能优化,智能研究院 杨赛君,性能优化的几个阶段,需求阶段 设计阶段 开发阶段 测试阶段 维护阶段,性能优化的“人月神话”,性能优化工作开始得越晚，则成本越大，而成效越小,性能优化的“人月神话”,需求篇,性能规划,预计高峰期使用的用户数，以及使用系统的频率，持续连接系统的时间 了解用户的期望请求响应时间（统计表明：人对请求等待的平均上限是7秒，超过7秒则开始不耐烦） 根据实际情况，定义出每个功能的性能指标（如：对单机的实时监控，延时不超过2秒）,设计篇,指导方针,根据性能指标做设计 尽早预见解决方案的性能瓶颈 以最少的投资来支持期望的负载（以简单的方案解决简单的问题，以优雅的方案解

2、决复杂的问题）,采用务实的技术,新技术并不都是好技术 好技术不一定是最适用的技术 慎用Oracle闪回日志 对于大批量的数据库操作，适当地适用存储过程，是一种理性回归,Oracle内存简介,SGA(System Glogal Area) 系统全局区 PGA(Program Glogal Area) 进程全局区,SGA参数设置,oracle10g之前: log_buffer（日志缓冲区）: 建议3M左右 large_pool_size（大缓冲池）: 根据是否MTS做调整 java_pool_size（java池）:根据数据库是否使用java做调整 share_pool_size（共享池）:用于缓

3、存解析过的sql语句，不宜太大或太小，建议在500M以内调整 data_buffer(数据缓冲区)：sga中都剩余部分，尽可能多地分配 Oracle10g: sga_target（初始化内存） Sga_max_size（最大内存）,PGA参数设置,Oracle9i前: SORT_AREA_SIZE：在信息换出到磁盘之前，用于对信息排序的RAM 总量 SORT_AREA_RETAINED_SIZE：排序完成后用于保存已排序数据的内存总量 HASH_AREA_SIZE：服务器进程在内存中存储散列表所用的内存量 Oracle9i: PGA_AGGREGATE_TARGET：这个参数会控制实例为完成数

4、据排序/散列的所有工作区（即排序 区和散列区）总共应分配多少内存。,表优化设计原则,尽量不使用复合主键 常访问的字段顺序应靠前 对于常关联查询的表应设计外键 对于查询消耗大而又不常变更的数据，可以适当做字段冗余,索引设计的几个原则,对索引的设计，必须基于对业务的精通 区别对待OTLP与OLAP 在常用做查询条件，且选择性高的字段上建索引 对新增频繁的表慎加索引 根据不同的特点选择建不同的索引（B*、函数、位图等） 外键必须建索引 组合索引里最常用的字段放到前面 索引与表分别放在不同的表空间 建多少个索引应使用中庸原则，在常查询与常更新中权衡,分而治之,分区的作用：1、规范数据管理；2、提升性能

5、 对特别大的表才采用分区管理 根据实际业务来分区，如要做数据定期备份与迁出，则宜按时间来分区 用于分区的键最好是常用作查询条件的字段，这样能提高查询性能 使用全局索引，慎用局部索引 分区粒度不宜太小,物化视图,对于复杂而高消耗的查询，如果使用频繁，应建成物化视图 物化视图是一种典型的以空间换时间的性能优化方式 对于更新频繁的表慎用物化视图 选择合适的刷新方式,并发处理,在批量操作或对大表操作时可以考虑采用并发，充分利用服务器资源 切勿滥用并发，很容易使系统瘫痪 并发只是在多核机器上才适用 设置合适的参数：db_block_multiblock_read_count,减少IO与网络传输次数,尽量

6、用较少的数据库请求，获取到需要的数据，能一次性取出的不分多次取出 对于频繁操作数据库的批量操作，应采用存储过程，减少不必要的网络传输,死锁与阻塞,对于需要平凡更新的数据，尽量避免放在长事务中，以免导致连锁反应 不是迫不得已，最好不要在Oracle锁机制外再加自己设计的锁 减少事务大小，及时提交事务 尽量避免跨数据库的分布式事务，因为环境的复杂性，很容易导致阻塞 慎用位图索引，更新时容易导致死锁,开发篇,SQL语句执行过程,每种类型的语句都需要如下阶段： 第1步: 创建游标 第2步: 分析语句 第5步: 绑定变量 第7步: 运行语句 第9步: 关闭游标 如果使用了并行功能，还会包含下面这个阶段：

7、 第6步: 并行执行语句 如果是查询语句，则需要以下几个额外的步骤，如图 3所示： 第3步: 描述查询的结果集 第4步: 定义查询的输出数据 第8步: 取查询出来的行,系统优化设计思路,采用务实的技术，不盲从技术 做出切合系统实际的参数配置 减少SQL解析次数，使用绑定变量 减少IO次数 减少网络传输 减少资源冲突，避免长时间锁 分而治之 以空间换时间 中庸原则，不走极端,SQL执行计划,了解sql执行计划是编写出良好性能sql语句的必修课 从sql执行计划可看到如何使用的索引，如何访问表，每一步所消耗的成本等有价值的信息 详细使用及阅读方法请参阅相关资料,编写SQL语句常用技巧,使用绑定变量

8、 尽量用索引列做查询 关联或嵌套查询时，使用返回数据集少的表作为驱动表 避免不必要的排序 使用表的别名 对于返回数据集大的嵌套查询，使用EXISTS代替IN 根据实际情况选择UNION或UNION ALL 尽量不取多余的字段，如所需的字段全在索引列里，则可避免回表查询 对于需快速取出少量数据，且返回数据集很少的关联查询，可以使用嵌套查询，否则慎用嵌套查询 对于特殊的情况，可以考虑使用hint，一般情况下忌用，避免表结构变化导致不可预见的结果,对全表扫描的认识,尽量避免对大表做全表扫描 当查询选择率很高时，全表扫描的效率可能高于索引扫描 设置适当的参数：db_block_multiblock_read_count，增加每次读取的块数 可以适当使用并发查询,测试及维护篇,性能压力测试,依照需求