Oracle学习之性能优化（八）优化器
我们知道，在sql语句解析的过程中，有一个过程叫优化。Oracle中有一个叫优化器的组件，专门来处理sql的优化。在考虑查询条件和对象引用的许多相关因素后，优化器能确定出执行SQL语句最有效的方式来。对于任何SQL语句，优化器优化的结果,可以极大地影响执行时间。
创新互联 - 成都服务器托管，四川服务器租用，成都服务器租用，四川网通托管,绵阳服务器托管，德阳服务器托管，遂宁服务器托管，绵阳服务器托管，四川云主机，成都云主机，西南云主机，成都服务器托管，西南服务器托管，四川/成都大带宽，大带宽服务器，四川老牌ＩＤＣ服务商
Oracle优化器的优化方法有两种：
CBO 基于成本的优化法则
RBO 基于规则的优化法则
初始化参数optimizer_mode控制着优化器优化的行为
SQL> show parameter optimizer_mode NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ optimizer_mode string ALL_ROWS
optimizer_mode有如下五个取值
CHOOSE 使用CBO还是RBO，基于统计信息是否存在，如果有统计系统则使用CBO，否则使用RBO。
ALL_ROWS 基于CBO，采用尽快返回所有结果的一种最优执行计划。
FIRST_ROWS_n 基于CBO，尽快的返回前n行数据，n的取值为1,10,100,1000
FIRST_ROWS 基于CBO和试探法相结合的方法，查找一种可以最快返回前面少数行的方法；这个参数主要用于向 后兼容。
RULE 采用基于CBO的优化法则。
Oracle 11g的版本只有中间三个参数有效，并且不推荐使用FIRST_ROWS .
我们看看优化器对查询的影响
SQL> alter system set optimizer_mode=all_rows;
System altered.
SQL> conn scott/tiger
Connected.
SQL> set autot traceonly exp
SQL> select * from emp,dept where emp.deptno=dept.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 812 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"="DEPT"."DEPTNO")修改优化器模式后
SQL> alter session set optimizer_mode=first_rows_1;
Session altered.
SQL> select * from emp,dept where emp.deptno=dept.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 3625962092
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 58 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 58 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 1 | 38 | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")CBO有如下三个组件构成
Query Transformer
Estimator
Plan Generator
一、Query Transformer
输入部分是已经被解析器解析过的sql。
查询转换包括如下技术：
View Merging（视图合并）
查询涉及到的每个视图被parser展开，并且分离成一个一个的查询块。查询块本质上代表了视图的定义。将其与查询剩余部分合并成一个总的执行计划，转换后的语句基本上不包含视图了。我们来举例说明
假设有这样一个视图
CREATE VIEW employees_50_vw AS SELECT employee_id, last_name, job_id, salary, commission_pct, department_id FROM employees WHERE department_id = 50;
进行如下查询
SELECT employee_id FROM employees_50_vw WHERE employee_id > 150;
优化器转换后，查询变为
SELECT employee_id FROM employees WHERE department_id = 50 AND employee_id > 150;
Predicate Pushing（谓词推进）
将谓词从内部查询块推进到一个不可合并的查询块中，这样可以使得谓词条件更早的被选择，更早的过滤掉不需要的数据行，提高效率，同样可以使用这种方式允许某些索引的使用。
假设有如下视图
CREATE VIEW all_employees_vw AS ( SELECT employee_id, last_name, job_id, commission_pct, department_id FROM employees ) UNION ( SELECT employee_id, last_name, job_id, commission_pct, department_id FROM contract_workers );
我们发出这样的查询
SELECT last_name FROM all_employees_vw WHERE department_id = 50;
转换后
SELECT last_name FROM ( SELECT employee_id, last_name, job_id, commission_pct, department_id FROM employees WHERE department_id=50 UNION SELECT employee_id, last_name, job_id, commission_pct, department_id FROM contract_workers WHERE department_id=50 );
Subquery Unnesting（子查询解嵌套）
最典型的就是子查询转变为表连接了，它和视图合并的主要区别就在于它的子查询位于where子句，由转换器进行解嵌套的检测。
假设有这样的一个查询
SELECT * FROM sales WHERE cust_id IN ( SELECT cust_id FROM customers );
查询转换后
SELECT sales.* FROM sales, customers WHERE sales.cust_id = customers.cust_id;
Query Rewrite with Materialized Views
假设建立一个物化视图
CREATE MATERIALIZED VIEW cal_month_sales_mv ENABLE QUERY REWRITE AS SELECT t.calendar_month_desc, SUM(s.amount_sold) AS dollars FROM sales s, times t WHERE s.time_id = t.time_id GROUP BY t.calendar_month_desc;
执行如下查询
SELECT t.calendar_month_desc, SUM(s.amount_sold) FROM sales s, times t WHERE s.time_id = t.time_id GROUP BY t.calendar_month_desc;
查询转换后
SELECT calendar_month, dollars FROM cal_month_sales_mv;
二、Estimator
Estimator决定了一个给定的执行计划的总成本。估计量生成三种不同类型的措施,以实现这一目标:
Selectivity
这里的第一个度量值——选择性，表示sql命中的行数与行集的比值。所谓行集可以是表、视图，或者是一个连接或GROUP BY操作的中间结果。选择性与查询中的谓词有关，比如last_name=’Smith’，或者一个联合谓词last_name=’Smith’ and job_type=’Clerk’。一个谓词充当着一个过滤器的角色，在行集中过滤了一定量的行，谓词的选择性是一个比值，它表示一个行集经过谓词的过滤后剩下的行占原有行集的比例。其值在0.0和1.0之间，0.0表示在行集中没有行被选择；1.0表示行集中的所有行都被选择了。如果没有可用的统计信息，评估器为选择性赋予一个内部的缺省值，这个内部缺省值随着谓词的不同而不同。例如：等式谓词（last_name=’Smith’）的内部缺省值低于范围谓词（last_name>’Smith’），评估器会假定等式谓词返回的行数小于范围谓词。当存在可用的统计信息，评估器将使用统计信息来估算选择性。例如：对于一个等式谓词（last_name=’Smith’），选择性的值是distinct last_name的倒数即：（1/count(distinct last_name)）。但是如果在last_name字段上存在直方图（histogram），则选择性值为：coun（last_name）where last_name=’Smith’ / count（last_name）where last_name is not null。可见在数据倾斜的字段上应用直方图能够帮助CBO进行准确的选择性评估
Cardinality
基数就是行集中行的数量。基数分为：
基础基数（Base cardinality）：就是基表中的行数。基础基数在表分析期间获得。如果表没有可用的统计信息，则评估器利用表中区（extents）的数量来估算基础基数。
有效基数（Effective cardinality）：就是从基表中选择的行数。有效基数与具体的谓词和字段有关。有效基数是根据基础基数和作用于该表的所有谓词的选择性得出的，如果没有谓词作用于该表，则有效基数就等于基础基数。
连接基数（Join cardinality）：就是两个行集在连接之后产生的行数。连接就是由两个行集产生的笛卡尔积，再由连接谓词过滤结果。因此，连接基数是两个行集基数与连接谓词选择性的乘积。
Distinct基数（Distinct cardinality）：就是一个行集的字段distinct之后的行数。一个
行集的distinct基数是基于字段中的数据的。例如：一个拥有100行的行集，如果一个字段distinct之后还剩下20行，则distinct基数就为20。
Group基数（Group cardinality）：就是一个行集在应用GROUP BY之后产生行的数量。Group基数依赖于每个组中字段的distinct基数和行集的行数。
Cost
成本是用来描述工作单元或资源使用的。CBO是用磁盘I/O、CPU和内存的使用情况来作为工作单元的，因此CBO使用的成本可以描述为，在一次操作的执行过程中所用的磁盘I/O数量以及CPU和内存的总使用量。
三、Plan Generator
主要功能是把给定的query生成各种可能的计划，并且挑出成本最低的一个。
新闻标题：Oracle学习之性能优化（八）优化器
文章来源：http://scyanting.com/article/jhpjih.html
