ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 全表扫描下的成本计算

全表扫描下的成本计算

原创 Linux操作系统 作者:keer7737 时间:2011-08-15 17:03:09 0 删除 编辑
转自网络,本人不是原文作者,再次声明:本人博客上的很大一部分不是本人写的。都是收集网络上或者其他渠道上的,作为自己学习使用。如果侵犯了您的著作权,请在博客留言 我会删除。
现在这个阶段我也就只能转点别人的,等我也期待自己可以写点高水平的文章。

Oracle的官方文档Oracle 9i Database performance Tuning Guide and Reference Release 2(9.2)

 

 

 

中有提及到oracle 的成本计算模型:

 Cost = (

 #SRds * sreadtim +

 #MRds * mreadtim +

 #CPUcycles / cpuspeed

 ) / sreadtime

 

 #SRds  —> 单块读的次数

 #MRds —> 多块读的次数

 #CPUCycles —> 需要调用CPU的周期数

 sreadtim —> 单块读的时间

 mreadtim —> 多块读的时间

 cpuspeed —> CPU每秒的周期数

 

 我们把这个公式转换一下,就变成了:

 Cost= #SRds + (#MRds * mreadtim)/sreadtime + #CPUcycles/(cpuspeed*sreadtime)

 也就是说ORACLE的成本计算=IO_COST + CPU_COST.

 

 

1.标准块下的IO_COST

 我们先暂时忽略CPU的成本,可以通过隐藏参数 _optimizer_cost_model进行设置,ORACLE9i开始提供这个参数,可设为三个选项:

 alter session set “_optimizer_cost_model”=choose; — default value

 alter session set “_optimizer_cost_model”=io;

 alter session set “_optimizer_cost_model”=cpu;

 在进行全表扫描时只有多块读,而与多块读相关的参数有:db_file_multiblock_read_countdb_block_size, db_block_size是建库时定义的标准参数,是不能随便改动的.那在忽略CPU成本后IO_COST的成本计算就是(#MRds * mreadtim)/sreadtime.假设mreadtimsreadtime不变,#MRds(多块读的次数)=total_block/db_file_multiblock_read_count,所以我们对db_file_multiblock_read_count

行调整后,分别对同一个表进行全表扫描(保持total_block不变),观察它对IO_COST的影响:

 

SQL> show parameter db_block_size

 

NAME                                 TYPE                             VALUE

———————————— ——————————– ——————————

db_block_size                        integer                          8192

 

alter session set optimizer_mode=all_rows;

 

alter session set “_optimizer_cost_model”=io;

 

create tablespace tbs1 datafile ‘/u02/oradata/orcl/tbs1_01.dbf’ size 200M uniform. size 1M;

 

execute dbms_random.seed(0)

 

create table t1 tablespace tbs1

pctfree 99

pctused 1

as

select

 rownum id,

 trunc(100*dbms_random.normal) val,

 rpad(‘x’,100) padding

from all_objects

where rownum <= 10000

;

 

 创建一个包含10000条记录的表,并且通过设置pctfree使其每条记录占用一个数据块,而且我们也知道全表扫描把HWM以下的块都扫描,再加上ASSMbitmap,这个表大约需要扫描10200个数据块.我们对表收集统计信息,然后进行测试:

begin

 dbms_stats.gather_table_stats(

 user,

 ‘t1′,

 cascade => true,

 estimate_percent => null,

 method_opt => ‘for all columns size 1′

 );

end;

/

 

set autotrace traceonly explain;

SQL> alter session set db_file_multiblock_read_count = 4;

SQL> select max(val) from t1;

 

Execution Plan

———————————————————-

Plan hash value: 3724264953

 

———————————————————–

| Id  | Operation          | Name | Rows  | Bytes | Cost  |

———————————————————–

|   0 | SELECT STATEMENT   |      |     1 |     4 |  2430 |

|   1 |  SORT AGGREGATE    |      |     1 |     4 |       |

|   2 |   TABLE ACCESS FULL| T1   | 10000 | 40000 |  2430 |

———————————————————–

 

Note

—–

- cpu costing is off (consider enabling it)

 

 

SQL> alter session set db_file_multiblock_read_count = 8;

SQL> select max(val) from t1;

 

Execution Plan

———————————————————-

Plan hash value: 3724264953

 

———————————————————–

| Id  | Operation          | Name | Rows  | Bytes | Cost  |

———————————————————–

|   0 | SELECT STATEMENT   |      |     1 |     4 |  1540 |

|   1 |  SORT AGGREGATE    |      |     1 |     4 |       |

|   2 |   TABLE ACCESS FULL| T1   | 10000 | 40000 |  1540 |

———————————————————–

 

Note

—–

- cpu costing is off (consider enabling it)

 

当我们分别把db_file_multiblock_read_count 设置为:4,8,16,32,64,128 详细输出请点这里,得到其对应的扫描成本为:

 db_file_multiblock_read_count  COST

 4 2430

 8 1450

 16 997

 32 619

 64 393

 128  250

 

 通过上面的对应关系我们发现,db_file_multiblock_read_count越大,IO_COST越小.那我们是否可以通过把db_file_multiblock_read_count设置成一个很大的值来降低IO_COST?很明显,这是不行的,因为要受OS最大IO能力影响,也就是说,如果你系统的硬件IO能力有限,即使设置再大的db_file_multiblock_read_count也是没有用的。通常OS的最大I/O限制是1M.

 Max(db_file_multiblock_read_count) = MaxOsIOsize/db_block_size

 具体的测试可以参考Eygle的文章:

http://www.eygle.com/faq/db_file_multiblock_read_count&OracleIO.htm

 

2.非标准块的IO_COST

 前面我们讨论的是在标准db_block_sizeIO_COST,但我们同样可以创建一些非标准块大小的表空间,2K,4K,16K,32K.那么在这种情况下,不同的块大小会对IO_COST有什么影响?ORACLE在处理非标准块时会以标准块大小(db_block_size)*db_file_multiblock_read_cout的值为基准,在对非标准块表扫描时自动调整当时的db_file_multiblock_read_cout.,如果当前db_block_size=8,db_file_multiblock_read_cout=8,那么在扫描块大小为2K的表时,db_file_multiblock_read_cout调为32(2*32=8*8);在扫描块大小为16K的表时,db_file_multiblock_read_cout调为4(16*4=8*8).可以通过10053trace来进行这个测试,这里就暂时省略了.

 所以如果扫描相同块数的表,块越小的表,成本越低.如果扫描相同大小的表,块越大的表,成本越低.(可参考成本公式进行计算).

 

 

 

 

3.系统统计信息

 首先我们可以通过sys.aux_stats$表查看当前的系统统计信息.

select pname,pval1 from sys.aux_stats$ where sname=’SYSSTATS_MAIN’;

 

PNAME                               PVAL1

—————————— ———-

CPUSPEEDNW                       1151.627

IOSEEKTIM                              10

IOTFRSPEED                           4096

SREADTIM

MREADTIM

CPUSPEED

MBRC

MAXTHR

SLAVETHR

 

上面的统计信息可以分为两组:

1)没负载时的系统统计信息,No Workload (NW) stats:

