【Oracle】CBO统计信息是基于dba_segment 还是dba_tables？

答案是：来自dba_tables

• 验证过程
  --- 首先创建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

--- 与此同时，对比

----------  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