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

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在执行计划上的一些改进。 目录 1-3Copyright Oracle Corporation, 2002. All rights reserved. 为什么要调优SQL？ 通常来讲，要打造高效快捷的应用系统，需要从最初的业务需求 入手，在分析、整理出闭环的业务操作流程后，按照范式的要求，尽 量用简单的数据结构，来实现业务的运行和流转（可以考虑对基础数 据作少量的数据冗余，以减少关联）；同时，根据业务的需求，兼考 虑对历史业务数据的迁移，只保留最近一段时期内的数据，以便让系 统轻装运行。 但是，由于业务的复杂性，设计人员的知识、视野、前瞻性等的 局限，在系统结构设计时，难以考虑周全；并且，由于开发人员的 水平参差不齐，编写的代码也存在缺陷。经统计评估，排除系统结构 设计不善导致的因素外，新的应用系统，有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需要优化？ 运行时间较长的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，再找出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 手工调优的粗略思路。 F 10046事件的使用方法。 G 两个案例。 1-10Copyright Oracle Corporation, 2002. All rights reserved. 如何获取语句的执行计划？ 2 根据SQL_ID查询， select * from table (dbms_xplan.display_cursor( 还有：advanced,typical,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); 1-11Copyright Oracle Corporation, 2002. All rights reserved. 如何解读执行计划中的执行顺序？ 在获取SQL语句的执行计划后，这样解读执行顺序： * 对同一凹层，先上后下执行， * 对不同凹层，先里后外执行。 1-12Copyright Oracle Corporation, 2002. All rights reserved. 对对于同一凹层层， 先上后下 对对于不同凹层层， 先里后外。所以 先NL,后 hash。 真正的执执行顺顺序 1-13Copyright Oracle Corporation, 2002. All rights reserved. 执执行顺顺序:3,5,4,2,7,6,1,0 1-14Copyright Oracle Corporation, 2002. All rights reserved. SQL 语句的调优原则 在一个OLTP系统里，优化SQL语句的原则，就是尽量 减少数据的读取。调优的目的，实际是设法让语句在执行过 程中，尽可能地只读取必要的数据，不读或尽量少读不符合 要求的数据。 1-15Copyright Oracle Corporation, 2002. All rights reserved. SQL调优中的一些常识 执行计划中涉及的一些概念 * 不论SQL中读取多少个表，在执行过程中，每次都是两个表/结 果集操作，得到新的结果后，再和下一个表/结果集操作， 直到结束。 在一个多表关联的执行计划中，必须包括这3要素： * 表/对象/数据集的读取顺序（ join order ）。 * 数据的读取方法（ access path ）。 * 表/数据的关联方法（join method）。 这3个要素是判断执行计划优秀与否的关键。 * 可选择性(Selectivity) ，=0 and , =,select paddr from v$session where sid=(select sid from v$mystat group by sid); 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-38Copyright Oracle Corporation, 2002. All rights reserved. 1 执行计划的详细过程. 2 每个步骤一致读的个数. 3 每个步骤的耗时，可根据耗时来 判断步骤的优劣. 1-39Copyright Oracle Corporation, 2002. All rights reserved. 语句在执行过程中 ，各事件的耗时， 1-40Copyright Oracle Corporation, 2002. All rights reserved. 使用10046事件的前提条件 1 TIMED_STATISTICS=TRUE, SESSION级可设置。 2 MAX_DUMP_FILE_SIZE 要有足够的空间，通常设置为 Umlimited. 10046事件为何有时没有执行计划？ 这是因为该语句在执行后，该语句的游标没有关闭，导致没写入执行 计划，可以在执行完该语句后，执行一简单语句，如：select * from dual; 促使之前的游标结束，即可得到执行计划信息。 1-41Copyright Oracle Corporation, 2002. 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 gv$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 17019 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.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, 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 = 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 xyb.dr = 0 and fb.fx = 1 and zb.djdl = ys and ( fb.ybye 0 ) and fb.bzbm = 00010000000000000001 and ( fb.bz_date = 2011-01-13 and zb.djrq = 2011-01-13 and fb.billdate = 2011-02-12 and bd_cumandoc.pk_corp = 1044 ) order by xyb.xydqr, zb.djbh, fb.fb_oid 执行语句，红色部分的条件约束性较强 1-58Copyright Oracle Corporation, 2002. All rights reserved. 执行计划，初步判断红色部分的ROWS/CARD有误 1-59Copyright Oracle Corporation, 2002. All rights reserved. 手工计算，符合该约束条件的记录数 1-60Copyright Oracle Corporation, 2002. All rights reserved. 语句的耗时 1-61Copyright Oracle Corporation, 2002. All rights reserved. 使用HINT的效果 1-62Copyright Oracle Corporation, 2002. All rights reserved