ORACLE培训 -SQL性能优化 内容概述内容概述 课程主要讨论： SQL语句执行的过程、ORACLE优化 器 ，表之间的关联，如何得到SQL执行 计划，如何分析执 行计划等内容，从而由浅到深的方式 了解SQL优化的过 程，使大家逐步掌握SQL优化。 目录目录 1. 优化基础知识 2. 性能调整综述 3. 有效的应用设计 4. SQL语句的处理过程 5. Oracle的优化器 6. Oracle的执行计划 7. 注意事项 一、优化基础知识一、优化基础知识 概述概述 性能管理 性能问题 调整的方法 SQL优化机制 应用的调整 SQL语句的处理过程 共享SQL区域 SQL语句处理的阶段 共享游标 SQL编码标准 Oracle 优化器介绍 SQL Tunning Tips 优化Tools 性能管理性能管理 尽早开始 设立合适目标 边调整边监控 相关人员进行合作 及时处理过程中发生的意外和变化 80/20定律 SQL SQL 优化衡量指标优化衡量指标 随着软件技术的不断发展，系统性能越来越重要。 系统性能主要用：系统响应时间和并发性来衡量。 造成SQL语句性能不佳大致有两个原因： 1. 开发人员只关注查询结果的正确性，忽视查询语句的效率。 2. 开发人员只关注SQL语句本身的效率，对SQL语句执行原理、影响 SQL执行效率的主要因素不清楚。 * 前者可以通过深入学习SQL语法及各种SQL调优技巧进行解决。 SQL调优是一个系统工程，熟悉SQL语法、掌握各种内嵌函数、分 析函数的用法只是编写高效SQL的必要条件。 * 后者从分析SQL语句执行原理入手，指出SQL调优应在优化SQL解 析和优化CBO上。 调优领域调优领域 调优领域： 1. 应用程序级调优： * SQL语句调优 * 管理变化调优 2. 实例级调优 * 内存 * 数据结构 * 实例配置 3. 操作系统交互 * I/O * SWAP * Parameters 本课程内容只讲解讨论应用程序级： Oracle SQL语句调优及管理变化调优 调整的方法调整的方法 1. 调整业务功能 2. 调整数据设计 3. 调整流程设计 4. 调整SQL语句 5. 调整物理结构 6. 调整内存分配 7. 调整I/O 8. 调整内存竞争 9. 调整操作系统 不同调整产生相应性能收益不同调整产生相应性能收益 调整的角色调整的角色 SQLSQL语句优化是提高性能的重要环节语句优化是提高性能的重要环节 开发人员不能只注重功能的实现，不管性能如何 开发人员不能把Oracle当成一个黑盒子，必须了 解其结构、处理SQL和数据的方法 必需遵守既定的开发规范 未经过SQL语句优化的模块不要上线 SQLSQL语句优化的过程语句优化的过程 定位有问题的语句 检查执行计划 检查执行过程中优化器的统计信息 分析相关表的记录数、索引情况 改写SQL语句、使用HINT、调整索引、表分析 有些SQL语句不具备优化的可能，需要优化处理方 式 达到最佳执行计划 什么是好的什么是好的SQLSQL语句？语句？ 尽量简单，模块化 易读、易维护 节省资源 内存 CPU 扫描的数据块要少 少排序 不造成死锁 SQLSQL共享原理共享原理 ORACLE将执行过的SQL语句存放在内存的共享池 (shared buffer pool)中，可以被所有的数据库用户共享。 当你执行一个SQL语句(有时被称为一个游标)时, 如果它和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的 执行路径. 这个功能大大地提高了 SQL的执行性能并节省了内存的使用。 SQLSQL共享原理共享原理 为了不重复解析相同的SQL语句，在第一次解析之后， Oracle将SQL语句存放在内存中。这块位于系统全局区域 SGA(systemglobal area)的共享池(shared buffer poo1)中的 内存可以被所有的数据库用户共享。因此，当你执行一个SQL 语句(有时被称为一个游标)时，如果它和之前执行过的语句完全 相同，Oracle就能很快获得已经被解析的语句以及最好的执行 方案。Oracle的这个功能大大地提高了SQL的执行性能并节省 了内存的使用。 可惜的是，Oracle只对简单的表提供高速缓冲 (cache bufferiIlg)，这个功能并不适用于多表连接查询。数据 库管理员必须在启动参数文件中为这个区域设置合适的参数， 当这个内存区域越大，就可以保留更多的语句，当然被共享的 可能性也就越大了。当向Oracle提交一个SQL语句时，Oracle 会首先在这块内存中查找相同的语句。 SQLSQL共享的三个条件共享的三个条件 当前被执行的语句和共享池中的语句必须完全相同 （包括大小写、空格、换行等） 两个语句所指的对象必须完全相同 （同义词与表是 不同的对象） 两个SQL语句中必须使用相同的名字的绑定变量 (bind variables) 共享共享SQLSQL语句语句 注意： Oracle对两者采取的是一种严格匹配策略，要达成共享。SQL语句必 须完全相同(包括空格、换行等)。能够使用共享的语句必须满足三个 条件： 字符级的比较。 当前被执行的语句和共享池中的语句必须完全相同。 例如： SELECT * FROM ATABLE；和下面每一个SQL语句都不同： SELECT *from ATABLE Select * From Atable； 语句所指对象必须完全相同 即两条SQL语句操作的数据库对象必须同一。 语句中必须使用相同命名的绑定变量。如：第一组的两个SQL语句是相同 的，可以共享；而第二组中两个语句不同，即使在运行时赋予不同的绑定变 量以相同的值： 第一组 select pin，name from people where pin = ：blk1.pin； select pin，name from people where pin =：blk1.pin； 第二组 select pin，name from people where pin =：blk1.ot_jnd； select pin，name from people where pin = ：blk1.ov_jnd； SQLSQL语句的处理过程语句的处理过程 共享SQL区域 Sql 处理过程 SQL PARSE与共享SQL语句 当一个Oracle实例接收一条sql后 1、Create a Cursor 创建游标 2、Parse the Statement 分析语句 3、Describe Results of a Query 描述查询的结果集 4、Define Output of a Query 定义查询的输出数据 5、Bind Any Variables 绑定变量 6、Parallelize the Statement 并行执行语句 7、Run the Statement 运行语句 8、Fetch Rows of a Query 取查询出来的行 9、Close the Cursor 关闭游标 为什么要bind variables? 字符级的比较: SELECT * FROM USER_FILES WHERE USER_NO = 10001234; 与 SELECT * FROM USER_FILES WHERE USER_NO = 10004321; 检查： select name,executions from v$db_object_cache where name like select * from user_files% 什么叫做重编译问题 什么叫做重编译？ 下面这个语句每执行一次就需要在SHARE POOL 硬解析一 次，一百万用户就是一百万次，消耗CPU和内存，如果业务 量大，很可能导致宕库 如果绑定变量，则只需要硬解析一次，重复调用即可 select * from dConMsg where contract_no = 32013484095139 绑定变量解决重编译问题 未使用绑定变量的语句 sprintf(sqlstr, insert into scott.test1 (num1, num2) values (%d,%d),n_var1, n_var2); EXEC SQL EXECUTE IMMEDIATE :sqlstr ; EXEC SQL COMMIT; 使用绑定变量的语句 strcpy(sqlstr, insert into test (num1, num2) values (:v1, :v2); EXEC SQL PREPARE sql_stmt FROM :sqlstr; EXEC SQL EXECUTE sql_stmt USING :n_var1, :n_var2; EXEC SQL COMMIT; 绑定变量的注意事项 注意： 1、不要使用数据库级的变量绑定参数 cursor_sharing来强 制绑定，无论其值为 force 还是similar 2、有些带 0性能优于select count(*)from tab； 尽量少嵌套子查询，这种查询会消耗大量的CPU资源；对于有比较多 or运算的查询，建议分成多个查询，用union all联结起来；多表查询 的查询语句中，选择最有效率的表名顺序。Oracle解析器对表解析从 右到左，所以记录少的表放在右边。 尽量多用commit语句提交事务，可以及时释放资源、解 锁、释放日志空间、减少管理花费；在频繁的、性能要求比较高的 数据操作中，尽量避免远程访问，如数据库链等，访问频繁的表可 以常驻内存：alter tablecache； 在Oracle中动态执行SQL，尽量用execute方式，不用 dbms_sql包。 * SQL Tunning Tips * sql 语句的编写原则和优化 n随着数据库中数据的增加，系统的响应速度就成为目前系 统需要解决的最主要的问题之一。系统优化中一个很重要 的方面就是SQL语句的优化。对于大量数据，劣质SQL语句 和优质SQL语句之间的速度差别可以达到上百倍，对于一 个系统不是简单地能实现其功能就可，而是要写出高质量 的SQL语句，提高系统的可用性。 n在多数情况下，Oracle使用索引来更快地遍历表，优化器 主要根据定义的索引来提高性能。如果在SQL语句的where 子句中写的SQL代码不合理，就会造成优化器删去索引而 使用全表扫描，一般就这种SQL语句就是所谓的劣质SQL语 句。 sql 语句的编写原则和优化 n在编写SQL语句时我们应清楚优化器根 据何种原则来使用索引，这有助于写 出高性能的SQL语句。 nSQL语句的编写原则和SQL语句的优化 ，请跟我一起学习以下几方面： Tunning Tip的各个方面 1.不要让Oracle做得太多； 2.给优化器更明确的命令； 3.减少访问次数； 4.细节上的影响； 1.不要让Oracle做得太多 避免复杂的多表关联 select from user_files uf, df_money_files dm, cw_charge_record cc where uf.user_no = dm.user_no and dm.user_no = cc.user_no and and not exists(sel