##################
# 1.1 优化器选项 #
##################
SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
该值可以为：
ALL_ROWS : 优化器将寻找能够在最短的时间内完成语句的执行计划(通常表示“返回所有行”)
FIRST_ROWS_N : N可以为1、10、100或1000，优化器首先通过彻底分析第一个连接顺序(join order)来估计返回行的总数目。
其目标在于找到能够以最小的资源消耗返回整个数据片断的执行计划。
FIRST_ROWS : 9i中过时，目标在于寻找能够在最短时的时间内返回结果集的第一行的执行计划。
RULE : 基于规则的优化，过时
CHOOSE : 提供运行时选择方式，可以在基于规则的优化和ALL_ROWS之间进行选择。
DBA_SCHEDULER_JOBS视图中有一个JOB_NAME为GATHER_STATS_JOB的作业
它默认情况下在晚上10点收集没有统计信息的表，如果数据库在此时还很忙，需要关闭该自动作业
关闭方法：exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
GATHER_STATS_JOB作业是由XX\RDBMS\ADMIN\catmwin.sql该脚本创建
动态采样：在10g中所有的缺少最新统计信息的表将采用动态采样。因为在10g中，参数optimizer_dynamic_sampling为2，在9i中为1
SQL> show parameter optimizer_dynamic_sampling
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
##############
# 成本的定义 #
##############
成本表示优化器对执行语句所用时间的最优估计。
9i中，优化器引入一个新功能，称CPU成本计算。
9i 10g 通过视图v$sql_plan_statistics 和 v$sql_plan_statistics_all收集运行时统计信息
##################
# 变换和成本计算 #
##################
在进行成本计算前，Oracle可能会将SQL语句变换为等价的语句，并为等价的语句进行成本计算
变换可以分为3类，a)不可以进行 b)如果可能的话总是可以进行 c)可以进行，但由于成本过高而放弃
例子：
创建视图：
create or replace view avg_val_view AS
select
id_par, avg(val) avg_val_t1
from t2
group by
id_par;
SQL语句：
select
t1.vc1, avg_val_t1
from
t1, avg_val_view
where
t1.vc2 = lpad(18,32)
and avg_val_view.id_par = t1.id_par
;
合并视图的“等价代码”
select
t1.vc1,
avg(t2.val)
from
t1, t2
where
t1.vc2 = lpad(18, 32)
and t2.id_par = t1.id_par
group by
t1.vc1, t1.id_par;
一些参数：
_optimizer_cost_model 可以为值为 IO, CHOOSE
_complex_view_merging 用于控制是否合并视图
merge view时的差异，主要参数在于"_complex_view_merging"
_complex_view_merging在8i上缺省是false，也就是不会打开视图重写语句。
而该参数在9i和10g上都缺省是true，允许重写语句。
其二是CBO在8i上受上面参数的影响，不会考虑打开视图重写语句；
在9i上则走到了另一个极端，只要允许（视图中含有GROUP BY或SELECT列表中含有DISTICNT）进行重写，一律重写；
10g上则是会进行COST的比较（这必须trace 10053事件了，这里并不能看出），找合算的做。
9i的对比：
SQL> alter session set "_complex_view_merging" = false;
会话已更改。
SQL> select
2 t1.vc1, avg_val_t1
3 from
4 t1, avg_val_view
5 where
6 t1.vc2 = lpad(18,32)
7 and avg_val_view.id_par = t1.id_par
8 ;
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=1 Bytes=95)
1 0 HASH JOIN (Cost=9 Card=1 Bytes=95)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=1 Bytes=69)
3 1 VIEW OF 'AVG_VAL_VIEW' (Cost=6 Card=32 Bytes=832)
4 3 SORT (GROUP BY) (Cost=6 Card=32 Bytes=224)
5 4 TABLE ACCESS (FULL) OF 'T2' (Cost=3 Card=1024 Bytes=
7168)
SQL> alter session set "_complex_view_merging" = true;
会话已更改。
SQL> select
2 t1.vc1, avg_val_t1
3 from
4 t1, avg_val_view
5 where
6 t1.vc2 = lpad(18,32)
7 and avg_val_view.id_par = t1.id_par
8 ;
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=23 Bytes=1909
)
1 0 SORT (GROUP BY) (Cost=8 Card=23 Bytes=1909)
2 1 HASH JOIN (Cost=6 Card=32 Bytes=2656)
3 2 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=1 Bytes=76)
4 2 TABLE ACCESS (FULL) OF 'T2' (Cost=3 Card=1024 Bytes=71
68)
10g的对比：
SQL> alter session set "_complex_view_merging" = false;
会话已更改。
SQL> select
2 t1.vc1, avg_val_t1
3 from
4 t1, avg_val_view
5 where
6 t1.vc2 = lpad(18,32)
7 and avg_val_view.id_par = t1.id_par
8 ;
执行计划
----------------------------------------------------------
Plan hash value: 661622897
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 95 | 10 |
| 1 | NESTED LOOPS | | 1 | 95 | 10 |
| 2 | VIEW | AVG_VAL_VIEW | 32 | 832 | 9 |
| 3 | HASH GROUP BY | | 32 | 224 | 9 |
| 4 | TABLE ACCESS FULL | T2 | 1024 | 7168 | 4 |
|* 5 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 69 | 1 |
|* 6 | INDEX UNIQUE SCAN | T1_PK | 1 | | |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("T1"."VC2"=' 18')
6 - access("AVG_VAL_VIEW"."ID_PAR"="T1"."ID_PAR")
Note
-----
- cpu costing is off (consider enabling it)
SQL> alter session set "_complex_view_merging" = true;
会话已更改。
SQL> select
2 t1.vc1, avg_val_t1
3 from
4 t1, avg_val_view
5 where
6 t1.vc2 = lpad(18,32)
7 and avg_val_view.id_par = t1.id_par
8 ;
执行计划
----------------------------------------------------------
Plan hash value: 1541385322
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 2024 | 9 |
| 1 | HASH GROUP BY | | 23 | 2024 | 9 |
| 2 | NESTED LOOPS | | 32 | 2816 | 5 |
| 3 | TABLE ACCESS FULL | T2 | 1024 | 7168 | 4 |
|* 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 81 | 1 |
|* 5 | INDEX UNIQUE SCAN | T1_PK | 1 | | |
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T1"."VC2"=' 18')
5 - access("ID_PAR"="T1"."ID_PAR")
Note
-----
- cpu costing is off (consider enabling it)
聚集视图：使用GROUP BY
如：
create or replace view avg_val_view AS
select
id_par, avg(val) avg_val_t1
from t2
group by
id_par;
总结：一般来说合并聚集视图时，使用no_merge会达到比较好的效果，它不重写视图
阅读(1968) | 评论(0) | 转发(0) |
