1 统计信息的查看
在进行数据库性能分析时，如果发现了一条SQL语句的执行计划有问题，走了错误的访问路径或者是执行了错误的连接顺序。这条SQL语句涉及到数据库对象（表和索引）的统计信息是否准确、是否过时是首先应该考虑到的。
Oracle提供了相应的视图，可以从这些视图里查询到统计信息的搜集时间，核对统计信息是否准确，涉及到表的统计信息的视图有2个，另外有一个视图可以查询基于分区的列的统计信息，在下面一一列出。
2.1表统计信息
SELECT TABLE_NAME,PARTITION_NAME,PARTITION_POSITION,OBJECT_TYPE,NUM_ROWS,BLOCKS,
SAMPLE_SIZE,LAST_ANALYZED
FROM user_TAB_STATISTICS
WHERE TABLE_NAME='SALES' ORDER BY PARTITION_NAME nulls first;
----------+--------------+------------------+-----------+--------+------+-----------+-----------------------+
TABLE_NAME|PARTITION_NAME|PARTITION_POSITION|OBJECT_TYPE|NUM_ROWS|BLOCKS|SAMPLE_SIZE|LAST_ANALYZED |
----------+--------------+------------------+-----------+--------+------+-----------+-----------------------+
SALES | | |TABLE | 918843| 14084| 918843|2025-01-16 06:15:47.000|
SALES |SALES_2018 | 1|PARTITION | 0| 0| |2025-01-20 03:22:46.000|
SALES |SALES_H1_2019 | 2|PARTITION | 79445| 1006| 79445|2025-01-20 03:22:46.000|
SALES |SALES_H2_2019 | 3|PARTITION | 99389| 1006| 99389|2025-03-04 06:20:55.000|
视图user_TAB_STATISTICS可以查询表的统计信息，如果是分区表，每个分区的统计信息也可以看到。
2.2 列的统计信息
SELECT TABLE_NAME,COLUMN_NAME,NUM_DISTINCT,DENSITY,NUM_NULLS,
SAMPLE_SIZE,LAST_ANALYZED
FROM USER_TAB_COL_STATISTICS
WHERE table_name='SALES' AND COLUMN_NAME='TIME_ID';
----------+-----------+------------+--------------------+---------+-----------+-----------------------+
TABLE_NAME|COLUMN_NAME|NUM_DISTINCT|DENSITY |NUM_NULLS|SAMPLE_SIZE|LAST_ANALYZED |
----------+-----------+------------+--------------------+---------+-----------+-----------------------+
SALES |TIME_ID | 1459|0.000685400959561343| 0| 918843|2024-11-22 06:55:52.000|
视图USER_TAB_COL_STATISTICS可以查询表的每一列的统计信息，查到的列的统计信息是表级的，不涉及到分区表里面的分区。
2.3 列的分区统计信息
SELECT TABLE_NAME,PARTITION_NAME,COLUMN_NAME,NUM_DISTINCT,DENSITY,NUM_NULLS,SAMPLE_SIZE,LAST_ANALYZED
FROM USER_PART_COL_STATISTICS
WHERE table_name='SALES' AND COLUMN_NAME='TIME_ID' ORDER BY PARTITION_NAME;
----------+--------------+-----------+------------+----------------------+---------+-----------+-----------------------+
TABLE_NAME|PARTITION_NAME|COLUMN_NAME|NUM_DISTINCT|DENSITY |NUM_NULLS|SAMPLE_SIZE|LAST_ANALYZED |
----------+--------------+-----------+------------+----------------------+---------+-----------+-----------------------+
SALES |SALES_2018 |TIME_ID | 0| 0| 0| |2024-09-09 08:20:31.000|
SALES |SALES_H1_2019 |TIME_ID | 181|0.00000629366228208194| 0| 79445|2024-11-22 06:56:01.000|
SALES |SALES_H2_2019 |TIME_ID | 184|0.00000503073780800692| 0| 99389|2024-11-22 06:56:02.000|
视图USER_PART_COL_STATISTICS可以查询分区表中每一列的分区级的统计信息。在检查列的统计信息时，要注意采用采样大小和最后分析时间，判断统计信息是否过时或者是采样比例不合适导致统计信息不准确。
索引的统计信息比较简单，可以从USER_IND_STATISTICS里面查询得到。
2 统计信息的收集
在检验统计信息的收集时，先写一个sql语句，查看一个统计信息各个视图的最后更新时间
SELECT
'table_statistics' sta_name,TABLE_NAME,PARTITION_NAME,LAST_ANALYZED
FROM
user_TAB_STATISTICS
WHERE
TABLE_NAME = 'SALES' AND (OBJECT_TYPE = 'TABLE'
OR PARTITION_NAME = 'SALES_H1_2019')
UNION
SELECT 'TAB_COL_STATISTICS' sta_name,TABLE_NAME,NULL,LAST_ANALYZED
FROM
USER_TAB_COL_STATISTICS
WHERE
table_name = 'SALES' AND COLUMN_NAME = 'TIME_ID'
UNION
SELECT
'PART_COL_STATISTICS' sta_name,TABLE_NAME,PARTITION_NAME,LAST_ANALYZED
FROM
USER_PART_COL_STATISTICS
WHERE
table_name = 'SALES' AND COLUMN_NAME = 'TIME_ID'
AND PARTITION_NAME = 'SALES_H1_2019';
--这条语句的查询结果如下
STA_NAME |TABLE_NAME|PARTITION_NAME|LAST_ANALYZED |
-------------------+----------+--------------+-----------------------+
table_statistics |SALES | |2025-01-16 06:15:47.000|
table_statistics |SALES |SALES_H1_2019 |2025-01-20 03:22:46.000|
TAB_COL_STATISTICS |SALES | |2024-11-22 06:55:52.000|
PART_COL_STATISTICS|SALES |SALES_H1_2019 |2024-11-22 06:56:01.000|
查询的前两行数据来自user_TAB_STATISTICS表，第一行是表的最后分析时间，第二行是其中一个分区的最后分析时间（为了显示起来紧凑一些，只显示一个分区），第三行来自USER_TAB_COL_STATISTICS,显示的是SALES表中一个列的最后分析时间，第四行来自USER_PART_COL_STATISTICS,显示的是SALES表上TIME_ID列SALES_H1_2019的最后分析时间。
2.1 使用ANALYZE TABLE
使用ANALYZE命令收集表的信息是比较早的方式，自Oracle12c以后得官网文档中有下面的提示
The use of ANALYZE for the collection of optimizer statistics is obsolete.
If you want to collect optimizer statistics, use the DBMS_STATS package, which lets you collect statistics in parallel, global statistics for partitioned objects, and helps you fine tune your statistics collection in other ways. See Oracle Database PL/SQL Packages and Types Reference for more information on the DBMS_STATS package.
Use the ANALYZE statement only for the following cases:
To use the VALIDATE or LIST CHAINED ROWS clauses
To collect information on freelist blocks
Oracle推荐使用 DBMS_STATS包来收集统计信息，它支持并行及不同的收集粒度，不过这种方式在Oracle23AI中仍可使用
ANALYZE TABLE sh.sales COMPUTE STATISTICS;
Table analyzed.
运行上一节的查询语句，返回结果如下
STA_NAME |TABLE_NAME|PARTITION_NAME|LAST_ANALYZED |
-------------------+----------+--------------+-----------------------+
table_statistics |SALES | |2025-01-16 06:15:47.000|
table_statistics |SALES |SALES_H1_2019 |2025-03-06 08:25:59.000|
TAB_COL_STATISTICS |SALES | |2024-11-22 06:55:52.000|
PART_COL_STATISTICS|SALES |SALES_H1_2019 |2025-03-06 08:25:59.000|
这个查询返回的结果比较奇怪，分区的相关统计信息的最后分析时间更新了，表的反而没有更新。
2.2 使用DBMS_STATS.GATHER_TABLE_STATS包
使用这个过程最简单的方式运行（第一参数是owner，第二个参数是表名），
begin
DBMS_STATS.GATHER_TABLE_STATS(
'SH', 'SALES');
end;
```查询语句的返回结果如下
```sql
STA_NAME |TABLE_NAME|PARTITION_NAME|LAST_ANALYZED
-------------------+----------+--------------+-----------------------
table_statistics |SALES | |2025-03-06 08:40:49.000
table_statistics |SALES |SALES_H1_2019 |2025-03-06 08:40:47.000
TAB_COL_STATISTICS |SALES | |2025-03-06 08:40:49.000
PART_COL_STATISTICS|SALES |SALES_H1_2019 |2025-03-06 08:40:47.000
表和分区的统计信息的最后分析时间都更新le，从各个视图的最后更新的时间来看，在分析时是采用了并行优化。
2.3 DBMS_STATS.GATHER_TABLE_STATS存储过程的使用
这个存储过程的语法如下
DBMS_STATS.GATHER_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
stattype VARCHAR2 DEFAULT 'DATA',
force BOOLEAN DEFAULT FALSE,
context DBMS_STATS.CCONTEXT DEFAULT NULL, -- non operative
options VARCHAR2 DEFAULT get_param('OPTIONS'));
可以看到，这个存储过程还是比较复杂的，选项比较多。在使用这个存储过程来收集统计信息时，尽量的使用默认选项，这样用起来比较简单。这个过程除了用户名和表名外，都有默认选项。有几个选项的默认值不容易看出来，下面详细说明一下。
granularity只对分区表有效，是收集的粒度，在Oracle 23AI中，这个选项的默认值是‘AUTO’,它的解释是Oracle根据分区的类型来确定搜集的粒度。如果默认选项下统计信息不能符合要求，就要选择‘ALL’，收集所有（全局，分区，子分区）统计信息，也可以分别收集全局、分区、子分区的统计信息，或者不同的组合。
METHOD_OPT的默认值是default is FOR ALL COLUMNS SIZE AUTO.SIZE AUTO的解释是Oracle根据列的数据分布和列上的负载确定是否收集列的直方图。
cascade决定是否收集表上创建的索引的统计信息，这个选项的默认设置是auto，即是否收集统计信息由Oracle根据索引的情况来确定。
如果只收集一个分区的统计信息(这种情况应该会碰到），使用下面的语句
begin
DBMS_STATS.GATHER_TABLE_STATS(
'SH', 'SALES','SALES_H1_2019',granularity=>'PARTITION');
end;
如果不设置granularity选项，会收集全局和填入的这个分区的统计信息。
3 执行计划评估行数异常
3.1 语句执行计划的基数同表中实际的行数差异巨大
下面这条sql语句查询SALES表的两个连续的分区，查询的范围包括这两个连续分区的所有数据，这条语句返回的结果如下
--sql1
SELECT count(*)
FROM SH.SALES
WHERE TIME_ID BETWEEN to_date('2019-01-01','YYYY-MM-DD') AND
to_date('2019-12-31','YYYY-MM-DD');
COUNT(*)|
--------+
178834|
这条语句的执行计划如下
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 547 (1)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | PARTITION RANGE ITERATOR| | 229K| 1794K| 547 (1)| 00:00:01 | 2 | 3 |
|* 3 | TABLE ACCESS FULL | SALES | 229K| 1794K| 547 (1)| 00:00:01 | 2 | 3 |
---------------------------------------------------------------------------------------------------
执行计划显示的rows（返回的行数，也称为基数）是229K，实际的行数应该是178834（178K左右），这两个值的差别是十分明显的。
3.2 改成union all形式
--sql2
SELECT count(*) FROM (SELECT * FROM SH.SALES WHERE TIME_ID between to_date('2019-01-01','YYYY-MM-DD') AND to_date('2019-06-30','YYYY-MM-DD')
UNION all
SELECT * FROM SH.SALES WHERE TIME_ID between to_date('2019-07-01','YYYY-MM-DD') AND to_date('2019-12-31','YYYY-MM-DD'));
COUNT(*)|
--------+
178834|
执行计划如下
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 547 (1)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | VIEW | | 178K| | 547 (1)| 00:00:01 | | |
| 3 | UNION-ALL | | 178K| 1397K| 547 (1)| 00:00:01 | | |
| 4 | PARTITION RANGE SINGLE| | 79445 | 620K| 274 (1)| 00:00:01 | 2 | 2 |
|* 5 | TABLE ACCESS FULL | SALES | 79445 | 620K| 274 (1)| 00:00:01 | 2 | 2 |
| 6 | PARTITION RANGE SINGLE| | 99389 | 776K| 274 (1)| 00:00:01 | 3 | 3 |
|* 7 | TABLE ACCESS FULL | SALES | 99389 | 776K| 274 (1)| 00:00:01 | 3 | 3 |
---------------------------------------------------------------------------------------------------
改成union all的形式之后，执行计划显示的行数同语句的返回结果是一致的。
3.3 原因分析
设置10053事件，执行sql1，从跟踪文件里截取的访问路劲分析相关部分如下
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for SALES[SALES]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
kkecdn: Single Table Predicate:"SALES"."TIME_ID">=TO_DATE(' 2019-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
Column (#3): TIME_ID(DATE)
AvgLen: 8 NDV: 1459 Nulls: 0 Density: 0.000685 Min: 2458485.000000 Max: 2459945.000000
Estimated selectivity: 1.000000 , col: #3
kkecdn: Single Table Predicate:"SALES"."TIME_ID"<=TO_DATE(' 2019-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
Estimated selectivity: 0.250000 , col: #3
Table: SALES Alias: SALES
Card: Original: 918843.000000rsel = 0.250000 Rounded: 229711 Computed: 229711.181353 Non Adjusted: 229711.181353
在Card部分（计算访问路径的基数）中，Original为918843.000000，这是表SALES的总行数，rsel = 0.250000，计算的结果是这两个相乘，取近似值229711，这也是执行计划中rows列显示的值。Oracle优化器在计算操作的返回的行数时，并没有采用分区的统计信息，却选择了表的统计信息的值，在表的各分区数据分布不那么均匀的情况下，这里的计算有着较大的偏差也就顺理成章了。
这个偏差有可能会影响到执行计划的选择，从而导致Oracle选择了不是最优的执行计划。如果将sql1改写成union的形式，写成sql2的形式路径分析部分如下：
kkecdn: Single Table Predicate:"SALES"."TIME_ID">=TO_DATE(' 2019-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
Column (#3):
NewDensity:0.000434, OldDensity:0.000006 BktCnt:79445.000000, PopBktCnt:79445.000000, PopValCnt:181, NDV:181
Column (#3): TIME_ID(DATE) Part#: 1
AvgLen: 8 NDV: 181 Nulls: 0 Density: 0.000434 Min: 2458485.000000 Max: 2458665.000000
Histogram: Freq #Bkts: 181 UncompBkts: 79445 EndPtVals: 181 ActualVal: yes
Column (#3): TIME_ID(DATE)
AvgLen: 8 NDV: 181 Nulls: 0 Density: 0.000434 Min: 2458485.000000 Max: 2458665.000000
Histogram: Freq #Bkts: 181 UncompBkts: 79445 EndPtVals: 181 ActualVal: yes
Estimated selectivity: 1.000000 , endpoint value predicate, col: #3
kkecdn: Single Table Predicate:"SALES"."TIME_ID"<=TO_DATE(' 2019-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
Estimated selectivity: 1.000000 , endpoint value predicate, col: #3
Table: SALES Alias: SALES
Card: Original: 79445.000000 Rounded: 79445 Computed: 79445.000000 Non Adjusted: 79445.000000
union all的两个子语句的路径分析是分别进行的，在计算操作的基数时，采用了分区的统计信息，为了节省篇幅，这里只截取了一个子语句的相关部分。这种情况下，优化器对基数的计算更准确。
以上可以看出，在单分区操作时，Oracle优化器在路径分析时会采用这个分区的统计信息，而当一个操作跨越多个分区时，Oracle优化器在路径分析时会采用表的统计信息。在写sql语句时，应该避免多个分区的操作，或者是将多个分区的操作写成union或者union all（不去重）的形式，尽可能保证Oracle使用分区级的统计信息。
4 写在后面
在数据库中，分区是比较负载的操作，分区的设计、管理和运维和非分区表相比都复杂了许多，涉及到分区的操作，需要全面分析操作的影响，审慎执行。
