ORACLE 培训 --SQL 性能优化

1. ORACLE培训--SQL性能优化 老方块内部培训班使用

2. 内容概述 课程主要讨论： SQL语句执行的过程、ORACLE优化器 ，表之间的关联，如何得到SQL执行计划，如何分析执 行计划等内容，从而由浅到深的方式了解SQL优化的过 程，使大家逐步掌握SQL优化。

3. 目录 • 优化基础知识 • 性能调整综述 • 有效的应用设计 • SQL语句的处理过程 • Oracle的优化器 • Oracle的执行计划 • 注意事项

4. 一、优化基础知识

5. 概述 • 性能管理 • 性能问题 • 调整的方法 • SQL优化机制 • 应用的调整 • SQL语句的处理过程 • 共享SQL区域 • SQL语句处理的阶段 • 共享游标 • SQL编码标准 • Oracle 优化器介绍 • SQL Tunning Tips • 优化Tools

6. 性能管理 • 尽早开始 • 设立合适目标 • 边调整边监控 • 相关人员进行合作 • 及时处理过程中发生的意外和变化 • 80/20定律

7. SQL 优化衡量指标 • 随着软件技术的不断发展，系统性能越来越重要。 • 系统性能主要用：系统响应时间和并发性来衡量。 • 造成SQL语句性能不佳大致有两个原因： • 开发人员只关注查询结果的正确性，忽视查询语句的效率。 • 开发人员只关注SQL语句本身的效率，对SQL语句执行原理、影响SQL执行效率的主要因素不清楚。 • * 前者可以通过深入学习SQL语法及各种SQL调优技巧进行解决。 • SQL调优是一个系统工程，熟悉SQL语法、掌握各种内嵌函数、分 • 析函数的用法只是编写高效SQL的必要条件。 • * 后者从分析SQL语句执行原理入手，指出SQL调优应在优化SQL解 • 析和优化CBO上。

8. 调优领域 • 调优领域： • 应用程序级调优： • * SQL语句调优 • * 管理变化调优 • 2. 实例级调优 • * 内存 • * 数据结构 • * 实例配置 • 3. 操作系统交互 • * I/O • * SWAP • * Parameters • 本课程内容只讲解讨论应用程序级： • Oracle SQL语句调优及管理变化调优

9. 调整的方法 • 调整业务功能 • 调整数据设计 • 调整流程设计 • 调整SQL语句 • 调整物理结构 • 调整内存分配 • 调整I/O • 调整内存竞争 • 调整操作系统

10. 不同调整产生相应性能收益

11. 调整的角色

12. SQL语句优化是提高性能的重要环节 • 开发人员不能只注重功能的实现，不管性能如何 • 开发人员不能把Oracle当成一个黑盒子，必须了解其结构、处理SQL和数据的方法 • 必需遵守既定的开发规范 • 未经过SQL语句优化的模块不要上线

13. SQL语句优化的过程 • 定位有问题的语句 • 检查执行计划 • 检查执行过程中优化器的统计信息 • 分析相关表的记录数、索引情况 • 改写SQL语句、使用HINT、调整索引、表分析 • 有些SQL语句不具备优化的可能，需要优化处理方式 • 达到最佳执行计划

14. 什么是好的SQL语句？ • 尽量简单，模块化 • 易读、易维护 • 节省资源 • 内存 • CPU • 扫描的数据块要少 • 少排序 • 不造成死锁

15. SQL共享原理 ORACLE将执行过的SQL语句存放在内存的共享池(shared buffer pool)中，可以被所有的数据库用户共享。 当你执行一个SQL语句(有时被称为一个游标)时, 如果它和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的 执行路径. 这个功能大大地提高了 SQL的执行性能并节省了内存的使用。

16. SQL共享原理 为了不重复解析相同的SQL语句，在第一次解析之后， Oracle将SQL语句存放在内存中。这块位于系统全局区域 SGA(systemglobal area)的共享池(shared buffer poo1)中的 内存可以被所有的数据库用户共享。因此，当你执行一个SQL 语句(有时被称为一个游标)时，如果它和之前执行过的语句完全 相同，Oracle就能很快获得已经被解析的语句以及最好的执行 方案。Oracle的这个功能大大地提高了SQL的执行性能并节省 了内存的使用。 可惜的是，Oracle只对简单的表提供高速缓冲 (cache bufferiIlg)，这个功能并不适用于多表连接查询。数据 库管理员必须在启动参数文件中为这个区域设置合适的参数， 当这个内存区域越大，就可以保留更多的语句，当然被共享的 可能性也就越大了。当向Oracle提交一个SQL语句时，Oracle 会首先在这块内存中查找相同的语句。

