Oracle优化案例分享.pptx

Oracle优化案例分享;使用反向索引减少索引热点块 索引压缩技术及优缺点总结 位图索引的锁代价 全表扫描比索引扫描效率更高？ 解决UNDO表空间过大故障 使用绑定变量 避免使用 “*” 减少访问表次数 避免索引列计算 IS NULL 和 IS NOT NULL LIKE vs INDEX COMMIT逐条？批量？ SQL 优化实例 ;反向索引概念 将正常的键值头尾调换后再进行存储 反向索引应用场合 索引块成为热点块 RAC环境中 反向索引优缺点 优点：减少索引叶子块的争用 缺点：不适合范围扫描（,,between and）;反向索引创建及维护方法;索引压缩创建演示;索引压缩系数确定方法 实践中反复测试，得出最佳的压缩系数 索引压缩优点 减少索引占用的磁盘空间 数据库缓冲区缓存能存放更多的索引条目 缓存命中率较高 物理I/O较少 ;索引压缩缺点 维护索引更耗时 需要更多的计算 检索索引需要较长的时间 需要更多的CPU处理索引 增加了块竞争 DISK与CPU间的权衡 使用场景 当联合索引前几列存在大量重复数据时使用 ;位图索引被存储为压缩的索引值 不支持行级别的锁定 一个键指向多行（成百上千） “牵一发而动全身” 较之B*Tree索引优点 压缩存放，占用磁盘空间小 较之B*Tree索引缺点 锁代价昂贵 严重影响更新和删除效率;位图索引使用原则 用于决策支持系统 用于数据仓库 绝大部分数据为静态数据 低cardinality列适用（性别等）;考虑下面的场景 思考一下，有何结论？ ;分析： 通过索引获取２００００行数据　　＝ 　约２００００个table access by rowid　 ＝ 　处理２００００个块； 　此表仅占用了１０００个块。 结论： 通过全表扫描方式代价更低，速度更快。 ;重建UNDO表空间方法 1.查询undo表空间大小，及系统可用空间； 2.创建一个新undo表空间； 3.将默认的undo表空间设置调整到UNDO_TBS1； 4.删除原有的undo表空间； 5.最后确认undo表空间的设置情况； 6.使用重新启动数据库的方法释放空间。 ;应使用变量绑定实现SQL语句共享，避免使用硬编码。 SQL语句能够被共享的三个必要条件： SQL写法需完全一样（大小写及空格数量都要一致） SQL引用的的对象必须一样（不同用户下的） 绑定变量名字必须一样 ;SELECT子句中避免使用“*” 原因一：Oracle会将“ * ”依次转换成所有的列名，需要查询Oracle数据字典完成上面的转换，因此效率极其低下 原因二：不利于程序扩展 原则：SQL中明确指定所需的字段 ;功能需求：得到7369和7844两位员工的基本信息 比较一下，哪个效率更高？;WHERE子句中，如果索引列是函数的一部分，则优化器无法使用索引，而会以全表扫描方式来获取数据 例一：不当的算术计算 例二：隐式转换（EMPNO是NUMBER类型） 应将SQL语句中的函数、计算表达式等放置在等号右边 无法避免时可考虑FBI函数索引（Function-Based Index）或物化视图 ;注意：Oracle的索引不记录NULL值 因此，WHERE子句中使用IS NULL和IS NOT NULL不会使用到索引 转换一例;LIKE ‘%***’、LIKE ‘%***%’及NOT LIKE语句无法使用到索引，尽量不使用LIKE语句，不可避免时，可使用like ’ ***%’ ;Oracle的COMMIT会触发Redo Log的物理写 Oracle数据库频繁的COMMIT会严重影响数据库性能 故障：ORA-00257:archiver error.Connect Internal only,untill freed ;批量提交再提速——使用嵌套表(数组)特性;SQL 优化实例--优化前;SQL 优化实例--优化后;同时INSERT多表;关联更新;关联更新与插入并举;重复数据查找与删除