[20190815]索引快速全扫描的成本.txt

来源：这里教程网时间：2026-03-03 14:03:32 作者：

[20190815]索引快速全扫描的成本.txt --//昨天听一个讲座，提到索引快速全扫描的成本由索引的统计信息叶子块数量决定，与其他无关比如blevel。 --//我开始非常不理解，不是听错了吧。索引快速全扫描就是把索引当作表，其扫描成本的计算方式与全表扫描的方式一样。 --//这样扫描的块数量应该是索引的hwm之下的块，不分索引根节点块，分支节点块，叶子节点块全部扫描，充分利用多块读取的特性完 --//成扫描操作。如果计算索引快速全扫描的成本由索引的统计信息叶子块数量决定，这样岂不是漏调索引根节点块，分支节点块的数量。 --//算出来的成本不是偏小了吗？ --//温习Jonathan Lewis的<基于成本的Oracle优化法则>中文版P30，书中提到叶块的数量。我还是通过例子验证看看。 --//英文 P61 So what number does the optimizer use as the basis for the cost of the index fast full scan? The answer seems to be the number of leaf blocks—which is fairly reasonable, because in a nice, clean randomly generated index, with no catastrophic updates and deletes, the number of leaf blocks is probably within 1% of the total number of blocks below the high water mark. Strangely, if you have not collected statistics on an index, Oracle uses its knowledge of the high water mark from the index’s segment header block to get the right answer. --//翻译：那么，优化器使用什么数字作为索引快速全扫描成本的基础？答案似乎是叶块的数量-这是相当合理的，因为在一个很好的、干净的随机生成的索引中，没有大量的更新和删除，叶块的数量可能在高水位以下的块总数的1%以内。奇怪的是，如果您还没有收集索引的统计数据，Oracle就会使用它对索引的段头块中的高水标记的知识来获得正确的答案。 --//其中一段"在一个很好的、干净的随机生成的索引中，没有大量的更新和删除，叶块的数量可能在高水位以下的块总数的1%以内" --//非常不理解，是指叶块的空块吗？还是指索引根节点以及分支块节点，而根本不是叶块。 --//测试例子来自<基于成本的Oracle优化法则>。 1.环境: SCOTT@test01p> @ ver1 PORT_STRING VERSION BANNER CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 SCOTT@test01p> show parameter db_block_size NAME TYPE VALUE ------------- ------- ----- db_block_size integer 8192 SCOTT@test01p> show parameter db_file_multiblock_read_count NAME TYPE VALUE ----------------------------- ------- ----- db_file_multiblock_read_count integer 8 SCOTT@test01p> execute dbms_stats.delete_system_stats; PL/SQL procedure successfully completed. SCOTT@test01p> alter session set "_optimizer_cost_model"=io; Session altered. --//主要不计cpu的成本。 2.建立测试例子： create table t1 pctfree 99 pctused 1 as select rownum id, trunc(100 * dbms_random.normal) val, rpad('x',100) padding from all_objects where rownum <= 10000 ; create index i_t1_val_padding on t1(val,padding) pctfree 99; --//注意我修改索引定义增加字段padding，并且pctfree属性等于99; begin dbms_stats.gather_table_stats( user, 't1', cascade => true, estimate_percent => null, method_opt => 'for all columns size 1' ); end; / SCOTT@test01p> select * from user_indexes where index_name = 'I_T1_VAL_PADDING' @ prxx ============================== INDEX_NAME : I_T1_VAL_PADDING INDEX_TYPE : NORMAL TABLE_OWNER : SCOTT TABLE_NAME : T1 TABLE_TYPE : TABLE UNIQUENESS : NONUNIQUE COMPRESSION : DISABLED PREFIX_LENGTH : TABLESPACE_NAME : USERS INI_TRANS : 2 MAX_TRANS : 255 INITIAL_EXTENT : 65536 NEXT_EXTENT : 1048576 MIN_EXTENTS : 1 MAX_EXTENTS : 2147483645 PCT_INCREASE : PCT_THRESHOLD : INCLUDE_COLUMN : FREELISTS : FREELIST_GROUPS : PCT_FREE : 99 LOGGING : YES BLEVEL : 3 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ LEAF_BLOCKS : 10000 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DISTINCT_KEYS : 570 AVG_LEAF_BLOCKS_PER_KEY : 17 AVG_DATA_BLOCKS_PER_KEY : 17 CLUSTERING_FACTOR : 10000 STATUS : VALID NUM_ROWS : 10000 SAMPLE_SIZE : 10000 LAST_ANALYZED : 2019-08-15 20:37:57 DEGREE : 1 INSTANCES : 1 PARTITIONED : NO TEMPORARY : N GENERATED : N SECONDARY : N BUFFER_POOL : DEFAULT FLASH_CACHE : DEFAULT CELL_FLASH_CACHE : DEFAULT USER_STATS : NO DURATION : PCT_DIRECT_ACCESS : ITYP_OWNER : ITYP_NAME : PARAMETERS : GLOBAL_STATS : YES DOMIDX_STATUS : DOMIDX_OPSTATUS : FUNCIDX_STATUS : JOIN_INDEX : NO IOT_REDUNDANT_PKEY_ELIM : NO DROPPED : NO VISIBILITY : VISIBLE DOMIDX_MANAGEMENT : SEGMENT_CREATED : YES ORPHANED_ENTRIES : NO INDEXING : FULL PL/SQL procedure successfully completed. --//可以发现没有一个统计相关索引HWM下的数据块数量。基本1个键值占1个数据块。 3.测试： SCOTT@test01p> alter session set statistics_level = all; Session altered. SCOTT@test01p> select /*+ index_ffs(t1) */ count(*) from t1 where val > 100; COUNT(*) ---------- 1537 SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 2f5bkshzhmg78, child number 1 ------------------------------------- select /*+ index_ffs(t1) */ count(*) from t1 where val > 100 Plan hash value: 643509802 -------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 1519 | 1 |00:00:00.03 | 10159 | | 1 | SORT AGGREGATE | | 1 | 1 | 4 | | 1 |00:00:00.03 | 10159 | |* 2 | INDEX FAST FULL SCAN| I_T1_VAL_PADDING | 1 | 3638 | 14552 | 1519 | 1537 |00:00:00.03 | 10159 | -------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("VAL">100) Note ----- - cpu costing is off (consider enabling it) ---//COST=1519.再看看全表扫描的成本： SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID b7ddr3041ysa4, child number 0 ------------------------------------- select /*+ full(t1) */ count(*) from t1 where val > 100 Plan hash value: 3724264953 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 1541 | 1 |00:00:00.04 | 10044 | | 1 | SORT AGGREGATE | | 1 | 1 | 4 | | 1 |00:00:00.04 | 10044 | |* 2 | TABLE ACCESS FULL| T1 | 1 | 3638 | 14552 | 1541 | 1537 |00:00:00.04 | 10044 | ----------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T1@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("VAL">100) Note ----- - cpu costing is off (consider enabling it) SCOTT@test01p> select NUM_ROWS,BLOCKS from user_tables where table_name='T1'; NUM_ROWS BLOCKS ---------- ---------- 10000 10143 --//db_file_multiblock_read_count=8 的情况下,adjusted_dbf_mbrc=6.588，这个数值可以看<基于成本的Oracle优化法则>里面提到。 --//10143/6.588 = 1539.61 约等于 1540。,_table_scan_cost_plus_one=true,cost还要+1,这样cost = 1541 --//索引快速全扫描的成本? cost呢 = LEAF_BLOCKS/adjusted_dbf_mbrc+1 --//10000/6.588 = 1517.91 约等于 1518,_table_scan_cost_plus_one=true,cost还要+1,cost= 1519. --//这样基本能对上,是否blevel部分很小，hack统计信息看看。 4.hack统计信息验证看看。 SCOTT@test01p> exec dbms_stats.set_index_stats(ownname=> NULL,indname=>'I_T1_VAL_PADDING',indlevel=> 10); PL/SQL procedure successfully completed. SCOTT@test01p> select BLEVEL,LEAF_BLOCKS from user_indexes where index_name = 'I_T1_VAL_PADDING'; BLEVEL LEAF_BLOCKS ---------- ----------- 10 10000 SCOTT@test01p> alter system flush shared_pool; System altered. SCOTT@test01p> select /*+ index_ffs() */ count(*) from t1 where val > 100; COUNT(*) ---------- 1537 SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID au98arwahm1df, child number 0 ------------------------------------- select /*+ index_ffs() */ count(*) from t1 where val > 100 Plan hash value: 643509802 -------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 1519 | 1 |00:00:00.04 | 10159 | | 1 | SORT AGGREGATE | | 1 | 1 | 4 | | 1 |00:00:00.04 | 10159 | |* 2 | INDEX FAST FULL SCAN| I_T1_VAL_PADDING | 1 | 3638 | 14552 | 1519 | 1537 |00:00:00.04 | 10159 | -------------------------------------------------------------------------------------------------------------------- --//可以发现cost依旧等于1519.说明索引快速全扫描的成本与blevel无关。 SCOTT@test01p> execute dbms_stats.set_index_stats(ownname=> NULL,indname=>'I_T1_VAL_PADDING',numlblks=> 5000); PL/SQL procedure successfully completed. SCOTT@test01p> alter system flush shared_pool; System altered. SCOTT@test01p> select /*+ index_ffs() */ count(*) from t1 where val > 100; COUNT(*) ---------- 1537 SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID au98arwahm1df, child number 0 ------------------------------------- select /*+ index_ffs() */ count(*) from t1 where val > 100 Plan hash value: 643509802 -------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 760 | 1 |00:00:00.02 | 10159 | | 1 | SORT AGGREGATE | | 1 | 1 | 4 | | 1 |00:00:00.02 | 10159 | |* 2 | INDEX FAST FULL SCAN| I_T1_VAL_PADDING | 1 | 3638 | 14552 | 760 | 1537 |00:00:00.02 | 10159 | -------------------------------------------------------------------------------------------------------------------- --//cost = LEAF_BLOCKS/adjusted_dbf_mbrc+1, 5000/6.588+1 = 759.95 约等于760.基本一致。 --//再来看看E-Row输入计算的。 SCOTT@test01p> @ tab_lh scott t1 val DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT OWNER TABLE_NAME COLUMN SAMPLE : @ TAB_LH TABLE_NAME [COLUMN_NAME] IF NOT INPUT COLUMN_NAME ,USE "" . COLUMN_NAME DATA_TYPE DATA_LENGTH N NUM_DISTINCT DENSITY SAMPLE_SIZE TRANS_LOW TRANS_HIGH NUM_NULLS NUM_BUCKETS LAST_ANALYZED HISTOGRAM DATA_DEFAULT ----------- --------- ----------- - ------------ ---------- ----------- --------- -------------------- ----------- ------------------- --------- ------------ VAL NUMBER 22 Y 570 .001754386 10000 -339 351 0 1 2019-08-15 20:37:48 NONE SCOTT@test01p> @ prxx ============================== COLUMN_NAME : VAL DATA_TYPE : NUMBER DATA_LENGTH : 22 NULLABLE : Y NUM_DISTINCT : 570 DENSITY : .00175438596491228 SAMPLE_SIZE : 10000 TRANS_LOW : -339 TRANS_HIGH : 351 NUM_NULLS : 0 NUM_BUCKETS : 1 LAST_ANALYZED : 2019-08-15 20:37:48 HISTOGRAM : NONE DATA_DEFAULT : PL/SQL procedure successfully completed. --//val在 -339 － 351 之间。查询条件是where val > 100.直方图不存在的情况下。 --//选择率=(TRANS_HIGH-100)/(TRANS_HIGH-TRANS_LOW) --//选择率等于(351-100)/(351+339) = .36376811594202898550 --//num_rows=10000,这样card = 3637.68,四舍五入等于3638. --//如果查询条件是where val >= 100,这样选择率=(TRANS_HIGH-100)/(TRANS_HIGH-TRANS_LOW)+1/NUM_DISTINCT --//选择率等于(351-100)/(351+339) +1/570 = .36552250190694126620 --//num_rows=10000,这样card = 3655.22,四舍五入等于3655.简单验证看看。？ SCOTT@test01p> select /*+ index_ffs() */ count(*) from t1 where val >=100; COUNT(*) ---------- 1572 SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 1w1v82g1xmkhg, child number 0 ------------------------------------- select /*+ index_ffs() */ count(*) from t1 where val >=100 Plan hash value: 643509802 -------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 1519 | 1 |00:00:00.04 | 10159 | | 1 | SORT AGGREGATE | | 1 | 1 | 4 | | 1 |00:00:00.04 | 10159 | |* 2 | INDEX FAST FULL SCAN| I_T1_VAL_PADDING | 1 | 3655 | 14856 | 1519 | 1572 |00:00:00.04 | 10159 | -------------------------------------------------------------------------------------------------------------------- --//基本一致。 5.顺便提一下: --//开始我并没有设置alter session set "_optimizer_cost_model"=io;。这样算出来差异很大，后面才知道实际上这样采用的是 --//noworkload模式。 SCOTT@test01p> execute dbms_stats.set_index_stats(ownname=> NULL,indname=>'I_T1_VAL_PADDING',numlblks=> 10000); PL/SQL procedure successfully completed. --//注：这里numlblks实际上就是修改LEAF_BLOCKS的统计值，感觉在这里存在一些歧义。 SCOTT@test01p> exec dbms_stats.set_index_stats(ownname=> NULL,indname=>'I_T1_VAL_PADDING',indlevel=> 3); PL/SQL procedure successfully completed. SCOTT@test01p> select * from sys.aux_stats$; SNAME PNAME PVAL1 PVAL2 -------------------- -------------------- ---------- ------------------------------ SYSSTATS_INFO STATUS COMPLETED SYSSTATS_INFO DSTART 08-15-2019 20:35 SYSSTATS_INFO DSTOP 08-15-2019 20:35 SYSSTATS_INFO FLAGS 0 SYSSTATS_MAIN CPUSPEEDNWG 2771 SYSSTATS_MAIN IOSEEKTIM 10 SYSSTATS_MAIN IOTFRSPEED 4096 SYSSTATS_MAIN SREADTIM SYSSTATS_MAIN MREADTIM SYSSTATS_MAIN CPUSPEED SYSSTATS_MAIN MBRC SYSSTATS_MAIN MAXTHR SYSSTATS_MAIN SLAVETHR 13 rows selected. --//SREADTIM= IOSEEKTIM+db_block_size/IOTFRSPEED = 10+8192/4096=12ms --//MREADTIM= IOSEEKTIM+db_file_multiblock_read_count*db_block_size/IOTFRSPEED = 10+8*8192/4096=26ms. SCOTT@test01p> alter session set statistics_level = all; Session altered. SCOTT@test01p> alter system flush shared_pool; System altered. SCOTT@test01p> select /*+ index_ffs() */ count(*) from t1 where val > 100; COUNT(*) ---------- 1537 SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID au98arwahm1df, child number 0 ------------------------------------- select /*+ index_ffs() */ count(*) from t1 where val > 100 Plan hash value: 643509802 ------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 2712 (100)| | 1 |00:00:00.02 | 10159 | | 1 | SORT AGGREGATE | | 1 | 1 | 4 | | | 1 |00:00:00.02 | 10159 | |* 2 | INDEX FAST FULL SCAN| I_T1_VAL_PADDING | 1 | 3638 | 14552 | 2712 (1)| 00:00:01 | 1537 |00:00:00.02 | 10159 | ------------------------------------------------------------------------------------------------------------------------------------ --//这样计算的cost=2712与前面的测试1519相差甚远。 --//cost = LEAF_BLOCKS/db_file_multiblock_read_count*MREADTIM/SREADTIM +1 = 10000/8*26/12 +1 = 2709.333 --//CPU 的成本占1%之内。也就是 2712 － 2709 ＝ 3.换1个方式计算： SCOTT@test01p> explain plan for select /*+ index_ffs() */ count(*) from t1 where val > 100; Explained. SCOTT@test01p> select cpu_cost from plan_table; CPU_COST ---------- 72914400 72914400 --//这样可以看到cpu_cost的消耗72914400,其他方式如何看到我不清楚。 --//计算公式： CPU_COST/(CPUSPEEDNW*1000000)/1000/SREADTIM,注CPUSPEEDNW的单位是MHZ。SREADTIM的单位ms(毫秒)。 --// CPU_COST/(CPUSPEEDNW*1000000) 计算出来的单位是秒，必须要除以1000才能毫秒。 --//疑问：sys.aux_stats$中的字段CPUSPEEDNW，CPUSPEED 有什么区别？ --//72914400/(2771*1000000)*1000 /12 = 2.19278238902923132416 , CPU的cost=3. --// cost = 2709+3 = 2712,基本能对上。 SCOTT@test01p> execute dbms_stats.delete_system_stats; PL/SQL procedure successfully completed. SCOTT@test01p> select * from sys.aux_stats$; SNAME PNAME PVAL1 PVAL2 -------------------- -------------------- ---------- ----------------------------- SYSSTATS_INFO STATUS COMPLETED SYSSTATS_INFO DSTART 08-15-2019 22:37 SYSSTATS_INFO DSTOP 08-15-2019 22:37 SYSSTATS_INFO FLAGS 0 SYSSTATS_MAIN CPUSPEEDNW 1050 SYSSTATS_MAIN IOSEEKTIM 10 SYSSTATS_MAIN IOTFRSPEED 4096 SYSSTATS_MAIN SREADTIM SYSSTATS_MAIN MREADTIM SYSSTATS_MAIN CPUSPEED SYSSTATS_MAIN MBRC SYSSTATS_MAIN MAXTHR SYSSTATS_MAIN SLAVETHR 13 rows selected. --//很奇怪，我每次删除system统计，CPUSPEEDNW都会变。 SCOTT@test01p> alter system flush shared_pool; System altered. SCOTT@test01p> select /*+ index_ffs() */ count(*) from t1 where val > 100; COUNT(*) ---------- 1537 SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID au98arwahm1df, child number 0 ------------------------------------- select /*+ index_ffs() */ count(*) from t1 where val > 100 Plan hash value: 643509802 ------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 2716 (100)| | 1 |00:00:00.03 | 10159 | | 1 | SORT AGGREGATE | | 1 | 1 | 4 | | | 1 |00:00:00.03 | 10159 | |* 2 | INDEX FAST FULL SCAN| I_T1_VAL_PADDING | 1 | 3638 | 14552 | 2716 (1)| 00:00:01 | 1537 |00:00:00.03 | 10159 | ------------------------------------------------------------------------------------------------------------------------------------ --//cost=2716 --//cpu_cost= 72914400/(1050*1000000)*1000 /12 = 5.78685714285714285666, --// cost= 2709 +6 = 2715 ?? 相差1。 --//或许应该是这样计算 --//io_cost = LEAF_BLOCKS/db_file_multiblock_read_count*MREADTIM/SREADTIM +1 = 10000/8*26/12 +1 = 2709.333 --//cpu_cost = CPU_COST/(CPUSPEEDNW*1000000)/1000/SREADTIM = 72914400/(1050*1000000)*1000 /12 = 5.78685714285714285666 --// cost = io_cost + cpu_cost = 2709.333+5.786 = 2715.119 ,最后ceil(2715.119) = 2716. 6.继续测试： SCOTT@test01p> delete from t1 where val between 1 and 100; 3334 rows deleted. SCOTT@test01p> commit ; Commit complete. begin dbms_stats.gather_table_stats( user, 't1', cascade => true, estimate_percent => null, method_opt => 'for all columns size 1' ); end; / SCOTT@test01p> select * from user_indexes where index_name = 'I_T1_VAL_PADDING' 2 @ prxx ============================== INDEX_NAME : I_T1_VAL_PADDING INDEX_TYPE : NORMAL TABLE_OWNER : SCOTT TABLE_NAME : T1 TABLE_TYPE : TABLE UNIQUENESS : NONUNIQUE COMPRESSION : DISABLED PREFIX_LENGTH : TABLESPACE_NAME : USERS INI_TRANS : 2 MAX_TRANS : 255 INITIAL_EXTENT : 65536 NEXT_EXTENT : 1048576 MIN_EXTENTS : 1 MAX_EXTENTS : 2147483645 PCT_INCREASE : PCT_THRESHOLD : INCLUDE_COLUMN : FREELISTS : FREELIST_GROUPS : PCT_FREE : 99 LOGGING : YES BLEVEL : 3 LEAF_BLOCKS : 6666 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DISTINCT_KEYS : 470 AVG_LEAF_BLOCKS_PER_KEY : 14 AVG_DATA_BLOCKS_PER_KEY : 14 CLUSTERING_FACTOR : 6666 STATUS : VALID NUM_ROWS : 6666 SAMPLE_SIZE : 6666 LAST_ANALYZED : 2019-08-15 23:08:03 DEGREE : 1 INSTANCES : 1 PARTITIONED : NO TEMPORARY : N GENERATED : N SECONDARY : N BUFFER_POOL : DEFAULT FLASH_CACHE : DEFAULT CELL_FLASH_CACHE : DEFAULT USER_STATS : NO DURATION : PCT_DIRECT_ACCESS : ITYP_OWNER : ITYP_NAME : PARAMETERS : GLOBAL_STATS : YES DOMIDX_STATUS : DOMIDX_OPSTATUS : FUNCIDX_STATUS : JOIN_INDEX : NO IOT_REDUNDANT_PKEY_ELIM : NO DROPPED : NO VISIBILITY : VISIBLE DOMIDX_MANAGEMENT : SEGMENT_CREATED : YES ORPHANED_ENTRIES : NO INDEXING : FULL PL/SQL procedure successfully completed. --//产生一些索引空块。LEAF_BLOCKS: 6666。 SCOTT@test01p> alter session set "_optimizer_cost_model"=io; Session altered. SCOTT@test01p> select /*+ index_ffs() */ count(*) from t1 where val > 100; COUNT(*) ---------- 1537 SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID au98arwahm1df, child number 1 ------------------------------------- select /*+ index_ffs() */ count(*) from t1 where val > 100 Plan hash value: 643509802 -------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 1013 | 1 |00:00:00.02 | 10159 | | 1 | SORT AGGREGATE | | 1 | 1 | 5 | | 1 |00:00:00.02 | 10159 | |* 2 | INDEX FAST FULL SCAN| I_T1_VAL_PADDING | 1 | 2425 | 12125 | 1013 | 1537 |00:00:00.02 | 10159 | -------------------------------------------------------------------------------------------------------------------- --//cost=1013 --//索引快速全扫描的成本cost = LEAF_BLOCKS/adjusted_dbf_mbrc+1 --//6666/6.588+1 = 1012.8397,不过如果查看Buffers=10159对比前面没有减少。 7.总结： --//索引快速全扫描的成本由索引的统计信息叶子块数量决定. --//cost的计算在11g以后发生了变化，cost计算依赖采用noworkload模式。大部分系统实际上并不采集system统计。 --//不小心又写的太长，好久不做这些探究，脑子有点迟钝。 --//在写这篇文章结尾，正好看了https://jonathanlewis.wordpress.com/2019/08/14/gather_system_stats/ --//里面提到exadata： MBRC : 128 MREADTIM : SREADTIM : CPUSPEED : CPUSPEEDNW : 918 IOSEEKTIM : 10 IOTFRSPEED : 204,800 MAXTHR : SLAVETHR : PL/SQL procedure successfully completed. It's also important to note that Oracle will use the 128 MBRC value in its calculation of the cost of the tablescan – even if you've set the db_file_mulitblock_read_count parameter for the session or system to something smaller; and if you have set the db_file_multiblock_read_count that's the maximum size of multiblock read that the run-time engine will use.

编辑推荐：

返回列表

[20190815]索引快速全扫描的成本.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推