现在这个阶段我也就只能转点别人的，等我也期待自己可以写点高水平的文章。
在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进行设置,ORACLE从9i开始提供这个参数,可设为三个选项:
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_count和db_block_size, db_block_size是建库时定义的标准参数,是不能随便改动的.那在忽略CPU成本后IO_COST的成本计算就是(#MRds * mreadtim)/sreadtime.假设mreadtim和sreadtime不变,#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以下的块都扫描,再加上ASSM的bitmap块,这个表大约需要扫描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_size的IO_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的系统统计信息.
我们继续使用上面的例子,在NoWorkload和Workload模式下,分别对各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 Statistics和Workload Statistics差别太大了,因此收集系统统计信息就变得十分重要,只有使用当前准确的系统负载统计信息,才能使优化器作出正确的判断,生成合理的执行计划.
我们可以在两个不同的时间T1，T2分别手工执行下面的语句来收集，ORACLE会通过两次收集到的信息计算出Workload Statistics:
T1：execute dbms_stats.gather_system_stats(‘start’);
T2：execute 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),其中的cpuspeed和sreadtime我们都可以通过前文提及的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独立分离出来,并且强制ORACLE对where子句的谓词进行排序.
第一个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转换需要100个CPU操作。
再看我们三个SESSION的COST值都是5，即使第一个SESSION与第三个SESSION的CPU_COST相差了1/3也没有导致COST变化。但根据我们的CPU成本公式，#CPUcycles/(cpuspeed*sreadtime)，把各个值代进去后，SESSION1为：1070604/(5*1000*500)=0.42，SESSION3为:770604/(5*1000*500)=0.30，所以结果相差不大。
当然，如果你通过dbms_stats.set_system_stats把cpuspeed设置为1，那两个SESSION的COST就会相差较大了，有兴趣的同学可以自己做下测试，^_^。
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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/7194105/viewspace-704907/
792
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
