[20230425]CBO cost與行遷移關係.txt
[20230425]CBO cost與行遷移關係.txt
--//一般現在很少使用analyze table分析表,如果出現大量行遷移是否考慮看看是否考慮cbo cost成本.
--//測試參考連結:
--//
--//
1.環境:
SCOTT@book> @ver1
PORT_STRING VERSION BANNER
------------------- ---------- ----------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.測試建立:
SCOTT@book> create table t1 pctfree 0 as select rownum id ,cast (null as varchar2(10)) vc from dual connect by level<=1e5;
Table created.
SCOTT@book> create index i_t1_id on t1(id);
Index created.
SCOTT@book> @ gts t1 '' ''
...
Gather Table Statistics for table t1...
exec dbms_stats.gather_table_stats('SCOTT', 'T1', estimate_percent => NULL, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>true, no_invalidate=>false)
if lock table t1, add force=>true.
press ctrl+c cancel, enter continue...
PL/SQL procedure successfully completed.
$ cat v_cnt.txt
SELECT table_name
, num_rows
, blocks
, empty_blocks
, avg_space
, avg_row_len
, chain_cnt
FROM user_tables
WHERE table_name = 'T1';
select index_name, blevel, leaf_blocks, clustering_factor from user_indexes where table_name='T1';
$
SCOTT@book> @ v_cnt.txt
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN CHAIN_CNT
---------- ---------- ---------- ------------ ---------- ----------- ----------
T1 100000 149 0 0 5 0
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
---------- ------ ----------- -----------------
I_T1_ID 1 222 137
3.測試:
SCOTT@book> @ sl all
alter session set statistics_level = all;
Session altered.
SCOTT@book> select count(distinct vc) from t1 where id > 1 and id < 1001;
COUNT(DISTINCTVC)
-----------------
0
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID f985dd35g9kav, child number 0
-------------------------------------
select count(distinct vc) from t1 where id > 1 and id < 1001
Plan hash value: 1993888300
---------------------------------------------------------------------------------------------------------------------------------------
|Id|Operation |Name |Starts|E-Rows|E-Bytes|Cost(%CPU)|E-Time |A-Rows| A-Time |Buffers|OMem |1Mem |Used-Mem|
---------------------------------------------------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 1| | | 7 (100)| | 1|00:00:00.01| 6| | | |
| 1| SORT AGGREGATE | | 1| 1| 7 | | | 1|00:00:00.01| 6| | | |
| 2| VIEW |VW_DAG_0| 1| 1| 7 | 7 (15)|00:00:01| 1|00:00:00.01| 6| | | |
| 3| HASH GROUP BY | | 1| 1| 5 | 7 (15)|00:00:01| 1|00:00:00.01| 6|2834K|2834K| 748K(0)|
| 4| TABLE ACCESS BY INDEX ROWID|T1 | 1| 999| 4995 | 6 (0)|00:00:01| 999|00:00:00.01| 6| | | |
|*5| INDEX RANGE SCAN |I_T1_ID | 1| 999| | 4 (0)|00:00:01| 999|00:00:00.01| 4| | | |
---------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$C33C846D
2 - SEL$5771D262 / VW_DAG_0@SEL$C33C846D
3 - SEL$5771D262
4 - SEL$5771D262 / T1@SEL$1
5 - SEL$5771D262 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("ID">1 AND "ID"<1001)
--//簡單說明cbo cost的計算:
Selectivity = (Highest Bound Value – Lowest Bound Value) / (Highest Value – Lowest Value)
--//Selectivity=(1001-1)/(100000-1) = .01000010000100001
Index Scan Cost = (blevel + ceil(effective index selectivity x leaf_blocks)) + ceil(effective table selectivity x clustering_factor)
--//1+celi(0.01*222)+ceil(0.01*137)
--//1+3+2=6
--//cost=6 ,感覺原始連結少算1個.
SCOTT@book> update t1 set vc=to_char(rownum)||lpad('a',4,'a') ;
100000 rows updated.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> @ gts t1 '' ''
...
Gather Table Statistics for table t1...
exec dbms_stats.gather_table_stats('SCOTT', 'T1', estimate_percent => NULL, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>true, no_invalidate=>false)
if lock table t1, add force=>true.
press ctrl+c cancel, enter continue...
PL/SQL procedure successfully completed.
SCOTT@book> @ v_cnt.txt
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN CHAIN_CNT
---------- ---------- ---------- ------------ ---------- ----------- ----------
T1 100000 886 0 0 15 0
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
---------- ------ ----------- -----------------
I_T1_ID 1 222 137
--//分析表後索引統計沒有變化.而表因為發生了行遷移,佔用塊數量從149=>886.
SCOTT@book> select count(distinct vc) from t1 where id > 1 and id < 1001;
COUNT(DISTINCTVC)
-----------------
999
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID f985dd35g9kav, child number 0
-------------------------------------
select count(distinct vc) from t1 where id > 1 and id < 1001
Plan hash value: 1993888300
---------------------------------------------------------------------------------------------------------------------------------------
|Id|Operation |Name |Starts|E-Rows|E-Bytes|Cost(%CPU)|E-Time |A-Rows| A-Time |Buffers|OMem |1Mem |Used-Mem|
---------------------------------------------------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 1| | | 7 (100)| | 1|00:00:00.01| 1992| | | |
| 1| SORT AGGREGATE | | 1| 1| 7 | | | 1|00:00:00.01| 1992| | | |
| 2| VIEW |VW_DAG_0| 1| 999| 6993 | 7 (15)|00:00:01| 999|00:00:00.01| 1992| | | |
| 3| HASH GROUP BY | | 1| 999| 14985 | 7 (15)|00:00:01| 999|00:00:00.01| 1992|1818K|1818K|1346K(0)|
| 4| TABLE ACCESS BY INDEX ROWID|T1 | 1| 999| 14985 | 6 (0)|00:00:01| 999|00:00:00.01| 1992| | | |
|*5| INDEX RANGE SCAN |I_T1_ID | 1| 999| | 4 (0)|00:00:01| 999|00:00:00.01| 4| | | |
---------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$C33C846D
2 - SEL$5771D262 / VW_DAG_0@SEL$C33C846D
3 - SEL$5771D262
4 - SEL$5771D262 / T1@SEL$1
5 - SEL$5771D262 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("ID">1 AND "ID"<1001)
--//cost=7,與前面沒有變化.
SCOTT@book> analyze table t1 compute statistics;
Table analyzed.
SCOTT@book> @ v_cnt.txt
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN CHAIN_CNT
---------- ---------- ---------- ------------ ---------- ----------- ----------
T1 100000 886 10 425 24 99835
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
---------- ------ ----------- -----------------
I_T1_ID 1 222 137
--//使用analyze分析後,CHAIN_CNT=99835
SCOTT@book> select /*+index(t1 i_t1_id) */ count(distinct vc) from t1 where id > 1 and id < 1001;
COUNT(DISTINCTVC)
-----------------
999
--//注:必須加入提示/*+index(t1 i_t1_id) */,不然執行計劃選擇全表掃描.
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID ahtcmzznvj0c9, child number 0
-------------------------------------
select /*+index(t1 i_t1_id) */ count(distinct vc) from t1 where id > 1
and id < 1001
Plan hash value: 1993888300
---------------------------------------------------------------------------------------------------------------------------------------
|Id|Operation |Name |Starts|E-Rows|E-Bytes|Cost(%CPU)|E-Time |A-Rows| A-Time |Buffers|OMem |1Mem |Used-Mem|
---------------------------------------------------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 1| | |1006 (100)| | 1|00:00:00.01| 1992| | | |
| 1| SORT AGGREGATE | | 1| 1| 7 | | | 1|00:00:00.01| 1992| | | |
| 2| VIEW |VW_DAG_0| 1| 999| 6993 |1006 (1)|00:00:13| 999|00:00:00.01| 1992| | | |
| 3| HASH GROUP BY | | 1| 999| 12987 |1006 (1)|00:00:13| 999|00:00:00.01| 1992|1818K|1818K|1362K(0)|
| 4| TABLE ACCESS BY INDEX ROWID|T1 | 1| 999| 12987 |1005 (1)|00:00:13| 999|00:00:00.01| 1992| | | |
|*5| INDEX RANGE SCAN |I_T1_ID | 1| 999| | 4 (0)|00:00:01| 999|00:00:00.01| 4| | | |
---------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$C33C846D
2 - SEL$5771D262 / VW_DAG_0@SEL$C33C846D
3 - SEL$5771D262
4 - SEL$5771D262 / T1@SEL$1
5 - SEL$5771D262 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("ID">1 AND "ID"<1001)
--//現在cost=1006.
SCOTT@book> @ v_cnt.txt
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN CHAIN_CNT
---------- ---------- ---------- ------------ ---------- ----------- ----------
T1 100000 886 10 425 24 99835
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
---------- ------ ----------- -----------------
I_T1_ID 1 222 137
Index Scan Cost = blevel +
ceil(effective index selectivity x leaf_blocks) +
ceil(effective table selectivity x clustering_factor) +
ceil(effective table selectivity x chain_cnt)
--//1+ceil(0.01*222)+ceil(0.01*137)+ceil(0.01*99835)
--//1+3+2+999 = 1005
--//cost=1005 ,基本接近1006.感覺原始連結少算1個.也許是一些oracle版本細節上的一些區別.
4.總結:
--//可以看出oracle選擇索引範圍查詢的cost計算會考慮行遷移的情況,不過這個給使用analyze分析,現在已經不用了.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2949124/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20180327]行遷移與ITL浪費.txt
- [20160726]行連結行遷移與ITL槽.txt
- EntityFramework Core 遷移忽略主外來鍵關係Framework
- [20160729]行連結行遷移與ITL槽4.txt
- [20160727]行連結行遷移與ITL槽2.txt
- [20160728]]行連結行遷移與ITL槽3.txt
- 深入淺出Calcite與SQL CBO（Cost-Based Optimizer）優化SQL優化
- [20180402]行連結行遷移與ITL槽6.txt
- [20121116]通過bbed觀察行連結與行遷移.txt
- [20160803]另類行遷移.txt
- [20140213]再論行遷移.txt
- [20190120]行連結遷移與dml.txt
- 關於行連結和行遷移和消除
- 行高與字型的關係
- [20230425]注意snapshot standby與activate standby的區別.txt
- 查詢行遷移及消除行遷移(chained rows)AI
- 【備份恢復】行遷移與行連結
- [20130607]行遷移與ALTER TABLE MINIMIZE RECORDS_PER_BLOCK.txtBloC
- 跪求關於〖執行緒遷移〗的相關資料！執行緒
- Oracle 行遷移 & 行連結的檢測與消除Oracle
- 行遷移測試
- 世界銀行報告：全球遷移人口增長10%與缺水有關
- Oracle資料庫中資料行遷移與行連結Oracle資料庫
- [20140209]行遷移和expdp匯出.txt
- 行遷移和行連結
- Oracle行遷移實驗Oracle
- 清除行遷移的例子
- 與if的關係
- 養老保險關係轉移
- 清除行遷移和行連結
- 行遷移(鏈化行)問題
- 中關於虛擬機器複製與遷移（中）虛擬機
- 關聯關係與依賴關係的區別
- CBO Cost Formulas基於成本優化器的成本計算公式大全ORM優化公式
- 遷移執行緒migration執行緒
- 行遷移_行連結的介紹
- 如何消除行連結和行遷移
- PV 與 PVC 狀態遷移