CBO学习----02--表扫描（Tablescans）

最新推荐文章于 2023-04-18 21:28:53 发布
置顶 最新推荐文章于 2023-04-18 21:28:53 发布
阅读量1.6k 收藏
点赞数 1
文章标签： CBO 数据库 Oracle 优化器 SQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/mvccc/article/details/9372509
版权

第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

查看执行计划的方法：

本书提供的方法：
在本章的代码附件中，存在下面两个脚本 
plan_run81.sql
plan_run92.sql
运行该脚本之前，在相对路径下建立一个名为target.sql的文件，在该文件中，写下需要查看执行计划的SQL语句。并在SQLplus中运行该脚本，如： 
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> 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;）

/**************************************************************************************************************************************/
本章代码附件中： 
tablescan_01.sql
通过pctfree 99 构建了一个10000行，且跨越10000个块（实际分配了10240个块，高水位线在10000个块上）的表
该脚本清除了系统统计，关闭了CPU_Costing计算。 
exec dbms_stats.delete_system_stats;
alter session set "_optimizer_cost_model"=io

执行结果： 
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的计算公式，从下面推算出来 
CPU costing model:
	Cost = (
	#SRds * sreadtim +
	#MRds * mreadtim +
	#CPUCycles / cpuspeed
	) / sreadtim
本示例中，#SRds=0（刚建立的新表，没有经过删改，没有碎片，只是连续10000个块的读取，没有单块），#MRds=10000，CPU_Costing关闭了。 

Cost=#MRds * mreadtim/sreadtim
adjusted_mbrc=mreadtim/sreadtim；即典型多块读取，平均一次读多少块。（adjusted_mbrc为调整后的dbf_mbrc，只用来计算cost用）
注意：adjusted_mbrc的值，在未使用系统统计和关闭CPU_Costing时，只与db_file_multiblock_read_count有关；在使用系统统计后，就使用系统统计的值来计算cost。 
Cost=10000/adjusted_mbrc

根据上面脚本执行后，不同db_file_multiblock_read_count值下的Cost值（该值为书中的8i值），可计算出adjusted_mbrc，统计于下表中

db_file_multiblock_read_count  Cost  Adjusted dbf_mbrc
4 2396 4.17
8 1518 6.59
16 962 10.4
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)
/**************************************************************************************************************************************/
本章代码附件中： 
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 '%N%'

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
/**************************************************************************************************************************************/
本章代码附件中： 
tablescan_01.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
本章代码附件中： 
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。 
execute dbms_stats.gather_system_stats('start');
--隔一段时间
execute dbms_stats.gather_system_stats('stop');

'start'，是记录下v$filestat(实际上是X$开头的基表)和v$sysstat表的初始信息。
'stop'，再次记录上面两个表的信息，然后通过相隔的时间，计算所需的系统统计参数。 
select
pname, pval1
from
sys.aux_stats$
where
sname = 'SYSSTATS_MAIN';
PNAME 			PVAL1
----------- ----------
CPUSPEED 		559
SREADTIM 		1.299
MREADTIM 		10.204
MBRC 				6
MAXTHR 			13938448
SLAVETHR 		244736

本章代码附件中： 
set_system_stats.sql

begin
	dbms_stats.set_system_stats('MBRC',12);
	dbms_stats.set_system_stats('MREADTIM',30);
	dbms_stats.set_system_stats('SREADTIM',5);
	dbms_stats.set_system_stats('CPUSPEED',500);
end;
/

--MBRC，典型多块读取12个块。
--MREADTIM，平均多块读取时间30ms
--SREADTIM，平均单块读取时间5ms
--CPUSPEED，CPU频率500MHz=500 000 000 Hz

alter system flush shared_pool;
--刷新shared_pool，清理游标，清理软分析

MAXTHR和SLAVETHR与并行执行有关，这两个值可控制最大并行度。这两个值可设置为-1。如果其他4个值的任何一个设置为-1，都不会调用cpu_costing。（有待验证）
在10g中，系统统计有两组，一组为nowordload，另一组为wordload。如果wordload不正确，则会回头实用nowordload
/**************************************************************************************************************************************/
本章代码附件中： 
tablescan_02.sql

