• 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. 解析(PARSE):SQL语句的处理过程在共享池中查找SQL语句 检查语法 检查语义和相关的权限 合并(MERGE)视图定义和子查询 确定执行计划
  • 28. 绑定(BIND):SQL语句的处理过程 在语句中查找绑定变量 赋值(或重新赋值)
  • 29. 执行(EXECUTE):SQL语句的处理过程 应用执行计划 执行必要的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. 例如: 低效: 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);用EXISTS替换DISTINCT
  • 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. 给优化器更明确的命令
  • 51. 自动选择索引如果表中有两个以上(包括两个)索引,其中有一个唯一性 索引,而其他是非唯一性. 在这种情况下,ORACLE将使用唯一性索引而完全忽略非唯 一性索引. 举例: SELECT ENAME FROM EMP WHERE EMPNO = 2326 AND DEPTNO = 20 ; 这里,只有EMPNO上的索引是唯一性的,所以EMPNO索 引将用来检索记录. TABLE ACCESS BY ROWID ON EMP INDEX UNIQUE SCAN ON EMP_NO_IDX
  • 52. 至少要包含组合索引的第一列如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引. SQL> create table multiindexusage ( inda number , indb number , descr varchar2(10)); Table created. SQL> create index multindex on multiindexusage(inda,indb); Index created. SQL> set autotrace traceonly SQL> select * from multiindexusage where inda = 1; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MULTIINDEXUSAGE' 2 1 INDEX (RANGE SCAN) OF 'MULTINDEX' (NON-UNIQUE) SQL> select * from multiindexusage where indb = 1; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'MULTIINDEXUSAGE' 很明显, 当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引
  • 53. 避免在索引列上使用函数WHERE子句中,如果索引列是函数的一部分.优化器将不 使用索引而使用全表扫描. 举例: 低效: SELECT … FROM DEPT WHERE SAL * 12 > 25000; 高效: SELECT … FROM DEPT WHERE SAL > 25000/12;
  • 54. 避免使用前置通配符WHERE子句中, 如果索引列所对应的值的第一个字符由通 配符(WILDCARD)开始, 索引将不被采用. SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES WHERE USER_NO LIKE '%109204421'; 在这种情况下,ORACLE将使用全表扫描.
  • 55. 避免在索引列上使用NOT通常,我们要避免在索引列上使用NOT, NOT会产生在和在 索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他就 会停止使用索引转而执行全表扫描. 举例: 低效: (这里,不使用索引) SELECT … FROM DEPT WHERE DEPT_CODE NOT = 0; 高效: (这里,使用了索引) SELECT … FROM DEPT WHERE DEPT_CODE > 0;
  • 56. 避免在索引列上使用 IS NULL和IS NOT NULL避免在索引中使用任何可以为空的列,ORACLE将无法使用该 索引 .对于单列索引,如果列包含空值,索引中将不存在此记 录. 对于复合索引,如果每个列都为空,索引中同样不存在此 记录. 如果至少有一个列不为空,则记录存在于索引中. 如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记 录的A,B值为(123,null) , ORACLE将不接受下一条具有相同 A,B值(123,null)的记录(插入). 然而如果所有的索引列都为 空,ORACLE将认为整个键值为空而空不等于空. 因此你可以 插入1000条具有相同键值的记录,当然它们都是空! 因为空值不存在于索引列中,所以WHERE子句中对索引列进行 空值比较将使ORACLE停用该索引. 任何在where子句中使用is null或is not null的语句优化器是 不允许使用索引的。
  • 57. 避免出现索引列自动转换当比较不同数据类型的数据时, ORACLE自动对列进行简单 的类型转换. 假设EMP_TYPE是一个字符类型的索引列. SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES WHERE USER_NO = 109204421 这个语句被ORACLE转换为: SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES WHERE TO_NUMBER(USER_NO) = 109204421 因为内部发生的类型转换, 这个索引将不会被用到!
  • 58. 在查询时尽量少用格式转换如用 WHERE a.order_no = b.order_no 不用 WHERE TO_NUMBER (substr(a.order_no, instr(b.order_no, '.') - 1) = TO_NUMBER (substr(a.order_no, instr(b.order_no, '.') - 1)
  • 59. 3.减少访问次数
  • 60. 减少访问数据库的次数当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少 ORACLE的工作量. 类比,工程实施
  • 61. 使用DECODE来减少处理时间例如: SELECT COUNT(*),SUM(SAL) FROM EMP WHERE DEPT_NO = 0020 AND ENAME LIKE ‘SMITH%’; SELECT COUNT(*),SUM(SAL) FROM EMP WHERE DEPT_NO = 0030 AND ENAME LIKE ‘SMITH%’; 你可以用DECODE函数高效地得到相同结果 SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT, COUNT(DECODE(DEPT_NO,0030,’X’,NULL)) D0030_COUNT, SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL, SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL FROM EMP WHERE ENAME LIKE ‘SMITH%’;
  • 62. 减少对表的查询在含有子查询的SQL语句中,要特别注意减少对表的查询. 例如: 低效 SELECT TAB_NAME FROM TABLES WHERE TAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604) AND DB_VER= ( SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604) 高效 SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER) FROM TAB_COLUMNS WHERE VERSION = 604)
  • 63. 4. 细节上的影响
  • 64. WHERE子句中的连接顺序ORACLE采用自下而上的顺序解析WHERE子句,根据这个原 理, 当在WHERE子句中有多个表联接时,WHERE子句中排 在最后的表应当是返回行数可能最少的表,有过滤条件的子 句应放在WHERE子句中的最后。 如:设从emp表查到的数据比较少或该表的过滤条件比较确定,能大大缩小查询范围,则将最具有选择性部分放在WHERE子句中的最后: select * from emp e,dept d where d.deptno >10 and e.deptno =30 ; 如果dept表返回的记录数较多的话,上面的查询语句会比下面的查询语句响应快得多。 select * from emp e,dept d where e.deptno =30 and d.deptno >10 ;
  • 65. WHERE子句 ——函数、表达式使用最好不要在WHERE子句中使用函或表达式,如果要使用的话,最好统一使用相同的表达式或函数,这样便于以后使用合理的索引。
  • 66. Order by语句 ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制,也可以将函数加入列中(象联接或者附加等)。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。 仔细检查order by语句以找出非索引项或者表达式,它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引,也可以为所使用的列建立另外一个索引,同时应绝对避免在order by子句中使用表达式。
  • 67. 联接列 对于有联接的列,即使最后的联接值为一个静态值,优化器 是不会使用索引的。 select * from employss   where   first_name||''||last_name ='Beill Cliton'; 系统优化器对基于last_name创建的索引没有使用。 当采用下面这种SQL语句的编写,Oracle系统就可以采用基 于last_name创建的索引。   select * from employee    where first_name ='Beill' and last_name ='Cliton';
  • 68. 带通配符(%)的like语句 通配符(%)在搜寻词首出现,Oracle系统不使用 last_name的索引。 select * from employee where last_name like '%cliton%'; 在很多情况下可能无法避免这种情况,但是一定要心中有底 ,通配符如此使用会降低查询速度。然而当通配符出现在字 符串其他位置时,优化器就能利用索引。在下面的查询中索 引得到了使用: select * from employee where last_name like 'c%';
  • 69. 用Where子句替换HAVING子句避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果 集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限 制记录的数目,那就能减少这方面的开销. 例如: 低效: SELECT REGION,AVG(LOG_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION REGION != ‘SYDNEY’ AND REGION != ‘PERTH’ 高效 SELECT REGION,AVG(LOG_SIZE) FROM LOCATION WHERE REGION REGION != ‘SYDNEY’ AND REGION != ‘PERTH’ GROUP BY REGION 顺序 WHERE > GROUP > HAVING
  • 70. 用NOT EXISTS 替代 NOT IN在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况 下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 使用NOT EXISTS 子句可以有效地利用索引。尽可能使用NOT EXISTS 来代替NOT IN,尽管二者都使用了NOT(不能使用索引而降低速度), NOT EXISTS要比NOT IN查询效率更高。 例如: 语句1 SELECT dname, deptno FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp); 语句2 SELECT dname, deptno FROM dept WHERE NOT EXISTS (SELECT deptno FROM emp WHERE dept.deptno = emp.deptno); 2要比1的执行性能好很多。 因为1中对emp进行了full table scan,这是很浪费时间的操作。而且1中没有用到emp的index, 因为没有where子句。而2中的语句对emp进行的是缩小范围的查询。
  • 71. 用索引提高效率索引是表的一个概念部分,用来提高检索数据的效率,ORACLE使 用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表 扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以 提高效率. 另一个使用索引的好处是,它提供了主键(primary key) 的唯一性验证。 通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小 表时,使用索引同样能提高效率. 虽然使用索引能得到查询效率的提 高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要 定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也 会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为 此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理, 那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引 是有必要的。
  • 72. 避免在索引列上使用计算WHERE子句中,如果索引列是函数的一部分.优化器将不 使用索引而使用全表扫描. 低效: SELECT … FROM DEPT WHERE SAL * 12 > 25000; 高效: SELECT … FROM DEPT WHERE SAL > 25000/12;
  • 73. 用>= 替代 >如果DEPTNO上有一个索引。 高效: SELECT * FROM EMP WHERE DEPTNO >=4 低效: SELECT * FROM EMP WHERE DEPTNO >3
  • 74. 通过使用>=、<=等,避免使用NOT命令例子: select * from employee where salary <> 3000; 对这个查询,可以改写为不使用NOT: select * from employee where salary<3000 or salary>3000; 虽然这两种查询的结果一样,但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引,而第一种查询则不能使用索引。
  • 75. 如果有其它办法,不要使用子查询。
  • 76. 外部联接"+"的用法 外部联接"+"按其在"="的左边或右边分左联接和右联接。若不带"+"运算符的表中的一个行不直接匹配于带"+"预算符的表中的任何行,则前者的行与后者中的一个空行相匹配并被返回。利用外部联接"+",可以替代效率十分低下的 not in 运算,大大提高运行速度。例如,下面这条命令执行起来很慢: select a.empno from emp a where a.empno not in (select empno from emp1 where job='SALE'); 利用外部联接,改写命令如下: select a.empno from emp a ,emp1 b where a.empno=b.empno(+) and b.empno is null and b.job='SALE'; 这样运行速度明显提高.
  • 77. 尽量多使用COMMIT 事务是消耗资源的,大事务还容易引起死锁 COMMIT所释放的资源: 回滚段上用于恢复数据的信息. 被程序语句获得的锁 redo log buffer 中的空间 ORACLE为管理上述3种资源中的内部花费
  • 78. 用TRUNCATE替代DELETE 当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有 COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准 确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的 信息.当命令运行后,数据不能被恢复.因此很少的资源被调用, 执行时间也会很短.
  • 79. 计算记录条数和一般的观点相反, count(*) 比count(1)稍快 , 当然如果可 以通过索引检索,对索引列的计数仍旧是最快的. 例如 COUNT(EMPNO)
  • 80. 比如有的表PHONE_NO字段是CHAR型,而且创建有索引, 但在WHERE条件中忘记了加引号,就不会用到索引。 WHERE PHONE_NO=‘13920202022’ WHERE PHONE_NO=13920202022 字符型字段的引号
  • 81. 优化EXPORT和IMPORT使用较大的BUFFER(比如10MB , 10,240,000)可以提高 EXPORT和IMPORT的速度; ORACLE将尽可能地获取你所指定的内存大小,即使在内存 不满足,也不会报错.这个值至少要和表中最大的列相当,否则 列值会被截断;
  • 82. ** 优化 Tools **
  • 83. SQL 语句的执行步骤语法分析 ,分析语句的语法是否符合规范,衡量语句中各表达式的意义。 语义分析 ,检查语句中涉及的所有数据库对象是否存在,且用户有相应的权限。 视图转换,将涉及视图的查询语句转换为相应的对基表查询语句。 表达式转换, 将复杂的 SQL 表达式转换为较简单的等效连接表达式。 选择优化器,不同的优化器一般产生不同的“执行计划” 选择连接方式, ORACLE 有三种连接方式,对多表连接 ORACLE 可选择适当的连接方式。 选择连接顺序, 对多表连接 ORACLE 选择哪一对表先连接,选择这两表中哪个表做为源数据表。 选择数据的搜索路径, 根据以上条件选择合适的数据搜索路径,如是选用全表搜索还是利用索引或是其他的方式。 运行“执行计划”
  • 84. 优化器与执行计划Oracle在执行一个SQL之前,首先要分析一下语句的执行计 划,然后再按执行计划去执行。分析语句的执行计划的工作 是由优化器(Optimizer)来完成的 Oracle的优化器共有两种的优化方式,即基于规则的优化方 式(Rule-Based Optimization,简称为RBO)和基于代价的优 化方式(Cost-Based Optimization,简称为CBO)。 A、RBO方式:优化器在分析SQL语句时,所遵循的是Oracle内部预定 的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走 索引。 B、CBO方式:是看语句的代价(Cost)了,这里的代价主要指Cpu和内存 。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息, 很多的时侯过期统计信息会令优化器做出一个错误的执行计划在 Oracle8及以后的版本,Oracle推荐用CBO的方式。 在Oracle10g中,取消了RBO的支持。
  • 85. 优化器与执行计划Rule:即走基于规则的方式 Choose:默认的情况下Oracle用的便是这种方式。当一个表或或索引有 统计信息,则走CBO的方式,如果表或索引没统计信息,表又不是特别的小, 而且相应的列有索引时,那么就走索引,走RBO的方式 First Rows:它与Choose方式是类似的,所不同的是当一个表有统计信息 时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时 间 All Rows:all_rows是oracle优化器默认的模式,它将选择一种在最短时 间内返回所有数据的执行计划,它将基于整体成本的考虑. first_rows_n:first_rows_n是根据成本而不是基于硬编码的规则来选择 执行计划.n可以是1,10,100,1000或者直接用first_rows(n) hint指定任意 正数.这里的n是我们想获取结果集的前n条记录,这种需求在很多分页语 句的需求中会碰到.
  • 86. 用EXPLAIN PLAN 分析SQL语句EXPLAIN PLAN 是一个很好的分析SQL语句的工具,它甚至 可以在不执行SQL的情况下分析语句. 通过分析,我们就可以 知道ORACLE是怎么样连接表,使用什么方式扫描表(索引扫 描或全表扫描)以及使用到的索引名称. 你需要按照从里到外,从上到下的次序解读分析的结果. EXPLAIN PLAN分析的结果是用缩进的格式排列的, 最内部 的操作将被最先解读, 如果两个操作处于同一层中,带有最小 操作号的将被首先执行. NESTED LOOP是少数不按照上述规则处理的操作, 正确的 执行路径是检查对NESTED LOOP提供数据的操作,其中操 作号最小的将被最先处理.
  • 87. Autotrace 解读Current mode: 对于修改的数据从数据段中读 Read-consistent mode: 读一致性模式 Physical block: 物理块(如8192字节) Recursive calls: 嵌套调用次数
  • 88. 使用TKPROF 工具SQL trace 工具收集正在执行的SQL的性能状态数据并记录到 一个跟踪文件中. 这个跟踪文件提供了许多有用的信息,例如解 析次数.执行次数,CPU使用时间等.这些数据将可以用来优化你 的系统. 设置SQL TRACE在会话级别: 有效 ALTER SESSION SET SQL_TRACE TRUE 设置SQL TRACE 在整个数据库有效, 你必须将SQL_TRACE 参数在init.ora中设为TRUE, USER_DUMP_DEST参数说明了 生成跟踪文件的目录 再使用TKPROF对TRACE文件进行分析 分析结果更加准确、清楚
  • 89. 在SQLPLUS 配置AUTOTRACEAUTOTRACE 参数解 释SET AUTOTRACE OFF不能获得AUTOTRACE报告. 这是默认的.SET AUTOTRACE ON EXPLAIN仅仅显示优化器执行计划的AUTOTRACE报告SET AUTOTRACE ON STATISTICS仅仅显示SQL语句执行的统计结果的 AUTOTRACE报告SET AUTOTRACE ON包括上面两项内容的AUTOTRACE报告SET AUTOTRACE TRACEONLY与SET AUTOTRACE ON类似,所有的统计和数 据都在,但不可以打印
  • 90. 在SQLPLUS 配置AUTOTRACE1、 首先创建PLUSTRACE角色并且赋给public: Sql> @$ORACLE_HOME/sqlplus/admin/plustrce.sql 2、 赋权限给用户 Sql> grant plustrace to public(预赋权的用户名); 3、以SYSTEM用户创建PLAN_TABLE表 Sql> @$ORACLE_HOME/rdbms/admin/utlxplan.sql Sql> create public synonym plan_table for plan_table; Sql> grant all on plan_table to public; 在每个用户下设置AUTOTRACE可显示其执行计划。
  • 91. SQL> select ename,dname    from emp, dept   where emp.deptno=dept.deptno     and dept.dname in ('ACCOUNTING','RESEARCH','SALES','OPERATIONS'); Execution Plan ----------------------------------------------------------    0      SELECT STATEMENT Optimizer=CHOOSE    1    0   NESTED LOOPS    2    1     TABLE ACCESS (FULL) OF 'EMP'    3    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'    4    3       INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE) 最起码要解决全表扫描问题 改变where条件的次序一般没有用优化器与执行计划
  • 92. 目录 优化基础知识 性能调整综述 有效的应用设计 SQL语句的处理过程 Oracle的优化器 Oracle的执行计划 注意事项
  • 93. 性能调整综述谁来调整系统? 什么时候调整? 建立有效调整的目标 在设计和开发时的调整
  • 94. 谁来调整系统应用设计人员必须传达应用系统的设计,使得每个人都清楚应用中的数据流动. 应用开发人员必须传达他们选择的实现策略,使得语句调整的过程中能快速、容易地识别有问题的应用模块和可疑的SQL语句. 数据库管理人员必须仔细地监控系统活动并提供它们的资料,使得异常的系统性能可被快速得识别和纠正. 硬件/软件管理人员必须传达系统的硬件、软件配置并提供它们的资料,使得相关人员能有效地设计和管理系统。
  • 95. 谁来调整系统与系统涉及的每个人都在调整过程中起某些作用,当上面提及的那些人员传达了系统的特性并提供了它们的资料,调整就能相对的容易和更快一些。 事实上的结果是:数据库管理员对调整负有全部或主要的责任。但是,数据库管理员很少有合适的系统方面的资料,而且,在很多情况下,数据库管理员往往是在实施阶段才介入数据库,这就给调整工作带来许多负面的影响,因为在设计阶段的缺陷是不能通过DBA的调整而得以解决,而设计阶段的缺陷往往对数据库性能造成极大的影响。 在真正成熟的开发环境下,开发人员作为纯代码编写人员时,对性能的影响最小,此时大部分的工作应由应用设计人员完成,而且数据库管理员往往在前期的需求管理阶段就介入,为设计人员提供必要的技术支持。 调整并不是数据库管理员的专利,相反大部分应该是设计人员和开发人员的工作,这就需要设计人员和开发人员具体必要的数据库知识,这样才能组成一个高效的团队,然而事实上往往并非如此。
  • 96. 什么时候调整系统多数人认为当用户感觉性能差时才进行调整,这对调整过程中使用某些最有效的调整策略来说往往是太迟了。此时,如果你不愿意重新设计应用的话,你只能通过重新分配内存(调整SGA)和调整I/O的办法或多或少地提高性能。Oracle提供了许多特性,这些特性只有应用到正确地设计的系统中时才能够很大地提高性能。 应用设计人员需要在设计阶段设置应用的性能期望值。然后在设计和开发期间,应用设计人员应考虑哪些Oracle 特性可以对系统有好处,并使用这些特性。 通过良好的系统设计,你就可以在应用的生命周期中消除性能调整的代价和挫折。下图说明在应用的生命周期中调整的相对代价和收益,最有效的调整 时间是在设计阶段。在设计期间的调整能以最低的代价给你最大的收益。
  • 97. 什么时候调整系统图: 在应用生命周期中调整的代价
  • 98. 什么时候调整系统图: 在应用生命周期中调整的收益
  • 99. 调整的目标不管正在设计或维护系统,应该建立专门的性能目标,它使你知道何时要作调整。调整你的系统的最有效方法如下: 􀁺 当设计系统时考虑性能 􀁺 调整操作系统的硬件和软件 􀁺 识别性能瓶颈 􀁺 确定问题的原因 􀁺 采取纠正的动作 当你设计系统时,制定专门的目标;例如,响应时间小于3秒。当应 用不能满足此目标时,识别造成变慢的瓶颈(例如,I/O竞争),确 定原因,采取纠正动作。在开发期间,你应测试应用研究,确定在采 取应用之前是否满足设计的性能目标。
  • 100. 调整的目标调整通常是一系列开销。一旦确定了瓶颈,可能要牺牲一些其它方面的指标来达到所要的结果。例如,如果I/O有问题,你可能需要更多内存或磁盘。如果不可能买,你可能要限制系统的并发性,来获取所需的性能。如果你已经明确地定义了性能的目标,那用什么来交换高性能的决策就变的很容易的,因为已经确定了哪些方面是最重要的,如果我的目标为高性能,可能牺牲一些空间资源。 随着应用的越来越庞大,硬件性能的提高,全面的调整应用逐渐变成代价高昂的行为,在这样情况下,要取得最大的投入/效率之比,较好的办法是调整应用的关键部分,使其达到比较高的性能,这样从总体上来说,整个系统的性能也是比较高的。这也就是有名的20/80原则,调整应用的20%(关键部分),能解决80%的问题。
  • 101. SQL 调整的目标去掉不必要的大型表的全表扫描。 缓存小型表的全表扫描。 校验优化索引的使用。 检验优化的连接技术。 以上目标任务将占据SQL调整90%以上的工作。
  • 102. 在设计和开发时调整良好设计的系统可防止在应用生命周期中产生性能问题。 系统设计人员和应用开发人员必须了解Oracle的查询处理机制以便写出高效的SQL语句。 “有效的应用设计”讨论了你的系统中各种可用的配置,以及每种配置更适合哪种类型的应用。 “优化器”讨论了Oracle的查询优化器,以及如何写语句以获取最快的结果。
  • 103. 在设计和开发时调整当设计你的系统时,使用下列优化性能的准则: - 消除客户机/服务器应用中不必要的网络传输。使用存储过程。 - 使用适合你系统的Oracle服务器选件(例如,并行查询或分布式数据库)。 - 除非你的应用有特殊的需要,否则使用缺省的Oracle锁。 - 利用数据库记住应用模块,以便能以每个模块为基础来追踪性能。 - 选择你的数据块的最佳大小。 -- 原则上来说大一些的性能较好。 - 分布你的数据,使得一个节点使用的数据本地存贮在该节点中。
  • 104. 目录 优化基础知识 性能调整综述 有效的应用设计 SQL语句的处理过程 Oracle的优化器 Oracle的执行计划 注意事项
  • 105. 有效的应用设计将最常用的应用分为2种类型:联机事务处理类型(OLTP),决策支持系统(DSS)。 联机事务处理(OLTP) 该类型的应用是高吞吐量,插入、更新、删除操作比较多的系统,这些系统以不断增长的大容量数据为特征,它们提供给成百用户同时存取,典型的OLTP系统是订票系统,银行的业务系统,订单系统。OTLP的主要目标是可用性、速度、并发性和可恢复性。 当设计这类系统时,必须确保大量的并发用户不能干扰系统的性能。还需要避免使用过量的索引与cluster 表,因为这些结构会使插入和更新操作变慢。
  • 106. 有效的应用设计将最常用的应用分为2种类型:联机事务处理类型(OLTP),决策支持系统(DSS)。 决策支持(DSS) 该类型的应用将大量信息进行提取形成报告,协助决策者作出正确的判断。典型情况是:决策支持系统将OLTP应用收集的大量数据进行查询。典型的应用为客户行为分析系统(超市,保险等)。 决策支持的关键目标是速度、精确性和可用性。 该种类型的设计往往与OLTP设计的理念背道而驰,一般建议使用数据冗余、大量索引、cluster table、并行查询等。 近年来,该类型的应用逐渐与OLAP、数据仓库紧密的联系在一起,形成的一个新的应用方向。
  • 107. 目录 优化基础知识 性能调整综述 有效的应用设计 SQL语句的处理过程 Oracle的优化器 Oracle的执行计划 注意事项
  • 108. SQL语句的处理过程1 查询语句处理 2 DML语句处理(insert, update, delete) 3 DDL 语句处理(create .. , drop .. , alter .. , ) 4 事务控制(commit, rollback)
  • 109. 如图: 列出了处理和运行一个sql语句的需要各个重要阶段。在某些情况下,Oracle运行sql的过程可能与下面列出的各个阶段的顺序有所不同。如DEFINE阶段可能在FETCH阶段之前,这主要依赖你如何书写代码SQL语句执行过程
  • 110. DML 语句的处理假设你使用Pro*C程序来为指定部门的所有职员增加工资。程序已经连到正确的用户,你可以在你的程序中嵌入如下的SQL语句: EXEC SQL UPDATE employees SET salary = 1.10 * salary WHERE department_id = :var_department_id; var_department_id是程序变量,里面包含部门号,我们要 修改该部门的职员的工资。当这个SQL语句执行时,使用该 变量的值。
  • 111. DML 语句的处理每种类型的语句都需要如下阶段: • 第1步: Create a Cursor 创建游标 • 第2步: Parse the Statement 分析语句 • 第5步: Bind Any Variables 绑定变量 • 第7步: Run the Statement 运行语句 • 第9步: Close the Cursor 关闭游标 如果使用了并行功能,还会包含下面这个阶段: • 第6步: Parallelize the Statement 并行执行语句 如果是查询语句,则需要几个额外的步骤,如图所示: • 第3步: Describe Results of a Query 描述查询的结果集 • 第4步: Define Output of a Query 定义查询的输出数据 • 第8步: Fetch Rows of a Query 取查询出来的行
  • 112. 以上语句处理步骤解释第1步: 创建游标(Create a Cursor) 由程序接口调用创建一个游标(cursor)。任何SQL语句都会创建它,特别在运行DML语句时,都是自动创建游标的,不需要开发人员干预。多数应用中,游标的创建是自动的。然而,在预编译程序(pro*c)中游标的创建,可能是隐含的,也可能显式的创建。在存储过程中也是这样的。 第2步:分析语句(Parse the Statement) 在语法分析期间,SQL语句从用户进程传送到Oracle,SQL语句经语法分析后,SQL语句本身与分析的信息都被装入到共享SQL区。在该阶段中,可以解决许多类型的错误。
  • 113. 以上语句处理步骤解释语法分析分别执行下列操作: 翻译SQL语句,验证它是合法的语句,即书写正确 实现数据字典的查找,以验证是否符合表和列的定义 在所要求的对象上获取语法分析锁,使得在语句的语法分析过程中不改变这些对象的定义 验证为存取所涉及的模式对象所需的权限是否满足 决定此语句最佳的执行计划 将它装入共享SQL区 对分布的语句来说,把语句的全部或部分路由到包含所涉及数据的远程节点 * 以上任何一步出错误,都将导致语句报错,中止执行。
  • 114. 以上语句处理步骤解释只有在共享池中不存在等价SQL语句的情况下,才对SQL语句作语法分析。在这种情况下,数据库内核重新为该语句分配新的共享SQL区,并对语句进行语法分析。进行语法分析需要耗费较多的资源,所以要尽量避免进行语法分析,这是优化的技巧之一。 语法分析阶段包含了不管此语句将执行多少次,而只需分析一次的处理要求。Oracle只对每个SQL语句翻译一次,在以后再次执行该语句时,只要该语句还在共享SQL区中,就可以避免对该语句重新进行语法分析,也就是此时可以直接使用其对应的执行计划对数据进行存取。这主要是通过绑定变量(bind variable)实现的,也就是我们常说的共享SQL,后面会给出共享SQL的概念。 虽然语法分析验证了SQL语句的正确性,但语法分析只能识别在SQL语句执行之前所能发现的错误(如书写错误、权限不足等)。因此,有些错误通过语法分析是抓不到的。例如,在数据转换中的错误或在数据中的错(如企图在主键中插入重复的值)以及死锁等均是只有在语句执行阶段期间才能遇到和报告的错误或情况。
  • 115. 查询 语句的处理查询与其它类型的SQL语句不同,因为在成功执行后作为结 果将返回数据。 第3步: 描述查询结果(Describe Results of a Query) 描述阶段只有在查询结果的各个列是未知时才需要;例如,当查询由用户交互地输入需要输出的列名。在这种情况要用描述阶段来决定查询结果的特征(数据类型,长度和名字)。 第4步: 定义查询的输出数据(Define Output of a Query) 在查询的定义阶段,你指定与查询出的列值对应的接收变量的位置、大小和数据类型,这样我们通过接收变量就可以得到查询结果。如果必要的话,Oracle会自动实现数据类型的转换。这是将接收变量的类型与对应的列类型相比较决定的。
  • 116. 查询 语句的处理第5步: 绑定变量(Bind Any Variables) Oracle知道了SQL语句的意思,但仍没有足够的信息用于执行该语句。Oracle 需要得到在语句中列出的所有变量的值。在该例中,Oracle需要得到对department_id列进行限定的值。得到这个值的过程就叫绑定变量(binding variables) 此过程称之为将变量值捆绑进来。程序必须指出可以找到该数值的变量名(该变量被称为捆绑变量,变量名实质上是一个内存地址,相当于指针)。应用的最终用户可能并没有发觉他们正在指定捆绑变量,因为Oracle 的程序可能只是简单地指示他们输入新的值,其实这一切都在程序中自动做了。 因为你指定了变量名,在你再次执行之前无须重新捆绑变量。你可以改变绑定变量的值,而Oracle在每次执行时,仅仅使用内存地址来查找此值。 如果Oracle 需要实现自动数据类型转换的话(除非它们是隐含的或缺省的),你还必须对每个值指定数据类型和长度。关于这些信息可以参考oracle的相关文档,如Oracle Call Interface Programmer's Guide
  • 117. 查询 语句的处理第6步: 并行执行语句(Parallelize the Statement ) ORACLE 可以在SELECTs, INSERTs, UPDATEs, MERGEs, DELETEs语句中执行相应并行查询操作,对某些DDL操作,如创建索引、用子查询创建表、在分区表上的操作,可以执行并行操作。并行化可导致多个服务器进程(oracle server processes)为同一个SQL语句工作,使该SQL语句可以快速完成,但是会耗费更多的资源,所以除非很有必要,否则不要使用并行查询。 第7步: 执行语句(Run the Statement) 此时,Oracle拥有所有需要的信息与资源,可以真正运行SQL语句了。如果该语句为SELECT查询或INSERT语句,则不需要锁定任何行,因没有数据需要被改变。如果语句为UPDATE或DELETE语句,则该语句影响的所有行都被锁定,防止该用户提交或回滚之前,别的用户对这些数据进行修改。这保证了数据的一致性。 对于某些语句,你可以指定执行的次数,这称为批处理(array processing)。指定执行N次,则绑定变量与定义变量被定义为大小为N的数组的开始位置,这种方法可以减少网络开销,也是优化的技巧之一。
  • 118. 查询 语句的处理第8步: 取出查询的行(Fetch Rows of a Query) 在fetch阶段,行数据被取出来,每个后续的存取操作检索结果集中的下一行数据,直到最后一行被取出来。上面提到过,批量的fetch是优化的技巧之一。 第9步: 关闭游标(Close the Cursor) SQL语句处理的最后一个阶段就是关闭游标。
  • 119. DDL 语句的处理DDL语句的执行不同与DML语句和查询语句的执行,这是因为DDL语句执行成功后需要对数据字典数据进行修改。对于DDL语句,语句的分析阶段包括:分析、查找数据字典信息和执行。 事务管理语句、会话管理语句、系统管理语句只有分析与执行阶段,为了重新执行该语句,会重新分析与执行该语句。
  • 120. 事务控制必须定义事务,这样在一个逻辑单元中的所有工作可以同时被提交或回滚,保证了数据的一致性。一个事务应该由逻辑单元中的所有必须部分组成,不应该多一个,也不应该少一个。 在事务开始和结束的这段时间内,所有被引用表中的数据都应该在一致的状态(或可以被回溯到一致的状态) 事务应该只包含可以对数据进行一致更改(one consistent change to the data)的SQL语句 Eg:在两个帐号之间的转帐(这是一个事务或逻辑工作单元),应该包含从一个帐号中借钱(由一个SQL完成),然后将借的钱存入另一个帐号(由另一个SQL完成)。这2个操作作为一个逻辑单元,应该同时成功或同时失败。其它不相关的操作,如向一个帐户中存钱,不应该包含在这个转帐事务中。 在设计应用时,除需要决定哪种类型的操作组成一个事务外,还需要决定使用BEGIN_DISCRETE_TRANSACTIO存储过程是否对提高小的、非分布式的事务的性能有作用。
  • 121. 目录 优化基础知识 性能调整综述 有效的应用设计 SQL语句的处理过程 Oracle的优化器 Oracle的执行计划 注意事项
  • 122. Oracle 优化器2 种类型的优化器: 基于规则的优化器 基于代价的优化器。 不同之处:取得代价的方法与衡量代价的大小不同。
  • 123. 基于规则的优化器 -- Rule Based (Heuristic) Optimization(简称RBO) 基于规则的优化器中采用启发式的方法(Heuristic Approach)或规则(Rules)来生成执行计划。例如,如果一个查询的where条件(where clause)包含一个谓词(predicate,其实就是一个判断条件,如”=”, “>”, ”<”等),而且该谓词上引用的列上有有效索引,那么优化器将使用索引访问这个表,而不考虑其它因素,如表中数据的多少、表中数据的易变性、索引的可选择性等。此时数据库中没有关于表与索引数据的统计性描述,如表中有多上行,每行的可选择性等。优化器也不考虑实例参数,如multi block i/o、可用排序内存的大小等,所以优化器有时就选择了次优化的计划作为真正的执行计划,导致系统性能不高。 如,对于 select * from emp where deptno = 10; 如果是使用基于规则的优化器,而且deptno列上有有效的索引,则会通过deptno列上的索引来访问emp表。在绝大多数情况下,这是比较高效的,但是在一些特殊情况下,使用索引访问也有比较低效的时候,现举例说明:
  • 124. 基于规则的优化器 -- Rule Based (Heuristic) Optimization(简称RBO) 1) emp表比较小,该表的数据只存放在几个数据块中。此时使用全表扫描比使用索引访问emp表反而要好。因为表比较小,极有可能数据全在内存中,所以此时做全表扫描是最快的。而如果使用索引扫描,需要先从索引中找到符合条件记录的rowid,然后再一一根据这些rowid从emp中将数据取出来,在这种条件下,效率就会比全表扫描的效率要差一些。 2) emp表比较大时,而且deptno = 10条件能查询出表中大部分的数据如(50%)。如该表共有4000万行数据,共放在有500000个数据块中,每个数据块为8k,则该表共有约4G,则这么多的数据不可能全放在内存中,绝大多数需要放在硬盘上。此时如果该查询通过索引查询,则是你梦魇的开始。db_file_multiblock_read_count参数的值200。如果采用全表扫描,则需要500000/db_file_multiblock_read_count=500000/200=2500次I/O。但是如果采用索引扫描,假设deptno列上的索引都已经cache到内存中,所以可以将访问索引的开销忽略不计。因为要读出4000万x 50% = 2000万数据,假设在读这2000万数据时,有99.9%的命中率,则还是需要20000次I/O,比上面的全表扫描需要的2500次多多了,所以在这种情况下,用索引扫描反而性能会差很多。在这样的情况下,用全表扫描的时间是固定的,但是用索引扫描的时间会随着选出数据的增多使查询时间相应的延长。
  • 125. 基于代价的优化器 -- Cost Based Optimization(简称CBO) Oracle把一个代价引擎(Cost Engine)集成到数据库内核中,用来估计每个执行计划需要的代价,该代价将每个执行计划所耗费的资源进行量化,从而CBO可以根据这个代价选择出最优的执行计划。 查询耗费的资源可以被分成3个基本组成部分:I/O代价、CPU代价、network代价。 I/O代价是将数据从磁盘读入内存所需的代价。访问数据包括将数据文件中数据块的内容读入到SGA的数据高速缓存中,在一般情况下,该代价是处理一个查询所需要的最主要代价,所以我们在优化时,一个基本原则就是降低查询所产生的I/O总次数。 CPU代价是处理在内存中数据所需要的代价,如一旦数据被读入内存,则我们在识别出我们需要的数据后,在这些数据上执行排序(sort)或连接(join)操作,这需要耗费CPU资源。 对于需要访问跨节点(即通常说的服务器)数据库上数据的查询来说,存在network代价,用来量化传输操作耗费的资源。查询远程表的查询或执行分布式连接的查询会在network代价方面花费比较大。
  • 126. 判断当前数据库使用何种优化器由optimizer_mode初始化参数决定的。该参数可能的取值为:first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows | choose | rule。 具体解释如下: RULE为使用RBO优化器。 CHOOSE则是根据实际情况,如果数据字典中包含被引用的表的统计数据,即引用的对象已经被分析,则就使用CBO优化器,否则为RBO优化器。 ALL_ROWS为CBO优化器使用的第一种具体的优化方法,是以数据的吞吐量为主要目标,以便可以使用最少的资源完成语句。 FIRST_ROWS为优化器使用的第二种具体的优化方法,是以数据的响应时间为主要目标,以便快速查询出开始的几行数据。 FIRST_ROWS_[1 | 10 | 100 | 1000] 为优化器使用的第三种具体的优化方法,让优化器选择一个能够把响应时间减到最小的查询执行计划,以迅速产生查询结果的前 n 行。该参数为ORACLE 9I新引入的。 查看命令:show parameter OPTIMIZER_MODE
  • 127. 目录 优化基础知识 性能调整综述 有效的应用设计 SQL语句的处理过程 Oracle的优化器 Oracle的执行计划 注意事项
  • 128. 内容提纲Oracle的执行计划 优化定义 概念分析 执行计划分析 优化器选择 访问数据存取方法 表间连接 如何产生执行计划 如何分析执行计划 如何干预执行计划 综合案例分析
  • 129. 优化定义什么是优化: 优化是选择最有效的执行计划来执行SQL语句的过程,这是在处理任何数据的语句(SELECT,INSERT,UPDATE或DELETE)中的一个重要步骤。对Oracle来说,执行这样的语句有许多不同的方法,譬如说,将随着以什么顺序访问哪些表或索引的不同而不同。所使用的执行计划可以决定语句能执行得有多快。Oracle中称之为优化器(Optimizer)的组件用来选择这种它认为最有效的执行计划。
  • 130. 内容提纲Oracle的执行计划 优化定义 概念分析 执行计划分析 优化器选择 访问数据存取方法 表间连接 如何产生执行计划 如何分析执行计划 如何干预执行计划 综合案例分析
  • 131. 概念分析共享SQL语句: 为了不重复解析相同的SQL语句(因为解析操作比较费资源,会导致性能下降),在第一次解析之后,ORACLE将SQL语句及解析后得到的执行计划存放在内存中。这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享。因此,当你执行一个SQL语句(有时被称为一个游标)时,如果该语句和之前的执行过的某一语句完全相同,并且之前执行的该语句与其执行计划仍然在内存中存在,则ORACLE就不需要再进行分析,直接得到该语句的执行路径。ORACLE的这个功能大大地提高了SQL的执行性能并大大节省了内存的使用。使用这个功能的关键是将执行过的语句尽可能放到内存中,所以这要求有大的共享池(通过设置shared buffer pool参数值)和尽可能的使用绑定变量的方法执行SQL语句。
  • 132. 概念分析Rowid的概念: rowid是一个伪列,既然是伪列,那么这个列就不是用户定义,而是系统自己给加上的。对每个表都有一个rowid的伪列,但是表中并不物理存储ROWID列的值。不过你可以像使用其它列那样使用它,但是不能删除该列,也不能对该列的值进行修改、插入。一旦一行数据插入数据库,则rowid在该行的生命周期内是唯一的,即即使该行产生行迁移,行的rowid也不会改变。
  • 133. 概念分析为什么使用Rowid: rowid对访问一个表中的给定的行提供了最快的访问方法,通过ROWID可以直接定位到相应的数据块上,然后将其读到内存。我们创建一个索引时,该索引不但存储索引列的值,而且也存储索引值所对应的行的ROWID,这样我们通过索引快速找到相应行的ROWID后,通过该ROWID,就可以迅速将数据查询出来。这也就是我们使用索引查询时,速度比较快的原因。 在ORACLE8以前的版本中,ROWID由FILE 、BLOCK、ROW NUMBER构成。随着oracle8中对象概念的扩展,ROWID发生了变化,ROWID由OBJECT、FILE、BLOCK、ROW NUMBER构成。利用DBMS_ROWID可以将rowid分解成上述的各部分,也可以将上述的各部分组成一个有效的rowid。
  • 134. 概念分析Recursive SQL概念 : 为了执行用户发出的一个sql语句,Oracle必须执行一些额外的语句,我们将这些额外的语句称之为‘recursive calls’或‘recursive SQL statements’。如当一个DDL语句发出后,ORACLE总是隐含的发出一些recursive SQL语句,来修改数据字典信息,以便用户可以成功的执行该DDL语句。当需要的数据字典信息没有在共享内存中时,经常会发生Recursive calls,这些Recursive calls会将数据字典信息从硬盘读入内存中。用户不必关心这些recursive SQL语句的执行情况,在需要的时候,ORACLE会自动的在内部执行这些语句。当然DML语句与SELECT都可能引起recursive SQL。简单的说,我们可以将触发器视为recursive SQL。
  • 135. 概念分析Row Source(行源) : 用在查询中,由上一操作返回的符合条件的行的集合,即可以是表的全部行数据的集合;也可以是表的部分行数据的集合;也可以为对上2个row source进行连接操作(如join连接)后得到的行数据集合。 Predicate(谓词) : 一个查询中的WHERE限制条件。
  • 136. 概念分析Driving Table(驱动表): 该表又称为外层表(OUTER TABLE)。这个概念用于嵌套与HASH连接中。如果该row source返回较多的行数据,则对所有的后续操作有负面影响。注意此处虽然翻译为驱动表,但实际上翻译为驱动行源(driving row source)更为确切。一般说来,是应用查询的限制条件后,返回较少行源的表作为驱动表,所以如果一个大表在WHERE条件有限制条件(如等值限制),则该大表作为驱动表也是合适的,所以并不是只有较小的表可以作为驱动表,正确说法应该为应用查询的限制条件后,返回较少行源的表作为驱动表。在执行计划中,应该为靠上的那个row source,后面会给出具体说明。在我们后面的描述中,一般将该表称为连接操作的row source 1。
  • 137. 概念分析Probed Table(被探查表): 该表又称为内层表(INNER TABLE)。在我们从驱动表中得到具体一行的数据后,在该表中寻找符合连接条件的行。所以该表应当为大表(实际上应该为返回较大row source的表)且相应的列上应该有索引。在我们后面的描述中,一般将该表称为连接操作的row source 2。
  • 138. 概念分析组合索引(concatenated index): 由多个列构成的索引,如create index idx_emp on emp(col1, col2, col3, ……),则我们称idx_emp索引为组合索引。在组合索引中有一个重要的概念:引导列(leading column),在上面的例子中,col1列为引导列。当我们进行查询时可以使用”where col1 = ? ”,也可以使用”where col1 = ? and col2 = ?”,这样的限制条件都会使用索引,但是”where col2 = ? ”查询就不会使用该索引。所以限制条件中包含先导列时,该限制条件才会使用该组合索引。
  • 139. 概念分析可选择性(selectivity): 比较一下列中唯一键的数量和表中的行数,就可以判断该列的可选择性。如果该列的”唯一键的数量/表中的行数”的比值越接近1,则该列的可选择性越高,该列就越适合创建索引,同样索引的可选择性也越高。在可选择性高的列上进行查询时,返回的数据就较少,比较适合使用索引查询。
  • 140. 内容提纲Oracle的执行计划 优化定义 概念分析 执行计划分析 优化器选择 访问数据存取方法 表间连接 如何产生执行计划 如何分析执行计划 如何干预执行计划 综合案例分析
  • 141. 执行计划分析为了执行语句,Oracle可能必须实现许多步骤。这些步骤中的每一步可能是从数据库中物理检索数据行,或者用某种方法准备数据行,供发出语句的用户使用。Oracle用来执行语句的这些步骤的组合被称之为执行计划。 执行计划是SQL优化中最为复杂也是最为关键的部分,只有知道了ORACLE在内部到底是如何执行该SQL语句后,才能知道优化器选择的执行计划是否为最优的。执行计划对于DBA来说,就象财务报表对于财务人员一样重要。所以我们面临的问题主要是: 1. 如何得到执行计划; 2. 如何分析执行计划; 从而找出影响性能的主要问题。
  • 142. 执行计划分析举例,如何得到执行计划: 显示下面SQL语句的执行计划。 SELECT ename, job, sal, dname FROM emp, dept WHERE emp.deptno = derpt.deptno AND NOT EXISTS ( SELECT * FROM salgrade WHERE emp.sal BETWEEN losal AND hisal ); 此语句查询薪水不在任何建议薪水范围内的所有雇员的 名字,工作,薪水和部门名。
  • 143. 执行计划分析如图显示了上例执行计划的图形表示:
  • 144. 执行计划的步骤第3步和第6步分别的从EMP表和SALGRADE表读所有行。 第5步在PK_DEPTNO索引中查找由步骤3返回的每个DEPTNO值。它找出与DEPT表中相关联的那些行的ROWID。 第4步从DEPT表中检索出ROWID为第5步返回的那些行。 由黑色字框指出的步骤在行源上操作,如做2表之间的关联,排序,或过滤等操作。 第2步实现嵌套的循环操作(相当于C语句中的嵌套循环),接收从第3步和第4步来的行源,把来自第3步源的每一行与它第4步中相应的行连接在一起,返回结果行到第1步。 第1步完成一个过滤器操作。它接收来自第2步和第6步的行源,消除掉第2步中来的,在第6步有相应行的那些行,并将来自第2步的剩下的行返回给发出语句的用户或应用。
  • 145. 实现执行计划步骤的顺序 执行计划中的步骤不是按照它们编号的顺序来实现的:Oracle首先实现上图树结构图形里作为叶子出现的那些步骤(例如步骤3、5、6)。由每一步返回的行称为它下一步骤的行源。然后Oracle实现父步骤。 Oracle以下列顺序实现这些步骤: 首先,Oracle实现步骤3,并一行一行地将结果行返回给第2步。 对第3步返回的每一行,Oracle实现这些步骤: Oracle实现步骤5,并将结果ROWID返回给第4步。 Oracle实现步骤4,并将结果行返回给第2步。 Oracle实现步骤2,将接受来自第3步的一行和来自第4步的一行,并返回给第1步一行。 Oracle实现步骤6,如果有结果行的话,将它返回给第1步。 Oracle实现步骤1,如果从步骤6返回行,Oracle将来自第2步的行返回给发出SQL语句的用户。
  • 146. 内容提纲Oracle的执行计划 优化定义 概念分析 执行计划分析 优化器选择 访问数据存取方法 表间连接 如何产生执行计划 如何分析执行计划 如何干预执行计划 综合案例分析
  • 147. 优化器选择 对于以上的操作过程,可以使用first_rows作为优化目 标以便于实现快速响应用户的请求。 有些父步骤在它们被实现之前需要来自子步骤的所有行 。对这样的父步骤,直到所有行从子步骤返回之前 Oracle不能实现该父步骤。这样的父步骤包括排序,排 序一合并的连接,组功能和总计。对于这样的操作,可 以用all_rows作为优化目标,使该中类型的操作耗费的 资源最少。 有时语句执行时,并不是象上面说的那样一步一步有先有后的进行,而是可能并行运行,如在实际环境中,3、5、4步可能并行运行,以便取得更好的效率。从上面的树型图上,是很难看出各个操作执行的先后顺序,而通过ORACLE生成的另一种形式的执行计划,则可以很容易的看出哪个操作先执行,哪个后执行,这样的执行计划是我们真正需要的,后面会给出详细说明。现在先来看一些预备知识。
  • 148. 内容提纲Oracle的执行计划 优化定义 概念分析 执行计划分析 优化器选择 访问数据存取方法 表间连接 如何产生执行计划 如何分析执行计划 如何干预执行计划 综合案例分析
  • 149. 访问路径(方法) -- access path 优化器在形成执行计划时需要做的一个重要选择是如何从数据库查询出需要的数据。对于SQL语句存取的任何表中的任何行,可能存在许多存取路径(存取方法),通过它们可以定位和查询出需要的数据。优化器选择其中自认为是最优化的路径。 在物理层,oracle读取数据,一次读取的最小单位为数据库块(由多个连续的操作系统块组成),一次读取的最大值由操作系统一次I/O的最大值与multiblock参数共同决定,所以即使只需要一行数据,也是将该行所在的数据库块读入内存。 逻辑上,oracle用如下存取方法访问数据: 1 全表扫描(Full Table Scans, FTS) 2 通过ROWID的表存取(Table Access by ROWID或rowid lookup) 3 索引扫描(Index Scan或index lookup)
  • 150. 全表扫描为实现全表扫描,Oracle读取表中所有的行,并检查每一行是否满足语句的WHERE限制条件。Oracle顺序读取分配给表的每个数据块,直到读到表的最高水线处(high water mark, HWM,标识表的最后一个数据块)。一个多块读操作可以使一次I/O能读取多块数据块(db_block_multiblock_read_count参数设定),而非只读取一个数据块,这极大的减少了I/O总次数,提高了系统的吞吐量,所以利用多块读的方法可以高效实现全表扫描,而且只有在全表扫描的情况下才能使用多块读操作。在这种访问模式下,每个数据块只被读一次。由于HWM标识最后一块被读入的数据,而delete操作不影响HWM值,所以一个表的所有数据被delete后,其全表扫描的时间不会有改善,一般我们需要使用truncate命令来使HWM值归为0。幸运的是oracle 10G后,可以人工收缩HWM的值。 由FTS模式读入的数据被放到高速缓存的Least Recently Used (LRU)列表的尾部,这样可以使其快速交换出内存,从而不使内存重要的数据被交换出内存。 使用FTS的前提:在较大的表上不建议使用全表扫描,除非取出数据的比较多,超过总量的5% -- 10%,或你想使用并行查询功能时。 使用全表扫描的例子: ~~~~~~~~~~~~~~~~~~~~~~~~ SQL> explain plan for select * from dual; Query Plan ----------------------------------------- SELECT STATEMENT [CHOOSE] Cost= TABLE ACCESS FULL DUAL
  • 151. 通过ROWID的表存取 (Table Access by ROWID或rowid lookup) 行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置,所以通过ROWID来存取数据可以快速定位到目标数据上,是Oracle存取单行数据的最快方法。 为通过ROWID存取表,Oracle 首先要获取被选择行的ROWID,或从语句的WHERE子句中得到,或者通过表的一个或多个索引的索引扫描得到。Oracle然后以得到的ROWID为依据定位每个被选择的行。 此存取方法不会用到多块读操作,一次I/O只能读取一个数据块。我们经常在执行计划中看到该存取方法,如通过索引查询数据。 使用ROWID存取的方法: SQL> explain plan for select * from dept where rowid = 'AAAAyGAADAAAAATAAF'; Query Plan ------------------------------------ SELECT STATEMENT [CHOOSE] Cost=1 TABLE ACCESS BY ROWID DEPT [ANALYZED]
  • 152. 索引扫描 (Index Scan或index lookup) 通过index查找到数据对应的rowid值(对于非唯一索引可能返回多个rowid值),然后根据rowid直接从表中得到具体的数据。 一个rowid唯一的表示一行数据,该行对应的数据块是通过一次i/o得到的,该次i/o只会读取一个数据库块。 在索引中,除了存储每个索引的值外,索引还存储具有此值的行对应的ROWID值。索引扫由2步组成: (1) 扫描索引得到对应的rowid值。 (2) 通过找到的rowid从表中读出具体的数据。 每步都是单独的一次I/O,但对于索引,由于经常使用,绝大多数都 已经CACHE到内存中,所以第1步的I/O经常是逻辑I/O,即数据可以 从内存中得到。但是对于第2步来说,如果表比较大,则其数据不可 能全在内存中,则其I/O很有可能是物理I/O,这是一个机械操作,相 对逻辑I/O来说,极其费时间。所以如果对大表进行索引扫描,取出 的数据如果大于总量的5% -- 10%,使用索引扫描会效率下降很多。 如下列所示: SQL> explain plan for select empno, ename from emp where empno=10; Query Plan ------------------------------------ SELECT STATEMENT [CHOOSE] Cost=1 TABLE ACCESS BY ROWID EMP [ANALYZED] INDEX UNIQUE SCAN EMP_I1 注意TABLE ACCESS BY ROWID EMP部分,这表明这不是通过FTS存取路径访问数据,而是通过rowid lookup存取路径访问数据的。此例中,所需要的rowid是由于在索引查找empno列的值得到的,这种方式是INDEX UNIQUE SCAN查找,后面给予介绍,EMP_I1为使用的进行索引查找的索引名字。
  • 153. 根据索引的类型与where限制条件的不同,有4种类型的索引扫描: 索引唯一扫描(index unique scan) 索引范围扫描(index range scan) 索引全扫描(index full scan) 索引快速扫描(index fast full scan) 索引扫描 (Index Scan或index lookup)
  • 154. 索引唯一扫描(index unique scan) 通过唯一索引查找一个数值经常返回单个ROWID。如果该唯一索引有多个列组成(即组合索引),则至少要有组合索引的引导列参与到该查询中,如创建一个索引:create index idx_test on emp(ename, deptno, loc)。则select ename from emp where ename = ‘JACK’ and deptno = ‘DEV’语句可以使用该索引。如果该语句只返回一行,则存取方法称为索引唯一扫描。而select ename from emp where deptno = ‘DEV’语句则不会使用该索引,因为where子句种没有引导列。如果存在UNIQUE 或PRIMARY KEY 约束(它保证了语句只存取单行),Oracle经常实现唯一性扫描。 使用唯一性约束的例子: SQL> explain plan for select empno,ename from emp where empno=10; Query Plan ------------------------------------ SELECT STATEMENT [CHOOSE] Cost=1 TABLE ACCESS BY ROWID EMP [ANALYZED] INDEX UNIQUE SCAN EMP_I1 索引扫描 (Index Scan或index lookup)
  • 155. 索引范围扫描(index range scan) 使用索引存取多行数据,如果索引是组合索引,如索引唯一扫描所示,且select ename from emp where ename = ‘JACK’ and deptno = ‘DEV’语句返回多行数据,虽然该语句还是使用该组合索引进行查询,可此时的存取方法称为索引范围扫描。在唯一索引上使用索引范围扫描的典型情况下是在谓词(where限制条件)中使用了范围操作符(如>、<、<>、>=、<=、between) 使用索引范围扫描的例子: SQL> explain plan for select empno,ename from emp where empno > 7876 order by empno; Query Plan -------------------------------------------------------------------------------- SELECT STATEMENT [CHOOSE] Cost=1 TABLE ACCESS BY ROWID EMP [ANALYZED] INDEX RANGE SCAN EMP_I1 [ANALYZED] 索引扫描 (Index Scan或index lookup) 在非唯一索引上,谓词col = 5可能返回多行数据,所以在非唯一索引上都使用索引范围扫描。 使用index rang scan的3种情况: (a) 在唯一索引列上使用了range操作符(> < <> >= <= between) (b) 在组合索引上,只使用部分列进行查询,导致查询出多行 (c) 对非唯一索引列上进行的任何查询。
  • 156. 索引全扫描(index full scan) 与全表扫描对应,也有相应的全索引扫描。在某些情况下,可能进行全索引扫描而不是范围扫描,需要注意的是全索引扫描只在CBO模式下才有效。CBO根据统计数值得知进行全索引扫描比进行全表扫描更有效时,才进行全索引扫描,而且此时查询出的数据都必须从索引中可以直接得到。 全索引扫描的例子: An Index full scan will not perform single block i/o's and so it may prove to be inefficient. e.g. Index BE_IX is a concatenated index on big_emp (empno, ename) SQL> explain plan for select empno, ename from big_emp order by empno,ename; Query Plan -------------------------------------------------------------------------------- SELECT STATEMENT [CHOOSE] Cost=26 INDEX FULL SCAN BE_IX [ANALYZED] 索引扫描 (Index Scan或index lookup)
  • 157. 索引快速扫描(index fast full scan) 扫描索引中的所有的数据块,与 index full scan很类似,但是一个显著的区别就是它不对查询出的数据进行排序,即数据不是以排序顺序被返回。在这种存取方法中,可以使用多块读功能,也可以使用并行读入,以便获得最大吞吐量与缩短执行时间。 索引快速扫描的例子: BE_IX 索引是一个多列索引:big_emp (empno,ename) SQL> explain plan for select empno, ename from big_emp; Query Plan ------------------------------------------ SELECT STATEMENT [CHOOSE] Cost=1 INDEX FAST FULL SCAN BE_IX [ANALYZED] 只选择多列索引的第2列: SQL> explain plan for select ename from big_emp; Query Plan ------------------------------------------ SELECT STATEMENT [CHOOSE] Cost=1 INDEX FAST FULL SCAN BE_IX [ANALYZED] 索引扫描 (Index Scan或index lookup)
  • 158. 内容提纲Oracle的执行计划 优化定义 概念分析 执行计划分析 优化器选择 访问数据存取方法 表间连接 如何产生执行计划 如何分析执行计划 如何干预执行计划 综合案例分析
  • 159. Join是一种试图将两个表结合在一起的谓词,一次只能连接2个表,表连接也可以被称为表关联。Join过程的各个步骤经常是串行操作,即使相关的row source可以被并行访问,即可以并行的读取做join连接的两个row source的数据,但是在将表中符合限制条件的数据读入到内存形成row source后,join的其它步骤一般是串行的。有多种方法可以将2个表连接起来,当然每种方法都有自己的优缺点,每种连接类型只有在特定的条件下才会发挥出其最大优势。 row source(表)之间的连接顺序对于查询的效率有非常大的影响。通过首先存取特定的表,即将该表作为驱动表,这样可以先应用某些限制条件,从而得到一个较小的row source,使连接的效率较高,这也就是我们常说的要先执行限制条件的原因。一般是在将表读入内存时,应用where子句中对该表的限制条件。 表间连接
  • 160. 根据2个row source的连接条件的中操作符的不同,可以将连接分为等值连接(如WHERE A.COL3 = B.COL4)、非等值连接(WHERE A.COL3 > B.COL4)、外连接(WHERE A.COL3 = B.COL4(+))。 下面以等值连接为例进行介绍。在后面的介绍中,都已: SELECT A.COL1, B.COL2 FROM A, B WHERE A.COL3 = B.COL4; 为例进行说明,假设A表为Row Soruce1,则其对应的连接操作关联列为COL 3;B表为Row Soruce2,则其对应的连接操作关联列为COL 4; 表间连接
  • 161. 连接类型: 排序 - - 合并连接 (Sort Merge Join (SMJ) ) 嵌套循环 (Nested Loops (NL) ) 哈希连接 (Hash Join) 笛卡儿乘积(Cartesian Product) 表间连接
  • 162. 内部连接过程: 首先生成row source1需要的数据,然后对这些数据按照连接操 作关联列(如A.col3)进行排序。 随后生成row source2需要的数据,然后对这些数据按照与sort source1对应的连接操作关联列(如B.col4)进行排序。 最后两边已排序的行被放在一起执行合并操作,即将2个row source按照连接条件连接起来。排序 - - 合并连接 (Sort Merge Join (SMJ) )下面是连接步骤的图形表示:
  • 163. 如果row source已经在连接关联列上被排序,则该连接操作就不需要再进行sort操作,这样可以大大提高这种连接操作的连接速度,因为排序是个极其费资源的操作, 特别是对于较大的表。 预先排序的row source包括已经被索引的列(如a.col3或b.col4上有索引)或row source已经在前面的步骤中被排序了。尽管合并两个row source的过程是串行的,但是可以并行访问这两个row source(如并行读入数据,并行排序). 排序是一个费时、费资源的操作,特别对于大表。基于这个原因,SMJ经常不是一个特别有效的连接方法,但是如果2个row source都已经预先排序,则这种连接方法的效率较高。 排序 - - 合并连接 (Sort Merge Join (SMJ) )SMJ连接的例子: SQL> explain plan for select /*+ ordered */ e.deptno, d.deptno from emp e, dept d where e.deptno = d.deptno order by e.deptno, d.deptno; Query Plan ------------------------------------- SELECT STATEMENT [CHOOSE] Cost=17 MERGE JOIN SORT JOIN TABLE ACCESS FULL EMP [ANALYZED] SORT JOIN TABLE ACCESS FULL DEPT [ANALYZED]
  • 164. 描述 该连接过程是一个2层嵌套循环,则外层循环的次数越少越好,这也就是我们为什么将小表或返回较小row source的表作为驱动表(用于外层循环)的理论依据。但是这个理论只是一般指导原则,因为遵循这个理论并不能总保证使语句产生的I/O次数最少。有时不遵守这个理论依据,反而会获得更好的效率。如果使用这种方法,决定使用哪个表作为驱动表很重要。有时如果驱动表选择不正确,将会导致语句的性能很差、很差。 嵌套循环 (Nested Loops (NL) )
  • 165. 内部连接过程: Row source1的Row 1 -------------- -- Probe -> Row source 2 Row source1的Row 2 -------------- -- Probe -> Row source 2 Row source1的Row 3 -------------- -- Probe -> Row source 2 ……. Row source1的Row n -------------- -- Probe -> Row source 2 从内部连接过程来看,需要用row source1中的每一行,去匹配row source2中的所有行,所以此时保持row source1尽可能的小与高效的访问row source2(一般通过索引实现)是影响这个连接效率的关键问题。这只是理论指导原则,目的是使整个连接操作产生最少的物理I/O次数,而且如果遵守这个原则,一般也会使总的物理I/O数最少。但是如果不遵从这个指导原则,反而能用更少的物理I/O实现连接操作,那尽管违反指导原则吧!因为最少的物理I/O次数才是我们应该遵从的真正的指导原则,在后面的具体案例分析中就给出这样的例子。 嵌套循环 (Nested Loops (NL) )
  • 166. 在上面的连接过程中,我们称Row Source1为驱动表或外部表。Row Source2被称为被探查表或内部表。 在NESTED LOOPS连接中,Oracle读取row source1中的每一行,然后在row sourc2中检查是否有匹配的行,所有被匹配的行都被放到结果集中,然后处理row source1中的下一行。这个过程一直继续,直到row source1中的所有行都被处理。这是从连接操作中可以得到第一个匹配行的最快的方法之一,这种类型的连接可以用在需要快速响应的语句中,以响应速度为主要目标。 如果driving row source(外部表)比较小,并且在inner row source(内部表)上有唯一索引,或有高选择性非唯一索引时,使用这种方法可以得到较好的效率。NESTED LOOPS有其它连接方法没有的的一个优点是:可以先返回已经连接的行,而不必等待所有的连接操作处理完才返回数据,这可以实现快速的响应时间。 嵌套循环 (Nested Loops (NL) )
  • 167. 如果不使用并行操作,最好的驱动表是那些应用了where 限制条件后,可以返回较少行数据的的表,所以大表也可能称为驱动表,关键看限制条件。对于并行查询,我们经常选择大表作为驱动表,因为大表可以充分利用并行功能。当然,有时对查询使用并行操作并不一定会比查询不使用并行操作效率高,因为最后可能每个表只有很少的行符合限制条件,而且还要看你的硬件配置是否可以支持并行(如是否有多个CPU,多个硬盘控制器),所以要具体问题具体对待。 嵌套循环 (Nested Loops (NL) ) NL连接的例子: SQL> explain plan for select a.dname, b.sql from dept a, emp b where a.deptno = b.deptno; Query Plan ------------------------- SELECT STATEMENT [CHOOSE] Cost=5 NESTED LOOPS TABLE ACCESS FULL DEPT [ANALYZED] TABLE ACCESS FULL EMP [ANALYZED]
  • 168. 理论上来说比NL与SMJ更高效,而且只用在CBO优化器中。 较小的row source被用来构建hash table与bitmap,第2个row source被用来被hansed,并与第一个row source生成的hash table进行匹配,以便进行进一步的连接。Bitmap被用来作为一种比较快的查找方法,来检查在hash table中是否有匹配的行。特别的,当hash table比较大而不能全部容纳在内存中时,这种查找方法更为有用。这种连接方法也有NL连接中所谓的驱动表的概念,被构建为hash table与bitmap的表为驱动表,当被构建的hash table与bitmap能被容纳在内存中时,这种连接方式的效率极高。 要使哈希连接有效,需要设置HASH_JOIN_ENABLED=TRUE,缺省情况下该参数为TRUE,另外,不要忘了还要设置hash_area_size参数,以使哈希连接高效运行,因为哈希连接会在该参数指定大小的内存中运行,过小的参数会使哈希连接的性能比其他连接方式还要低。 哈希连接 (Hash Join, HJ) HASH连接的例子: SQL> explain plan for select /*+ use_hash(emp) */ empno from emp, dept where emp.deptno = dept.deptno; Query Plan ---------------------------- SELECT STATEMENT [CHOOSE] Cost=3 HASH JOIN TABLE ACCESS FULL DEPT TABLE ACCESS FULL EMP
  • 169. 当两个row source做连接,但是它们之间没有关联条件时,就会在两个row source中做笛卡儿乘积,这通常由编写代码疏漏造成(即程序员忘了写关联条件)。笛卡尔乘积是一个表的每一行依次与另一个表中的所有行匹配。在特殊情况下我们可以使用笛卡儿乘积,如在星形连接中,除此之外,我们要尽量避免使用笛卡儿乘积。 注意在下面的语句中,在2个表之间没有连接。 SQL> explain plan for select emp.deptno, dept,deptno from emp,dept Query Plan ------------------------------ SLECT STATEMENT [CHOOSE] Cost=5 MERGE JOIN CARTESIAN TABLE ACCESS FULL DEPT SORT JOIN TABLE ACCESS FULL EMP CARTESIAN关键字指出了在2个表之间做笛卡尔乘积。假如表emp有n行,dept表有m行,笛卡尔乘积的结果就是得到n * m行结果。 笛卡儿乘积(Cartesian Product)
  • 170. 在哪种情况下用哪种连接方法比较好: 排序 - - 合并连接(Sort Merge Join, SMJ): 对于非等值连接,这种连接方式的效率是比较高的。 如果在关联的列上都有索引,效果更好。 对将2个较大的row source做连接,该连接方法比NL连接要好些。 但是如果sort merge返回的row source过大,则又会导致使用过 多的rowid在表中查询数据时,数据库性能下降,因为过多的I/O。 嵌套循环(Nested Loops, NL): 如果driving row source(外部表)比较小,并且在inner row source(内部表)上有唯一索引,或有高选择性非唯一索引时,使用这种方法可以得到较好的效率。 NESTED LOOPS有其它连接方法没有的的一个优点是:可以先返回已经 连接的行,而不必等待所有的连接操作处理完才返回数据,这可以实现快速的响应时间。 总 结
  • 171. 在哪种情况下用哪种连接方法比较好: 哈希连接(Hash Join, HJ): 此方法是在oracle7后来引入的,使用了比较先进的连接理论, 其效率应该好于其它2种连接,但是这种连接只能用在 CBO优化器中,而且需要设置合适的hash_area_size参数,才能 取得较好的性能。 在2个较大的row source之间连接时会取得相对较好的效率,在一 个 row source较小时则能取得更好的效率。 只能用于等值连接中 总 结
  • 172. 内容提纲Oracle的执行计划 优化定义 概念分析 执行计划分析 优化器选择 访问数据存取方法 表间连接 如何产生执行计划 如何分析执行计划 如何干预执行计划 综合案例分析
  • 173. 要为一个语句生成执行计划,可以有3种方法: 1).最简单的办法 Sql> set autotrace on Sql> select * from dual; 执行完语句后,会显示explain plan 与 统计信息。 这个语句的优点就是它的缺点,这样在用该方法查看执行时间较长的sql语句时,需要等待该语句执行成功后,才返回执行计划,使优化的周期大大增长。 如果不想执行语句而只是想得到执行计划可以采用: Sql> set autotrace traceonly 这样,就只会列出执行计划,而不会真正的执行语句,大大减少了优化时间。虽然也列出了统计信息,但是因为没有执行语句,所以该统计信息没有用处,如果执行该语句时遇到错误,解决方法为: a. 在要分析的用户下: Sqlplus > @ ?\rdbms\admin\utlxplan.sql b. 用sys用户登陆 Sqlplus > @ ?\sqlplus\admin\plustrce.sql Sqlplus > grant plustrace to user_name; -- user_name 是上面所说的分析用户 如何产生执行计划
  • 174. 2).用explain plan命令 (1) sqlplus > @ ?\rdbms\admin\utlxplan.sql (2) sqlplus > explain plan set statement_id =’???’ for select ……………… 注意: 此方法时,并不执行sql语句,所以只会列出执行计划,不会列出统计信息,并且执行计划只存在plan_table中。所以该语句比起set autotrace traceonly可用性要差。需要用下面的命令格式化输出,所以这种方式我用的不多: 如何产生执行计划 set linesize 150 set pagesize 500 col PLANLINE for a120 SELECT EXECORD EXEC_ORDER, PLANLINE FROM (SELECT PLANLINE, ROWNUM EXECORD, ID, RID FROM (SELECT PLANLINE, ID, RID, LEV FROM (SELECT lpad(' ',2*(LEVEL),rpad(' ',80,' '))|| OPERATION||' '|| -- Operation DECODE(OPTIONS,NULL,'','('||OPTIONS || ') ')|| -- Options DECODE(OBJECT_OWNER,null,'','OF '''|| OBJECT_OWNER||'.')|| -- Owner DECODE(OBJECT_NAME,null,'',OBJECT_NAME|| ''' ')|| -- Object Name DECODE(OBJECT_TYPE,null,'','('||OBJECT_TYPE|| ') ')|| -- Object Type DECODE(ID,0,'OPT_MODE:')|| -- Optimizer DECODE(OPTIMIZER,null,'','ANALYZED','', OPTIMIZER)|| DECODE(NVL(COST,0)+NVL(CARDINALITY,0)+NVL(BYTES,0), 0,null,' (COST='||TO_CHAR(COST)||',CARD='|| TO_CHAR(CARDINALITY)||',BYTES='||TO_CHAR(BYTES)||')') PLANLINE, ID, LEVEL LEV, (SELECT MAX(ID) FROM PLAN_TABLE PL2 CONNECT BY PRIOR ID = PARENT_ID AND PRIOR STATEMENT_ID = STATEMENT_ID START WITH ID = PL1.ID AND STATEMENT_ID = PL1.STATEMENT_ID) RID FROM PLAN_TABLE PL1 CONNECT BY PRIOR ID = PARENT_ID AND PRIOR STATEMENT_ID = STATEMENT_ID START WITH ID = 0 AND STATEMENT_ID = 'aaa') ORDER BY RID, -LEV)) ORDER BY ID;
  • 175. 2).用explain plan命令 以上2种方法只能为在本会话中正在运行的语句产生执行计划,即我们需要已经知道了哪条语句运行的效率很差,我们是有目的只对这条SQL语句去优化。在很多情况下,我们只会听一个客户抱怨说现在系统运行很慢,而我们不知道是哪个SQL引起的。此时有许多现成的语句可以找出耗费资源比较多的语句,如: SELECT ADDRESS, substr(SQL_TEXT,1,20) Text, buffer_gets, executions, buffer_gets/executions AVG FROM v$sqlarea WHERE executions>0 AND buffer_gets > 100000 ORDER BY 5; 从而对找出的语句进行进一步优化。还可以为一个正在运行的会话中运行的所有SQL语句生成执行计划,这需要对该会话进行跟踪,产生trace文件,然后对该文件用tkprof程序格式化一下,这种得到执行计划的方式很有用,因为它包含其它额外信息,如SQL语句执行的每个阶段(如Parse、Execute、Fetch)分别耗费的各个资源情况(如CPU、DISK、elapsed等)。 如何产生执行计划
  • 176. 3).用dbms_system存储过程生成执行计划 使用dbms_system存储过程可以跟踪另一个会话发出的sql语句,并记录所使用的执行计划,而且还提供其它对性能调整有用的信息。这种方法是对SQL进行调整比较有用的方式之一,有些情况下非它不可。具体内容后面介绍。 如何产生执行计划
  • 177. 内容提纲Oracle的执行计划 优化定义 概念分析 执行计划分析 优化器选择 访问数据存取方法 表间连接 如何产生执行计划 如何分析执行计划 如何干预执行计划 综合案例分析
  • 178. 通过如下示例进行分析演示: 例1: 假设LARGE_TABLE是一个较大的表,且username列上没有索引,则 运行下面的语句: SQL> SELECT * FROM LARGE_TABLE where USERNAME = ‘TEST’; Query Plan ----------------------------------------- SELECT STATEMENT Optimizer=CHOOSE (Cost=1234 Card=1 Bytes=14) TABLE ACCESS FULL LARGE_TABLE [:Q65001] [ANALYZED] 在这个例子中,TABLE ACCESS FULL LARGE_TABLE是第一个操作 ,意思是在LARGE_TABLE表上做全表扫描。当这个操作完成之后,产 生的row source中的数据被送往下一步骤进行处理,在此例中, SELECT STATEMENT 操作是这个查询语句的最后一步。 Optimizer=CHOOSE 指明这个查询的optimizer_mode,即 optimizer_mode 初始化参数指定的值,它并不是指语句执行时真的使用 了该优化器。决定该语句使用何种优化器的唯一方法是看后面的cost部 分。如果给出的是下面的形式,则表明使用的是CBO优化器,此 处的cost表示优化器认为该执行计划的代价: SELECT STATEMENT Optimizer=CHOOSE (Cost=1234 Card=1 Bytes=14) 如何分析执行计划
  • 179. 假如执行计划中给出的是类似下面的信息,则表明是使用RBO优化器,因为cost部分的值为空,或者压根就没有cost部分。 SELECT STATEMENT Optimizer=CHOOSE Cost= SELECT STATEMENT Optimizer=CHOOSE 这样我们从Optimizer后面的信息中可以得出执行该语句时到底用了 什么样的优化器。特别的,如果Optimizer=ALL_ROWS| FIRST_ROWS| FIRST_ROWS_n,则使用的是CBO优化器; 如果Optimizer=RULE,则使用的是RBO优化器。 cost属性的值是一个在oracle内部用来比较各个执行计划所耗费代价的值,从而使优化器可以选择最好的执行计划。不同语句的cost值不具有可比性,只能对同一个语句的不同执行计划的cost值进行比较。 [:Q65001] 表明该部分查询是以并行方式运行的。里面的数据表示这个操作是由并行查询的一个slave进程处理的,以便该操作可以区别于串行执行的操作。 [ANALYZED] 表明操作中引用的对象被分析过了,在数据字典中有该对象的统计信息可以供CBO使用。 如何分析执行计划
  • 180. 例2: 假定A、B、C都是不是小表,且在A表上一个组合索引:A(a.col1,a.col2) ,注意a.col1列为索引的引导列。 考虑下面的查询: 在表做连接时,只能2个表先做连接,然后将连接后的结果作为一个row source,与剩下的表做连接,在上面的例子中,连接顺序为B与A先连接,然后再与C连接: B <---> A <---> C col3=10 col3=5 如何分析执行计划
  • 181. 如果没有执行计划,分析一下,上面的3个表应该拿哪一个作为第一个驱动表?从SQL语句看来,只有B表与C表上有限制条件,所以第一个驱动表应该为这2个表中的一个,到底是哪一个呢? B表有谓词B.col3 = 10,这样在对B表做全表扫描的时候就将where子句中的限制条件(B.col3 = 10)用上,从而得到一个较小的row source, 所以B表应该作为第一个驱动表。而且这样的话,如果再与A表做关联,可以有效利用A表的索引(因为A表的col1列为leading column)。 当然上面的查询中C表上也有谓词(C.col3 = 5),有人可能认为C表作为第一个驱动表也能获得较好的性能。让我们再来分析一下:如果C表作为第一个驱动表,则能保证驱动表生成很小的row source,但是看看连接条件A.col2 = C.col2,此时就没有机会利用A表的索引,因为A表的col2列不为leading column,这样nested loop的效率很差,从而导致查询的效率很差。所以对于NL连接选择正确的驱动表很重要。 如何分析执行计划
  • 182. 基于以上原则:上面查询比较好的连接顺序为(B - - > A) - - > C。如果数据库是基于代价的优化器,它会利用计算出的代价来决定合适的驱动表与合适的连接顺序。一般来说,CBO都会选择正确的连接顺序,如果CBO选择了比较差的连接顺序,我们还可以使用ORACLE提供的hints来让CBO采用正确的连接顺序。 如下所示: select /*+ ordered */ A.col4 from B,A,C where B.col3 = 10 and A.col1 = B.col1 and A.col2 = C.col2 and C.col3 = 5 如何分析执行计划
  • 183. 通过执行计划如何判断驱动表: 在执行计划中,需要知道哪个操作是先执行的,哪个操作是后执行的,这对于判断哪个表为驱动表有用处。判断之前,如果对表的访问是通过rowid,且该rowid的值是从索引扫描中来得,则将该索引扫描先从执行计划中暂时去掉。然后在执行计划剩下的部分中,判断执行顺序的指导原则就是:最右、最上的操作先执行。具体解释如下: 得到去除妨碍判断的索引扫描后的执行计划: Execution Plan ----------------------------------------------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 MERGE JOIN 2 1 SORT (JOIN) 3 2 NESTED LOOPS 4 3 TABLE ACCESS (FULL) OF 'B' 5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A' 7 1 SORT (JOIN) 8 7 TABLE ACCESS (FULL) OF 'C'如何分析执行计划
  • 184. 通过执行计划如何判断驱动表: 谈论上下关系时,只对连续的、缩进一致的行有效。 从这个图中我们可以看到,对于NESTED LOOPS部分,最右、最上的操作是TABLE ACCESS (FULL) OF 'B',所以这一操作先执行,所以该操作对应的B表为第一个驱动表(外部表),自然,A表就为内部表了。从图中还可以看出,B与A表做嵌套循环后生成了新的row source ,对该row source进行来排序后,与C表对应的排序了的row source(应用了C.col3 = 5限制条件)进行MSJ连接操作。所以从上面可以得出如下事实:B表先与A表做嵌套循环,然后将生成的row source与C表做排序—合并连接。 如何分析执行计划
  • 185. 通过执行计划如何判断驱动表: 通过分析上面的执行计划,我们不能说C表一定在B、A表之后才被读取,事实上,B表有可能与C表同时被读入内存,因为将表中的数据读入内存的操作可能为并行的。事实上许多操作可能为交叉进行的,因为ORACLE读取数据时,如果就是需要一行数据也是将该行所在的整个数据块读入内存,而且还有可能为多块读。 看执行计划时,我们的关键不是看哪个操作先执行,哪个操作后执行,而是关键看表之间连接的顺序(如得知哪个为驱动表,这需要从操作的顺序进行判断)、使用了何种类型的关联及具体的存取路径(如判断是否利用了索引) 在从执行计划中判断出哪个表为驱动表后,根据我们的知识判断该表作为驱动表(就像上面判断ABC表那样)是否合适,如果不合适,对SQL语句进行更改,使优化器可以选择正确的驱动表。 如何分析执行计划
  • 186. 优化器分析 练习: 如何分析执行计划
  • 187. 内容提纲Oracle的执行计划 优化定义 概念分析 执行计划分析 优化器选择 访问数据存取方法 表间连接 如何产生执行计划 如何分析执行计划 如何干预执行计划 综合案例分析
  • 188. 使用hints提示: 基于代价的优化器在绝大多数情况下它会选择正确的优化器,减轻了DBA的负担。但有时它也聪明反被聪明误,选择了很差的执行计划,使某个语句的执行变得奇慢无比。此时就需要DBA进行人为的干预,告诉优化器使用我们指定的存取路径或连接类型生成执行计划,从而使语句高效的运行。例如: 对于一个特定的语句,执行全表扫描要比执行索引扫描更有效,则我们可以指示优化器使用全表扫描。在ORACLE中,是通过为语句添加hints(提示)来实现干预优化器优化的目的。 hints是oracle提供的一种机制,用来告诉优化器按照我们的告诉它的方式生成执行计划。可以用hints来实现: 1) 使用的优化器的类型 2) 基于代价优化器的优化目标,是all_rows还是first_rows。 3) 表的访问路径,是全表扫描、索引扫描,还是直接利用rowid。 4) 表之间的连接类型 5) 表之间的连接顺序 6) 语句的并行程度 如何干预执行计划
  • 189. 如何使用hints: Hints只应用在它们所在sql语句块(statement block,由select、update、delete关键字标识)上,对其它SQL语句或语句的其它部分没有影响。如:对于使用union操作的2个sql语句,如果只在一个sql语句上有hints,则该hints不会影响另一个sql语句。 可以使用注释(comment)来为一个语句添加hints,一个语句块只能有一个注释,而且注释只能放在SELECT, UPDATE, or DELETE关键字的后面。 使用hints的语法: {DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */ or {DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]... 注解: 1) DELETE、INSERT、SELECT和UPDATE是标识一个语句块开始的关键字,包含提示的注释只能出现在这些关键字的后面,否则提示无效。 2) “+”号表示注释是一个hints,加号必须立即跟在”/*”后面,中间不能有空格。 3) hint是下面介绍的具体提示之一,如果包含多个提示,则每个提示之间需要用一个或多个空格隔开。 4) text 是其它说明hint的注释性文本 如果你没有正确的指定hints,Oracle将忽略该hints,并且不会给出任何错误。 如何干预执行计划
  • 190. 使用全套的hints: 当使用hints时,在某些情况下,为了确保让优化器产生最优的执行计划,我们可能指定全套的hints。例如,如果有一个复杂的查询,包含多个表连接,如果你只为某个表指定了INDEX提示(指示存取路径在该表上使用索引),优化器需要来决定其它应该使用的访问路径和相应的连接方法。因此,即使你给出了一个INDEX提示,优化器可能觉得没有必要使用该提示。这是由于我们让优化器选择了其它连接方法和存取路径,而基于这些连接方法和存取路径,优化器认为用户给出的INDEX提示无用。为了防止这种情况,我们要使用全套的hints,如:不但指定要使用的索引,而且也指定连接的方法与连接的顺序等。 下面是一个使用全套hints的例子,ORDERED提示指出了连接的顺序,而且为不同的表指定了连接方法: SELECT /*+ ORDERED INDEX (b, jl_br_balances_n1) USE_NL (j b) USE_NL (glcc glf) USE_MERGE (gp gsb) */ b.application_id, b.set_of_books_id , b.personnel_id, p.vendor_id Personnel, p.segment1 PersonnelNumber, p.vendor_name Name FROM jl_br_journals j, jl_br_balances b, gl_code_combinations glcc, fnd_flex_values_vl glf, gl_periods gp, gl_sets_of_books gsb, po_vendors p WHERE ... 如何干预执行计划
  • 191. 指示优化器的方法与目标的hints: ALL_ROWS -- 基于代价的优化器,以吞吐量为目标 FIRST_ROWS(n) -- 基于代价的优化器,以响应时间为目标 CHOOSE -- 根据是否有统计信息,选择不同的优化器 RULE -- 使用基于规则的优化器 例子: SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_id FROM employees WHERE department_id = 20; SELECT /*+ CHOOSE */ employee_id, last_name, salary, job_id FROM employees WHERE employee_id = 7566; SELECT /*+ RULE */ employee_id, last_name, salary, job_id FROM employees WHERE employee_id = 7566; 如何干预执行计划
  • 192. 指示存储路径的hints: FULL /*+ FULL ( table ) */ 指定该表使用全表扫描 ROWID /*+ ROWID ( table ) */ 指定对该表使用rowid存取方法,该提示用的较少 INDEX /*+ INDEX ( table [index]) */ 使用该表上指定的索引对表进行索引扫描 INDEX_FFS /*+ INDEX_FFS ( table [index]) */ 使用快速全表扫描 NO_INDEX /*+ NO_INDEX ( table [index]) */ 不使用该表上指定的索引进行存取,仍然可以使用其它的索引进行索引扫描 SELECT /*+ FULL(e) */ employee_id, last_name FROM employees e WHERE last_name LIKE :b1; SELECT /*+ROWID(employees)*/ * FROM employees WHERE rowid > 'AAAAtkAABAAAFNTAAA' AND employee_id = 155; SELECT /*+ INDEX(A sex_index) use sex_index because there are few male patients */ A.name, A.height, A.weight FROM patients A WHERE A.sex = ’m’; SELECT /*+NO_INDEX(employees emp_empid)*/ employee_id FROM employees WHERE employee_id > 200; 如何干预执行计划
  • 193. 指示连接顺序的hints: ORDERED /*+ ORDERED */ 按from 字句中表的顺序从左到右的连接 STAR /*+ STAR */ 指示优化器使用星型查询 SELECT /*+ORDERED */ o.order_id, c.customer_id, l.unit_price * l.quantity FROM customers c, order_items l, orders o WHERE c.cust_last_name = :b1 AND o.customer_id = c.customer_id AND o.order_id = l.order_id; /*+ ORDERED USE_NL(FACTS) INDEX(facts fact_concat) */ 如何干预执行计划
  • 194. 指示连接类型的hints: USE_NL /*+ USE_NL ( table [,table, ...] ) */ 使用嵌套连接 USE_MERGE /*+ USE_MERGE ( table [,table, ...]) */ 使用排序- -合并连接 USE_HASH /*+ USE_HASH ( table [,table, ...]) */ 使用HASH连接 如何干预执行计划 注意: 如果表有alias(别名),则上面的table指的是表的别名,而不是真实的表名。
  • 195. 案例演示: 测试实例见文档 如何干预执行计划 以上示例得出: 通过给语句添加HINTS,让其按照我们的意愿执行,有时是一件很困难的事情,需要不断的尝试各种不同的hints。对于USE_NL与USE_HASH提示,建议同ORDERED提示一起使用,否则不容易指定那个表为驱动表。
  • 196. 内容提纲Oracle的执行计划 优化定义 概念分析 执行计划分析 优化器选择 访问数据存取方法 表间连接 如何产生执行计划 如何分析执行计划 如何干预执行计划 综合案例分析
  • 197. 案例分析: 见文档 综合案例分析
  • 198. 两个以上的查询都是采用nested loop循环,这时采用哪个表作为driving table就很重要。在第一个sql中,小表(SUPER_USER)作为driving table,符合oracle优化的建议,但是由于SWD_BILLDETAIL表中cn列的值有很多重复的,这样对于SUPER_USER中的每一行,都会在SWD_BILLDETAIL中有很多行,利用索引查询出这些行的rowid很快,但是再利用这些rowid去查询SWD_BILLDETAIL表中的user_class列的值,就比较慢。原因是这些rowid是随机的,而且该表比较大,不可能缓存到内存,所以几乎每次按照rowid查询都需要读物理磁盘,这就是该执行计划比较慢的真正原因。从结果可以得到验证:查询 出1186387行,需要利用rowid从SWD_BILLDETAIL表中读取1186387次,而且大部分为从硬盘上读取。 总 结
  • 199. 如果利用大表(SWD_BILLDETAIL)作为driving表,这样大表只需要做一次全表扫描(而且会使用多块读功能,每次物理I/O都会读取几个oracle数据块,从而一次读取很多行,加快了执行效率),对于读出的每一行,都与SUPER_USER中的行进行匹配,因为SUPER_USER表很小,所以可以全部放到内存中,这样匹配操作就极快,所以该sql执行的时间与SWD_BILLDETAIL表全表扫描的时间差不多(SWD_BILLDETAIL全表用11分钟,而此查询用20分钟)。 另外:如果SWD_BILLDETAIL表中cn列的值唯一,则第一个sql执行计划执行的结果或许也会不错。如果SUPER_USER表也很大,如500万行,则第2个sql执行计划执行的结果反而又可能会差。其实,如果SUPER_USER表很小,则第2个sql语句的执行计划如果不利用SUPER_USER表的索引,查询或许会更快一些,我没有对此进行测试。 总之:在进行性能调整时,具体问题要具体分析,没有一个统一的标准。 总 结
  • 200. 目录 优化基础知识 性能调整综述 有效的应用设计 SQL语句的处理过程 Oracle的优化器 Oracle的执行计划 注意事项
  • 201. 不要认为将optimizer_mode参数设为rule,就认为所有的语句都使用基于规则的优化器。 不管optimizer_mode参数如何设置,只要满足下面3个条件,就一定使用CBO。 1) 如果使用Index Only Tables(IOTs), 自动使用CBO。 2) Oracle 7.3以后,如果表上的Paralle degree option设为>1, 则自动使用CBO, 而不管是否用rule hints。 3) 除rlue以外的任何hints都将导致自动使用CBO来执行语句。注意事项
  • 202. 一个语句在运行时到底使用何种优化器可以从下面的表格中识别出来,从上到下看你的语句到底是否满足description列中描述的条件: Description 对象是否被分析 优化器的类型 ~~~~~~~~~~~ ~~~~~~~~~~~~ ~~~~~~~~~ Non-RBO Object (Eg:IOT) n/a #1 Parallelism > 1 n/a #1 RULE hint n/a RULE ALL_ROWS hint n/a ALL_ROWS FIRST_ROWS hint n/a FIRST_ROWS *Other Hint n/a #1 OPTIMIZER_GOAL=RULE n/a RULE OPTIMIZER_GOAL=ALL_ROWS n/a ALL_ROWS OPTIMIZER_GOAL=FIRST_ROWS n/a FIRST_ROWS OPTIMIZER_GOAL=CHOOSE NO RULE OPTIMIZER_GOAL=CHOOSE YES ALL_ROWS #1 表示除非OPTIMIZER_GOAL 被设置为FIRST_ROWS ,否则将使用 ALL_ROWS。在PL/SQL中,则一直是使用ALL_ROWS 。 *Other Hint 表示是指除RULE、ALL_ROWS 和FIRST_ROWS以外的 其它提示 。注意事项
  • 203. 当CBO选择了一个次优化的执行计划时, 不要同CBO过意不去, 先采取如下措施: 1) 检查是否在表与索引上又最新的统计数据 2) 对所有的数据进行分析,而不是只分析一部分数据 3) 检查是否引用的数据字典表,在oracle 10G之前,缺省情况下 是不对数据字典表进行分析的。 4) 试试RBO优化器,看语句执行的效率如何,有时RBO能比 CBO产生的更好的执行计划 5) 如果还不行,跟踪该语句的执行,生成trace信息,然后用 tkprof格式化trace信息,可以得到全面的供优化的信息。 注意事项
  • 204. 假如利用附录的方法对另一个会话进行trace,则该会话 应该为专用连接 。 不要认为绑定变量(bind variables)的缺点只有书写麻烦,而优点多多,实际上使用绑定变量虽然避免了重复parse,但是它导致优化器不能使用数据库中的列统计,从而选择了较差的执行计划。而使用硬编码的SQL则可以使用列统计。当然随着CBO功能的越来越强,这种情况会得到改善。目前就已经实现了在第一次运行绑定变量的sql语句时,考虑列统计。 如果一个row source 超过10000行数据,则可以被认为大row source 。 有(+)的表不是driving table,注意:如果有外联接,而且order hint指定的顺序与外联结决定的顺序冲突,则忽略order hint。注意事项
  • 205. 影响CBO选择execution plan的初始化参数: 这些参数会影响cost值 ALWAYS_ANTI_JOIN B_TREE_BITMAP_PLANS COMPLEX_VIEW_MERGING DB_FILE_MULTIBLOCK_READ_COUNT FAST_FULL_SCAN_ENABLED HASH_AREA_SIZE HASH_JOIN_ENABLED HASH_MULTIBLOCK_IO_COUNT OPTIMIZER_FEATURES_ENABLE OPTIMIZER_INDEX_CACHING OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_MODE> / GOAL OPTIMIZER_PERCENT_PARALLEL OPTIMIZER_SEARCH_LIMIT PARTITION_VIEW_ENABLED PUSH_JOIN_PREDICATE SORT_AREA_SIZE SORT_DIRECT_WRITES SORT_WRITE_BUFFER_SIZE STAR_TRANSFORMATION_ENABLED V733_PLANS_ENABLED CURSOR_SHARING 注意事项
  • 206. 问题: 如何通过跟踪一个客户端程序发出的sql的方法来优化SQL。 概括介绍,跟踪一个客户程序发出的SQL主要分成下面几步: 识别要跟踪的客户端程序到数据库的连接(后面都用session代替),主要找出能唯一识别一个session的sid与serial#。 设定相应的参数,如打开时间开关(可以知道一个sql执行了多长时间),存放跟踪数据的文件的位置、最大值。 启动跟踪功能。 让系统运行一段时间,以便可以收集到跟踪数据。 关闭跟踪功能。 格式化跟踪数据,得到我们易于理解的跟踪结果。
  • 207. (本页无文本内容)