oraclesql性能优化(全)

《oraclesql性能优化(全)》由会员分享，可在线阅读，更多相关《oraclesql性能优化(全)（207页珍藏版）》请在金锄头文库上搜索。

1、ORACLE培训 -SQL性能优化,老方块内部培训班使用,内容概述,课程主要讨论： SQL语句执行的过程、ORACLE优化器 ，表之间的关联，如何得到SQL执行计划，如何分析执 行计划等内容，从而由浅到深的方式了解SQL优化的过 程，使大家逐步掌握SQL优化。,目录,优化基础知识 性能调整综述 有效的应用设计 SQL语句的处理过程 Oracle的优化器 Oracle的执行计划 注意事项,一、优化基础知识,概述,性能管理 性能问题 调整的方法 SQL优化机制 应用的调整 SQL语句的处理过程 共享SQL区域 SQL语句处理的阶段 共享游标 SQL编码标准 Oracle 优化器介绍 SQL Tun

2、ning Tips 优化Tools,性能管理,尽早开始 设立合适目标 边调整边监控 相关人员进行合作 及时处理过程中发生的意外和变化 80/20定律,SQL 优化衡量指标,随着软件技术的不断发展，系统性能越来越重要。 系统性能主要用：系统响应时间和并发性来衡量。 造成SQL语句性能不佳大致有两个原因： 开发人员只关注查询结果的正确性，忽视查询语句的效率。 开发人员只关注SQL语句本身的效率，对SQL语句执行原理、影响SQL执行效率的主要因素不清楚。 * 前者可以通过深入学习SQL语法及各种SQL调优技巧进行解决。 SQL调优是一个系统工程，熟悉SQL语法、掌握各种内嵌函数、分 析函数的用法只是

3、编写高效SQL的必要条件。 * 后者从分析SQL语句执行原理入手，指出SQL调优应在优化SQL解 析和优化CBO上。,调优领域,调优领域： 应用程序级调优： * SQL语句调优 * 管理变化调优 2. 实例级调优 * 内存 * 数据结构 * 实例配置 3. 操作系统交互 * I/O * SWAP * Parameters 本课程内容只讲解讨论应用程序级： Oracle SQL语句调优及管理变化调优,调整的方法,调整业务功能 调整数据设计 调整流程设计 调整SQL语句 调整物理结构 调整内存分配 调整I/O 调整内存竞争 调整操作系统,不同调整产生相应性能收益,调整的角色,SQL语句优化是提高性

4、能的重要环节,开发人员不能只注重功能的实现，不管性能如何 开发人员不能把Oracle当成一个黑盒子，必须了解其结构、处理SQL和数据的方法 必需遵守既定的开发规范 未经过SQL语句优化的模块不要上线,SQL语句优化的过程,定位有问题的语句 检查执行计划 检查执行过程中优化器的统计信息 分析相关表的记录数、索引情况 改写SQL语句、使用HINT、调整索引、表分析 有些SQL语句不具备优化的可能，需要优化处理方式 达到最佳执行计划,什么是好的SQL语句？,尽量简单，模块化 易读、易维护 节省资源 内存 CPU 扫描的数据块要少 少排序 不造成死锁,SQL共享原理,ORACLE将执行过的SQL语句存

5、放在内存的共享池(shared buffer pool)中，可以被所有的数据库用户共享。 当你执行一个SQL语句(有时被称为一个游标)时, 如果它和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的 执行路径. 这个功能大大地提高了 SQL的执行性能并节省了内存的使用。,SQL共享原理,为了不重复解析相同的SQL语句，在第一次解析之后， Oracle将SQL语句存放在内存中。这块位于系统全局区域 SGA(systemglobal area)的共享池(shared buffer poo1)中的 内存可以被所有的数据库用户共享。因此，当你执行一个SQL 语句(有时被称

6、为一个游标)时，如果它和之前执行过的语句完全 相同，Oracle就能很快获得已经被解析的语句以及最好的执行 方案。Oracle的这个功能大大地提高了SQL的执行性能并节省 了内存的使用。 可惜的是，Oracle只对简单的表提供高速缓冲 (cache bufferiIlg)，这个功能并不适用于多表连接查询。数据 库管理员必须在启动参数文件中为这个区域设置合适的参数， 当这个内存区域越大，就可以保留更多的语句，当然被共享的 可能性也就越大了。当向Oracle提交一个SQL语句时，Oracle 会首先在这块内存中查找相同的语句。,SQL共享的三个条件,当前被执行的语句和共享池中的语句必须完全相同 （

