CBO学习---第2章--表扫描（Tablescans）第2章表扫描（Tablescans）全书代码下载CBO的4种策略：（1）传统策略：IO次数(oracle7)（2）SS1IOST--系统统计（systemstatistics，针对操作系统的统计信息）1；...SyntaxH
CBO学习---第2章--表扫描（Tablescans）
CBO的4种策略：
（1）传统策略：IO次数 (oracle7)
（2）SS1=
IOST --系统统计（system statistics，针对操作系统的统计信息）1；IO的大小和时间 (oracle8i)
（3）SS2=IOST+CPU--增加了CPU Costing(oracle9i)
（4）SS3=IOST+CPU+Cache--增加了缓存的说明(oracle10g)
Oracle7是传统策略，后面版本陆续增加CBO功能，Oracle9i引入CPU，10g进一步加强。SS2是normal变体。
隐藏参数“_optimizer_cost_model=io”,使优化器处于SS1，不计算CPU等，即使有workload参数，也不会用其参与计算
/**************************************************************************************************************************************/
2.1Getting Started
查看执行计划的方法：
本书提供的方法：
在本章的代码附件中，存在下面两个脚本
[sql]
plan_run81.sql
plan_run92.sql
运行该脚本之前，在相对路径下建立一个名为target.sql的文件，在该文件中，写下需要查看执行计划的SQL语句。并在SQLplus中运行该脚本，如：
[sql]
SQL> @plan_run81
STATE_ID
----------
110004
Id Par Pos Ins Plan
---- ---- ---- ---- ---------------------------------------------------------
0 3 SELECT STATEMENT (all_rows) Cost (3,14,1218)
1 0 1 1 TABLE ACCESS TABLE SCOTT EMP (full) Cost (3,14,1218)
Output file is 110004.lst
--则会打出相应的执行计划，并输出于110004.lst文件中。
SQL> @plan_run92
会话已更改。
STATE_ID
----------
110004
已选择 1 行。
已解释。
Id Par Pos Ins Plan
---- ---- ---- ---- -----------------------------------------------------------------------------------
0 3 SELECT STATEMENT (all_rows) Old Cost (3,14,1218) New Cost (3,39667,0)
1 0 1 1 TABLE ACCESS TABLE SCOTT EMP (full) Old Cost (3,14,1218) New Cost (3,39667,0)
Output file is 110004.lst
--其中New Cost (3,39667,0)，括号中第二个值是CPU操作次数，第3个值是tempspace占用字节
注意：要及时了解Oracle的最新发展情况，就必须密切关注dbmsutl.sql和dbms_xplan包。（有机会做的对比专题）
/**************************************************************************************************************************************/
最常用方法：sqlplus的自动追踪
[sql]
SQL> set autot trace exp
SQL> select * from emp;
执行计划
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1218 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
/**************************************************************************************************************************************/
本书示例最初环境如下：
(1)db_block_size=8192
(2)db_file_multiblock_read_count=8
(3)本地管理的表空间
(4)统一extent大小1MB
(5)freelist的块管理
(6)optimizer_mode=ALL_ROWS)
(7)cpu_costing最初禁用（alter session set "_optimizer_cost_model"=io;）
/**************************************************************************************************************************************/
本章代码附件中：
[sql]
tablescan_01.sql
通过pctfree 99 构建了一个10000行，且跨越10000个块（实际分配了10240个块，高水位线在10000个块上）的表
该脚本清除了
系统统计，关闭了CPU_Costing计算。
[sql]
exec dbms_stats.delete_system_stats;
alter session set "_optimizer_cost_model"=io
执行结果：
[sql]
SQL> sta tablescan_01
会话已更改。
PL/SQL 过程已成功完成。
drop table t1
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
PL/SQL 过程已成功完成。
表已创建。
PL/SQL 过程已成功完成。
会话已更改。
db_file_multiblock_read_count = 4
执行计划
----------------------------------------------------------
Plan hash value: 3724264953
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 2431 |
| 1 | SORT AGGREGATE | | 1 | 4 | |
| 2 | TABLE ACCESS FULL| T1 | 10000 | 40000 | 2431 |
-----------------------------------------------------------
Note
-----
- cpu costing is off (consider enabling it)
会话已更改。
db_file_multiblock_read_count = 8
执行计划
----------------------------------------------------------
Plan hash value: 3724264953
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1541 |
| 1 | SORT AGGREGATE | | 1 | 4 | |
| 2 | TABLE ACCESS FULL| T1 | 10000 | 40000 | 1541 |
-----------------------------------------------------------
Note
-----
- cpu costing is off (consider enabling it)
会话已更改。
db_file_multiblock_read_count = 16
执行计划
----------------------------------------------------------
Plan hash value: 3724264953
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 977 |
| 1 | SORT AGGREGATE | | 1 | 4 | |
| 2 | TABLE ACCESS FULL| T1 | 10000 | 40000 | 977 |
-----------------------------------------------------------
Note
-----
- cpu costing is off (consider enabling it)
会话已更改。
db_file_multiblock_read_count = 32
执行计划
----------------------------------------------------------
Plan hash value: 3724264953
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 620 |
| 1 | SORT AGGREGATE | | 1 | 4 | |
| 2 | TABLE ACCESS FULL| T1 | 10000 | 40000 | 620 |
-----------------------------------------------------------
Note
-----
- cpu costing is off (consider enabling it)
会话已更改。
db_file_multiblock_read_count = 64
执行计划
----------------------------------------------------------
Plan hash value: 3724264953
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 393 |
| 1 | SORT AGGREGATE | | 1 | 4 | |
| 2 | TABLE ACCESS FULL| T1 | 10000 | 40000 | 393 |
-----------------------------------------------------------
Note
-----
- cpu costing is off (consider enabling it)
会话已更改。
db_file_multiblock_read_count = 128
执行计划
----------------------------------------------------------
Plan hash value: 3724264953
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 250 |
| 1 | SORT AGGREGATE | | 1 | 4 | |
| 2 | TABLE ACCESS FULL| T1 | 10000 | 40000 | 250 |
-----------------------------------------------------------
Note
-----
- cpu costing is off (consider enabling it)
会话已更改。
执行计划
----------------------------------------------------------
Plan hash value: 136660032
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 582 | 2328 | 1555 |
| 1 | HASH GROUP BY | | 582 | 2328 | 1555 |
| 2 | TABLE ACCESS FULL| T1 | 10000 | 40000 | 1541 |
-----------------------------------------------------------
Note
-----
- cpu costing is off (consider enabling it)
在清除了系统统计和关闭CPU_Costing时，随着db_file_multiblock_read_count的增加，Cost逐步减小
上面的结果是该脚本在10g下运行的，虽然关闭了cpu_costing，但所计算的cost仍然比8i下略大
/**************************************************************************************************************************************/
传统COST的计算公式，从下面推算出来
[sql]
CPU costing model:
Cost = (
#SRds * sreadtim +
#MRds * mreadtim +
#CPUCycles / cpuspeed
) / sreadtim
本示例中，#SRds=0（刚建立的新表，没有经过删改，没有碎片，只是连续10000个块的读取，没有单块），#MRds=10000，CPU_Costing关闭了。
[sql]
Cost=#MRds * mreadtim/sreadtim
adjusted_mbrc=mreadtim/sreadtim；即典型多块读取，平均一次读多少块。（adjusted_mbrc为调整后的dbf_mbrc，只用来计算cost用）
注意：adjusted_mbrc的值，在未使用系统统计和关闭CPU_Costing时，只与db_file_multiblock_read_count有关；在使用系统统计后，就使用系统统计的值来计算cost。
[sql]
Cost=10000/adjusted_mbrc
根据上面脚本执行后，不同db_file_multiblock_read_count值下的Cost值（该值为书中的8i值），可计算出adjusted_mbrc，统计于下表中
[sql]
db_file_multiblock_read_count Cost Adjusted dbf_mbrc
4 2,396 4.17
8 1,518 6.59
16 962 10.40
32 610 16.39
64 387 25.84
128 245 40.82
db_file_multiblock_read_count与adjusted_mbrc为一一对应关系，例如：db_file_multiblock_read_count=32时，扫描23729个块的表时，代价为ceil(23729/16.39)
/**************************************************************************************************************************************/
本章代码附件中：
[sql]
calc_mbrc.sql
通过dbms_stats.set_table_stats，欺骗优化器，指出T1表有128000个块，平均行长3500，来让优化器算出更加准确的Cost，来计算adjusted_mbrc.
详细列出db_file_multiblock_read_count从1到128，/*+ nocpu_costing */下，所对应的mbrc值，OLD_COST值可能是比8i还早的Cost值
虽然输出了/*+ cpu_costing */的计划到plan_table中，但最后的查询并没有查到它们，因为statement_id like &＃39;%N%&＃39;
[sql]
SQL> sta calc_mbrc
已选择 1 行。
会话已更改。
会话已更改。
表已删除。
表已创建。
表已分析。
PL/SQL 过程已成功完成。
已删除768行。
提交完成。
PL/SQL 过程已成功完成。
Id ACT_COST OLD_COST EFF_MBRC
---- ---------- ---------- ----------
1 76353 128000 1.676
2 48383 64000 2.646
3 37051 42667 3.455
4 30660 32000 4.175
5 26472 25600 4.835
6 23479 21333 5.452
... ... ... ...
125 3183 1024 40.226
126 3166 1016 40.442
127 3150 1008 40.648
128 3134 1000 40.855
已选择128行。
adjusted_mbrc值变化不大，比较准确，Oracle启动时，会根据所在的操作系统，来设置最大db_file_multiblock_read_count，并通过它来确定adjusted_mbrc
/**************************************************************************************************************************************/
本章代码附件中：
[sql]
tablescan_01.sql
中的最后一个查询，如下：
[sql]
alter session set db_file_multiblock_read_count = 8;
select
val, count(*)
from t1
group by
val
;
执行计划
----------------------------------------------------------
Plan hash value: 136660032
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 582 | 2328 | 1555 |
| 1 | HASH GROUP BY | | 582 | 2328 | 1555 |
| 2 | TABLE ACCESS FULL| T1 | 10000 | 40000 | 1541 |
-----------------------------------------------------------
Note
-----
- cpu costing is off (consider enabling it)
表明两点：
(1)HASH GROUP BY并非像上面的SORT AGGREGATE不产生代价，而是产生了14的Cost，是否合理有待研究（8i中Cost为22）
(2)Rows为582显然是从系统表（user_tab_columns.num_distinct）中读取的，作为一个预估值放在这里。
/**************************************************************************************************************************************/
2.2Onwards & Upwards
转战Oracle9i，分析下9i的ASSM特性（负面影响太多，为特殊需求而定，平常不实用）。
2.2.1块大小的影响
在9i中，做8i中的同样查询，代价会明显增加1，这是由隐藏参数，"_tablescan_cost_plus_One=ture"引起的
这意味着，在做表扫描时，会先访问段头，获取段的本地管理信息（bitmap）等，以避免在非常小的表中，访问索引。
ASSM：不同的表空间使用不同的block_size的块；对此下面的代码，测试不同块大小下，如何根据db_file_multiblock_read_count值来计算cost
本章代码附件中：
[sql]
tablescan_01a.sql
tablescan_01b.sql
分别在block size不同的表空间中，建立测试表，然后进行mbrc的计算
发现随着block大小的变化，db_file_multiblock_read_count值也会发生变化，
block_size*db_file_multiblock_read_count(block_size下)的结果=8k*db_file_multiblock_read_count(8k下)
说明：一次从硬盘上读取的最大值=8k*db_file_multiblock_read_count(8k下)，是一次性确定的，不会因ASSM改变而改变。
by the way
db_file_multiblock_read_count=128，是指128个操作系统块（0.5k）的说法，也听说过，有待研究
/**************************************************************************************************************************************/
2.2.2CPU代价计算
系统统计(system statistics)
是对于操作系统的一组参数，用于计算Cost。
[sql]
execute dbms_stats.gather_system_stats(&＃39;start&＃39;);
--隔一段时间
execute dbms_stats.gather_system_stats(&＃39;stop&＃39;);
&＃39;start&＃39;，是记录下v$filestat(实际上是X$开头的基表)和v$sysstat表的初始信息。
&＃39;stop&＃39;，再次记录上面两个表的信息，然后通过相隔的时间，计算所需的系统统计参数。
[sql]
select
pname, pval1
from
sys.aux_stats$
where
sname = &＃39;SYSSTATS_MAIN&＃39;;
[sql]
PNAME PVAL1
----------- ----------
CPUSPEED 559
SREADTIM 1.299
MREADTIM 10.204
MBRC 6
MAXTHR 13938448
SLAVETHR 244736
本章代码附件中：
[sql]
set_system_stats.sql
[sql]
begin
dbms_stats.set_system_stats(&＃39;MBRC&＃39;,12);
dbms_stats.set_system_stats(&＃39;MREADTIM&＃39;,30);
dbms_stats.set_system_stats(&＃39;SREADTIM&＃39;,5);
dbms_stats.set_system_stats(&＃39;CPUSPEED&＃39;,500);
end;
/
[sql]
--MBRC，典型多块读取12个块。
--MREADTIM，平均多块读取时间30ms
--SREADTIM，平均单块读取时间5ms
--CPUSPEED，CPU频率500MHz=500 000 000 Hz
[sql]
alter system flush shared_pool;
--刷新shared_pool，清理游标，清理软分析
MAXTHR和SLAVETHR与并行执行有关，这两个值可控制最大并行度。这两个值可设置为-1。如果其他4个值的任何一个设置为-1，都不会调用cpu_costing。（有待验证）
在10g中，系统统计有两组，一组为nowordload，另一组为wordload。如果wordload不正确，则会回头实用nowordload
/**************************************************************************************************************************************/
本章代码附件中：
[sql]
tablescan_02.sql
Oracle9i中，开启workload，Cost不再随db_file_multiblock_read_count值的变化而变化
[sql]
alter session set "_optimizer_cost_model"=choose;
--注意："_optimizer_cost_model"=io；是会关闭workload的参数参与计算的。
@tablescan_02
db_file_multiblock_read_count = 4
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5031 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF &＃39;T1&＃39; (Cost=5031 Card=10000 Bytes=40000)
?
db_file_multiblock_read_count = 8
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5031 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF &＃39;T1&＃39; (Cost=5031 Card=10000 Bytes=40000)
?
db_file_multiblock_read_count = 16
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5031 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF &＃39;T1&＃39; (Cost=5031 Card=10000 Bytes=40000)
?
db_file_multiblock_read_count = 32
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5032 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF &＃39;T1&＃39; (Cost=5032 Card=10000 Bytes=40000)
?
db_file_multiblock_read_count = 64
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5032 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF &＃39;T1&＃39; (Cost=5032 Card=10000 Bytes=40000)
?
db_file_multiblock_read_count = 128
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5032 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF &＃39;T1&＃39; (Cost=5032 Card=10000 Bytes=40000)
/**************************************************************************************************************************************/
[sql]
alter session set db_file_multiblock_read_count = 8;
[sql]
--将下面语句放入TARGET.SQL中
select
val, count(*)
from t1
group by
val
;
--运行plan_run92.sql
@plan_run92
SELECT STATEMENT (all_rows) Cost(5031,1,4) New(5001,72914400,0)
SORT (aggregate)
TABLE ACCESS (analyzed) T1 (full) Cost(5031,10000,40000) New(5001,72914400,0)
[sql]
Cost = (
#SRds * sreadtim +
#MRds * mreadtim +
#CPUCycles / cpuspeed
) / sreadtim
将sreadtim移到括号内
[sql]
Cost = (
#SRds +
#MRds * mreadtim / sreadtim +
#CPUCycles / (cpuspeed * sreadtim)
)
--#SRds=0
--#MRds=blocks/mbrc=10000/12
Cost=(10000/12*30/5)+CPUCycles / (500 * 5)=5000+ CPUCycles/2500
_tablescan_cost_plus_one
Cost=5001+ CPUCycles/2500
/**************************************************************************************************************************************/
2.2.2.1 IO位
[sql]
IOcost=#MRds * mreadtim / sreadtim
IOcost=10000/12*30/5 --其实mreadtim的单位为ms，计算中是需要10^-6s为单位，应该加3个0，只是对于sreadtim的比值说，结果是一致的。
_tablescan_cost_plus_One=ture
IOcost=5001；与下面new中的IOcost相同
[sql]
SELECT STATEMENT (all_rows) Cost(5031,1,4) New(5001,72914400,0)
SORT (aggregate)
TABLE ACCESS (analyzed) T1 (full) Cost(5031,10000,40000) New(5001,72914400,0)
在使用系统统计后，mbrc的实际值不再是根据db_file_multiblock_read_count所算出adjusted_mbrc值了
实际MBRC=system mbrc*mreadtim/sreadtim
/**************************************************************************************************************************************/
10g中，如果没有收集系统统计（或删除了系统统计），你会发现下面的三组值，用于无负载状态（noworkload）
[sql]
SQL> exec dbms_stats.delete_system_stats;
PL/SQL 过程已成功完成。
select
pname, pval1
from
sys.aux_stats$
where
sname = &＃39;SYSSTATS_MAIN&＃39;;
PNAME PVAL1
------------------------------ ----------
CPUSPEED
CPUSPEEDNW 2287.401
IOSEEKTIM 10
IOTFRSPEED 4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM
已选择9行。
nowordload时，就会用这三个值去就计算cost
[sql]
MBRC =db_file_multiblock_read_count
sreadtim =IOSEEKTIM+db_block_size/IOTFRSPEED
mreadtim =IOSEEKTIM+db_block_size*db_file_multiblock_read_count/IOTFRSPEED
如果db_file_multiblock_read_count=8
MBRC=8
sreadtim=10+8192/4096=12ms
mreadtim=10+8192*8/4096=26ms
如果db_file_multiblock_read_count=16
MBRC=16
sreadtim=10+8192/4096=12ms
mreadtim=10+8192*16/4096=32ms
因此，使用nowordload计算cost时，cost会随db_file_multiblock_read_count变化而变化
本章代码附件中：
[sql]
tablescan_03.sql
设置noworkload的三个参数，清除其他workload参数，然后计算cost值，统计入下表中
[sql]
db_file_multiblock_read_count Traditional Standard cpu_costing Noworkload cpu_costing
4 2,397 2,717 3,758
8 1,519 2,717 2,717
16 963 2,717 2,196
32 611 2,717 1,936
64 388 2,717 1,806
128 246 2,717 1,740
将脚本中"-- tablespace test_8k"该行注释，一般默认表空间即为8k块的表空间，有此句反而可能过不去。
使用IO，得到第二列值
[sql]
alter session set "_optimizer_cost_model"=io
使用choose，并使用noworkload，得到第四列值
[sql]
alter session set "_optimizer_cost_model"=choose
begin
dbms_stats.set_system_stats(&＃39;CPUSPEEDNW&＃39;,913.641725);
dbms_stats.set_system_stats(&＃39;IOSEEKTIM&＃39;,10);
dbms_stats.set_system_stats(&＃39;IOTFRSPEED&＃39;,4096);
end;
/
使用choose，并使用workload，关闭清除系统统计，得到第三列值
[sql]
alter session set "_optimizer_cost_model"=choose
begin
dbms_stats.set_system_stats(&＃39;MBRC&＃39;,8);
dbms_stats.set_system_stats(&＃39;MREADTIM&＃39;,26.0);
dbms_stats.set_system_stats(&＃39;SREADTIM&＃39;,12.0);
dbms_stats.set_system_stats(&＃39;CPUSPEED&＃39;,913.641725);
end;
/
-- begin execute immediate &＃39;begin dbms_stats.delete_system_stats; end;&＃39;;
-- exception when others then null;
-- end;
/**************************************************************************************************************************************/
不管有没有系统统计，优化器只是用这些值来计算代价，执行器用db_file_multiblock_read_count来进行扫描，不是说mbrc=12，就每次扫描12个块。（有待研究）
当db_file_multiblock_read_count=8时，显然将MBRC设置为12是件很傻的事，但为了优化器的算法，相信我这么设置是对的，之后执行器每次会读取8个块。
/**************************************************************************************************************************************/
本章代码附件中：
[sql]
tablescan_04.sql
测试的是不同数据块下，noworkload在db_file_multiblock_read_count = 8时，所计算出的不同cost
[sql]
Block size noworkload normal
---------- ---------- ------
2K 7,729 10,854
4K 4,387 5,429
8K 2,717 2,717
16K 1,881 1,361
--没有实测，ASSM用的几率不高，懒得分析了
/**************************************************************************************************************************************/
2.2.2.1 CPU位
[sql]
Cost = (
#SRds +
#MRds * mreadtim / sreadtim +
#CPUCycles / (cpuspeed * sreadtim)
)
经上面IO位的计算
[sql]
Cost =5001 + #CPUCycles / (cpuspeed * sreadtim)
=5001 + #CPUCycles / (500 * 5000)
上面运行的结果中
[sql]
SELECT STATEMENT (all_rows) Cost(5031,1,4) New(5001,72914400,0)
SORT (aggregate)
TABLE ACCESS (analyzed) T1 (full) Cost(5031,10000,40000) New(5001,72914400,0)
New(5001,72914400,0)，第二个参数为#CPUCycles
[sql]
Cost=5001 + 72914400 / (500 * 5000) =5001+29.2=5030.2
与结果5031相当接近
/**************************************************************************************************************************************/
2.2.3 CPU Costing的作用
本章代码附件中：
[sql]
cpu_costing.sql
执行完全相同的SQL语句，只是where条件的顺序不同
注意:使用/*+ cpu_costing ordered_predicates */ 这个hint来强迫Oracle遵循where后面条件的顺序来执行语句，否则Oracle的CBO将会自己选择最佳的次序。
[sql]
Id Par Pos Ins Plan
-- ---- ---- ---- -----------------------------------------------------------------------------------------------------------------------------------------------
0 6 SELECT STATEMENT (all_rows) Old Cost (6,1,9) New Cost (6,1091968,0)
1 0 1 1 TABLE ACCESS (analyzed) TABLE SCOTT T1 (full) Old Cost (6,1,9) New Cost (6,1091968,0) Filter (TO_NUMBER("V1")=1 AND "N2"=18 AND "N1"=998)
New Cost (6,1091968,0)中的1091968与下面的对应值相等，说明该脚本打出的CPU cost仅为#CPUCycles（CPU操作数）
[sql]
SQL> @cpu_costing
会话已更改。
Predicted cost (9.2.0.6): 1070604
Filter Predicate CPU cost
--------------------------------------------------------------------- ------------
TO_NUMBER("V1")=1 AND "N2"=18 AND "N1"=998 1,091,968
Predicted cost (9.2.0.6): 762787
Filter Predicate CPU cost
--------------------------------------------------------------------- ------------
"N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1 784,150
Predicted cost (9.2.0.6): 1070232
Filter Predicate CPU cost
--------------------------------------------------------------------- ------------
TO_NUMBER("V1")=1 AND "N1"=998 AND "N2"=18 1,091,595
Predicted cost (9.2.0.6): 762882
Filter Predicate CPU cost
--------------------------------------------------------------------- ------------
"N1"=998 AND TO_NUMBER("V1")=1 AND "N2"=18 784,245
Predicted cost (9.2.0.6): 770237
Filter Predicate CPU cost
--------------------------------------------------------------------- ------------
"N2"=18 AND "N1"=998 AND TO_NUMBER("V1")=1 791,600
Predicted cost (9.2.0.6): 785604
Filter Predicate CPU cost
--------------------------------------------------------------------- ------------
"N2"=18 AND TO_NUMBER("V1")=1 AND "N1"=998 806,968
Left to its own choice of predicate order
Filter Predicate CPU cost
--------------------------------------------------------------------- ------------
"N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1 784,150
And one last option where the coercion on v1 is not needed
Predicted cost (9.2.0.6): 770604
Filter Predicate CPU cost
--------------------------------------------------------------------- ------------
"V1"=&＃39;1&＃39; AND "N2"=18 AND "N1"=998 791,968
尽管这几个SQL是同义的，但由于条件的顺序不同，导致了CPU的cost不同；如果去掉hint，优化器会自动选择#CPUCycles最小值的执行；当然如果v1=&＃39;1&＃39;来执行，会减少类型转换上的CPU操作。
/**************************************************************************************************************************************/
2.3 BCHR(The BCHR Is Dead! Long Live the BCHR!)
_cache_stats_monitor (Default value TRUE)
_optimizer_cache_stats (Default value FALSE)
[sql]
alter system flush BUFFER_CACHE;
set autotrace traceonly explain
alter session set "_optimizer_cache_stats" = true;
select count(*) from t1;
select count(*) from t1;
alter system flush BUFFER_CACHE;
select count(*) from t1;
alter session set "_optimizer_cache_stats" = false;
select count(*) from t1;
set autotrace off
在Cost上会略有影响，但是清理缓存后，计划仍然不变，是有些问题的，有待发展吧
/**************************************************************************************************************************************/
2.4 并行执行(Parallel Execution)
本章代码附件中：
[sql]
parallel.sql
关闭系统统计和CPU Cost计算，通过hint计算不同并行度下的Cost
by the way：在计划中，是无法看出并行度的，所有的计划都一样，只是Cost不同
本章代码附件中：
[sql]
parallel_2.sql
设置系统统计和开启CPU Cost计算，通过hint计算不同并行度下的Cost
得到下面的列表
[sql]
Degree 8i 9i (I/O) 10g (I/O) 9i (CPU) 10g (CPU)
Serial 1,518 1,519 1,519 5,031 5,030
2 1,518 760 844 2,502 2,779
3 1,518 507 563 1,668 1,852
4 1,518 380 422 1,252 1,389
5 1,518 304 338 1,002 1,111
6 1,518 254 282 835 926
7 1,518 217 242 716 794
8 1,518 190 211 627 695
8i中，Cost值不变的原因在于 "_optimizer_percent_parallel"=0 ，而9i中"_optimizer_percent_parallel"=101，该参数在0~101之间取值(10053的Resc~Resp)
[sql]
alter session set "_optimizer_percent_parallel"=0;
@parallel
10g中如此运行该脚本，所得Cost值就不变了
8i、9i、10g的IOCost计算公式如下：
[sql]
8i Cost at degree N = serial cost
9i Cost at degree N = ceil(serial cost / N )
10g Cost at degree N = ceil(serial cost / (0.9 * N))
10g比9i多了个0.9的因子
多用户并发时，通过参数parallel_adaptive_multi_user=ture来控制是否允许n个用户同时进行并行执行SQL，n是由隐藏参数"_parallel_adaptive_max_users"控制的，10g=2
[sql]
select x.ksppinm, y.ksppstvl, x.ksppdesc
from x$ksppi x , x$ksppcv y
where x.indx = y.indx
and y.inst_id = userenv(&＃39;Instance&＃39;)
and x.inst_id = userenv(&＃39;Instance&＃39;)
and x.ksppinm like &＃39;\_parallel_adaptive_max_users%&＃39; escape &＃39;\&＃39;
/
KSPPINM KSPPSTVL KSPPDESC
--------------------------------------------- ---------- ------------------------------------------------
_parallel_adaptive_max_users 2 maximum number of users running with default DOP
该参数貌似不能设置太大，而且执行时，可能还需要hint（有待研究，弄个并发工具先）；并且该参数是计算parallel_max_servers的参数之一
10g中，关闭系统统计的并行全表扫描，会直接进行路径读取（绕开缓冲区），为避免脏块，直读前检查点。
将parallel.sql中，set autotrace on，执行可以看到每次都是（10000 physical reads），缓存中已经有全部或者部分数据了，但依然会进行物理读取
11g中就已经改进了。(有待研究)
表中后两列值，是9i和10g启用系统统计(CPU Cost)，10g依然有0.9的计算因子；
并行度2的行2502几乎是5001的一半，但5001仅是IOCost，还有30的CPUCost丢失了？
其实并行仅仅是去直读，绕过了缓冲区，就消除了the CPU cost of locating,latching, and pinning a buffered block
/**************************************************************************************************************************************/
2.5 Index Fast Full Scan
Index Fast Full Scan与表的全表扫描类似，但索引是个有序的瘦表，包含一些无用信息（一列rowid和一些无意义的分枝块）。
但有时，快速扫描索引，比扫描数据后再排序更有效。
本章代码附件中：
[sql]
index_ffs.sql
hack_stats.sql
开通两个session，session1执行index_ffs.sql，session2执行hack_stats.sql，然后继续执行session1，查看Cost的变化
将m_numlblks := 4;改大一些（改到1000），效果更佳明显
通过修改索引不同的统计值，确认影响Index Fast Full Scan的基础参数是leaf_blocks
index_ffs.sql中有个模拟下面的场景（有待研究）
[sql]
Execution Plan (? 11.1.0.0 ?)
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=30 Card=18 Bytes=144)
1 0 TABLE ACCESS (BY INDEX ROWID) OF &＃39;T1&＃39; (Cost=30 Card=18 Bytes=144)
2 1 SORT (ORDER BY)
3 2 INDEX (FAST FULL SCAN) OF &＃39;T1_I&＃39; (INDEX) (Cost=12 Card=18)
DBA的一项工作，就是定期重建索引，就是因为叶块数目远小于HWM
/**************************************************************************************************************************************/
2.6 分区
本章代码附件中：
[sql]
partition.sql
partition_2.sql
partition_8.sql
partition.sql构建一个多分区的大表，并收集统计信息；设计了三个查询：query1扫描单个分区、query2扫描两个相邻分区、query3绑定变量=query2
注意：autotrace在10.2之后，才能够看出所查的分区信息，所以该脚本所用的是table(dbms_xplan.display)
根据前面列出的系统表信息，可以在优化器中估算出返回的行数，具体计算结果如下：
query1：
[sql]
120000*(350-250)/199+120000*2/200=61502
--120000为该分区总函数
--199为199个不同的值
query2：
query3：
[sql]
2500=1000000*0.25%
有时绑定变量，使优化器不能很好的明确路径，就在于此
/**************************************************************************************************************************************/
[sql]
alter table pt1
exchange partition p0999 with table load_table
including indexes
without validation
;
将表以上面方式装载入分区表中，不会产生表级的统计信息
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有