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