[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分析,现在已经不用了.

• 还没有人评论，欢迎说说您的想法！