CPUSPEEDNW    1151.627       — CPU在没负载时的运行速度

IOSEEKTIM 10       — 磁盘的寻道时间(毫秒)

IOTFRSPEED 4096 – 磁盘每毫秒可传输的字节数

2)有负载时的系统统计信息,Workload-related stats:

SREADTIM                     — 单块读需要的时间

MREADTIM                     — 多块读需要的时间

CPUSPEED                     — CPU频率

MBRC  – 多块读的平均读取块数,Average blocks read per multiblock read

MAXTHR – Maximum I/O throughput (for OPQ only)

SLAVETHR – OPQ Factotum (slave) throughput (OPQ only)

对以上信息更详细的描述,可以参考ORACLE的官方文档:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/stats.htm#i41496

 

 官方文档里面说明,NoWorkload的系统统计信息是数据库启动时收集的,Workload的系统统计信息需要手工收集或者设置定期收集,当然,两组信息都可以通过dbms_stats.set_system_stats进行修改.当收集到Workload的系统统计信息,ORACLE会忽略NoWorkload的系统统计信息.

 我们继续使用上面的例子,NoWorkloadWorkload模式下,分别对各db_file_multiblock_read_count值的全表扫描进行测试,观察ORACLE在成本计算的不同.

 

 在测试进行之前,把之前更改过的 “_optimizer_cost_model”设置为默认值.

 alter session set “_optimizer_cost_model”=choose;

