SQL语句常用的优化方法

1、1-1Copyright Oracle Corporation, 2002. All rights reserved.背景：背景：OLTP系统，系统，ORACLE10G作者作者: ZALBB SQL语句常用的调优方法语句常用的调优方法1-2Copyright Oracle Corporation, 2002. All rights reserved. 1 为什么要调优为什么要调优SQL? 2 哪些哪些SQL需要调优需要调优? 3 如何获取需要调优的如何获取需要调优的SQL? 4 如何手工调优如何手工调优SQL? 5 另外一些调优方法和工具。另外一些调优方法和工具。 6 11G在执行计划上的一些

2、改进。在执行计划上的一些改进。 目录目录1-3Copyright Oracle Corporation, 2002. All rights reserved.为什么要调优为什么要调优SQL？通常来讲，要打造高效快捷的应用系统，需要从最初的业务需求通常来讲，要打造高效快捷的应用系统，需要从最初的业务需求入手，在分析、整理出闭环的业务操作流程后，按照范式的要求，尽入手，在分析、整理出闭环的业务操作流程后，按照范式的要求，尽量用简单的数据结构，来实现业务的运行和流转（可以考虑对基础数量用简单的数据结构，来实现业务的运行和流转（可以考虑对基础数据作少量的数据冗余，以减少关联）；同时，根据业务的需求，兼

3、考据作少量的数据冗余，以减少关联）；同时，根据业务的需求，兼考虑对历史业务数据的迁移，只保留最近一段时期内的数据，以便让系虑对历史业务数据的迁移，只保留最近一段时期内的数据，以便让系统轻装运行。统轻装运行。 但是，由于业务的复杂性，设计人员的知识、视野、前瞻性等的但是，由于业务的复杂性，设计人员的知识、视野、前瞻性等的局限，在系统结构设计时，难以考虑周全；并且，由于开发人员的局限，在系统结构设计时，难以考虑周全；并且，由于开发人员的水平参差不齐，编写的代码也存在缺陷。经统计评估，排除系统结构水平参差不齐，编写的代码也存在缺陷。经统计评估，排除系统结构设计不善导致的因素外，新的应用系统，有设计不

4、善导致的因素外，新的应用系统，有80%的效率问题，是因为的效率问题，是因为低效的低效的SQL导致，这就需要导致，这就需要DBA找出这些低效的找出这些低效的SQL，加以优化。，加以优化。1-4Copyright Oracle Corporation, 2002. All rights reserved.例子例子1-5Copyright Oracle Corporation, 2002. All rights reserved.1-6Copyright Oracle Corporation, 2002. All rights reserved.哪些哪些SQL需要优化？需要优化？ 运行时间较长的运行

5、时间较长的SQL。 逻辑读较高的逻辑读较高的SQL。 物理读较高的物理读较高的SQL。1-7Copyright Oracle Corporation, 2002. All rights reserved.从哪里获取需要调优的从哪里获取需要调优的SQL？* AWR(ASH,ADDM)， 1 Elapsed Time(含含CPU较高者较高者) 2 Buffer Gets 3 Physical Reads* EM， 性能分析性能分析- SQL Tuning* 当前库，当前库， 根据根据V$SESSION.LAST_CALL_ET，找到运行时间，找到运行时间最长的进程，获取最长的进程，获取SQL_ID

6、，再找出，再找出SQL语句和执行计划。语句和执行计划。1-8Copyright Oracle Corporation, 2002. All rights reserved.AWR上要关注的上要关注的SQL项项1-9Copyright Oracle Corporation, 2002. All rights reserved.如何手工调优如何手工调优SQL?A 如何获取语句的执行计划？如何获取语句的执行计划？ B 如何解读执行计划中的执行顺序？如何解读执行计划中的执行顺序？ C SQL语句的调优原则。语句的调优原则。 D 一些调优常识。一些调优常识。 E 手工调优的粗略思路。手工调优的粗略思路。

