[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 levelTable 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 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 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”
–//简单说明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 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 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”–//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 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 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”–//现在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分析,现在已经不用了.