[20190815]索引快速全掃描的成本.txt
[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統計。
--//不小心又寫的太長，好久不做這些探究，腦子有點遲鈍。
--//在寫這篇文章結尾，正好看了
--//裡面提到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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210220]全索引掃描快速索引掃描的邏輯讀.txt索引
- 全表掃描和全索引掃描索引
- MySQL中的全表掃描和索引樹掃描MySql索引
- 全表掃描和全索引掃描繼續（PG-TiDB）索引TiDB
- 【Oracle】 索引的掃描方式Oracle索引
- [20210219]全表掃描邏輯讀問題.txt
- 索引掃描可能不如全表掃描的場景的理解__純粹資料量而言，不涉及CLUSTERING_FACTOR索引
- [20190221]使用nmap掃描埠的問題.txt
- AppBoxFuture: 二級索引及索引掃描查詢資料APP索引
- PostgreSQL技術內幕（七）索引掃描SQL索引
- oracle是如何進行全表掃描的Oracle
- 掃描器的存在、奧普 掃描器
- [20200306]hash join會提前終止掃描嗎.txt
- 技術分享 | 為什麼 SELECT 查詢選擇全表掃描，而不走索引？索引
- ReconSage一個快速的線上子域名掃描工具
- AWVS掃描器掃描web漏洞操作Web
- 24_Oracle資料庫全表掃描詳解(四)_全表掃描生產最佳化案例三則Oracle資料庫
- win10系統掃描器提示掃描不到掃描器如何解決Win10
- 掃描器
- 掃描王 for Mac專業圖片掃描工具Mac
- PostgreSQL DBA(55) - MVCC#8(對全表掃描的影響)SQLMVCC#
- PHP 快速掃描列表建立無限極分類樹PHP
- 什麼是漏洞掃描?漏洞掃描功能有哪些?
- SQL Server索引查詢/掃描沒有出現key lookup的案例淺析SQLServer索引
- 目錄掃描
- 埠掃描器
- python掃描埠Python
- 怎麼解決因全表掃描帶來的 Buffer Pool 汙染
- DAST 黑盒漏洞掃描器 第四篇：掃描效能AST
- 電腦掃描檔案怎麼掃描 win10電腦掃描檔案方法介紹Win10
- 關係型資料庫全表掃描分片詳解資料庫
- 23_Oracle資料庫全表掃描詳解(三)Oracle資料庫
- 22_Oracle資料庫全表掃描詳解(二)Oracle資料庫
- 21_Oracle資料庫全表掃描詳解(一)Oracle資料庫
- 全棧工程師之路-React Native之掃描二維碼全棧工程師React Native
- RustScan: 能在3秒內掃描65k個埠的Rust掃描工具Rust
- TWAIN掃描識別控制元件：Web應用程式的掃描器SDKAI控制元件Web
- 獨立按鍵的掃描