1)NoWorkload模式

execute dbms_stats.delete_system_stats

 

alter system flush shared_pool;

 

begin

 dbms_stats.gather_table_stats(

 user,

 ‘t1′,

 cascade => true,

 estimate_percent => null,

 method_opt => ‘for all columns size 1′

 );

end;

/

 

SQL> select NUM_ROWS,BLOCKS,EMPTY_BLOCKS from dba_tables where table_name=’T1′;

 

NUM_ROWS     BLOCKS EMPTY_BLOCKS

———- ———- ————

9655       9790            0

 

 

set autotrace traceonly explain;

SQL> alter session set db_file_multiblock_read_count = 4;

SQL> select max(val) from t1;

 

Execution Plan

———————————————————-

Plan hash value: 3724264953

 

—————————————————————————

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

—————————————————————————

|   0 | SELECT STATEMENT   |      |     1 |     4 |  3679   (1)| 00:00:45 |

|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |

|   2 |   TABLE ACCESS FULL| T1   |  9655 | 38620 |  3679   (1)| 00:00:45 |

—————————————————————————

 

SQL> alter session set db_file_multiblock_read_count = 8;

SQL> select max(val) from t1;

 

Execution Plan

———————————————————-

Plan hash value: 3724264953

 

—————————————————————————

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

—————————————————————————

|   0 | SELECT STATEMENT   |      |     1 |     4 |  2659   (1)| 00:00:32 |

|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |

|   2 |   TABLE ACCESS FULL| T1   |  9655 | 38620 |  2659   (1)| 00:00:32 |

—————————————————————————

 

 其他略,详细测试结果在这里.

 

2)Workload模式

 通过dbms_stats.set_system_stats进行修改系统统计信息来欺骗优化器,让优化器使用我们设定的系统统计信息

begin

 dbms_stats.set_system_stats(‘CPUSPEED’,500);

 dbms_stats.set_system_stats(‘SREADTIM’,5.0);

 dbms_stats.set_system_stats(‘MREADTIM’,30.0);

 dbms_stats.set_system_stats(‘MBRC’,12);

 

end;

/

 

alter system flush shared_pool;

 

begin

 dbms_stats.gather_table_stats(

 user,

 ‘t1′,

 cascade => true,

 estimate_percent => null,

 method_opt => ‘for all columns size 1′

 );

end;

/

 

SQL> select NUM_ROWS,BLOCKS,EMPTY_BLOCKS from dba_tables where table_name=’T1′;

 

NUM_ROWS     BLOCKS EMPTY_BLOCKS

———- ———- ————

9655       9790            0

 

 

SQL> alter session set db_file_multiblock_read_count = 4;

SQL> select max(val) from t1;

 

Execution Plan

———————————————————-

Plan hash value: 3724264953

 

—————————————————————————

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

—————————————————————————

|   0 | SELECT STATEMENT   |      |     1 |     4 |  4926   (1)| 00:00:25 |

|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |

|   2 |   TABLE ACCESS FULL| T1   |  9655 | 38620 |  4926   (1)| 00:00:25 |

—————————————————————————

 

SQL> alter session set db_file_multiblock_read_count = 8;

SQL> select max(val) from t1;

 

db_file_multiblock_read_count = 8

 

Execution Plan

———————————————————-

Plan hash value: 3724264953

 

—————————————————————————

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

—————————————————————————

|   0 | SELECT STATEMENT   |      |     1 |     4 |  4926   (1)| 00:00:25 |

|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |

|   2 |   TABLE ACCESS FULL| T1   |  9655 | 38620 |  4926   (1)| 00:00:25 |

—————————————————————————

 其他略,详细测试结果在这里.

 

3)对比

 通过上面的两个测试,我们可以得到下面的对应关系表:

 db_file_multiblock_read_count  NoWorkload_cost Workload_cost

 4 3679  4926

 8 2659 4926

16 2149 4926

32 1894 4926

64 1767 4926

128 1703 4926

 

在这个表中,我们可以看到,Workload_cost没变化,NoWorkload_cost随着db_file_multiblock_read_count的增加而减少.再对应我们前文所提及的成本对应公式,我们就可以得到证明了:

Cost= #SRds + (#MRds * mreadtim)/sreadtime + #CPUcycles/(cpuspeed*sreadtime)

