Oracle 12c新特性之如何检测有用的多列统计信息详解

Oracle 12c引入新特性，可检测多列统计信息的实用性，助力优化查询性能。

Oracle 12c新特性探索：多列统计信息的检测与应用详解

技术内容：

在Oracle数据库中，统计信息对于优化器来说至关重要，优化器根据统计信息来选择最佳的执行计划，从而提高查询性能，在Oracle 12c之前，数据库只能收集单列的统计信息，而在Oracle 12c中，引入了多列统计信息（Multicolunm Statistics）的新特性，这使得优化器能够更加准确地估计多列之间的相关性，进而提高查询性能，本文将详细介绍如何检测有用的多列统计信息及其在查询优化中的应用。

多列统计信息简介

1、什么是多列统计信息？

多列统计信息是指同时收集两列或多列之间的相关性统计信息，这有助于优化器在处理多列条件查询时，更准确地估计行数，从而选择更优的执行计划。

2、多列统计信息的类型

在Oracle 12c中，支持以下两种类型的多列统计信息：

（1）单表多列统计信息：针对单个表的两列或多列收集统计信息。

（2）多表多列统计信息：针对多个表的两列或多列收集统计信息。

3、多列统计信息的收集方法

可以使用DBMS_STATS包中的以下过程来收集多列统计信息：

（1）GATHER_TABLE_STATS：收集单表的多列统计信息。

（2）GATHER_SCHEMA_STATS：收集指定模式下的多表多列统计信息。

（3）GATHER_DATABASE_STATS：收集整个数据库的多表多列统计信息。

如何检测有用的多列统计信息

在实际应用中，并非所有的多列统计信息都对优化器有帮助，如何检测哪些多列统计信息是有用的呢？以下方法可以帮助我们判断：

1、使用SQL Access Advisor

SQL Access Advisor是一个基于成本的优化工具，它可以分析SQL语句并推荐最优的执行计划，通过运行SQL Access Advisor，可以检测当前的多列统计信息是否有助于生成最优的执行计划。

2、比较不同统计信息下的执行计划

通过比较在收集多列统计信息前后，同一SQL语句的执行计划，可以判断多列统计信息是否对优化器有帮助。

3、检查统计信息的基数估计

通过查询数据字典视图，如DBA_TAB_COL_STATISTICS，查看多列统计信息的基数估计是否与实际值接近，如果接近，说明多列统计信息对优化器有帮助。

多列统计信息在查询优化中的应用

下面通过一个示例来演示多列统计信息在查询优化中的应用。

1、创建测试表

CREATE TABLE t1 (c1 NUMBER, c2 NUMBER, c3 NUMBER);
CREATE TABLE t2 (c1 NUMBER, c2 NUMBER, c3 NUMBER);
INSERT INTO t1 SELECT LEVEL, MOD(LEVEL, 5), MOD(LEVEL, 10) FROM dual CONNECT BY LEVEL <= 10000;
INSERT INTO t2 SELECT LEVEL, MOD(LEVEL, 5), MOD(LEVEL, 10) FROM dual CONNECT BY LEVEL <= 10000;
COMMIT;

2、收集单表多列统计信息

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCOTT',
    tabname => 'T1',
    method_opt => 'FOR COLUMNS (C1, C2, C3)',
    cascade => TRUE
  );
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCOTT',
    tabname => 'T2',
    method_opt => 'FOR COLUMNS (C1, C2, C3)',
    cascade => TRUE
  );
END;
/

3、查看执行计划

在收集多列统计信息之前，执行以下查询：

SELECT * FROM t1, t2 WHERE t1.c1 = t2.c1 AND t1.c2 = t2.c2 AND t1.c3 = t2.c3;

查看执行计划，发现优化器选择了嵌套循环连接（NESTED LOOPS）。

在收集多列统计信息之后，再次查看执行计划，发现优化器选择了哈希连接（HASH JOIN），并且执行性能有所提高。

4、分析原因

通过查询数据字典视图DBA_TAB_COL_STATISTICS，发现多列统计信息使得优化器能够更准确地估计连接条件（t1.c1 = t2.c1, t1.c2 = t2.c2, t1.c3 = t2.c3）的基数，优化器选择了更合适的连接方法（哈希连接），从而提高了查询性能。

Oracle 12c的多列统计信息特性使得优化器能够更准确地估计多列之间的相关性，进而选择更优的执行计划，在实际应用中，我们可以通过SQL Access Advisor、比较执行计划、检查统计信息的基数估计等方法来检测有用的多列统计信息，通过合理利用多列统计信息，可以提高查询性能，优化数据库的整体性能。