SQL优化汇总ORACLE.docVIP

SQL性能优化汇总 作者: eric.zhu 创建日期: October 10, 2012 最后更新日期: October 10, 2012 参考: 版本: 1.0.0 文档控制 变更记录 3 日期 作者 版本 变更说明 2012-06-29 liang.zhu 1.0 初版 审阅 姓名 职位 分发 拷贝编号 姓名 位置/岗位 1 2 3 4 目录 文档控制 2 SQL优化概述 5 SQL 优化概述 5 SQL共享原理 5 SQL优化诊断 6 Oracle SQL优化诊断 6 执行计划 6 SQL优化技巧 8 SQL优化技巧 8 绑定变量 8 自动绑定器 14 索引 15 提交事物处理 16 锁表 16 已解决及待解决问题 i 待解决问题 i 已解决问题 i SQL优化概述 SQL 优化概述 Oracle优化性能因素 SQL共享原理 设置跟踪文件 Oracle优化性能因素 Oracle优化性能因素 硬件的环境 cpu,内存,网络传输条件等均会影响到oracle的性能。 数据库运行的配置参数 这可能是由于糟糕的建议或是错误的假设造成的。大多数项目都可以在基本参数下实现可接受的性能。特别是，SPIN_COUNT等你和闩相关的参数，以及没有在文档中提及的优化特性可能会造成严重的问题，这需要相当大的调查。 同样，在初始化参数文件中设置的优化参数可能覆盖了经过验证的优化过的执行计划。因此，模式，模式统计和优化器设置应该一起考虑和管理。 这在大部分时间已经由DBA设置好了。 对于不合理的表结构设计 对于不合理的表结构对性能的影响,当前有一张表,存储了用户手机号码,在统计时需要对用户的手机号码进行分类统计,而另一张表中存储了与手机号码前四或前七位匹配的地区名称,这时间程序员写了一条sql语句为: select * from userPhone,Userarea where (left(userPhone.phone,4) == userarea.id or left(userPhone,7)==userarea.id);该语句在前期userPhone表数据量不多时,没有感觉到性能方面的影响,但当userPhone达到1万条时,性能迅速降低,经过分析后, 确定是由于表结构不太合理,于是在userPhone中增加一个userarea的id列,sql语句修改为 select * from userPhone,Userarea where userPhone.areaid ==userarea.id),程序运行性能恢复正常. 烂SQL 这很大一部分由我们开发原因没有注意到自己写的Sql执行效率的问题导致，可能导致的原因很多，这正是我写着文档所要达到的目标。 SQL共享原理 共享原理 ORACLE将执行过的SQL语句存放在内存的共享池(shared buffer pool)中，可以被所有的数据库用户共享。 当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同,ORACLE就能很快获得已经被解析的语句以及最好的 执行路径. 这个功能大大地提高了 SQL的执行性能并节省了内存的使用。为了不重复解析相同的SQL语句，在第一次解析之后，Oracle将SQL语句存放在内存中。这块位于系统全局区域SGA(systemglobal area)的共享池(shared buffer poo1)中的内存可以被所有的数据库用户共享。因此，当你执行一个SQL语句(有时被称为一个游标)时，如果它和之前执行过的语句完全相同，Oracle就能很快获得已经被解析的语句以及最好的执行方案。Oracle的这个功能大大地提高了SQL的执行性能并节省了内存的使用。 可惜的是，Oracle只对简单的表提供高速缓冲(cache bufferiIlg)，这个功能并不适用于多表连接查询。数据库管理员必须在启动参数文件中为这个区域设置合适的参数，当这个内存区域越大，就可以保留更多的语句，当然被共享的可能性也就越大了。当向Oracle提交一个SQL语句时，Oracle会首先在这块内存中查找相同的语句。 SQL优化诊断 Oracle SQL优化诊断 执行计划 设置跟踪文件 TRC文件查看 执行计划 执行计划 我们对 SQL 语句进行调优时，非常重要的一个方法就是分析 SQL 的执行计划。查看 Oracle 中 SQL 语句的执行计划有很多方法，例如通过 PL/SQL Developer 、 EM 或者 SQLPLUS 工具等。在网上搜索了一些比较重要的知识点，以备忘。 Rowid的概念：rowid是一个伪列，既然是伪列，那么这个列就不是用户定义，而是系