7、 F 10046事件的使用方法。事件的使用方法。 G 两个案例。两个案例。1-10Copyright Oracle Corporation, 2002. All rights reserved.如何获取语句的执行计划？如何获取语句的执行计划？ 2 根据根据SQL_ID查询，查询， select * from table (dbms_xplan.display_cursor(&sql_id,0, allstats last); 还有：还有：advanced,typical,serial,basic. v$session.sql_child_number=0,1,3 从视图从视图v$sql

8、_plan 中获取。中获取。1 直接解析直接解析SQL语句语句. Explain plan for XXX； Select * from table(dbms_xplan.display);1-11Copyright Oracle Corporation, 2002. All rights reserved.如何解读执行计划中的执行顺序？如何解读执行计划中的执行顺序？ 在获取在获取SQL语句的执行计划后，这样解读执行顺序：语句的执行计划后，这样解读执行顺序： * 对同一凹层，先上后下执行，对同一凹层，先上后下执行， * 对不同凹层，先里后外执行。对不同凹层，先里后外执行。1-12Copyrig

9、ht Oracle Corporation, 2002. All rights reserved.对于同一凹层，对于同一凹层，先上后下先上后下对于不同凹层，对于不同凹层，先里后外。所以先里后外。所以先先NL,后后 hash。真正的执行顺序真正的执行顺序1-13Copyright Oracle Corporation, 2002. All rights reserved.执行顺序执行顺序:3,5,4,2,7,6,1,01-14Copyright Oracle Corporation, 2002. All rights reserved.SQL 语句的调优原则语句的调优原则在一个在一个OLTP系统

10、里，优化系统里，优化SQL语句的原则，就是尽量语句的原则，就是尽量减少数据的读取。调优的目的，实际是设法让语句在执行过减少数据的读取。调优的目的，实际是设法让语句在执行过程中，尽可能地只读取必要的数据，不读或尽量少读不符合程中，尽可能地只读取必要的数据，不读或尽量少读不符合要求的数据。要求的数据。1-15Copyright Oracle Corporation, 2002. All rights reserved.SQL调优中的一些常识调优中的一些常识执行计划中涉及的一些概念执行计划中涉及的一些概念* 不论不论SQL中读取多少个表，在执行过程中，每次都是两个表中读取多少个表，在执行过程中，每次

11、都是两个表/结结 果集操作，得到新的结果后，再和下一个表果集操作，得到新的结果后，再和下一个表/结果集操作，结果集操作， 直到结束。直到结束。 在一个多表关联的执行计划中，必须包括这在一个多表关联的执行计划中，必须包括这3要素：要素：* 表表/对象对象/数据集的读取顺序（数据集的读取顺序（ join order ）。）。* 数据的读取方法（数据的读取方法（ access path ）。）。* 表表/数据的关联方法（数据的关联方法（join method）。）。 这这3个要素是判断执行计划优秀与否的关键。个要素是判断执行计划优秀与否的关键。* 可选择性可选择性(Selectivity) ，=0

12、and =1。* 预估记录数预估记录数(Cardinality) ，表，表/视图视图/操作后的结果集。操作后的结果集。* 开销开销(Cost) ，CBO选择最佳执行计划的标准：越低越好。选择最佳执行计划的标准：越低越好。1-16Copyright Oracle Corporation, 2002. All rights reserved.ACCESS和和FILTER的区别的区别在解析出在解析出SQL语句的执行计划后，在执行计划的末尾，通常会出现语句的执行计划后，在执行计划的末尾，通常会出现这些信息：这些信息：FILTER 指按照某个条件过滤数据，指按照某个条件过滤数据，ACCESS 指按照某个

13、条件指按照某个条件/关系获取数据，关系获取数据，1-17Copyright Oracle Corporation, 2002. All rights reserved.在本文中，这样定义此词汇在本文中，这样定义此词汇关联条件：关联条件：where a.col1 =b.col1，过滤条件：过滤条件：where a.col1, =, select paddr from v$session where sid=(select sid from v$mystat group by sid);PADDR-0000000376B84438Elapsed: 00:00:00.06SQLselect spid

14、 from v$process where addr=0000000376B84438;SPID-75482 查看文件路径：查看文件路径：SQLShow parameter user_dump_destElapsed: 00:00:00.013 在操作系统下，调用在操作系统下，调用 Tkprof 格式化裸文件：格式化裸文件：C:Oraclediagrdbmsncbincbitracetkprof ncbi_ora_7548.trc ncbi_ora_7548.log1-38Copyright Oracle Corporation, 2002. All rights reserved.1 执行计

15、划的详细过程执行计划的详细过程.2 每个步骤一致读的个数每个步骤一致读的个数.3 每个步骤的耗时，可根据耗时来每个步骤的耗时，可根据耗时来 判断步骤的优劣判断步骤的优劣.1-39Copyright Oracle Corporation, 2002. All rights reserved.语句在执行过程中，语句在执行过程中，各事件的耗时，各事件的耗时，1-40Copyright Oracle Corporation, 2002. All rights reserved. 使用使用10046事件的前提条件事件的前提条件1 TIMED_STATISTICS=TRUE, SESSION级可设置。级可

16、设置。2 MAX_DUMP_FILE_SIZE 要有足够的空间，通常设置为要有足够的空间，通常设置为 Umlimited. 10046事件为何有时没有执行计划？事件为何有时没有执行计划？ 这是因为该语句在执行后，该语句的游标没有关闭，导致没写入执行这是因为该语句在执行后，该语句的游标没有关闭，导致没写入执行计划，可以在执行完该语句后，执行一简单语句，如：计划，可以在执行完该语句后，执行一简单语句，如：select * from dual; 促使之前的游标结束，即可得到执行计划信息。促使之前的游标结束，即可得到执行计划信息。1-41Copyright Oracle Corporation, 20

17、02. All rights reserved.列表分区优化一例列表分区优化一例SQLselect sid,serial#,seq#, erminal,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 g

18、v$session a 4 where a.status = ACTIVE and a.username is not null and 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 170

19、19 unknown s30 2395 JDBC Thin Client ARAP_DJFB db file sequential read 23 rows selected.Last_call_et ：当前状态的持续时间，若是某个查询，则意味着该查询已经执行的时长。：当前状态的持续时间，若是某个查询，则意味着该查询已经执行的时长。上面上面SID=2150的进程中，当前语句已经运行了的进程中，当前语句已经运行了2395秒。秒。1-42Copyright Oracle Corporation, 2002. All rights reserved.运行的语句运行的语句select zb.vouch

20、id, 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, fb.ordercusmandoc, xyb.xydqr, fb.hsdj, zb.kmbm, fb.kmbm, xyb.ybye, xyb.fbye, xyb.bbye, ductline, zb.xslxbm, pk_

21、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_cumandoc.pk_cumandoc and bd_cumandoc.pk_salestru = 0001AA1000000001ELSE and fb.ksbm_cl is not null and fb.vouchid = zb.vouchi

22、d 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 xyb.dr = 0 and fb.fx = 1 and zb.djdl = ys and ( fb.ybye 0 ) and fb.bzbm = 00010000000000000001 and ( fb.bz_date = 2009-07

23、-01 and zb.djrq = 2009-07-01 and fb.billdate 0 OR FB.YBYE=2009-07-01 AND FB.DWBM=1023 AND FB.VERIFYFINSHED=N AND FB.WLDX=0 AND FB.BILLDATE=2009-07-31 AND FB.XGBH1 AND (FB.PAUSETRANSACT IS NULL OR FB.PAUSETRANSACT=N) AND FB.BZBM=00010000000000000001 AND (FB.BZ_DATE IS NULL OR FB.BZ_DATE=2009-07-01 AN

24、D ZB.DWBM=1023 AND ZB.DJDL=ys AND ZB.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-45Copyright Oracle Corporation, 2002. All rights reserved.创建列表分区表创建列表分区表1-46Copyright Oracl

25、e Corporation, 2002. All rights reserved.1-47Copyright Oracle Corporation, 2002. All rights reserved.改成分区表后语句的耗时，不到改成分区表后语句的耗时，不到原来的原来的1/330。1-48Copyright Oracle Corporation, 2002. All rights reserved.使用使用MV优化优化SQL 某个报表，过程语句有某个报表，过程语句有40+条，其中关键耗时的语句有条，其中关键耗时的语句有8条，经调优，在系统空闲时条，经调优，在系统空闲时间运行，大概耗时间运行，大

26、概耗时25M，但在系统运行时查询，耗时，但在系统运行时查询，耗时40-50M，无法满足需求，求改善。，无法满足需求，求改善。 经了解，业务人员只要求查询昨天某分公司的数据，此条件一直不变。由于之前已经了解，业务人员只要求查询昨天某分公司的数据，此条件一直不变。由于之前已经获知此报表涉及到的经获知此报表涉及到的SQL，经思考，决定使用，经思考，决定使用MV+OUTLINE来实现此需求。来实现此需求。 1 对此对此8条耗时语句，修改其中的日期后，建成条耗时语句，修改其中的日期后，建成MV。 2 对此对此8条耗时语句，修改其中的日期，加入提示条耗时语句，修改其中的日期，加入提示 /*+ rewrit

27、e */，之后生成，之后生成 OUTLINE。 3 对此对此8条耗时语句，修改其中的日期，生成条耗时语句，修改其中的日期，生成OUTLINE(此是生成的是正常查询此是生成的是正常查询 语句的语句的OUTLINE)。 4 对对2，3步的步的OUTLINE作交换，目的是迫使正常查询时，使用步骤作交换，目的是迫使正常查询时，使用步骤2 的执行计划，的执行计划， 此时该语句将取读取步骤此时该语句将取读取步骤1生成的生成的MV里的数据。里的数据。 5 修改参数修改参数query_rewrite_integrity= stale_tolerated，并建一，并建一SCHEDULER JOB，定时运行上述过

28、程。，定时运行上述过程。 经上述优化后，业务人员在查询报表时，经上述优化后，业务人员在查询报表时，CBO将直接读取事先生成的将直接读取事先生成的MV里的数据，里的数据，在本地查询时，在本地查询时，2分钟内出结果。语句的执行计划如下：分钟内出结果。语句的执行计划如下：1-49Copyright Oracle Corporation, 2002. All rights reserved.1-50Copyright Oracle Corporation, 2002. All rights reserved.另外一些调优方法和工具另外一些调优方法和工具执行大纲的本意，就是对某些特定语句，使用指定执行大

29、纲的本意，就是对某些特定语句，使用指定/固固定的执行计划。步骤如下：定的执行计划。步骤如下： 1 先创建原始语句的先创建原始语句的OUTLINE。 2 创建特定语句的创建特定语句的OUTLINE，此语句通常是加了，此语句通常是加了 HINT来达到使用指定执行计划。来达到使用指定执行计划。 3 交换步骤交换步骤1，2的执行计划。的执行计划。 4 启用启用outline目录。目录。执行大纲的使用方法执行大纲的使用方法1-51Copyright Oracle Corporation, 2002. All rights reserved.SQLTXPLAN SQLTXPLAN，ORACLE的内部工具，

30、可用来协助诊断效率差的的内部工具，可用来协助诊断效率差的SQL，详细用法请上，详细用法请上MOS查阅相关文档查阅相关文档ID 215187.11-52Copyright Oracle Corporation, 2002. All rights reserved.DBMS_SQLTUNE Dbms_sqltune 是是ORACLE在在10G版本里推出的版本里推出的Sql调优工具。调优工具。用户可以通过创建作业来把要调优的用户可以通过创建作业来把要调优的Sql语句放入语句放入Dbms_sqltune 包，包，运行该作业，之后查询相关视图，获取该语句最佳的执行计划及建议。运行该作业，之后查询相关视图

31、，获取该语句最佳的执行计划及建议。如下步骤：如下步骤： 1 创建优化任务。创建优化任务。 DBMS_SQLTUNE.CREATE_TUNING_TASK. 2 运行优化任务。运行优化任务。 3 查询视图，获取任务的当前状态。查询视图，获取任务的当前状态。 select status from user_advisor_taks where task_name=; 4 查询语句的优化结果。查询语句的优化结果。 select dbms_sqltune.report_tuning_task() from dual; 此时可以看到最佳的执行计划及此时可以看到最佳的执行计划及ORACLE的优化建议。的优

32、化建议。 5 删除优化任务。删除优化任务。 exec dbms_sqltune.drop_tuning_task();1-53Copyright Oracle Corporation, 2002. All rights reserved.DBMS_ADVISOR 10G版本新推出的顾问框架，支持各种性能调优需求，通版本新推出的顾问框架，支持各种性能调优需求，通过过DBMS_ADVISOR包来支持各种需求，如，包来支持各种需求，如，SQL调优，调优，MV，索引建议等。，索引建议等。1-54Copyright Oracle Corporation, 2002. All rights reserve

33、d.DBMS_PROFILE DBMS_PROFILE 包主要用来调优存储过程的执行过包主要用来调优存储过程的执行过程，通过跟踪记录存储过程中每条程，通过跟踪记录存储过程中每条SQL的执行时间，来确定的执行时间，来确定哪些哪些SQL语句最耗时，从而定位出需要调优的语句最耗时，从而定位出需要调优的SQL语句。语句。1-55Copyright Oracle Corporation, 2002. All rights reserved.11G在执行计划上的一些改进在执行计划上的一些改进1自适应共享游标自适应共享游标(ACS) ，克服带绑定变量的执行计划存在，克服带绑定变量的执行计划存在 的弊端。的弊

34、端。2 在收集统计信息上的改进在收集统计信息上的改进A 设置收集统计信息时的选项，可对指定的表作单独设置收集统计信息时的选项，可对指定的表作单独 设置。设置。 B 对联合列收集统计信息。对联合列收集统计信息。 C 对函数以及表达式收集统计信息。对函数以及表达式收集统计信息。3Spm ，Sql paln management ， Sql 计划管理，实际是计划管理，实际是 10G中顾问框架（中顾问框架（DBMS_ADVISOR）的升级版，通过这）的升级版，通过这 个特性，个特性，ORACLE自动判断某个自动判断某个SQL新的执行计划是否新的执行计划是否更加优秀（成本更低），只有新的执行计划比原来的

35、更佳更加优秀（成本更低），只有新的执行计划比原来的更佳，才会被使用。，才会被使用。1-56Copyright Oracle Corporation, 2002. All rights reserved.例子例子:联合列收集统计信息对执行计划的改善联合列收集统计信息对执行计划的改善。1-57Copyright Oracle Corporation, 2002. All rights reserved.select zb.vouchid, fb.fb_oid,xyb.fkxyb_oid, zb.ywbm, fb.hbbm, fb.deptid, fb.ywybm, fb.jobid, fb.szx

36、mid, fb.cinventoryid, fb.ddh, fb.fph, zb.djdl, zb.djbh, fb.flbh, zb.djrq, zb.shrq, zb.effectdate, 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_cumandoc.pk_cumandoc and bd_cumandoc.pk_salestru =1044V610000000000BL1 and fb.ksbm_cl is