SQL语句常用的优化方法.ppt

1、背景：OLTP系统，ORACLE10G,作者: ZALBB,SQL语句常用的调优方法,1 为什么要调优SQL? 2 哪些SQL需要调优? 3 如何获取需要调优的SQL? 4 如何手工调优SQL? 5 另外一些调优方法和工具。 6 11G在执行计划上的一些改进。,目录,为什么要调优SQL？,通常来讲，要打造高效快捷的应用系统，需要从最初的业务需求 入手，在分析、整理出闭环的业务操作流程后，按照范式的要求，尽 量用简单的数据结构，来实现业务的运行和流转（可以考虑对基础数 据作少量的数据冗余，以减少关联）；同时，根据业务的需求，兼考 虑对历史业务数据的迁移，只保留最近一段时期内的数据，以便让系 统轻

2、装运行。 但是，由于业务的复杂性，设计人员的知识、视野、前瞻性等的 局限，在系统结构设计时，难以考虑周全；并且，由于开发人员的 水平参差不齐，编写的代码也存在缺陷。经统计评估，排除系统结构 设计不善导致的因素外，新的应用系统，有80%的效率问题，是因为 低效的SQL导致，这就需要DBA找出这些低效的SQL，加以优化。,例子,哪些SQL需要优化？,运行时间较长的SQL。 逻辑读较高的SQL。 物理读较高的SQL。,从哪里获取需要调优的SQL？,* AWR(ASH,ADDM)， 1 Elapsed Time(含CPU较高者) 2 Buffer Gets 3 Physical Reads,* EM，

