• 1. 第二部分 ORACLE高效SQL分析亿阳信通 无线与交换 MSN: tsingsong@126.com Email:zhangjiahong@boco.com.cn 张家宏 2009-12-021
  • 2. 培训大纲SQL优化基础 Oracle内存空间 SQL的执行过程 使SQL语句共享 Oracle优化器 数据读取方式(全表,ROWID,索引) 表连接实现方式(SMJ,NL,HJ) 用提示调整执行计划 用OutLine固定执行计划2
  • 3. Oracle 内存结构SGA+PGASGA(System Global Area )是Oracle各个进程共用的一块巨大的共享内存区域。Data Buffer Cache: 缓存从磁盘读取的数据,Oracle所有的数据处理都在该内存区完成之后再写入磁盘Shared pool 共 享 池Redo log bufferJava PoolLarge PoolData Dictionary Cache:缓冲解析SQL或其他Oracle运作需要的数据字典SQL Library Cache:缓存解析后的SQL信息,供下次直接使用,避免再次解析3
  • 4. SGA和高效SQLSGA是Oracle 运作的核心区域,分析SGA的作用,不难得出以下结论: 当该次访问的SQL语句已经缓存在SGA中,那么该SQL就很可能不用再解析,缩短了SQL的执行时间; 当该次访问的数据已经缓存在SGA中,那么很可能就不会再从磁盘读取了,减少了磁盘的I/O; 在SGA中的数据和SQL越多,I/O开销就越少,性能就越好;4
  • 5. SQL执行过程概述方式一: 硬解析:SQL初次执行 (1)语法检测(selec<>select) (2)语义检查(对象,权限?) 优化器(Optimizer) (1)WHERE子句信息 (2)优化模式(CBO,RBO) (3)生成执行计划Plan 执行Plan取得所需数据 方式二 软解析:SQL及Plan已在SGA中 直接执行Plan取得所需数据 SQL共享可以实现软解析!5
  • 6. SQL共享实现高速软解析SQL共享是实现软解析的方法,基本条件: SQL语句文本的ASCII码值相同; 比如:大小写,空格,换行等等; 例如: SELECT * FROM TB_EMP; 和下列每一个都不同 SELECT * from TB_EMP; (大小写) SELECT * FROM TB_EMP;(空格不对) SELECT * FROM TB_EMP; (换行) SQL语句所指的对象必须完全相同; 表TB_ABC:select count(1) from tb_abc 和同义词TB_ABC: select count(1) from tb_abc 是不相同的;6
  • 7. SQL变量绑定实现高速软解析绑定变量并且同名 如有以下100条语句: select name from tb_a where pin = 1; select name from tb_a where pin = 2; …… select name from tb_a where pin = 100; 上述100语句仅仅只是WHER pin= 值不同,都需要硬解析,如果将pin 的值 1…100 先分别赋给变量v_pin,再用select name from tb_a where pin = :v_pin 就 可以使上述100条SQL语句共享的目的! 注意,变量需同名 如下变量绑定的sql之间不会共享: select name from tb_a where pin = :v_pin select name from tb_a where pin = :t_pin7
  • 8. JAVA中的变量绑定oracle绑定变量的java 代码如下: String v_id = 'xxxxx'; //嵌入绑定变量 String v_sql = 'select name from table_a where id = ? '; stmt = con.prepareStatement( v_sql ); stmt.setString(1, v_id ); //为绑定变量赋值 stmt.executeQuery();8
  • 9. RBO(Rule-Based Optimizer)CBO(Cost-Based Optimizer)优化器(Optimizer)的操作步骤: 计算表达式和条件 语句转换:eg对View的访问转换为对基表的访问 选择优化模式:CBO还是RBO 选择访问路径:where子句,确定可用访问路径,索引扫描...... 选择join顺序:from,统计,驱动表,第1步,第2步...... 选择join方法,Hash Join/Nest Loop/Sort-Merge 生成执行计划(RBO),或者生成各执行计划并选成本最低的计划(CBO)优化器及模式(where /from)Statistics 统计信息执行计划(where /from)Oracle 规则执行计划9
  • 10. CBO(Cost Based Optimizer) ——依据统计分析估算成本并选择最低成本的执行计划 Oracle默认模式是CHOOSE ——CBO可用则用不然用RBO; 使用CBO必须有统计信息: analyze table abc compute statistics; analyze table abc estimate statistics sample 20 percent; 提示与CBO: (1)除了rule之外的提示都会会激发 CBO 优化器; (2)如果有提示(hints)存在,那么会依提示确定获取路径; (3)对有提示的语句中引用的表和索引进行统计; FIRST ROWS:CHOOSE+优先返回 ALL ROWS:CHOOSE+得到全部值再返回优化模式CBO10
  • 11. 查看SQL执行计划OPTIMIZER当前优化程序的模式对具体执行步骤的描述OBJECT_OWNER相关表或索引的所有者OBJECT_NAME表或索引的名称COSTCBO估算的操作开销(RBO为空),用于比较开销大小的权重值,无单位CARDINALITY CBO下对操作所访问行数的估计值BYTES CBO对操作所访问字节的估计执行计划主要查看:访问路径,连接顺序,连接方法11
  • 12. 怎么看SqlPlan的执行顺序方法一:使用工具中的四个小箭头; 方法二:内上原则:内层先执行,同层次时上层先执行。12
  • 13. SQL访问基础Oracle访问数据的存取方法 1、全表扫描(Full Table Scans, FTS) 2、用ROWID访问表(Table Access by ROWID) 3、索引扫描(Index Scan) (3.1)索引唯一扫描(index unique scan) (3.2)索引范围扫描(index range scan) (3.3)索引全扫描(index full scan) (3.4)索引快速扫描(index fast full scan) 表之间的连接方法 排序合并连接(Sort Merge Join, SMJ) 嵌套循环(Nested Loops, NL) 哈希连接(Hash Join, HJ) 13
  • 14. Oracle访问数据之全表扫描全表扫描(Full Table Scans, FTS) (1)全表扫描: Oracle顺序地读取分配给表的每个数据块的每一行,并检查每一行是否满足语句的WHERE限制条件,直到读到表的最高水线处(high water mark, HWM,标识表的最后一个数据块)。 (2)可设定一次I/O读取多块数据块(db_file_multiblock_read_count参数),这极大的减少了I/O次数,可以十分高效地完成全表扫描,而且只有在全表扫描的情况下才能使用多块读操作。 (3)由于delete操作不影响HWM值,所以delete一个表后,其全表扫描的时间不会有改善。 (4)缩减HWM的方法(a)truncate命令使HWM值归为0。(b)oracle 10G后,可以Shrink收缩HWM的值,行级锁。(c)alter table MOVE, 由FTS模式读入的数据被放到高速缓存的Least Recently Used (LRU)列表的 尾部,这样可以使其快速交换出内存,不会影响内存中重要的数据。 使用FTS的前提条件:大表上不建议使用全表扫描,除非取出数据的比 较多,超过总量的10%,或你想使用并行查询功能时。14
  • 15. 使用全表扫描的场合在必须使用全表扫描的场合: (1)无可用索引; (2)返回的数据量为大表的25%左右; (3)对小表的访问; (4)SQL中有全表扫描提示/*+ full (tab)*/; 提高全表扫描的方法: (1)db_file_multiblock_read (2)/*+ full (tab) parallel(tab,10)*/ 并行度(10):一般为CPU数和表所在的磁盘数的最小值15
  • 16. 避免全表扫描的场合在不必使用全表扫描时:消除全表扫描导致全表扫描的问题解决方法没有索引建立索引,索引提示(hints)使用NULL : where col is null内置函数索引:nvl(col,0)=0使用col like ‘%abc’反向索引:reverse(col) like ‘%abc’使用了不等: <>, != , not in转化逻辑,关联查询内置函数索引使用不当规范使用16
  • 17. Oracle访问数据之用ROWID访问用ROWID访问表(Table Access by ROWID)最快! ——行的ROWID指出了该行所在的数据文件、数据块以及行在块中的位 置,所以通过ROWID来存取数据可以快速定位到目标数据上,是Oracle 存取单行数据的最快方法。 ——为了通过ROWID存取表,Oracle 首先要获取被选择行的ROWID,或 者从语句的WHERE子句中得到,或者通过表的一个或多个索引的索引扫 描得到。Oracle然后以得到的ROWID为依据定位每个被选择的行。 ——这种存取方法不会用到多块读操作,一次I/O只能读取一个数据块。 我们会经常在执行计划中看到该存取方法,如通过索引查询数据。 ——ROWID方式几乎不能直接指定(由我们提供数据行的ROWID),而是通过索引扫描来间接的实现,所以本质问题还是索引使用!17
  • 18. Oracle访问数据之索引扫描索引扫描(Index Scan) ——索引(索引值,行ROWID值) ——通过索引值找到行对应的rowid值(可能多个),然后根据rowid直接从表段中得到具体的行数据,这种查找方式称为索引扫描。 一个rowid唯一的指向一行记录所在的数据块,该块通过一次I/O得到的,在此情况下该次I/O只会读取一个数据块。 索引扫描可以由2步组成: 扫描索引得到对应的rowid值,多为逻辑I/O。 ——索引常用,多数已缓存,常为索引深度次逻辑I/O(内存I/O); (2) 依据找到的rowid通过一次I/O从表中读出具体的数据。 ——如果表比较大,则其数据不可能全在内存中,所以其I/O很有可能是物理I/O,相对逻辑I/O来说,是极低效的。 如果大表访问取出的数据大于总量的20%,使用索引扫描是低率的。1818
  • 19. 索引扫描(Index Scan)分类根据索引的类型与where条件的不同,有4种索引扫描方式: 索引唯一扫描(index unique scan):通过唯一索引查找一个数值经常返回单个ROWID。如果存在UNIQUE 或PRIMARY KEY 约束(它保证了语句只存取单行)的话,Oracle经常实现唯一性扫描; 索引范围扫描(index range scan):使用一个索引存取多行数据。(1)唯一索引列在where条件中使用范围运算(<、>=、between等); (2)在组合索引上,只使用部分列进行查询,导致查询出多行; (3)对非唯一索引列上进行的任何查询。 索引全扫描(index full scan):与全表扫描对应,也有相应的全索引扫描。而且此时查询出的数据都必须从索引中可以直接得到; 索引快速扫描(index fast full scan):扫描索引中的所有的数据块,与 index full scan很类似,但是区别是它不对查询出的数据进行排序,即数据不是以排序顺序被返回。在这种存取方法中,可以使用多块读功能,也可以使用并行读入,以便获得最大吞吐量与缩短执行时间;19
  • 20. 用提示指定索引扫描使用方法:/*+index(tb_name,idx_name)*/ 注意:(1)tb_name和idx_name必须拼写正确,不然提示无效; (2)tb_name 在from之后有别名时使用别名,不然提示无效; (3)tb_name不可缺少,idx_name可忽略,最好指定,以防不确定性; Select /*+ index(e)*/ * from tfa_alarm_act e;正确但缺索引不好 以下都不会使用索引提示: Select /*+ index(alarm,ix3_alarm_no)*/ * from tfa_alarm;表名不对 Select /*+ index(ix3_alarm_no)*/ * from tfa_alarm_act;缺表名 Select /*+ index(tfa_alarm,ix3_alarm_no)*/ * from tfa_alarm e;用别名 ——在用正确指定了索引提示后,具体是哪种索引扫描方式,将由优化器根据索引的类型,where条件情况决定!20
  • 21. SQL执行计划显示索引扫描,以及使用了何种索引:21科学利用索引,是进行SQL优化的重要技能!索引扫描查看21
  • 22. 表之间的连接方法 排序合并连接(Sort Merge Join, SMJ) 嵌套循环(Nested Loops, NL) 哈希连接(Hash Join, HJ) 22
  • 23. 排序合并连接(SortMergeJoin, SMJ)排序合并连接的方法非常简单。两个互相连接的表按连接列的值先排序,两个排序后的结果集再更具WHERE条件进行合并连接,提取符合条件的记录。23
  • 24. SMJ特点 对于非等值连接,效率比较高。 b) 如果在关联的列上都有索引,效果更好。 c) 对2个较大的row source做SMJ,比NL要好。 d) 如果最终返回的数据量过大,SORT_AREA_SIZE排序内存有限,则会导致使用TEMP表空间进行排序的I/O过多,使数据库性能下降。24
  • 25. 排序合并连接(Sort Merge Join, SMJ)执行计划展示25
  • 26. 嵌套循环(Nested Loops, NL)嵌套连接:一个2层嵌套循环,先选定一个表作为驱动表(外部表),取它的每一行去和另一个表(内部表)的所有行进行比较而得到所需数据。 一般驱动表采用全表扫描,内部表采用索引访问,所以驱动表越小(或者通过其他条件过滤之后返回数据少),外层循环的次数越少,效率越高,这就是“将小表或返回较小 row source的表作为驱动表(用于外层循环)”的理论依据。 使用嵌套连接方法中,决定使用哪个表作为驱动表很重要。有时如果驱动表选择不正确,将会导致语句的性能很差、很差。 “小表作驱动表”——这个只是一般指导原则,并非总能保证最少的I/O。 特点: (1)如果外部表(驱动表)比较小,并且在内部表上有唯一索引,或有高选择性非唯一索引时,使用这种方法可以得到较好的效率。 (2)NL方法有其它连接方法没有的优点是:可以先返回已经连接的行,而不必等待所有的连接操作处理完才返回数据,这可以实现快速的响应时间。26
  • 27. 嵌套循环(Nested Loops, NL)的执行计划展示一般驱动表采用全表扫描,内部表采用索引访问,但是也常能看到由于外部表在where中的列适合做索引扫描。27
  • 28. NL驱动表选择:现象NL连接方式中驱动表(小表)在后,不具有普遍性,需使用执行计划进行确定,或者还和其他条件过滤之后的数据量有关系!28
  • 29. NL驱动表选择:分析例如: 有两个表: tb_l(little): 5万 (小表), tb_b(big): 400万 (大表), 他们每一条记录都恰好占用一个块BLOCK,因此读一行需要一个I/O • 二者在连接列上都有唯一性索引 • 对于ta_1:记录少,设索引深度为2,则使用索引读一行需 (2索引块+1数据块)=3个I/O • 对于tb_b:记录多,设索引深度为3,则使用索引读一行需 (3索引块+1数据块)=4个I/O 分析如下SQL采用NL连接方式进行查询的I/O开销情况? SELECT * FROM tb_l , tb_b where tb_l .col1 = tb_b .col2 分析: 1)设TB_L为驱动表,TB_L全表扫描使每一行与索引读取的TB_B表比较: I/O开销:TB_L全表读5万+4个(TB_B索引读一行)*5万(TB_L行数)=25万; 2)设TB_B为驱动表,TB_B全表扫描使每一行与索引读取的TB_L表比较: I/O开销:TB_B全表读400万+3个(TB_L索引读一行)*400万(TB_B行数)=1600万;29
  • 30. NL驱动表选择:结论(1)NL连接方式驱动表的选择至关重要; (2)在RBO模式下,驱动表在FROM子句的最外面; (3)在CBO模式下,驱动表一般在FROM后的第一个,但Oracle还会根据统计信息做动态选择 ——故驱动表的确定请以实际的执行计划为准!; (4)驱动表在NL的执行计划中,是第一步执行; (5)对于A,AB,B三个表,选AB表作为驱动表; (6)驱动表常发生的执行方式为: 全表扫描(TABLE ACCESS FULL SCAN) 或者索引范围扫描(INDEX RANGE SCAN) (7)决定驱动表的提示:ordered,swap_join_inputs, leading30
  • 31. 用提示控制NL的驱动表1./*+use_nl(t2,t) */ 仅仅提示oracle走nest loop,没有指定任何驱动表 2./*+ ordered use_nl(t2,t) */走nest loop,order指定以from后面的第一个表做为驱动表。 3./*+ leading(t2) use_nl(t) */ 走nest loop,提示t2为驱动表。 结论:use_nl不能让优化器确定谁是驱动表谁是被驱动的表,use_nl(t,t2)也没有指出哪个是驱动表,这时候我们需要使用ordered,leading来强制指定驱动表,以达到我们的目的。31
  • 32. ordered这个HINT是表示表的连接顺序按from后面表的顺序依次连接的,有多张表关联的时候也是走这个顺序,在多表关联的时候稳定执行计划这个HINT用得比较多,要注意from后面表的连接顺序不能随意更改。 leading这个HINT主要是用来明确指定连接中的驱动表。 swap_join_inputs:比如有A、B、C三张表需要关联,a.col=b.col and a.col1=c.col1,这时候理想的执行计划是A和B先HASH JOIN,得出来的结果集再和C关联,由于相对来说C和A、B得出的结果集相比还是小表,显然用C来做为二次HASH JOIN的驱动表比较合适,那么就可以用swap_join_inputs来控制了。 32
  • 33. 用提示控制NL的驱动表示例3333
  • 34. 哈希连接(Hash Join, HJ) 哈希链接:在哈希连接中,Oracle访问一张表(驱动表,通常是较小的表),并在特定内存区(hash_area_size )中建立一张基于连接键的哈希表。然后它扫描连接中其他的表(通常是较大的表),并根据哈希表检测是否有匹配的记录。 哈希连接有效的条件: 设置hash_join_enabled = true,缺省为 TRUE 设置hash_area_size参数为适当的值,哈希连接会在该参数指定大小的内存中运行,过小的参数会使哈希连接的性能比其他连接方式还要低。 特点: a) 只能用于等值连接中; b) 这种连接方法,理论先进,其效率应该最高,但是它只能用在CBO优化器中,且需要设置合适的hash_area_size参数,才能取得较好的性能。 c) 在2个较大的row source之间连接时会取得相对较好的效率,在一个row source较小时则能取得更好的效率。34
  • 35. 哈希连接(Hash Join, HJ)的执行计划展示135
  • 36. 哈希连接(Hash Join, HJ)的执行计划展示236
  • 37. 三种表连接的对比排序合并连接SMJ (Sort Merge Join) 嵌套循环NL (Nested Loops) 哈希连接HJ (Hash Join) a)非等值连接效率较高 b)关联列都有索引效果好 c) 两大表做连接比NL好 d) 如果最终返回的数据 量过大,sort_area_size有限,会导致使用TEMP表空间排序的I/O过多,使数据库性能下降。a)外部表比较小,内部表 有唯一索引/高选择性非唯 一索引时效率好,否则效 率会很低。驱动表很大, 不如用其他方式; b) NL特有优点是:可先返 回已经连接的行,而不必 等待所有的连接操作处理 完才返回数据,这可以实 现快速的响应时间。a)只能用户CBO优化器中 的于等值连接中! b)一般是最高效率,合理 设置hash_area_size, 能得较好性能。 c)当2个较大的row source 连接时会取得相对较好的 效率,在一个row source 较小时则效率更好。37
  • 38. 使用提示指定表连接排序合并连接SMJ (Sort Merge Join) 嵌套循环NL (Nested Loops) 哈希连接HJ (Hash Join) (1)两大表范围查询 (2)SORT_AREA_SIZE外部表小, 内部表索引选择性好(1)CBO/统计数字/等值(2)HASH_AREA_SIZESelect /*+use_merge(e,b) parallel(e,4)parallel(b,4)+/ e.name,hiredate,b.comm From emp e,bonus b Where e.ename=b.ename;Select /*+use_nl(e,b) parallel(b,4)+/ e.name,hiredate,b.comm From emp e,bonus b Where e.ename=b.ename;Select /*+use_hash(e,b) parallel(b,4)+/ e.name,hiredate,b.comm From emp e,bonus b Where e.ename=b.ename;38
  • 39. SQL语句,执行计划,表访问方式之间的关系39SQL源代码完全表访问全表 扫描排序合并链接散列 链接嵌套循环链接ROWID访问执行计划表访问方式39
  • 40. 疯狂的提示 4040
  • 41. 疯狂的提示1 4141
  • 42. 疯狂的提示2 4242
  • 43. 疯狂的提示3 4343
  • 44. 疯狂的提示4 4444
  • 45. 疯狂的提示5 4545
  • 46. 在Sql Plus中查看执行计划 46在现场,常使用Sql Plus访问数据库,那么需要使用 SET AUTOTRACE ON查看执行计划SET AUTOTRACE OFF不生成报告,默认设置执行语句SET AUTOTRACE ON EXPLAIN只显示执行计划执行语句SET AUTOTRACE ON STATISTICS 只显示统计信息执行语句SET AUTOTRACE ON 显示执行计划和统计信息 执行语句SET AUTOTRACE TRACEONLY 显示执行计划和统计信息不执行语句前提:创建表PLAN_TABLE: SQL>@?/rdbms/admin/utlxplan.sql;46
  • 47. 4747
  • 48. TOAD查看执行计划 示例4848
  • 49. 固定执行计划 49最了解数据的是我们自己,所以我们更能把握最优的执行计划; 提示帮助我们确定我们想要的执行计划,那么,怎样强迫Oracle使用我们设定的执行计划呢? OutLine为此而生!49
  • 50. 用OutLine固定执行计划 50OutLine机制: ——Outline保存了执行计划的hint在outline的表中。当执行SQL时,Oracle会与outline中的SQL比较,如果该SQL有保存的outline,则通过保存的hint生成执行计划。 使用 OutLine的步骤: (1)生成新老SQL的Outline(两个OutLine) (2)交换两个SQL的提示信息 (3) ON LOGON触发器设定session的CATEGORY(自定义类别) SQL命令行为:SQL> alter session set use_stored_outlines=special;50
  • 51. 5151
  • 52. 5252
  • 53. 5353
  • 54. 5454
  • 55. 高效SQL的脉络55SQL1低效的 执行计划提示 索引高效的 执行计划存储大纲 OutLine 55
  • 56. OutLine在Oracle10.2.0.4中不起作用, 是该版本的一个bug 56Bug 6455659 RDBMS: Stored outlines are not used when expected in a multi byte character set database.56
  • 57. 谢 谢! 5757