使用扩展统计信息优化基于多列关系和函数的统计信息

目的

本教程的目标是向您介绍如何使用扩展统计信息优化基于多列关系和函数的统计信息。

所需时间

大约 30 分钟

主题

本教程包括下列主题：

概述
前提条件
确定单列统计信息
收集偏差列的柱状图
创建扩展统计信息来关联列
为在列上使用的函数创建扩展统计信息
清理
总结

查看屏幕截图

将鼠标置于此图标上以加载和查看本教程的所有屏幕截图。（警告：因为此操作会同时加载所有屏幕截图，所以网速较慢时，响应时间可能会比较长。）

注：此外，您还可以在下列步骤中将鼠标放在每个单独的图标上，从而仅加载和查看与该步骤相关的屏幕截图。可以通过单击各个屏幕截图来将其隐藏。

概述

在实际数据中，表中的两个或多个列之间通常存在一个关联。例如，职务和薪酬相关联（公司 VP 的薪酬可能比看门人多得多），或者汽车品牌和价格相关联（BMW 可能比 Honda 贵很多）。到目前为止，优化器还没有办法了解这些关系存在于表中的列之间。针对具有多个单列谓词的表执行查询时，优化器无法计算这些谓词的正确选择性，因为它无法确定这些列是否相关。

让优化器为应用了函数的列计算正确选择性也极其困难。例如 UPPER(surname)=.SMITH。

在 Oracle 数据库 11g 中，扩展统计信息（多列统计信息）已经引入，这使您可以收集一组列（作为一个整体）和函数的统计信息，从而使优化器可以正确地计算这些谓词的选择性。因此，优化器可以了解正确选择性（基数）。本教程将演示为何需要扩展的统计信息以及如何进行创建。

返回主题列表

前提条件

开始学习本教程之前，您应该先完成以下步骤：

1.

安装 Oracle 数据库 11g。

2.

确保已解除对 SH 用户的锁定。打开终端窗口，执行以下命令：

sqlplus sys/<syspassword> as sysdba
alter user sh identified by sh account unlock;
exit
3.

将 multicolstats.zip 文件下载并解压缩到一个工作目录。
4.

您需要创建 CUSTOMERS_OBE 表并为其加载数据。从终端窗口中，执行以下命令：

cd <sql_files_directory>
imp sh/sh file= customers_obe.dmp log=imp.log full=y

返回主题列表

确定单列统计信息

CUSTOMERS_OBE 表中的 country_id 和 cust_state_province 列是相关列的一个良好示例。当 cust_state_province 的值为 'CA' 时，country_id 的值为 'US'。这两列中的数据有偏差，这意味着表中大多数行的值为 'CA' 和 'US'。当列之间的关系和数据中的偏差在查询中一同使用时，它们都会使优化器难以正确计算这些列的选择性或基数。在这种情况下，扩展的统计信息应该有所帮助。让我们首先检查当 country_id 为 'US' 并且 cust_state_province 为 'CA' 时，CUSTOMERS_OBE 表中的实际基数或行数。执行以下步骤：

1.

您首先要检查当 country_id 为 'US' 并且 cust_state_province 为 'CA' 时，CUSTOMER_OBE 表中的实际基数或行数。打开一个终端窗口，执行以下命令： 

CD<sql_files_directory>
sqlplus sh/sh
set echo on
@check_cardinality

check_cardinality.sql 代码如下所示：

select count(*)
from customers_obe
where country_id = 'US' and cust_state_province = 'CA';
2.

既然您知道返回的实际行数，您就可以收集表上的统计信息了。从终端窗口中，执行以下 SQL 脚本： 

@gather_stats

gather_stats.sql 代码如下所示：

exec dbms_stats.gather_table_stats(null,'customers_obe', -
      method_opt => 'for all columns size 1');
3.

检查生成的列统计信息。从终端窗口中，执行以下 SQL 脚本： 

@review_stats

review_stats.sql 代码如下所示：

select column_name, num_distinct, histogram
from user_tab_col_statistics where table_name = 'CUSTOMERS_OBE';

您可以看到不同值的数量是针对 country_id 和 cust_state_province 两个列的。这些值看起来都是准确的。
4.

给定这些统计信息，检查优化器估算的查询将返回的行数。从终端窗口中，执行以下 SQL 脚本： 

@explain_plan

explain_plan.sql 代码如下所示：

explain plan for
select *
from customers_obe
where country_id = 'US' and cust_state_province = 'CA';

select plan_table_output
from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));

如果只有基本的统计信息，优化器认为仅将返回 1 行。您知道这是不正确的，因此需要向优化器提供更好的统计信息，使它可以确定正确的行数。

返回主题列表

收集偏差列的柱状图

在 Oracle 数据库 11g 以前，没有办法告诉优化器多个列的正确组合选择性是什么。向优化器提供的唯一信息是在这些列中存在一个数据偏差。通过使优化器了解 Oracle 数据库 11g 中的数据偏差，可以计算更准确的选择性。在 Oracle 数据库 11g 中，您可以收集偏差列的柱状图。得到列的柱状图之后，您可以重新检查优化器对行数的估算。执行以下步骤：

1.

收集偏差列的柱状图。从终端窗口中，执行以下 SQL 脚本： 

@gather_histogram