Oracle9i中，开启workload，Cost不再随db_file_multiblock_read_count值的变化而变化

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 'T1' (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 'T1' (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 'T1' (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 'T1' (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 'T1' (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 'T1' (Cost=5032 Card=10000 Bytes=40000)

/**************************************************************************************************************************************/ 
alter session set db_file_multiblock_read_count = 8;

--将下面语句放入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)

Cost = (
#SRds * sreadtim +
#MRds * mreadtim +
#CPUCycles / cpuspeed
) / sreadtim

将sreadtim移到括号内 
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位
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相同 
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> exec dbms_stats.delete_system_stats;
PL/SQL 过程已成功完成。
select
pname, pval1
from
sys.aux_stats$
where
sname = 'SYSSTATS_MAIN';


PNAME                               PVAL1
------------------------------ ----------
CPUSPEED
CPUSPEEDNW                       2287.401
IOSEEKTIM                              10
IOTFRSPEED                           4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM


已选择9行。

nowordload时，就会用这三个值去就计算cost 
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变化而变化

本章代码附件中： 
tablescan_03.sql

设置noworkload的三个参数，清除其他workload参数，然后计算cost值，统计入下表中

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，得到第二列值 
alter session set "_optimizer_cost_model"=io

使用choose，并使用noworkload，得到第四列值 
alter session set "_optimizer_cost_model"=choose
begin
	dbms_stats.set_system_stats('CPUSPEEDNW',913.641725);
	dbms_stats.set_system_stats('IOSEEKTIM',10);
	dbms_stats.set_system_stats('IOTFRSPEED',4096);
end;
/

使用choose，并使用workload，关闭清除系统统计，得到第三列值 
alter session set "_optimizer_cost_model"=choose
begin
	dbms_stats.set_system_stats('MBRC',8);
	dbms_stats.set_system_stats('MREADTIM',26.0);
	dbms_stats.set_system_stats('SREADTIM',12.0);
	dbms_stats.set_system_stats('CPUSPEED',913.641725);
end;
/
--	begin		execute immediate 'begin dbms_stats.delete_system_stats; end;';
--	exception 	when others then null;
--	end;


/**************************************************************************************************************************************/
不管有没有系统统计，优化器只是用这些值来计算代价，执行器用db_file_multiblock_read_count来进行扫描，不是说mbrc=12，就每次扫描12个块。（有待研究）
当db_file_multiblock_read_count=8时，显然将MBRC设置为12是件很傻的事，但为了优化器的算法，相信我这么设置是对的，之后执行器每次会读取8个块。

/**************************************************************************************************************************************/
本章代码附件中： 
tablescan_04.sql

测试的是不同数据块下，noworkload在db_file_multiblock_read_count = 8时，所计算出的不同cost

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位
Cost = (
#SRds +
#MRds * mreadtim / sreadtim +
#CPUCycles / (cpuspeed * sreadtim)
)

经上面IO位的计算

Cost = 5001 + #CPUCycles / (cpuspeed * sreadtim)
  = 5001 + #CPUCycles / (500 * 5000)

上面运行的结果中

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 
Cost=5001 + 72914400 / (500 * 5000) =5001+29.2=5030.2
与结果5031相当接近

/**************************************************************************************************************************************/

2.2.3 CPU Costing的作用

本章代码附件中： 
cpu_costing.sql

执行完全相同的SQL语句，只是where条件的顺序不同
注意:使用/*+ cpu_costing ordered_predicates */ 这个hint来强迫Oracle遵循where后面条件的顺序来执行语句，否则Oracle的CBO将会自己选择最佳的次序。 
  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> @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"='1' AND "N2"=18 AND "N1"=998                                     		791,968

尽管这几个SQL是同义的，但由于条件的顺序不同，导致了CPU的cost不同；如果去掉hint，优化器会自动选择#CPUCycles最小值的执行；当然如果v1='1'来执行，会减少类型转换上的CPU操作。

/**************************************************************************************************************************************/

2.3 BCHR(The BCHR Is Dead! Long Live the BCHR!)

_cache_stats_monitor (Default value TRUE)
_optimizer_cache_stats (Default value FALSE) 
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)


本章代码附件中： 
parallel.sql
关闭系统统计和CPU Cost计算，通过hint计算不同并行度下的Cost
by the way：在计划中，是无法看出并行度的，所有的计划都一样，只是Cost不同
本章代码附件中： 
parallel_2.sql
设置系统统计和开启CPU Cost计算，通过hint计算不同并行度下的Cost

得到下面的列表

Degree  8i  9i (I/O)  10g (I/O)  9i (CPU)  10g (CPU)
Serial  1518 1519 1519 5031 5030
2 1518 760 844 2502 2779
3 1518 507 563 1668 1852
4 1518 380 422 1252 1389
5 1518 304 338 1002 1111
6 1518 254 282 835 926
7 1518 217 242 716 794
8 1518 190 211 627 695

8i中，Cost值不变的原因在于 "_optimizer_percent_parallel"=0 ，而9i中"_optimizer_percent_parallel"=101，该参数在0~101之间取值(10053的Resc~Resp) 
alter session set "_optimizer_percent_parallel"=0;
@parallel
10g中如此运行该脚本，所得Cost值就不变了

8i、9i、10g的IOCost计算公式如下： 
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 
select x.ksppinm, y.ksppstvl, x.ksppdesc
from x$ksppi x , x$ksppcv y
     where x.indx = y.indx
     and y.inst_id = userenv('Instance')
     and x.inst_id = userenv('Instance')
     and x.ksppinm like '\_parallel_adaptive_max_users%' escape '\'
/


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和一些无意义的分枝块）。
但有时，快速扫描索引，比扫描数据后再排序更有效。
本章代码附件中： 
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中有个模拟下面的场景（有待研究） 
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 'T1' (Cost=30 Card=18 Bytes=144)
2 1 SORT (ORDER BY)
3 2 INDEX (FAST FULL SCAN) OF 'T1_I' (INDEX) (Cost=12 Card=18)
DBA的一项工作，就是定期重建索引，就是因为叶块数目远小于HWM

/**************************************************************************************************************************************/

2.6 分区

本章代码附件中： 
partition.sql
partition_2.sql
partition_8.sql

partition.sql构建一个多分区的大表，并收集统计信息；设计了三个查询：query1扫描单个分区、query2扫描两个相邻分区、query3绑定变量=query2
注意：autotrace在10.2之后，才能够看出所查的分区信息，所以该脚本所用的是table(dbms_xplan.display)

根据前面列出的系统表信息，可以在优化器中估算出返回的行数，具体计算结果如下：
query1： 
120000*(350-250)/199+120000*2/200=61502
--120000为该分区总函数
--199为199个不同的值

query2：

query3： 
2500=1000000*0.25%

有时绑定变量，使优化器不能很好的明确路径，就在于此

/**************************************************************************************************************************************/ 
alter table pt1
exchange partition p0999 with table load_table
including indexes
without validation
;

将表以上面方式装载入分区表中，不会产生表级的统计信息

全书代码下载


aio_o
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
how-cbo-works
02-06
how_cbo_works
yoloV5-train-best
06-08
yoloV5-train-best
oracle分区界限值过大,超出边界的基数估算问题
weixin_35056641的博客
04-04 751
- cpu costing is off (consider enabling it)SQL> SELECT COUNT(1) FROM T WHERE ID=2;执行计划----------------------------------------------------------Plan hash value: 1842905362--------------------------...
【sql调优之执行计划】merge join cartesian and buffer sort
08-16 471
数据库版本：oracle 9208 有时在执行计划中看到了笛卡尔连接，通常会发现是关联有问题，例如： SQL> select * from t_bank_class a,t_log_detail b; n...
一次ORA-4030问题诊断及解决【解决思路不错，说明了对象的统计信息与优化器的优化操作（即选择执行一个SQL语句在该优化参数环境下最佳的执行计划）间的关系】
haiross的专栏
02-12 2294
在报表数据库的后台alert文件中发现了这个错误，简单记录一下问题的诊断和解决过程。数据库版本9204 for Solaris sparc64。 　　错误信息如下： 　　Errors in file /u1/oracle/admin/repdb01/bdump/repdb01_j000_9888.trc: 　　ORA-12012: error on auto execute of job 3
oracle执行计划案例,返璞归真 - Oracle 9i的RBO执行计划案例
weixin_30227789的博客
04-08 187
预计阅读时间：15分钟最近配合DBA团队，做了一次Oracle 9i老旧应用的搬迁工作，偶然发现应用某一个逻辑，执行缓慢，进而根据代码，找到对应的SQL语句，分析过程，还是有些意思，也暴露了些问题。考虑到信息安全，我们通过创建模拟表，进行问题的说明，1. 这是一张普通的堆表，表的数据量大约有1000万，业务数据按照日期存储，即日期类型字段c，存储了180天的数据。2.有两个索引，一个是含(a,b...
oracle数据相同的比率,sql优化中的单表选择率（selectivity)
weixin_39820226的博客
04-04 177
继续讨论：使用列表时，选择率的计算以上我们知道了如何处理column=constant的情况，比如包含列表的查询、包含空值列表的查询、包含两列的查询、指定范围的查询和棒定变量的查询。首先从最简单的情况in-list开始比如我们随便统计3个月(6，7，8)出生的人数SQL select count(*) from audiencewhere month_no in (6,7,8)首先环境的搭建：...
CBO邦定板设计规范 免费-综合文档
05-25
CBO邦定板设计规范COB 邦定板设计规范 !DPC !!!!!!!xxx/cpoejoh.dpc/dpn!!! jean 第 1 页 共 1 页 2004-4-9 COB 邦定板设计规范 !DPC !!!!!!!xxx/cpoejoh.dpc/dpn!!! jean 第 2 页 共 2 页 2004-4-9 COB ...
CBO：CBO-巴西巴西利亚足球俱乐部（ClassificaçãoBrasileira deOcupações）
02-17
cbo CBO-巴西杯足球俱乐部从2020年第1季度开始至在CBO-CBO-巴西巴西杯比赛之前，截止到2020年）。 O Fato de aVERSÃOser 2002não意义上的替代品，algumasocupaçõesforaminclídas。 Apenas o FORMATO de ...
BigData－‘基于代价优化’究竟是怎么一回事？
02-25
提到CBO，就不得不提起一位’老熟人’–基于规则优化（Rule-BasedOptimization，简称RBO）。RBO是一种经验式、启发式的优化思路，优化规则都已经预先定义好，只需要将SQL往这些规则上套就可以（对RBO还不了解的童鞋...
oracle松散索引扫描,索引扫描的CPU COST成本公式
weixin_31741271的博客
04-03 162
SQL> create table t as select * from dba_objects;Table created.SQL> create index idx_t on t(object_id);Index created.SQL> BEGIN2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TEST'...
SQLserver中执行计划的名词解释Table Scan, Index Scan, Index Seek
hzp666的博客
08-13 1585
原文：https://www.cnblogs.com/xwdreamer/archive/2012/07/06/2579504.html Sql Server中的表访问方式Table Scan, Index Scan, Index Seek 0.参考文献 Table Scan, Index Scan, Index Seek SQL SERVER – Index Seek vs. Index Scan – Diffefence and Usage – A Simple Note oracle表.
oracle实验记录 （全表扫描COST计算方法）
cuiye1492的博客
08-31 339
实验下FTS 时候 COST 的计算 公式: According to the CPU costing model:Cost = (#SRds * sreadtim +#MRds * mreadtim +~~~...
【头歌】HBase综合测试
qq_46048008的博客
04-18 3426
使用 HBase shell 命令创建表： exam_tb1 ，向表中添加数据，表的数据与结构如下：
HBase Java API 开发：表的扫描与扫描的缓存和批量处理 第3关：在扫描中使用缓存和批量参数
qq_61604164的博客
12-14 1230
HBase Java API 开发：表的扫描与扫描的缓存和批量处理 第3关：在扫描中使用缓存和批量参数
in_list 11g优化器改进
cszt85637的博客
07-24 156
测试脚本：create table audience asselect trunc(dbms_random.value(1,13)) month_nofrom all_objectswhere...
获得执行计划
逝鸿的博客
08-28 311
参考盖国强《深入解析oracle》 2.1 v$sql_plan 通过这个视图，可以获取正在执行中或者仍然缓存着的 SQL 执行计划 通过 HASH_VALUE（可以通过 V$SESSION 或者 V$SQL、V$SQL_PLAN 视图获得 SQL的 HASH_VALUE）输入来获取 SQL 及其执行计划： SQL> alter session set sql_trace=true; Sess
（8）连接基数
事后诸葛亮的程序人生（微信：zq9017197）
02-27 1030
1、两表连接 oracle有两个公式用于连接基数的计算： 假设我们对表t1和t2进行连接，连接列分别是c1和c2。 Join Selectivity = ((num_rows(t1) - num_nulls(t1.c1)) / num_rows(t1)) * ((num_rows(t2) - num_nulls(t2.c2)) / num_rows(t2)) /
Semi join 与anti join
ciliting2867的博客
04-19 256
1.semi join Oracle在处理exists或in的时候，会使用semi join的连接方式： sys@EBANK>select object_name,object_type from ...
fpga那些事儿--timequest 静态时序分析
最新发布
07-28
TimeQuest使用的一种关键方法是时钟缓存优化（Clock Buffer Optimization，CBO）。CBO会优化时钟延迟，使时钟信号在设计中的传输延迟尽可能小。通过提前优化时钟延迟，可以最大限度地减少数据路径中的延迟，以满足更...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值