7、包括大小写、空格、换行等） 两个语句所指的对象必须完全相同 （同义词与表是不同的对象） 两个SQL语句中必须使用相同的名字的绑定变量(bind variables),共享SQL语句,注意： Oracle对两者采取的是一种严格匹配策略，要达成共享。SQL语句必 须完全相同(包括空格、换行等)。能够使用共享的语句必须满足三个 条件： 字符级的比较。 当前被执行的语句和共享池中的语句必须完全相同。 例如： SELECT * FROM ATABLE；和下面每一个SQL语句都不同： SELECT *from ATABLE Select * From Atable； 语句所指对象必须完全相同 即两条SQL

8、语句操作的数据库对象必须同一。 语句中必须使用相同命名的绑定变量。如：第一组的两个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.

9、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 o

10、f 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%,什么叫做重编译问题,什么叫做重编译？ 下面这个语句每执行一次就需要在

11、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;

12、使用绑定变量的语句 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语句的四个处理阶

13、段,SQL语句的处理过程,解析(PARSE)：,SQL语句的处理过程,在共享池中查找SQL语句 检查语法 检查语义和相关的权限 合并(MERGE)视图定义和子查询 确定执行计划,绑定(BIND)：,SQL语句的处理过程,在语句中查找绑定变量 赋值(或重新赋值),执行(EXECUTE)：,SQL语句的处理过程,应用执行计划 执行必要的I/O和排序操作,提取(FETCH)：,从查询结果中返回记录 必要时进行排序 使用ARRAY FETCH机制,共享游标：好处,减少解析 动态内存调整 提高内存使用率,书写可共享的SQL,绑定变量和共享游标,ORACLE 优化器模式 概述,Oracle的优化器共有3种

14、模式：RULE (基于规则)、COST (基于成本)、CHOOSE(基于选择)。 设置缺省的优化器的方法，是在启动参数文件中针对 OPTIMIZER_ MODE参数的各种声明进行选择，如RULE、COST、 CHOOSE、ALL_ ROWS、FIRST_ ROWS。当然也可以在SQL语 句级别或是会话级别对其进行覆盖。 为了使用基于成本的优化器(CBO，CostBased Optimizer)， 必须经常运行analyze命令，以增加数据库中的对象统计信息 (object statistics)的准确性。 如果数据库的优化器模式设置为基于选择，那么实际的优化 器模式将和是否运行过analyze

15、命令有关。如果数据表已经被 analyze过，优化器模式将自动切换成CBO，反之，数据库将采用 RULE形式的优化器。在缺省情况下，Oracle采用CHOOSE优化 器。为避免那些不必要的全表扫描，必须尽量避免使用 CHOOSE优化器，而直接采用基于规则或者基于成本的优化器。,访问数据表的方式, 全表扫描 全表扫描就是顺序地访问表中每条记录。Oracle采用一次读入多个数据块(database block)的方式优化全表扫描。 通过ROWID访问表 ROWID包含了表中记录的物理位置信息。可以采用基于ROWID的访问方式情况提高访问表的效率。Oracle采用索引实现了数据和存放数据的物理位置(

16、ROWID)之间的联系 通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能的提高。,数据库性能,影响数据库系统性能的要素： 主机CPU,RAM,存储系统; OS参数配置,ORACLE参数配置; 应用方面：数据库设计及SQL编程的质量 一个性能优秀的应用系统需要： 良好的硬件配置；正确合理的数据库及中间件参数配置；合理的数据库设计；良好的sql编程；运行期的性能优化,SQL Tunning 的重点,SQL: insert, update, delete, select; 主要关注的是select 关注的是：如何用最小的硬件资源消耗、最少的响应时间定位数据位置,SQL优化的一般性原则,目标： 减少服务器资源消耗（主要是磁盘IO）； 设计方面： 尽量依赖oracle的优化器，并为其提供条件； 合适的索引，索引的双重效应，列的选择性； 编码方面： 利用索引，避免大表FULL TABLE SCAN； 合理使用临时表； 避免写过于复杂的sql，不一定非要一个sql解决问题； 在不影响业务的前提下减小事