17. SQL共享的三个条件 当前被执行的语句和共享池中的语句必须完全相同 （包括大小写、空格、换行等） 两个语句所指的对象必须完全相同 （同义词与表是不同的对象） 两个SQL语句中必须使用相同的名字的绑定变量(bind variables)

18. 共享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；

19. SQL语句的处理过程 共享SQL区域

20. Sql 处理过程

21. 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 关闭游标

22. 为什么要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%'

23. 什么叫做重编译问题 什么叫做重编译？ 下面这个语句每执行一次就需要在SHARE POOL 硬解析一 次，一百万用户就是一百万次，消耗CPU和内存，如果业务 量大，很可能导致宕库…… 如果绑定变量，则只需要硬解析一次，重复调用即可 select * from dConMsg where contract_no = 32013484095139

24. 绑定变量解决重编译问题 未使用绑定变量的语句 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;

25. 绑定变量的注意事项 注意： 1、不要使用数据库级的变量绑定参数cursor_sharing来强 制绑定，无论其值为 force 还是similar 2、有些带> < 的语句绑定变量后可能导致优化器无法正确 使用索引

26. SQL语句的处理过程 SQL语句的四个处理阶段

27. SQL语句的处理过程 解析(PARSE)： • 在共享池中查找SQL语句 • 检查语法 • 检查语义和相关的权限 • 合并(MERGE)视图定义和子查询 • 确定执行计划

28. SQL语句的处理过程 绑定(BIND)： • 在语句中查找绑定变量 • 赋值(或重新赋值)

29. SQL语句的处理过程 执行(EXECUTE)： • 应用执行计划 • 执行必要的I/O和排序操作 提取(FETCH)： • 从查询结果中返回记录 • 必要时进行排序 • 使用ARRAY FETCH机制

30. 共享游标：好处 • 减少解析 • 动态内存调整 • 提高内存使用率

31. 书写可共享的SQL

32. 绑定变量和共享游标

33. ORACLE 优化器模式 概述 Oracle的优化器共有3种模式：RULE (基于规则)、COST (基于成本)、CHOOSE(基于选择)。 设置缺省的优化器的方法，是在启动参数文件中针对 OPTIMIZER_ MODE参数的各种声明进行选择，如RULE、COST、 CHOOSE、ALL_ ROWS、FIRST_ ROWS。当然也可以在SQL语 句级别或是会话级别对其进行覆盖。 为了使用基于成本的优化器(CBO，Cost—Based Optimizer)， 必须经常运行analyze命令，以增加数据库中的对象统计信息 (object statistics)的准确性。 如果数据库的优化器模式设置为基于选择，那么实际的优化 器模式将和是否运行过analyze命令有关。如果数据表已经被 analyze过，优化器模式将自动切换成CBO，反之，数据库将采用 RULE形式的优化器。在缺省情况下，Oracle采用CHOOSE优化 器。为避免那些不必要的全表扫描，必须尽量避免使用 CHOOSE优化器，而直接采用基于规则或者基于成本的优化器。

34. 访问数据表的方式 ①全表扫描 全表扫描就是顺序地访问表中每条记录。Oracle采用一次读入多个数据块(database block)的方式优化全表扫描。 ②通过ROWID访问表 ROWID包含了表中记录的物理位置信息。可以采用基于ROWID的访问方式情况提高访问表的效率。Oracle采用索引实现了数据和存放数据的物理位置(ROWID)之间的联系 通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能的提高。

35. 数据库性能 • 影响数据库系统性能的要素： • 主机CPU,RAM,存储系统; • OS参数配置,ORACLE参数配置; • 应用方面：数据库设计及SQL编程的质量 • 一个性能优秀的应用系统需要： • 良好的硬件配置；正确合理的数据库及中间件参数配置；合理的数据库设计；良好的sql编程；运行期的性能优化

36. SQL Tunning 的重点 • SQL: insert, update, delete, select; • 主要关注的是select • 关注的是：如何用最小的硬件资源消耗、最少的响应时间定位数据位置