NoWorkload模式下:

MBRC = db_file_multiblock_read_count

sreadtime = ioseektim + db_block_size/iotfrspeed

mreadtime = ioseektim + db_file_multiblock_read_count * db_block_size/iotfrspeed

Workload模式下,无论db_file_multiblock_read_count怎么变化都是使用我们设定的固定值:

MBRC=12

sreadtime=5

mreadtime=30

cpuspeed=500

所以在NoWorkload模式下,db_file_multiblock_read_count=4,多块读的成本为:

(9790/4)*(10+4*8092/4096)/(10+8092/4096)=3671.25 这个值跟我们看到的就非常接近了.

同样在WorkloadNoWorkload模式下,因为都是固定值,所以多块读的成本为:

(9790/12)*30/5=4895 那么剩下的31(4926-4895)就是CPU_COST.

但有一点需要注意,9i开始(10G也是一样),ORACLE把隐藏参数_table_scan_cost_plus_one的默认值改为TRUE,这个参数会把表扫描和索引快速扫描的成本加1.(bump estimated full table scan and index ffs cost by one)

 

4)总结

从上面的测试可以看到,使用 Noworkload StatisticsWorkload Statistics差别太大了,因此收集系统统计信息就变得十分重要,只有使用当前准确的系统负载统计信息,才能使优化器作出正确的判断,生成合理的执行计划.

我们可以在两个不同的时间T1T2分别手工执行下面的语句来收集,ORACLE会通过两次收集到的信息计算出Workload Statistics:

T1execute dbms_stats.gather_system_stats(‘start’);

T2execute dbms_stats.gather_system_stats(‘stop’);

也可以使用下面的方法自动收集系统负载统计信息:

execute dbms_stats.gather_system_stats(‘interval’, interval=>N)

where N is the number of minutes when statistics gathering will be stopped automatically.

也可以删除系统统计信息,删除后ORACLE会改为使用Noworkload Statistics:

execute dbms_stats.delete_system_stats

 

 

4.CPU_COSTING

作为COST的组成部份,那么CPU的成本又是怎样计算的呢?同样的,我们也是借作上面的成本计算公式,其中,CPU部分是:#CPUcycles/(cpuspeed*sreadtime),其中的cpuspeedsreadtime我们都可以通过前文提及的sys.aux_stats$,从系统统计信息中查到,但是对于#CPUcycles,需要调用的CPU次数,我们就很难通过人工计算的方法得到,毕竟我们的大脑可没有CPU跑得快,在这里就要借助Explain Plan 里的plan_table.

通过$ORACLE_HOME/rdbms/admin/utlxplan.sql 可以创建用于Explain Plan plan_table脚本,我们拷贝一份,对其作一点小改动, CREATE TABLE 语句改为 CREATE GLOBAL TEMPORARY TABLE,再在后面加上on commit preserve rows.plan_table变成一个会话结束后自动清除的临时表,并为其创建公共同义词,把所有权限授予public.

当然,如果你想恢复成原来的样子,只要删除新建的plan_table,然后再运行一次$ORACLE_HOME/rdbms/admin/utlxplan.sql就可以了.

 

drop table plan_table pruge;

 

CREATE GLOBAL TEMPORARY TABLE plan_table(

statement_id       varchar2(30),

plan_id            number,

timestamp          date,

remarks            varchar2(4000),

operation          varchar2(30),

options            varchar2(255),

object_node        varchar2(128),

object_owner       varchar2(30),

object_name        varchar2(30),

object_alias       varchar2(65),

object_instance    numeric,

object_type        varchar2(30),

optimizer          varchar2(255),

search_columns     number,

id                 numeric,

parent_id          numeric,

depth              numeric,

position           numeric,

cost               numeric,

cardinality        numeric,

bytes              numeric,

other_tag          varchar2(255),

partition_start    varchar2(255),

partition_stop     varchar2(255),

partition_id       numeric,

other              long,

distribution       varchar2(30),

cpu_cost           numeric,

io_cost            numeric,

temp_space         numeric,

access_predicates  varchar2(4000),

filter_predicates  varchar2(4000),

projection         varchar2(4000),

time               numeric,

qblock_name        varchar2(30),

other_xml          clob

)on commit preserve rows;

 

create public synonym plan_table for plan_table;

 

