SQL语句常用的优化方法详解

2
3
1-10
如何解读执行计划中的执行顺序？
在获取SQL语句的执行计划后，这样解读执行顺序：
* 对同一凹层，先上后下执行， * 对不同凹层，先里后外执行。
1-11
对于同一凹层， 先上后下
对于不同凹层， 先里后外。所以 先NL,后 hash。
真正的执行顺序
1-12
执行顺序:3,5,4,2,7,6,1,0
1-17
手工调优的粗略思路
1 获取SQL的执行计划。 2 判断当前的执行计划是否正常： 手工计算Where语句后各过滤条件(非关联条件)的预估数值，找出最强 的过滤条件（过滤后剩余数据最少的条件）。一般来讲，若语句中各对 象的统计信息准确，CBO经过计算后，基本上都是从过滤条件最强的表 开始，判断执行计划是否从此条件开始。 3 检查执行计划中第1步的预估值，是否与实际值相近。否，转步骤7。 4 根据过滤条件判断，数据的读取方式是否合适（读表，读索引，或根据 索引返回原表获取）。 5 找出与第1步要执行的表存在关联关系的表，根据其过滤后的结果集判断 ，两表间的关联方法是否合适（也可能和一结果集关联）。 6 再根据其它关联条件，找出最近的表/结果集和上述结果集，作关联。如 估算不准，可手工计算与剩下的各条件关联后的结果集情况，再判断。
* 当前库， 根据V$SESSION.LAST_CALL_ET，找到运行时间 最长的进程，获取SQL_ID，再找出SQL语句和执行计划。
1-7
AWR上要关注的SQL项
1-8
如何手工调优SQL?
A 如何获取语句的执行计划？ B 如何解读执行计划中的执行顺序？ C SQL语句的调优原则。 D 一些调优常识。 E 手工调优的粗略思路。 F 10046事件的使用方法。 G 两个案例。
1-15
ACCESS和FILTER的区别
在解析出SQL语句的执行计划后，在执行计划的末尾，通常会出现 这些信息：
FILTER 指按照某个条件过滤数据， ACCESS 指按照某个条件/关系获取数据，
1-16
在本文中，这样定义此词汇
关联条件：where a.col1 =b.col1，，， 过滤条件：where a.col1<=103（常量），，， 关联条件，和过滤条件都称为约束条件。
1-9
如何获取语句的执行计划？
1 直接解析SQL语句. Explain plan for XXX； Select * from table(dbms_xplan.display); 根据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,,, 从视图v$sql_plan 中获取。
1-13
SQL 语句的调优原则
在一个OLTP系统里，优化SQL语句的原则，就是尽量 减少数据的读取。调优的目的，实际是设法让语句在执行过 程中，尽可能地只读取必要的数据，不读或尽量少读不符合 要求的数据。
1-14
SQL调优中的一些常识
执行计划中涉及的一些概念
* 不论SQL中读取多少个表，在执行过程中，每次都是两个表/结 果集操作，得到新的结果后，再和下一个表/结果集操作，，， 直到结束。 在一个多表关联的执行计划中，必须包括这3要素： * 表/对象/数据集的读取顺序（ join order ）。 * 数据的读取方法（ access path ）。 * 表/数据的关联方法（join method）。 这3个要素是判断执行计划优秀与否的关键。 * 可选择性(Selectivity) ，>=0 and <=1。 * 预估记录数(Cardinality) ，表/视图/操作后的结果集。 * 开销(Cost) ，CBO选择最佳执行计划的标准：越低越好。
1-3
例子
1-4
1-5
哪些SQL需要优化？
•
运行时间较长的SQL。
• 逻辑读较高的SQL。
• 物理读较高的SQL。
1-6
从哪里获取需要调优的SQL？
* AWR(ASH,ADDM)， 1 Elapsed Time(含CPU较高者) 2 Buffer Gets 3 Physical Reads
* EM， 性能分析--> ቤተ መጻሕፍቲ ባይዱQL Tuning
1-18
7 若觉得计划中的预估值与手工计算的结果相差太大，可以先对SQL中涉及到 的表作统计，或者，有针对性对约束条件中的字段/索引作统计。在这过程 中，分析数据的分布属性，可考虑建索引，建分区等方法，尽 量让执行计 划只读取必要的数据。 8 在上述各环节的判断过程中，可使用10046事件跟踪部分SQL的执行过程中 的运行效率，判断是否合理。还可使用HINT来改变执行计划中，表/结果集 的读取顺序，关联方法，数据的读取方法等。对比不同执行计划的效率，分 析原因，再调整改进，包括改写成等效的SQL。 9 必要时，可以考虑对不清晰，不符合判断的部分SQL作10053事件分析。 10 对于由多个动态视图组成的复杂的语句，若发觉整个语句效率比拆分执行 的总的耗时要多，可以先单独拆分找出各个视图的最佳执行计划，之后设 法确保整个语句按照拆分时各个模块的执行计划执行。 以上是我分析SQL语句的执行计划时的大致思路，现实中的情况千变万化 ，可能与上面的思路稍有不同，但大体都是这样，都是从最强条件入手，再往 外扩展/关联与上一步有关系对象。实际分析中，没有我上述描述的那么复杂， DBA根据语句的约束条件，对比分析CBO给出的执行计划，大体一眼能找出计 划中的疑点，再加以计算分析比较，即可找出问题的结症。
SQL语句常用的调优方法
背景：OLTP系统，ORACLE10G
1-1
目录
1 2 3 4 5 6 为什么要调优SQL? 哪些SQL需要调优? 如何获取需要调优的SQL? 如何手工调优SQL? 另外一些调优方法和工具。 11G在执行计划上的一些改进。
1-2
为什么要调优SQL？
通常来讲，要打造高效快捷的应用系统，需要从最初的业务需求 入手，在分析、整理出闭环的业务操作流程后，按照范式的要求，尽 量用简单的数据结构，来实现业务的运行和流转（可以考虑对基础数 据作少量的数据冗余，以减少关联）；同时，根据业务的需求，兼考 虑对历史业务数据的迁移，只保留最近一段时期内的数据，以便让系 统轻装运行。 但是，由于业务的复杂性，设计人员的知识、视野、前瞻性等的 局限，在系统结构设计时，难以考虑周全；并且，由于开发人员的 水平参差不齐，编写的代码也存在缺陷。经统计评估，排除系统结构 设计不善导致的因素外，新的应用系统，有80%的效率问题，是因为 低效的SQL导致，这就需要DBA找出这些低效的SQL，加以优化。