37. SQL优化的一般性原则 • 目标： • 减少服务器资源消耗（主要是磁盘IO）； • 设计方面： • 尽量依赖oracle的优化器，并为其提供条件； • 合适的索引，索引的双重效应，列的选择性； • 编码方面： • 利用索引，避免大表FULL TABLE SCAN； • 合理使用临时表； • 避免写过于复杂的sql，不一定非要一个sql解决问题； • 在不影响业务的前提下减小事务的粒度；

38. 优化概括 课程Oracle数据库SQL语句优化的总体策略。以这些 优化策略为指导，通过经验总结，我们可以不断地丰富优 化方案，进而指导我们进行应用系统的数据库性能优化。 以下枚举几则被证明行之有效的优化方案： ● 创建表的时候。应尽量建立主键，尽量根据实际需要调整数据表的PCTFREE和PCTUSED参数；大数据表删除，用truncate table代替delete。 ● 合理使用索引，在OLTP应用中一张表的索引不要太多。数据重复量大的列不要建立二叉树索引，可以采用位图索引；组合索引的列顺序尽量与查询条件列顺序保持一致；对于数据操作频繁的表，索引需要定期重建，以减少失效的索引和碎片。

39. 优化概括 ● 查询尽量用确定的列名，少用*号。select count(key)from tab where key> 0性能优于select count(*)from tab； 尽量少嵌套子查询，这种查询会消耗大量的CPU资源；对于有比较多 or运算的查询，建议分成多个查询，用union all联结起来；多表查询 的查询语句中，选择最有效率的表名顺序。Oracle解析器对表解析从 右到左，所以记录少的表放在右边。 ● 尽量多用commit语句提交事务，可以及时释放资源、解 锁、释放日志空间、减少管理花费；在频繁的、性能要求比较高的 数据操作中，尽量避免远程访问，如数据库链等，访问频繁的表可 以常驻内存：alter table．．．cache； ● 在Oracle中动态执行SQL，尽量用execute方式，不用 dbms_sql包。

40. ** SQL Tunning Tips **

41. sql 语句的编写原则和优化 • 随着数据库中数据的增加，系统的响应速度就成为目前系统需要解决的最主要的问题之一。系统优化中一个很重要的方面就是SQL语句的优化。对于大量数据，劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍，对于一个系统不是简单地能实现其功能就可，而是要写出高质量的SQL语句，提高系统的可用性。 • 在多数情况下，Oracle使用索引来更快地遍历表，优化器主要根据定义的索引来提高性能。如果在SQL语句的where子句中写的SQL代码不合理，就会造成优化器删去索引而使用全表扫描，一般就这种SQL语句就是所谓的劣质SQL语句。

42. sql 语句的编写原则和优化 • 在编写SQL语句时我们应清楚优化器根据何种原则来使用索引，这有助于写出高性能的SQL语句。 • SQL语句的编写原则和SQL语句的优化，请跟我一起学习以下几方面：

43. Tunning Tip的各个方面 1.不要让Oracle做得太多； 2.给优化器更明确的命令； 3.减少访问次数； 4.细节上的影响；

44. 1.不要让Oracle做得太多

45. 避免复杂的多表关联 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(select …) ??? 很难优化，随着数据量的增加性能的风险很大。

46. 避免使用 ‘ * ‘ 当你想在SELECT子句中列出所有的COLUMN时,使用动态 SQL列引用 ‘*’ 是一个方便的方法.不幸的是,这是一个非常低 效的方法. 实际上,ORACLE在解析的过程中, 会将’*’ 依次转 换成所有的列名, 这个工作是通过查询数据字典完成的, 这意 味着将耗费更多的时间； 只提取你所要使用的列； 使用别名能够加快解析速度；

47. 避免使用耗费资源的操作 带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的 SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次 排序. 例如,一个UNION查询,其中每个查询都带有GROUP BY子句 , GROUP BY会触发嵌入排序(NESTED SORT) ; 这样, 每个 查询需要执行一次排序, 然后在执行UNION时, 又一个唯一 排序(SORT UNIQUE)操作被执行而且它只能在前面的嵌入 排序结束后才能开始执行. 嵌入的排序的深度会大大影响查 询的效率. 通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以 用其他方式重写.

48. 用EXISTS替换DISTINCT 例如: 低效: SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO 高效: SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X’ FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);

49. 用UNION-ALL 替换UNION ( if possible) 当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以 UNION-ALL的方式被合并, 然后在输出最终结果前进行排序. 举例: 低效： SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’ UNION SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’ 高效: SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’ UNION ALL SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’

50. 2. 给优化器更明确的命令