ORACLE培训 -SQL性能优化,老方块内部培训班使用,内容概述,课程主要讨论： SQL语句执行的过程、ORACLE优化器 ，表之间的关联，如何得到SQL执行计划，如何分析执 行计划等内容，从而由浅到深的方式了解SQL优化的过 程，使大家逐步掌握SQL优化。,目录,优化基础知识 性能调整综述 有效的应用设计 SQL语句的处理过程 Oracle的优化器 Oracle的执行计划 注意事项,一、优化基础知识,概述,性能管理 性能问题 调整的方法 SQL优化机制 应用的调整 SQL语句的处理过程 共享SQL区域 SQL语句处理的阶段 共享游标 SQL编码标准 Oracle 优化器介绍 SQL Tunning Tips 优化Tools,性能管理,尽早开始 设立合适目标 边调整边监控 相关人员进行合作 及时处理过程中发生的意外和变化 80/20定律,SQL 优化衡量指标,随着软件技术的不断发展，系统性能越来越重要。 系统性能主要用：系统响应时间和并发性来衡量。 造成SQL语句性能不佳大致有两个原因： 开发人员只关注查询结果的正确性，忽视查询语句的效率。 开发人员只关注SQL语句本身的效率，对SQL语句执行原理、影响SQL执行效率的主要因素不清楚。 * 前者可以通过深入学习SQL语法及各种SQL调优技巧进行解决。 SQL调优是一个系统工程，熟悉SQL语法、掌握各种内嵌函数、分 析函数的用法只是编写高效SQL的必要条件。 * 后者从分析SQL语句执行原理入手，指出SQL调优应在优化SQL解 析和优化CBO上。,调优领域,调优领域： 应用程序级调优： * SQL语句调优 * 管理变化调优 2. 实例级调优 * 内存 * 数据结构 * 实例配置 3. 操作系统交互 * I/O * SWAP * Parameters 本课程内容只讲解讨论应用程序级： Oracle SQL语句调优及管理变化调优,调整的方法,调整业务功能 调整数据设计 调整流程设计 调整SQL语句 调整物理结构 调整内存分配 调整I/O 调整内存竞争 调整操作系统,不同调整产生相应性能收益,调整的角色,SQL语句优化是提高性能的重要环节,开发人员不能只注重功能的实现，不管性能如何 开发人员不能把Oracle当成一个黑盒子，必须了解其结构、处理SQL和数据的方法 必需遵守既定的开发规范 未经过SQL语句优化的模块不要上线,SQL语句优化的过程,定位有问题的语句 检查执行计划 检查执行过程中优化器的统计信息 分析相关表的记录数、索引情况 改写SQL语句、使用HINT、调整索引、表分析 有些SQL语句不具备优化的可能，需要优化处理方式 达到最佳执行计划,什么是好的SQL语句？,尽量简单，模块化 易读、易维护 节省资源 内存 CPU 扫描的数据块要少 少排序 不造成死锁,SQL共享原理,ORACLE将执行过的SQL语句存放在内存的共享池(shared buffer pool)中，可以被所有的数据库用户共享。 当你执行一个SQL语句(有时被称为一个游标)时, 如果它和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的 执行路径. 这个功能大大地提高了 SQL的执行性能并节省了内存的使用。,SQL共享原理,为了不重复解析相同的SQL语句，在第一次解析之后， Oracle将SQL语句存放在内存中。这块位于系统全局区域 SGA(systemglobal area)的共享池(shared buffer poo1)中的 内存可以被所有的数据库用户共享。因此，当你执行一个SQL 语句(有时被称为一个游标)时，如果它和之前执行过的语句完全 相同，Oracle就能很快获得已经被解析的语句以及最好的执行 方案。Oracle的这个功能大大地提高了SQL的执行性能并节省 了内存的使用。 可惜的是，Oracle只对简单的表提供高速缓冲 (cache bufferiIlg)，这个功能并不适用于多表连接查询。数据 库管理员必须在启动参数文件中为这个区域设置合适的参数， 当这个内存区域越大，就可以保留更多的语句，当然被共享的 可能性也就越大了。当向Oracle提交一个SQL语句时，Oracle 会首先在这块内存中查找相同的语句。,SQL共享的三个条件,当前被执行的语句和共享池中的语句必须完全相同 （包括大小写、空格、换行等） 两个语句所指的对象必须完全相同 （同义词与表是不同的对象） 两个SQL语句中必须使用相同的名字的绑定变量(bind variables),共享SQL语句,注意： 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；,SQL语句的处理过程,共享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、有些带 的语句绑定变量后可能导致优化器无法正确 使用索引,SQL语句的四个处理阶段,SQL语句的处理过程,解析(PARSE)：,SQL语句的处理过程,在共享池中查找SQL语句 检查语法 检查语义和相关的权限 合并(MERGE)视图定义和子查询 确定执行计划,绑定(BIND)：,SQL语句的处理过程,在语句中查找绑定变量 赋值(或重新赋值),执行(EXECUTE)：,SQL语句的处理过程,应用执行计划 执行必要的I/O和排序操作,提取(FETCH)：,从查询结果中返回记录 必要时进行排序 使用ARRAY FETCH机制,共享游标：好处,减少解析 动态内存调整 提高内存使用率,书写可共享的SQL,绑定变量和共享游标,ORACLE 优化器模式 概述,Oracle的优化器共有3种模式：RULE (基于规则)、COST (基于成本)、CHOOSE(基于选择)。 设置缺省的优化器的方法，是在启动参数文件中针对 OPTIMIZER_ MODE参数的各种声明进行选择，如RULE、COST、 CHOOSE、ALL_ ROWS、FIRST_ ROWS。当然也可以在SQL语 句级别或是会话级别对其进行覆盖。 为了使用基于成本的优化器(CBO，CostBased Optimizer)， 必须经常运行analyze命令，以增加数据库中的对象统计信息 (object statistics)的准确性。 如果数据库的优化器模式设置为基于选择，那么实际的优化 器模式将和是否运行过analyze命令有关。如果数据表已经被 analyze过，优化器模式将自动切换成CBO，反之，数据库将采用 RULE形式的优化器。在缺省情况下，Oracle采用CHOOSE优化 器。为避免那些不必要的全表扫描，必须尽量避免使用 CHOOSE优化器，而直接采用基于规则或者基于成本的优化器。,访问数据表的方式, 全表扫描 全表扫描就是顺序地访问表中每条记录。Oracle采用一次读入多个数据块(database block)的方式优化全表扫描。 通过ROWID访问表 ROWID包含了表中记录的物理位置信息。可以采用基于ROWID的访问方式情况提高访问表的效率。Oracle采用索引实现了数据和存放数据的物理位置(ROWID)之间的联系 通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能的提高。,数据库性能,影响数据库系统性能的要素： 主机CPU,RAM,存储系统; OS参数配置,ORACLE参数配置; 应用方面：数据库设计及SQL编程的质量 一个性能优秀的应用系统需要： 良好的硬件配置；正确合理的数据库及中间件参数配置；合理的数据库设计；良好的sql编程；运行期的性能优化,SQL Tunning 的重点,SQL: insert, update, delete, select; 主要关注的是select 关注的是：如何用最小的硬件资源消耗、最少的响应时间定位数据位置,SQL优化的一般性原则,目标： 减少服务器资源消耗（主要是磁盘IO）； 设计方面： 尽量依赖oracle的优化器，并为其提供条件； 合适的索引，索引的双重效应，列的选择性； 编码方面： 利用索引，避免大表FULL TABLE SCAN； 合理使用临时表； 避免写过于复杂的sql，不一定非要一个sql解决问题； 在不影响业务的前提下减小事