3、 性能分析- SQL Tuning,* 当前库， 根据V$SESSION.LAST_CALL_ET，找到运行时间 最长的进程，获取SQL_ID，再找出SQL语句和执行计划。,AWR上要关注的SQL项,如何手工调优SQL?,A 如何获取语句的执行计划？ B 如何解读执行计划中的执行顺序？ C SQL语句的调优原则。 D 一些调优常识。 E 手工调优的粗略思路。 F 10046事件的使用方法。 G 两个案例。,如何获取语句的执行计划？,2 根据SQL_ID查询， select * from table (dbms_xplan.display_cursor( 还有：advanced,typical,

4、serial,basic. v$session.sql_child_number=0,1,3 从视图v$sql_plan 中获取。,1 直接解析SQL语句. Explain plan for XXX； Select * from table(dbms_xplan.display);,如何解读执行计划中的执行顺序？,在获取SQL语句的执行计划后，这样解读执行顺序： * 对同一凹层，先上后下执行， * 对不同凹层，先里后外执行。,对于同一凹层， 先上后下,对于不同凹层，先里后外。所以先NL,后 hash。,真正的执行顺序,执行顺序:3,5,4,2,7,6,1,0,SQL 语句的调优原则,在一个OL

5、TP系统里，优化SQL语句的原则，就是尽量 减少数据的读取。调优的目的，实际是设法让语句在执行过 程中，尽可能地只读取必要的数据，不读或尽量少读不符合 要求的数据。,SQL调优中的一些常识执行计划中涉及的一些概念,* 不论SQL中读取多少个表，在执行过程中，每次都是两个表/结 果集操作，得到新的结果后，再和下一个表/结果集操作， 直到结束。 在一个多表关联的执行计划中，必须包括这3要素： * 表/对象/数据集的读取顺序（ join order ）。 * 数据的读取方法（ access path ）。 * 表/数据的关联方法（join method）。 这3个要素是判断执行计划优秀与否的关键。

6、* 可选择性(Selectivity) ，=0 and =1。 * 预估记录数(Cardinality) ，表/视图/操作后的结果集。 * 开销(Cost) ，CBO选择最佳执行计划的标准：越低越好。,ACCESS和FILTER的区别,在解析出SQL语句的执行计划后，在执行计划的末尾，通常会出现 这些信息：,FILTER 指按照某个条件过滤数据， ACCESS 指按照某个条件/关系获取数据，,在本文中，这样定义此词汇,关联条件：where a.col1 =b.col1， 过滤条件：where a.col1=103（常量），,关联条件，和过滤条件都称为约束条件。,手工调优的粗略思路,1 获取SQ

7、L的执行计划。 2 判断当前的执行计划是否正常： 手工计算Where语句后各过滤条件(非关联条件)的预估数值，找出最强 的过滤条件（过滤后剩余数据最少的条件）。一般来讲，若语句中各对 象的统计信息准确，CBO经过计算后，基本上都是从过滤条件最强的表 开始，判断执行计划是否从此条件开始。 3 检查执行计划中第1步的预估值，是否与实际值相近。否，转步骤7。 4 根据过滤条件判断，数据的读取方式是否合适（读表，读索引，或根据 索引返回原表获取）。 5 找出与第1步要执行的表存在关联关系的表，根据其过滤后的结果集判断 ，两表间的关联方法是否合适（也可能和一结果集关联）。 6 再根据其它关联条件，找出最

8、近的表/结果集和上述结果集，作关联。如 估算不准，可手工计算与剩下的各条件关联后的结果集情况，再判断。,7 若觉得计划中的预估值与手工计算的结果相差太大，可以先对SQL中涉及到 的表作统计，或者，有针对性对约束条件中的字段/索引作统计。在这过程 中，分析数据的分布属性，可考虑建索引，建分区等方法，尽 量让执行计 划只读取必要的数据。 8 在上述各环节的判断过程中，可使用10046事件跟踪部分SQL的执行过程中 的运行效率，判断是否合理。还可使用HINT来改变执行计划中，表/结果集 的读取顺序，关联方法，数据的读取方法等。对比不同执行计划的效率，分 析原因，再调整改进，包括改写成等效的SQL。

9、9 必要时，可以考虑对不清晰，不符合判断的部分SQL作10053事件分析。 对于由多个动态视图组成的复杂的语句，若发觉整个语句效率比拆分执行 的总的耗时要多，可以先单独拆分找出各个视图的最佳执行计划，之后设 法确保整个语句按照拆分时各个模块的执行计划执行。 以上是我分析SQL语句的执行计划时的大致思路，现实中的情况千变万化 ，可能与上面的思路稍有不同，但大体都是这样，都是从最强条件入手，再往 外扩展/关联与上一步有关系对象。实际分析中，没有我上述描述的那么复杂， DBA根据语句的约束条件，对比分析CBO给出的执行计划，大体一眼能找出计 划中的疑点，再加以计算分析比较，即可找出问题的结症。,数据

10、的采集统计,推荐使用 DBMS_STATS.GATHER_XXX_STATS(); ORACLE不再改进ANALYZE TABLE. 尽量不要锁住表的统计信息. DBMS_STATS.LOCK_TABLE_STATS();,如何快速获取语句所涉及到的表？,1 将语句创建成一视图。 2 通过user_dependencies 视图，查询与此视图相关的表。,给CBO采集系统的统计信息，执行计划将更优。,1 Exec dbms_stats.gather_system_stats(INTERVAL, 180); 2 Select * from sys.aux_stats$;,4种关联方式和两个参数,嵌

11、套连接 Nest Loops outer table inner table 对于从outer table 出来的每一条记录，都要在inner table 里过滤一遍。 适用于小表间返回较少的结果集，并且有好的关联关系。 哈希连接 适用等于条件下，大数据量的关联产生大结果集。 排序合并连接 排序合并连接适用于两个已经按照关联字段排序后的结果集间的关联，尤其对大数据量 需要不全等于（, =, =）操作的情况下，效率要比NL好。 笛卡尔连接(Cartesian Joins),OPTIMIZER_INDEX_COST_ADJ: （1 to 10000） 用来给DBA人为对索引访问的开销作比例设定。缺

12、省值100 ，表示默认情况下， Cbo将按照正常情况下计算出来的索引访问开销和全表扫描的开销来比较。 DBA 调整此值后，CBO将这样计算索引访问的开销：正常情况下计算出来的Cost * Optimizer_index_cost_adj。此值越小，则表示索引的开销越小，Cbo将越倾 向于走索引；超过100，越大，Cbo将越倾向于走全表扫描。 OPTIMIZER_INDEX_CACHING：（0 to 100） 表示数据缓冲区中，缓存着的索引的数量。此值越大，意味着缓冲区中，缓存的 索引块越多，这对于使用索引作嵌套循环的代价越低，此时CBO将更加偏向走 嵌套循环连接，而非哈希或排序连接。,IN

13、和 EXISTS适用的场景,在一个带子查询的语句中，通常来讲，若主语句上的约 束条件强(返回记录数少)，则适合使用EXISTS；若是子查询 语句上的约束条件强，则适用 IN。 这一点符合我在“手工调优的粗略思路”章节中，提 到的思路：CBO尽可能从过滤性最强的条件入手。,例子,注意执行计划中的Cost,O.CUSTOMER_ID=144是最强的过滤条件，在这种条件下，语句适合用IN写法，但语句使用了Exists,改用IN写法后，COST大大降低,过滤条件e.department_id=80 在主语句上，此时应用 exist, 但语句用了IN，看看其 cost,改为 exists后的 cost,

14、索引,确保唯一性（唯一性索引）。 加快数据查询。,B树索引。 升序，降序，反向。 2 位图索引。 3 位图连接索引。 4 函数索引。 5 应用域索引。,提示： 可dump出 索引的结构，来加强对索引结构的了解，注： 33632 为索引的 Object_id: alter session set events immediate trace name treedump level 33632;,常用的HINT,All_rows, First_rows_n Optimization Goals Leading, Ordered - Join orders Full, Index, No_index

15、 -Access path Use_hash, Use_nl, Use_merge, Use_nl_with_index Join method 5 Append, Push_pred, Push_subq, Qb_name, Dynamic_sampling(X) , Gather_plan_statistics No_expand, Opt_param(optimizer_ind_ex_adj,10), Cardinality(alias 200), swap_join_inputs(), No_merge, Hash_Aj, Hash_SJ,善用分区,分区，实际上是ORACLE提供的多种

16、视角，让用户根据不同性质的 数据，去分组分割存放数据的方法。 正常情况下，系统设计人员/DBA应该了解清楚各类型分区的特点， 系统设计时，根据业务的运行需求，结合各种分区特性，事先规划设计 好将来业务数据存储方案，并将此思想和系统开发人员充分沟通，以便 开发人员在编写程序时，利用好这些分区属特性，编写出高效SQL。,善用分区,到11GR2为止，ORACLE提供的分区类型,各版本中的分区功能,与SQL调优有关的几个数字字典,v$session V$sqltext V$sql_plan V$sql_plan_statistics V$sql_plan_statistics_all V$ses_op

17、timizer_env User_table_histograms User_tab_statistics User_tab_col_statistics User_ind_statistics,10046事件,10046事件是oracle提供用于分析SQL语句性能最方便的工具。 使用10046事件，可以跟踪某个SQL语句完整的执行过程， 获取其解析，执行，CPU使用时间，等待事件，每个操作的 具体耗时等信息。这对获取语句详细的执行计划，分析定位 其效率问题，从而有针对性地优化该语句，非常有用。,10046事件的用法，跟踪级别,level 1： 跟踪sql语句，包括解析、执行、提取、提交和回

18、滚等。 level 4： 包括变量的详细信息。 level 8： 包括等待事件。 level 12：包括绑定变量与等待事件。,Alter session set events 10046 trace name conetxt, level n; 执行SQL。 Alter session set events 10046 trace name conetxt, off;,10046事件的查看方法,1 获取当前会话在操作系统下的进程号 SQLselect paddr from v$session where sid=(select sid from v$mystat group by sid);

19、PADDR - 0000000376B84438 Elapsed: 00:00:00.06 SQLselect spid from v$process where addr=0000000376B84438; SPID - 7548 2 查看文件路径： SQLShow parameter user_dump_dest Elapsed: 00:00:00.01 3 在操作系统下，调用 Tkprof 格式化裸文件： C:Oraclediagrdbmsncbincbitracetkprof ncbi_ora_7548.trc ncbi_ora_7548.log,1 执行计划的详细过程. 2 每个步骤

20、一致读的个数. 3 每个步骤的耗时，可根据耗时来 判断步骤的优劣.,语句在执行过程中，各事件的耗时，,使用10046事件的前提条件 TIMED_STATISTICS=TRUE, SESSION级可设置。 MAX_DUMP_FILE_SIZE 要有足够的空间，通常设置为 Umlimited. 10046事件为何有时没有执行计划？ 这是因为该语句在执行后，该语句的游标没有关闭，导致没写入执行 计划，可以在执行完该语句后，执行一简单语句，如：select * from dual; 促使之前的游标结束，即可得到执行计划信息。,列表分区优化一例,SQLselect sid,serial#,seq#, e

21、rminal,machine,last_call_et call_et,module, 2 (select object_name from user_objects where a.row_wait_obj#=object_id) object_name, event,wait_class, 3 row_wait_file# r_w_f#,row_wait_block# r_w_b#,row_wait_row# r_w_r#,p1,p2,p3 from gv$session a 4 where a.status = ACTIVE and a.username is not null and

22、wait_class!=Idle order by last_call_et; SID SERIAL# SEQ# TERMINAL MACHINE CALL_ET MODULE OBJECT_NAME EVENT - - - - - - - - - - - - 1540 3915 5154 unknown s55 1038 JDBC Thin Client IC_GENERAL_B db file sequential read 2150 23384 17019 unknown s30 2395 JDBC Thin Client ARAP_DJFB db file sequential rea

23、d 23 rows selected. Last_call_et ：当前状态的持续时间，若是某个查询，则意味着该查询已经执行的时长。 上面SID=2150的进程中，当前语句已经运行了2395秒。,运行的语句,select zb.vouchid, fb.fb_oid, xyb.fkxyb_oid, zb.ywbm, fb.hbbm, fb.deptid, fb.ywybm, fb.jobid, fb.szxmid, fb.cinventoryid, fb.ddh, fb.fph, zb.djdl, zb.djbh, fb.flbh, zb.djrq, zb.shrq, zb.effectdate

24、, fb.ordercusmandoc, xyb.xydqr, fb.hsdj, zb.kmbm, fb.kmbm, xyb.ybye, xyb.fbye, xyb.bbye, ductline, zb.xslxbm, pk_salestru, ( fb.jfybje + fb.dfybje ), fb.ybye, zb.dwbm, fb.wldx from arap_djfkxyb xyb, arap_djfb fb, bd_cumandoc, arap_djzb zb where xyb.fb_oid = fb.fb_oid and fb.ksbm_cl = bd_cumand

25、oc.pk_cumandoc and bd_cumandoc.pk_salestru = 0001AA1000000001ELSE and fb.ksbm_cl is not null and fb.vouchid = zb.vouchid and ( zb.dwbm = 1023 and fb.dwbm = 1023 and fb.wldx = 0 and zb.sxbz = 10 and fb.verifyfinshed = N and ( fb.xgbh 1 ) and ( fb.pausetransact is null or fb.pausetransact = N ) and xy

26、b.dr = 0 and fb.fx = 1 and zb.djdl = ys and ( fb.ybye 0 ) and fb.bzbm = 00010000000000000001 and ( fb.bz_date = 2009-07-01 and zb.djrq = 2009-07-01 and fb.billdate = 2009-07-31 and bd_cumandoc.pk_corp = 1023 ) order by xyb.xydqr, zb.djbh, fb.fb_oid 红色部分是经计算后，较强的过滤条件。,- | Id | Operation | Name | Rows

27、 | Bytes | Cost (%CPU)| Time | - | 0 | SELECT STATEMENT | | | | 530 (100)| | | 1 | SORT ORDER BY | | 8 | 4024 | 530 (1)| 00:00:04 | | 2 | NESTED LOOPS | | 8 | 4024 | 529 (1)| 00:00:04 | | 3 | NESTED LOOPS | | 8 | 3520 | 522 (1)| 00:00:03 | | 4 | NESTED LOOPS | | 8 | 2520 | 519 (1)| 00:00:03 | | 5 |

28、TABLE ACCESS BY INDEX ROWID| BD_CUMANDOC | 8 | 264 | 2 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | I_BD_CUMANDOC_SALESTRU | 8 | | 1 (0)| 00:00:01 | |* 7 | TABLE ACCESS BY INDEX ROWID| ARAP_DJFB | 1 | 282 | 65 (2)| 00:00:01 | |* 8 | INDEX RANGE SCAN | I_ARAP_DJFB_TY1 | 259 | | 1 (0)| 00:00:01 | |* 9 |

29、TABLE ACCESS BY INDEX ROWID | ARAP_DJZB | 1 | 125 | 1 (0)| 00:00:01 | |* 10 | INDEX UNIQUE SCAN | PK_ARAP_DJZB | 1 | | 1 (0)| 00:00:01 | |* 11 | TABLE ACCESS BY INDEX ROWID | ARAP_DJFKXYB | 1 | 63 | 1 (0)| 00:00:01 | |* 12 | INDEX RANGE SCAN | I_ARAP_FKXYB01 | 1 | | 1 (0)| 00:00:01 | - Predicate Inf

30、ormation (identified by operation id): - 6 - access(BD_CUMANDOC.PK_SALESTRU=0001AA1000000001ELSE AND BD_CUMANDOC.PK_CORP=1023) 7 - filter(FB.YBYE0 OR FB.YBYE=2009-07-01 AND FB.DWBM=1023 AND FB.VERIFYFINSHED=N AND FB.WLDX=0 AND FB.BILLDATE1 AND (FB.PAUSETRANSACT IS NULL OR FB.PAUSETRANSACT=N) AND FB.

31、BZBM=00010000000000000001 AND (FB.BZ_DATE IS NULL OR FB.BZ_DATE=2009-07-01 AND ZB.DWBM=1023 AND ZB.DJDL=ys AND ZB.DJRQ=2009-07-31 AND ZB.SXBZ=10) 10 - access(FB.VOUCHID=ZB.VOUCHID) 11 - filter(XYB.DR=0) 12 - access(XYB.FB_OID=FB.FB_OID) 但是，该条件并没有被用于ACCESS，而是被FILTER。,分析原因,分析计算语句中的约束条件，发现语句中，过滤性最强的是这个

32、 条件：zb.dwbm =1023，djrq = 2009-07-01 and djrq create index IDX_ARAP_DJZB_20090326_0703 on ARAP_DJZB_20090326(DJRQ,DJDL,LRR) tablespace nnc_index03 local; Index created. Elapsed: 00:01:34.69,创建列表分区表,改成分区表后语句的耗时，不到原来的1/330。,使用MV优化SQL,某个报表，过程语句有40+条，其中关键耗时的语句有8条，经调优，在系统空闲时 间运行，大概耗时25M，但在系统运行时查询，耗时40-50M

33、，无法满足需求，求改善。 经了解，业务人员只要求查询昨天某分公司的数据，此条件一直不变。由于之前已 经获知此报表涉及到的SQL，经思考，决定使用MV+OUTLINE来实现此需求。 1 对此8条耗时语句，修改其中的日期后，建成MV。 2 对此8条耗时语句，修改其中的日期，加入提示 /*+ rewrite */，之后生成 OUTLINE。 3 对此8条耗时语句，修改其中的日期，生成OUTLINE(此是生成的是正常查询 语句的OUTLINE)。 4 对2，3步的OUTLINE作交换，目的是迫使正常查询时，使用步骤2 的执行计划， 此时该语句将取读取步骤1生成的MV里的数据。 5 修改参数query_

34、rewrite_integrity= stale_tolerated，并建一SCHEDULER JOB，定时运行上述过程。 经上述优化后，业务人员在查询报表时，CBO将直接读取事先生成的MV里的数据， 在本地查询时，2分钟内出结果。语句的执行计划如下：,另外一些调优方法和工具,执行大纲的本意，就是对某些特定语句，使用指定/固 定的执行计划。步骤如下： 1 先创建原始语句的OUTLINE。 2 创建特定语句的OUTLINE，此语句通常是加了 HINT来达到使用指定执行计划。 3 交换步骤1，2的执行计划。 4 启用outline目录。,执行大纲的使用方法,SQLTXPLAN,SQLTXPLAN，

35、ORACLE的内部工具，可用来协助诊断效率差的 SQL，详细用法请上MOS查阅相关文档ID 215187.1,DBMS_SQLTUNE,Dbms_sqltune 是ORACLE在10G版本里推出的Sql调优工具。 用户可以通过创建作业来把要调优的Sql语句放入Dbms_sqltune 包， 运行该作业，之后查询相关视图，获取该语句最佳的执行计划及建议。 如下步骤： 1 创建优化任务。 DBMS_SQLTUNE.CREATE_TUNING_TASK. 2 运行优化任务。 3 查询视图，获取任务的当前状态。 select status from user_advisor_taks where ta

36、sk_name=; 4 查询语句的优化结果。 select dbms_sqltune.report_tuning_task() from dual; 此时可以看到最佳的执行计划及ORACLE的优化建议。 5 删除优化任务。 exec dbms_sqltune.drop_tuning_task();,DBMS_ADVISOR,10G版本新推出的顾问框架，支持各种性能调优需求，通 过DBMS_ADVISOR包来支持各种需求，如，SQL调优， MV，索引建议等。,DBMS_PROFILE,DBMS_PROFILE 包主要用来调优存储过程的执行过 程，通过跟踪记录存储过程中每条SQL的执行时间，来确定 哪些SQL语句最耗时，从而定位出需要调优的SQL语句。,11G在执行计划上的一些改进,自适应共享游标(ACS) ，克服带绑定变量的执行计划存在 的弊端。 2 在收集统计信息上的改进 A 设置收集统计信息时的选项，可对指定的表作单独 设置。 B 对联合列收集统计信息。 C 对函数以及表达式收集统计信息。 Spm ，Sql paln management ， Sql 计划管理，实际是 10G中顾问框架（DBMS_ADVISOR）的升级版，通过这 个特性，ORACLE自动判断某个SQL新的