CBO学习----02--表扫描(Tablescans)
来源:互联网 发布:java基础编程题 编辑:程序博客网 时间:2024/05/24 06:23
第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.sqlplan_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 expSQL> 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/sreadtimadjusted_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与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;selectval, count(*)fromt1group byval;执行计划----------------------------------------------------------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.sqltablescan_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',再次记录上面两个表的信息,然后通过相隔的时间,计算所需的系统统计参数。
selectpname, pval1fromsys.aux_stats$wheresname = 'SYSSTATS_MAIN';
PNAME PVAL1----------- ----------CPUSPEED 559SREADTIM 1.299MREADTIM 10.204MBRC 6MAXTHR 13938448SLAVETHR 244736
本章代码附件中:
set_system_stats.sql
begindbms_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_02db_file_multiblock_read_count = 4Execution 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 = 8Execution 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 = 16Execution 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 = 32Execution 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 = 64Execution 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 = 128Execution 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中selectval, count(*)fromt1group byval;--运行plan_run92.sql@plan_run92SELECT 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/12Cost=(10000/12*30/5)+CPUCycles / (500 * 5)=5000+ CPUCycles/2500_tablescan_cost_plus_oneCost=5001+ CPUCycles/2500
/**************************************************************************************************************************************/
2.2.2.1 IO位
IOcost=#MRds * mreadtim / sreadtimIOcost=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 过程已成功完成。selectpname, pval1fromsys.aux_stats$wheresname = 'SYSSTATS_MAIN';PNAME PVAL1------------------------------ ----------CPUSPEEDCPUSPEEDNW 2287.401IOSEEKTIM 10IOTFRSPEED 4096MAXTHRMBRCMREADTIMSLAVETHRSREADTIM已选择9行。
nowordload时,就会用这三个值去就计算cost
MBRC=db_file_multiblock_read_countsreadtim=IOSEEKTIM+db_block_size/IOTFRSPEEDmreadtim=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值,统计入下表中
将脚本中"-- tablespace test_8k"该行注释,一般默认表空间即为8k块的表空间,有此句反而可能过不去。
使用IO,得到第二列值
alter session set "_optimizer_cost_model"=io
使用choose,并使用noworkload,得到第四列值
alter session set "_optimizer_cost_model"=choosebegindbms_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"=choosebegindbms_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;/--beginexecute 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
没有实测,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): 1070604Filter Predicate CPU cost--------------------------------------------------------------------- ------------TO_NUMBER("V1")=1 AND "N2"=18 AND "N1"=998 1,091,968Predicted cost (9.2.0.6): 762787Filter Predicate CPU cost--------------------------------------------------------------------- ------------"N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1 784,150Predicted cost (9.2.0.6): 1070232Filter Predicate CPU cost--------------------------------------------------------------------- ------------TO_NUMBER("V1")=1 AND "N1"=998 AND "N2"=18 1,091,595Predicted cost (9.2.0.6): 762882Filter Predicate CPU cost--------------------------------------------------------------------- ------------"N1"=998 AND TO_NUMBER("V1")=1 AND "N2"=18 784,245Predicted cost (9.2.0.6): 770237Filter Predicate CPU cost--------------------------------------------------------------------- ------------"N2"=18 AND "N1"=998 AND TO_NUMBER("V1")=1 791,600Predicted cost (9.2.0.6): 785604Filter Predicate CPU cost--------------------------------------------------------------------- ------------"N2"=18 AND TO_NUMBER("V1")=1 AND "N1"=998 806,968Left to its own choice of predicate orderFilter Predicate CPU cost--------------------------------------------------------------------- ------------"N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1 784,150And one last option where the coercion on v1 is not neededPredicted cost (9.2.0.6): 770604Filter 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 explainalter 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
得到下面的列表
8i中,Cost值不变的原因在于 "_optimizer_percent_parallel"=0 ,而9i中"_optimizer_percent_parallel"=101,该参数在0~101之间取值(10053的Resc~Resp)
alter session set "_optimizer_percent_parallel"=0;@parallel10g中如此运行该脚本,所得Cost值就不变了
8i、9i、10g的IOCost计算公式如下:
8i Cost at degree N = serial cost9i Cost at degree N = ceil(serial cost / N )10gCost 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.ksppdescfrom 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.sqlhack_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.sqlpartition_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 pt1exchange partition p0999 with table load_tableincluding indexeswithout validation;
将表以上面方式装载入分区表中,不会产生表级的统计信息
全书代码下载
- CBO学习----02--表扫描(Tablescans)
- CBO模式下优化器采取全表扫描
- oracle CBO优化器何时会选择全表扫描
- 11GR2下基于CBO全表扫描cost计算
- CBO学习----03--选择率(Selectivity)
- 《Oracle编程艺术》学习笔记(29)-临时表和CBO
- ORACLE学习笔记(一) -- RBO 和 CBO
- CBO学习笔记9:histograms --part1
- CBO参数学习之查询隐藏参数
- ORACLE的CBO及表分析
- oracle 表分析,CBO RBO 详解
- 直方图(Histogram)对CBO的影响
- Oracle优化器(RBO与CBO)
- Oracle 优化器(RBO,CBO)
- CBO和RBO介绍(好)
- Oracle优化器(RBO与CBO)
- Oracle优化器(RBO与CBO)
- CBO (基于代价的优化方式)
- oracle主键自增长
- RFC浏览器
- Permutations I(II) 数组的全排列
- C# 如何让TextBox只允许输入数字
- 杭电1018-Big Number
- CBO学习----02--表扫描(Tablescans)
- php的pear包管理器下载pear包
- python制作的google map分片下载工具
- SecureCRT基本配置
- 设计模式——(Abstract Factory)抽象工厂
- QTP 菜单项消失的解决办法
- 7月18日
- properties文件读取
- getchar()和EOF总结