【Oracle】CBO统计信息是基于dba_segment 还是dba_tables？
答案是：对于Oracle数据库的CBO（Cost-Based Optimizer），统计信息的基础是来自DBA_TAB_STATISTICS视图，而不是DBA_SEGMENTS视图。DBA_TAB_STATISTICS视图提供了表级别的统计信息，这些信息用于CBO生成查询执行计划和优化查询。在CBO的优化过程中，它使用DBA_TAB_STATISTICS视图中的统计信息，如行数、块数、平均行长度等来评估查询的成本，并选择最优的执行计划。这些统计信息对于优化器的决策非常重要。DBA_SEGMENTS视图提供了有关数据库中所有段（包括表、索引、分区等）的信息，但它不包含详细的表级别统计信息。DBA_SEGMENTS视图主要提供了段的占用空间、类型和状态等信息。因此，CBO的统计信息是基于DBA_TAB_STATISTICS视图中的表级别统计信息，而不是DBA_SEGMENTS视图。为了确保准确的查询执行计划，您应该定期收集和更新表的统计信息，并确保DBA_TAB_STATISTICS视图中的统计信息是最新的。
- 验证过程
--- 首先创建t2 ，查看当前user_segment以及user_tables信息
create tablespace damondba_tbs01;
create user damondba identified by damondba_tbs01 DEFAULT TABLESPACE damondba_tbs01 quota unlimited on damondba_tbs01;
grant dba to damondba;
connect damondba/damondba_tbs01@ORCLPDB1;
create table t2 as select * from dba_objects;
select segment_name,bytes/1024/1024 as mb from user_segments;
SALES 54
-- 查看当前user_segment以及user_tables信息
col table_name for a20;
set lines 200 pages 0;
select table_name,num_rows,blocks,empty_blocks,avg_space ,sample_size,last_analyzed from user_tables;
T2 72362 1438 0 0 72362 13-AUG-23 <<<<<<<<<<<<<< 表行数- 72362
col segment_name for a20;
select segment_name,bytes,blocks from user_segments;
SEGMENT_NAME BYTES BLOCKS
-------------------- ---------- ----------
T2 12582912 1536
--- 打开autotrace 并查看 select * from t2 执行计划
--- 因为autotrace会显示结果集，因此这里我用count(*) 来替代全表扫描
SQL> select count(*) from t2;
72362
Execution Plan
----------------------------------------------------------
Plan hash value: 3321871023
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 392 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T2 | 72362 | 392 (1)| 00:00:01 | <<<<<<<<<<<<<< 这里row是真实的row，且行数同user_tables一致。
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1410 consistent gets
1162 physical reads
0 redo size
751 bytes sent via SQL*Net to client
362 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--- 接下来，我们清空t2，并收集下统计信息，最后重复如上操作
delete t2;
select table_name,num_rows,blocks,empty_blocks,avg_space ,sample_size,last_analyzed from user_tables;
T2 72362 1438 0 0 72362 13-AUG-23
select segment_name,bytes,blocks from user_segments;
T2 12582912 1536
Analyze table t2 compute statistics;
select table_name,num_rows,blocks,empty_blocks,avg_space ,sample_size,last_analyzed from user_tables;
T2 0 1438 98 7943 0 13-AUG-23 <<<<<<<<<<<<<< numrows = 0
select segment_name,bytes,blocks from user_segments;
T2 12582912 1536
SQL> set autot on;
SQL> select count(*) from t2;
0
Execution Plan
----------------------------------------------------------
Plan hash value: 3321871023
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 391 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T2 | 1 | 391 (0)| 00:00:01 | <<<<<<<<<<<<<< 这里row显示1，(后续验证为什么是1而不是0)
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1410 consistent gets
0 physical reads
0 redo size
549 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
exec DBMS_STATS.GATHER_TABLE_STATS('DAMONDBA','T2');
SQL> select table_name,num_rows,blocks,empty_blocks,avg_space ,sample_size,last_analyzed from user_tables;
T2 0 1438 98 7943 0 13-AUG-23
SQL> select segment_name,bytes,blocks from user_segments;
T2 12582912 1536
--- 再一次验证 -- 继续插入
SQL> insert into t2 select * from dba_objects;
72366 rows created.
------ 注意，此时并未收集统计信息
SQL> select table_name,num_rows,blocks,empty_blocks,avg_space ,sample_size,last_analyzed from user_tables;
T2 0 1438 98 7943 0 13-AUG-23
1 row selected.
SQL> select segment_name,bytes,blocks from user_segments;
T2 12582912 1536
1 row selected.
SQL> set autot on;
SQL> select count(*) from t2;
72366
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3321871023
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 391 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T2 | 1 | 391 (0)| 00:00:01 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1410 consistent gets
0 physical reads
0 redo size
552 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
------ 注意，收集统计信息
SQL> exec DBMS_STATS.GATHER_TABLE_STATS('DAMONDBA','T2');
PL/SQL procedure successfully completed.
SQL> select count(*) from t2;
72366
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3321871023
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 392 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T2 | 72366 | 392 (1)| 00:00:01 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1410 consistent gets
0 physical reads
0 redo size
552 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
select table_name,num_rows,blocks,empty_blocks,avg_space ,sample_size,last_analyzed from user_tables;
T2 72366 1438 98 7943 72366 13-AUG-23 <<<< rows 准确
select segment_name,bytes,blocks from user_segments;
T2 12582912 1536
--- 与此同时，对比
---------- Analyze table t2 compute statistics;
select table_name,num_rows,blocks,empty_blocks,avg_space ,sample_size,last_analyzed from user_tables;
T2 0 1438 98 7943 0 13-AUG-23 <<<<<<<<<<<<<< numrows = 0
select segment_name,bytes,blocks from user_segments;
T2 12582912 1536
---------- exec DBMS_STATS.GATHER_TABLE_STATS('DAMONDBA','T2');
SQL> select table_name,num_rows,blocks,empty_blocks,avg_space ,sample_size,last_analyzed from user_tables;
T2 0 1438 98 7943 0 13-AUG-23
SQL> select segment_name,bytes,blocks from user_segments;
T2 12582912 1536
