[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.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2653964/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-2653964/
1444
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
