SQL语句常用的优化方法

1-13
SQL 语句的调优原则
在一个OLTP系统里，优化SQL语句的原则，就是尽量 减少数据的读取。调优的目的，实际是设法让语句在执行过 程中，尽可能地只读取必要的数据，不读或尽量少读不符合 要求的数据。
1-14
SQL调优中的一些常识
执行计划中涉及的一些概念
* 不论SQL中读取多少个表，在执行过程中，每次都是两个表/结 果集操作，得到新的结果后，再和下一个表/结果集操作，，， 直到结束。 在一个多表关联的执行计划中，必须包括这3要素： * 表/对象/数据集的读取顺序（ join order ）。 * 数据的读取方法（ access path ）。 * 表/数据的关联方法（join method）。 这3个要素是判断执行计划优秀与否的关键。 * 可选择性(Selectivity) ，>=0 and <=1。 * 预估记录数(Cardinality) ，表/视图/操作后的结果集。 * 开销(Cost) ，CBO选择最佳执行计划的标准：越低越好。
5
1-29
善用分区
分区，实际上是ORACLE提供的多种视角，让用户根据不同性质的 数据，去分组分割存放数据的方法。 正常情况下，系统设计人员/DBA应该了解清楚各类型分区的特点， 系统设计时，根据业务的运行需求，结合各种分区特性，事先规划设计 好将来业务数据存储方案，并将此思想和系统开发人员充分沟通，以便 开发人员在编写程序时，利用好这些分区属特性，编写出高效SQL。
1-18
7 若觉得计划中的预估值与手工计算的结果相差太大，可以先对SQL中涉及到 的表作统计，或者，有针对性对约束条件中的字段/索引作统计。在这过程 中，分析数据的分布属性，可考虑建索引，建分区等方法，尽 量让执行计 划只读取必要的数据。 8 在上述各环节的判断过程中，可使用10046事件跟踪部分SQL的执行过程中 的运行效率，判断是否合理。还可使用HINT来改变执行计划中，表/结果集 的读取顺序，关联方法，数据的读取方法等。对比不同执行计划的效率，分 析原因，再调整改进，包括改写成等效的SQL。 9 必要时，可以考虑对不清晰，不符合判断的部分SQL作10053事件分析。 10 对于由多个动态视图组成的复杂的语句，若发觉整个语句效率比拆分执行 的总的耗时要多，可以先单独拆分找出各个视图的最佳执行计划，之后设 法确保整个语句按照拆分时各个模块的执行计划执行。 以上是我分析SQL语句的执行计划时的大致思路，现实中的情况千变万化 ，可能与上面的思路稍有不同，但大体都是这样，都是从最强条件入手，再往 外扩展/关联与上一步有关系对象。实际分析中，没有我上述描述的那么复杂， DBA根据语句的约束条件，对比分析CBO给出的执行计划，大体一眼能找出计 划中的疑点，再加以计算分析比较，即可找出问题的结症。
1-17
手工调优的粗略思路
1 获取SQL的执行计划。 2 判断当前的执行计划是否正常： 手工计算Where语句后各过滤条件(非关联条件)的预估数值，找出最强 的过滤条件（过滤后剩余数据最少的条件）。一般来讲，若语句中各对 象的统计信息准确，CBO经过计算后，基本上都是从过滤条件最强的表 开始，判断执行计划是否从此条件开始。 3 检查执行计划中第1步的预估值，是否与实际值相近。否，转步骤7。 4 根据过滤条件判断，数据的读取方式是否合适（读表，读索引，或根据 索引返回原表获取）。 5 找出与第1步要执行的表存在关联关系的表，根据其过滤后的结果集判断 ，两表间的关联方法是否合适（也可能和一结果集关联）。 6 再根据其它关联条件，找出最近的表/结果集和上述结果集，作关联。如 估算不准，可手工计算与剩下的各条件关联后的结果集情况，再判断。
用来给DBA人为对索引访问的开销作比例设定。缺省值100 ，表示默认情况下， Cbo将按照正常情况下计算出来的索引访问开销和全表扫描的开销来比较。 DBA 调整此值后，CBO将这样计算索引访问的开销：正常情况下计算出来的Cost * Optimizer_index_cost_adj。此值越小，则表示索引的开销越小，Cbo将越倾 向于走索引；超过100，越大，Cbo将越倾向于走全表扫描。
1-19
数据的采集统计
1 2 推荐使用 DBMS_STATS.GATHER_XXX_STATS(); ORACLE不再改进ANALYZE TABLE. 尽量不要锁住表的统计信息. DBMS_STATS.LOCK_TABLE_STATS();
给CBO采集系统的统计信息，执行计划将更优。
1 2 Exec dbms_sta源自文库s.gather_system_stats(‘INTERVAL’, 180); Select * from sys.aux_stats$;
1-26
改为 exists后的 cost
1-27
索引
1 2 确保唯一性（唯一性索引）。 加快数据查询。
1 B树索引。 升序，降序，反向。 2 位图索引。 3 位图连接索引。 4 函数索引。 5 应用域索引。 提示： 可dump出 索引的结构，来加强对索引结构的了解，注： 33632 为索引的 Object_id:
2
3
1-10
如何解读执行计划中的执行顺序？
在获取SQL语句的执行计划后，这样解读执行顺序：
* 对同一凹层，先上后下执行， * 对不同凹层，先里后外执行。
1-11
对于同一凹层， 先上后下
对于不同凹层， 先里后外。所以 先NL,后 hash。
真正的执行顺序
1-12
执行顺序:3,5,4,2,7,6,1,0
哈希连接
适用等于条件下，大数据量的关联产生大结果集。
排序合并连接
排序合并连接适用于两个已经按照关联字段排序后的结果集间的关联，尤其对大数据量 需要不全等于（>, >=,<, <=）操作的情况下，效率要比NL好。
笛卡尔连接(Cartesian Joins)
1-21
OPTIMIZER_INDEX_COST_ADJ: （1 to 10000）
如何快速获取语句所涉及到的表？
1 2 将语句创建成一视图。 通过user_dependencies 视图，查询与此视图相关的表。
1-20
4种关联方式和两个参数
嵌套连接
Nest Loops outer table inner table – 对于从outer table 出来的每一条记录，都要在inner table 里过滤一遍。 适用于小表间返回较少的结果集，并且有好的关联关系。
1-30
善用分区
到11GR2为止，ORACLE提供的分区类型
分区类型
范围分区 列表分区 哈希分区 引用分区 间隔分区 虚拟列分区 系统分区 完全组合分区 没有分区键，数据被插入时，需要指定存放的分区。 范围，哈希，列表等3种分区的完全二维组合。
特点
常用在业务数据表，适应于时间或可量度数值等可用来分割数据的情况。 常用在地址，编码等较少量固定数值的情况。 按照哈希算法分布存放数据，只能用于等价查询条件，最佳分区数为2的N次幂。 主键在某一分区集，对应的从键也集中存放在某一分区集。
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， 性能分析--> SQL Tuning
1-23
例子
O.CUSTOMER_ID=144是最强的过滤条件，在这种 条件下，语句适合用IN写法，但语句使用了Exists
注意执行计划中 的Cost
1-24
改用IN写法后， COST大大降低
1-25
过滤条件e.department_id=80 在主语句上， 此时应用 exist, 但语句用了IN，看看其 cost
* 当前库， 根据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（常量），，， 关联条件，和过滤条件都称为约束条件。
OPTIMIZER_INDEX_CACHING：（0 to 100）
表示数据缓冲区中，缓存着的索引的数量。此值越大，意味着缓冲区中，缓存的 索引块越多，这对于使用索引作嵌套循环的代价越低，此时CBO将更加偏向走 嵌套循环连接，而非哈希或排序连接。
1-22
IN 和 EXISTS适用的场景
在一个带子查询的语句中，通常来讲，若主语句上的约 束条件强(返回记录数少)，则适合使用EXISTS；若是子查询 语句上的约束条件强，则适用 IN。 这一点符合我在“手工调优的粗略思路”章节中，提 到的思路：CBO尽可能从过滤性最强的条件入手。
SQL语句常用的调优方法
背景：OLTP系统，ORACLE10G
1-1
目录
1 2 3 4 5 6 为什么要调优SQL? 哪些SQL需要调优? 如何获取需要调优的SQL? 如何手工调优SQL? 另外一些调优方法和工具。 11G在执行计划上的一些改进。
1-2
为什么要调优SQL？
通常来讲，要打造高效快捷的应用系统，需要从最初的业务需求 入手，在分析、整理出闭环的业务操作流程后，按照范式的要求，尽 量用简单的数据结构，来实现业务的运行和流转（可以考虑对基础数 据作少量的数据冗余，以减少关联）；同时，根据业务的需求，兼考 虑对历史业务数据的迁移，只保留最近一段时期内的数据，以便让系 统轻装运行。 但是，由于业务的复杂性，设计人员的知识、视野、前瞻性等的 局限，在系统结构设计时，难以考虑周全；并且，由于开发人员的 水平参差不齐，编写的代码也存在缺陷。经统计评估，排除系统结构 设计不善导致的因素外，新的应用系统，有80%的效率问题，是因为 低效的SQL导致，这就需要DBA找出这些低效的SQL，加以优化。
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 中获取。
alter session set events 'immediate trace name treedump level 33632';
1-28
常用的HINT
1 2 3 4 All_rows, First_rows_n – Optimization Goals Leading, Ordered -- Join orders Full, Index, No_index --Access path Use_hash, Use_nl, Use_merge, Use_nl_with_index – Join method 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