grant all on plan_table to public;

 

创建我们的测试表,并收集统计信息:

 

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;

/

 

alter system flush shared_pool;

 

create table cpu_test(

v1,

n1,

n2

)

as

select

to_char(mod(rownum,20)),

rownum,

mod(rownum,20)

from

all_objects

where

rownum <= 3000

;

begin

dbms_stats.gather_table_stats(

user,

‘cpu_test’,

cascade => true

);

end;

/

 

然后分别在三个session,独立执行下面语句:

SESSION 1:

explain plan for

select /*+ cpu_costing ordered_predicates */

*

from cpu_test

where

v1 = 1

and n2 = 18

and n1 = 998

;

set linesize 120

col FILTER for a60

select substr(filter_predicates,1,60) Filter, cpu_cost CPU,cost from plan_table where id = 1;

 

 

——–

SESSION 2:

explain plan for

select /*+ cpu_costing ordered_predicates */

*

from cpu_test

where

n1 = 998

and n2 = 18

and v1 = 1

;

set linesize 120

col FILTER for a60

select substr(filter_predicates,1,60) Filter, cpu_cost CPU,cost from plan_table where id = 1;

 

——-

SESSION 3

explain plan for

select /*+ cpu_costing ordered_predicates */

*

from cpu_test

where

v1 = ’1′

and n2 = 18

and n1 = 998

;

set linesize 120

col FILTER for a60

select substr(filter_predicates,1,60) Filter, cpu_cost CPU,cost from plan_table where id = 1;

 

这三个SESSION分别返回的结果是:

SESSION 1:

FILTER                                                              CPU       COST

———————————————————— ———- ———-

TO_NUMBER(“V1″)=1 AND “N2″=18 AND “N1″=998                      1070604          5

 

SESSION 2:

FILTER                                                              CPU       COST

———————————————————— ———- ———-

“N1″=998 AND “N2″=18 AND TO_NUMBER(“V1″)=1                       762786          5

 

SESSION 3:

FILTER                                                              CPU       COST

———————————————————— ———- ———-

“V1″=’1′ AND “N2″=18 AND “N1″=998                                770604          5

 

三个SESSION的查询都加上了/*+ cpu_costing ordered_predicates */提示,意思是把CPU_COSTING独立分离出来,并且强制ORACLEwhere子句的谓词进行排序.

第一个SESSION语句的谓词排序为:TO_NUMBER(“V1″)=1 AND “N2″=18 AND “N1″=998,再对照我们的cpu_test表时的信息,我们分析他的运行的先后顺序是:

先把VI进行to_number,总共有3000,即转换了3000;

在转换后的3000中选中等于1的行,得到150;

再在这150行中,N2的值与18匹配;

若有匹配,把匹配行的N1值跟998进行比较.

我们直接看第三个SESSION,与第一个非常相似,它比第一个SESSION少了转换操作,那就是少了3000次转换,通过两者相减,cpu_cost刚好少了300000(1070604-770604)次操作,那就是说,一次to_number转换需要100CPU操作。

再看我们三个SESSIONCOST值都是5,即使第一个SESSION与第三个SESSIONCPU_COST相差了1/3也没有导致COST变化。但根据我们的CPU成本公式,#CPUcycles/(cpuspeed*sreadtime),把各个值代进去后,SESSION1为:1070604/(5*1000*500)=0.42SESSION3:770604/(5*1000*500)=0.30,所以结果相差不大。

当然,如果你通过dbms_stats.set_system_statscpuspeed设置为1,那两个SESSIONCOST就会相差较大了,有兴趣的同学可以自己做下测试,^_^

 

 

 

5.总结

通过上面的所有测试,对全表扫描(还有索引快速扫描,这会在另一话题中说明)的成本影响较大的有db_file_multiblock_read_count,还有系统统计信息,当然根据COST的计算公式,系统统计信息对其他的扫描也非常重要。总结了一下有三点需要注意的:

1)根据系统的MaxIOsize和数据库的db_block_size合理的调整db_file_multiblock_read_count参数;

2)定期的收集系统统计信息;

3)合理的谓词排序,可以减少不必要的CPU消耗。

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7194105/viewspace-704907/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 2011-08-12
请登录后发表评论 登录
全部评论

注册时间:2008-01-08

  • 博文量
    69
  • 访问量
    117585