gather_histogram.sql 代码如下所示：

exec dbms_stats.gather_table_stats(null,'customers_obe', -
      method_opt => 'for all columns size skewonly');

select column_name, num_distinct, histogram
from user_tab_col_statistics where table_name = 'CUSTOMERS_OBE';
2.

现在，您可以生成解释计划来查看优化器目前的估算。从终端窗口中，执行以下 SQL 脚本： 

@explain_plan

explain_plan.sql 代码如下所示：

explain plan for
select *
from customers_obe
where country_id = 'US' and cust_state_province = 'CA';

select plan_table_output
from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));

因为获得了各个列的柱状图，优化器的估算有轻微的改进。但是，优化器仍然不知道两个列之间存在一个关系或关联。

返回主题列表

创建扩展统计信息来关联列

在 Oracle 数据库 11g 中，优化器可以通过为列创建扩展统计信息来了解关联性。扩展统计信息意味着，下次您收集 CUSTOMERS_OBE 表的统计信息时，会为组合的组 country_id 和 cust_state_province 收集额外一组统计信息。执行以下步骤：

1.

创建扩展的统计信息组。从终端窗口中，执行以下 SQL 脚本： 

@create_extended_stats

create_extended_stats.sql 代码如下所示：

select  dbms_stats.create_extended_stats(null,'customers_obe',
         '(country_id, cust_state_province)')
from dual;

create_extended_stats.sql 的输出是系统为创建的虚拟列生成的名称。
2.

执行您之前运行的脚本，收集偏差列的柱状图。从终端窗口中，执行以下 SQL 脚本：

@gather_histogram

gather_histogram.sql 代码如下所示：

exec dbms_stats.gather_table_stats(null,'customers_obe', -
      method_opt => 'for all columns size skewonly');

select column_name, num_distinct, histogram
from user_tab_col_statistics where table_name = 'CUSTOMERS_OBE';

您应该看到为该表列出了一个具有系统生成名称的新列，这是由 create_extended_stats 过程返回的。

3.

重新运行解释计划。在终端窗口中，执行以下脚本： 

@explain_plan

explain_plan.sql 代码如下所示：

explain plan for
select *
from customers_obe
where country_id = 'US' and cust_state_province = 'CA';

select plan_table_output
from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));

现在，优化器获得了查询的正确行数。

返回主题列表

为在列上使用的函数创建扩展统计信息

当某列使用了函数或表达式时，优化器也会很难发现正确的基数。以 LOWER 函数为例，该函数处理一个字符串或字符列，然后以全部小写字母的形式返回该字符串。执行以下步骤：

1.

您要使用选择 country_id 为 'US' 的总行数的函数运行一个查询。传递给该查询的值为小写，因此指定了列 country_id 的 LOWER 函数。在终端窗口中，执行以下脚本： 

@get_count_lower

get_count_lower.sql 代码如下所示：

select count(*)
from customers_obe
where lower(country_id) = 'us';

返回实际的行数。
2.

现在，您可以再次运行解释计划，查看优化器认为该查询将返回多少行。在终端窗口中，执行以下脚本： 

@explain_plan_lowercase

explain_plan_lowercase.sql 代码如下所示：

explain plan for
select *
from customers_obe
where lower(country_id) = 'us';

select plan_table_output
from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));

优化器的估算不接近正确的行数。

3.

如果您为表达式 LOWER(country_id) 创建扩展统计信息，则可以帮助优化器获得正确的行数。在标准的 gather_table_stats 过程中，method_opt 算法允许您指定希望收集的扩展或扩展统计信息。gather_table_stats 过程自动创建扩展统计信息（如果该信息不存在）。在终端窗口中，执行以下脚本： 

@gather_stats_lower_col

gather_stats_lower_col.sql 代码如下所示：

exec dbms_stats.gather_table_stats(null,'customers_obe', -
      method_opt =>  -
      'for all columns size skewonly for columns (lower(country_id))');
4.

既然您创建了扩展的统计信息并已收集到统计信息，您可以查看 CUSTOMERS_OBE 表的统计信息。您应该看到一个具有系统生成名称的新列。在终端窗口中，执行以下脚本： 

@review_col_stats

review_col_stats.sql 代码如下所示：

select column_name, num_distinct, histogram
from user_tab_col_statistics where table_name = 'CUSTOMERS_OBE';
5.

重新运行解释计划，查看优化器的估算。在终端窗口中，执行以下脚本： 

@explain_plan_lowercase

explain_plan_lowercase.sql 代码如下所示：

explain plan for
select *
from customers_obe
where lower(country_id) = 'us';

select plan_table_output
from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));
6.

您还可以从系统生成的名称重新生成虚拟列定义。从终端窗口中，执行以下命令： 

select dbms_metadata.get_ddl('TABLE','CUSTOMERS_OBE') from dual;

返回主题列表

清理

现在，您可以通过执行以下步骤清理您的环境：

1.

从 SQL*Plus 会话中，执行以下命令： 

DROP TABLE CUSTOMERS_OBE;

返回主题列表

总结

在本教程中，您学习了如何：

确定单列统计信息
收集偏差列的柱状图
创建扩展统计信息来关联列
为在列上使用的函数创建扩展统计信息

返回主题列表

将鼠标置于该图标上，以隐藏所有屏幕截图。