[20230425]CBO cost与行迁移关系.txt
[20230425]CBO cost与行迁移关系.txt
--//一般现在很少使用analyze table分析表,如果出现大量行迁移是否考虑看看是否考虑cbo cost成本.
--//测试参考链接:
--//https://richardfoote.wordpress.com/2023/03/21/cbo-costing-plans-with-migrated-rows-part-i-ignoreland/
--//https://richardfoote.wordpress.com/2023/03/28/cbo-costing-plans-with-migrated-rows-part-ii-new-killer-star/
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分析,现在已经不用了.
--//一般现在很少使用analyze table分析表,如果出现大量行迁移是否考虑看看是否考虑cbo cost成本.
--//测试参考链接:
--//https://richardfoote.wordpress.com/2023/03/21/cbo-costing-plans-with-migrated-rows-part-i-ignoreland/
--//https://richardfoote.wordpress.com/2023/03/28/cbo-costing-plans-with-migrated-rows-part-ii-new-killer-star/
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分析,现在已经不用了.
