频道栏目
首页 > 数据库 > Oracle > 正文
CBO学习---第2章--表扫描(Tablescans)
2013-07-19 10:06:58           
收藏   我要投稿

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 '%N%'

 

[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('start');  

--隔一段时间  

execute dbms_stats.gather_system_stats('stop');  

 

'start',是记录下v$filestat(实际上是X$开头的基表)和v$sysstat表的初始信息。

'stop',再次记录上面两个表的信息,然后通过相隔的时间,计算所需的系统统计参数。

[sql] 

select  

pname, pval1  

from  

sys.aux_stats$  

where  

sname = 'SYSSTATS_MAIN';  

[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('MBRC',12);  

    dbms_stats.set_system_stats('MREADTIM',30);  

    dbms_stats.set_system_stats('SREADTIM',5);  

    dbms_stats.set_system_stats('CPUSPEED',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 '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)  

 

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

[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 = 'SYSSTATS_MAIN';  

  

  

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 <span style="white-space:pre">      </span>Standard cpu_costing     Noworkload cpu_costing  

4               2,397           2,717       <span style="white-space:pre">  </span>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('CPUSPEEDNW',913.641725);  

    dbms_stats.set_system_stats('IOSEEKTIM',10);  

    dbms_stats.set_system_stats('IOTFRSPEED',4096);  

end;  

/  

 

使用choose,并使用workload,关闭清除系统统计,得到第三列值

[sql] 

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个块。

 

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

本章代码附件中:

[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         <span style="white-space:pre">   </span>     1,881             1,361  

 

--没有实测,ASSM用的几率不高,懒得分析了

 

 

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

2.2.2.1 CPU位

[sql] 

Cost = (  

#SRds +  

#MRds * mreadtim / sreadtim +  

#CPUCycles / (cpuspeed * sreadtim)  

)  

 

经上面IO位的计算

[sql] 

Cost<span style="white-space:pre">  </span>=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,<span style="color:#ff0000;">1091968</span>,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  

--------------------------------------------------------------------- <span style="white-space:pre">        </span>------------  

TO_NUMBER("V1")=1 AND "N2"=18 AND "N1"=998                              <span style="color:#ff0000;">1,091,968</span>  

  

  

Predicted cost (9.2.0.6): 762787  

Filter Predicate                                                        CPU cost  

--------------------------------------------------------------------- <span style="white-space:pre">        </span>------------  

"N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1                              784,150  

  

  

Predicted cost (9.2.0.6): 1070232  

Filter Predicate                                                        CPU cost  

--------------------------------------------------------------------- <span style="white-space:pre">        </span>------------  

TO_NUMBER("V1")=1 AND "N1"=998 AND "N2"=18                              1,091,595  

  

  

Predicted cost (9.2.0.6): 762882  

Filter Predicate                                                        CPU cost  

--------------------------------------------------------------------- <span style="white-space:pre">        </span>------------  

"N1"=998 AND TO_NUMBER("V1")=1 AND "N2"=18                              784,245  

  

  

Predicted cost (9.2.0.6): 770237  

Filter Predicate                                                        CPU cost  

--------------------------------------------------------------------- <span style="white-space:pre">        </span>------------  

"N2"=18 AND "N1"=998 AND TO_NUMBER("V1")=1                              791,600  

  

  

Predicted cost (9.2.0.6): 785604  

Filter Predicate                                                        CPU cost  

--------------------------------------------------------------------- <span style="white-space:pre">        </span>------------  

"N2"=18 AND TO_NUMBER("V1")=1 AND "N1"=998                              806,968  

  

  

Left to its own choice of predicate order  

Filter Predicate                                                        CPU cost  

--------------------------------------------------------------------- <span style="white-space:pre">        </span>------------  

"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  

--------------------------------------------------------------------- <span style="white-space:pre">        </span>------------  

"V1"='1' AND "N2"=18 AND "N1"=998                                     <span style="white-space:pre">        </span>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)

[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   <span style="white-space:pre">  </span>1,519        1,519       5,031       5,030  

2   1,518   <span style="white-space:pre">  </span>760      844         2,502       2,779  

3   1,518   <span style="white-space:pre">  </span>507      563         1,668       1,852  

4   1,518   <span style="white-space:pre">  </span>380      422         1,252       1,389  

5   1,518   <span style="white-space:pre">  </span>304      338         1,002       1,111  

6   1,518   <span style="white-space:pre">  </span>254      282         835         926  

7   1,518   <span style="white-space:pre">  </span>217      242         716         794  

8   1,518   <span style="white-space:pre">  </span>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('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和一些无意义的分枝块)。

但有时,快速扫描索引,比扫描数据后再排序更有效。

本章代码附件中:

[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 '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 分区

本章代码附件中:

[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  

;  

 

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

 

点击复制链接 与好友分享!回本站首页
相关TAG标签
上一篇:CBO学习---第1章--What do You Mean by Cost
下一篇:Oracle synonym(同义词)
相关文章
图文推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站