数据库的优化与调优：从理论到实践

第一篇 执行计划 执行计划是指示Oracle如何获取和过滤数据.产生最终结果集,是影响SQL语句执行性能的关键因素.我们在深入了解执行计划之前,首先需要知道执行计划是在什么时候产生的,以及如何让SQL引擎为语句生成执行计划. 在深入了解执行计划之前,我们先了解SQL语句的处理执行过程.当一条语句提交到Oracle后,SQL引擎会分为三个步骤对其处理和执行:解析(Parse).执行(Execute)和获取(Fetch),分别由SQL引擎的不同组件完成.SQL引擎的组件如图1-1所示. 1. SQL

2.5 执行计划中其他信息的含义 通过DBMS_XPLAN输出执行计划,除了计划本身外,还可以获得一些其他信息帮助我们进一步分析执行计划及语句性能. 2.5.1 查询块和对象别名 在使用DBMS_XPLAN显示执行计划时,选择'ADVANCED'预定义格式作为参数或者加入'ALIAS'控制字符串,可以在输出中看到以下内容: Query Block Name / Object Alias (identified by operation id): -------------------------

1.2 显示执行计划 我们现在知道,有三个途径可以获取查询计划:v$sql_plan.dba_hist_sql_plan和PLAN_TABLE.如果需要读取一条SQL语句的执行计划,就需要知道该条语句的SQL_ID,如果该语句存在多个游标或者执行计划,则还需要知道游标的CHILD_NUMBER或计划的哈希值(可选).而无论我们通过哪个途径来获取执行计划,显示方式主要是两种:语句查询和包DBMS_XPLAN显示. 1.2.1 通过查询语句显示计划 通过查询语句从一些视图里读出执行计划并作格式化输出

1.1 生成执行计划 在Oracle中,任何一条语句在解析过程中都会生成一个唯一的数值标识,即SQL_ID.而同一条语句,在解析过程中,可能会因为执行环境的改变(例如某些优化参数被改变)而生成多个版本的游标,不同的游标会有不同的执行计划.每个游标都会按顺序赋予一个序列号,即CHILD_NUMBER,一条语句生成的第一个游标的CHILD_NUMBER为0:相应的,Oracle会为每个执行计划生成一个哈希值以作区分.而多个不同版本的游标,其执行计划可能会相同,也可能不同. 因此,我们可以知道,一条合

2.4 执行计划各个操作的含义 通常我们所说的执行计划操作包含两个部分:操作与其选项.例如,哈希关联反关联(HASH JOIN ANTI)中,哈希关联(HASH JOIN)是一种操作,"反"关联(ANTI)则是其选项:该操作还可以与其他选项(如"半"关联,SEMI)配合形成不同的执行计划操作. 执行计划中的操作数量非常多.我们下面列出的操作是Oracle 10gR2中的绝大多数操作.Oracle的每个版本都会有一些新的特性出现,而其中一些新特性又会带来新的操作,或者

3.1 启发式查询转换 所有的启发式查询转换都是基于一套优化器内建的规则.在查询转换阶段,转换器会逐个针对这些规则对查询进行检查,确定其是否满足转换规则,一旦满足,转换器就对其进行转换. 3.1.1 简单视图合并 我们知道,视图(View)的实质就是一条查询语句.在解析阶段,语句中的每个视图都会被展开至一个查询块中.如果未做视图合并,优化器则会单独分析每个视图,并为定义视图的查询语句生成一个视图子计划.然后再分析整个查询的其他部分,并生成执行计划.在这种情况下,由于视图的执行计划和整体执行计划不

2.3 执行计划各个列的含义 在执行计划中,除了ID.Operation和Name之外,还有其他一些列.这些列的数据是根据需要从PLAN_TABLE.V$SQL_PLAN.V$SQL_PLAN_STATISTICS_ALL等表和视图中读取的.它们可以帮助我们进一步理解该执行计划(例如优化器对各个操作的估算数据.实际运行中各个操作的性能数据等).以下就是各个列的描述. Rows/E-Rows:优化器估算出当前操作返回给上一级操作的数据记录数,如果计划中同时输出收集到实际记录数,则会显示为E-Row

2.1 执行计划的基本数据 我们用代码清单2-1中的查询计划为例,解释计划访问中基本数据的含义.代码清单2-1执行计划查询 HELLODBA.COM>exec sql_explain('select o.owner, o.object_name, o.object_id from t_users u, t_objects o where u.username=o.owner and o.object_name like :A and u.user_id=:B','BASIC PREDICATE'

3.2 基于代价的查询转换 在进行基于代价的查询转换时,转换器先确认查询是否满足转换条件.一旦满足,就会对各种可行的转换方式进行枚举,并对它们进行代价估算,找到代价最低的方式.由此可见,相对于启发式查询转换,基于代价的查询转换是一个相当消耗资源(CPU和内存)的过程.提示:Oracle中有一个优化器参数_OPTIMIZER_COST_BASED_TRANSFORMATION,用它来控制是否进行基于代价的查询转换,以及如何进行基于代价的查询转换,从而限制其对资源的消耗. 3.2.1 复杂视图合并