分别收集和发布统计信息

目的

本教程的目标是向您介绍如何更改首选项以及如何分别收集和发布统计信息。

所需时间

大约 30 分钟

主题

本教程包括下列主题：

概述
前提条件
更改全局和表统计信息首选项
收集正在审核的统计信息
测试正在审核的统计信息
发布正在审核的统计信息
重置统计信息和首选项
总结

查看屏幕截图

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

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

概述

在 Oracle 数据库 11g 之前，在您收集到优化器统计信息后，统计信息会在收集完成后自动发布。现在，在 Oracle 数据库 11g 中，您可以将统计信息收集和统计信息发布分离。通过允许分离这些过程，您现在有机会在发布新收集到的统计信息之前对其进行测试。在本 OBE 教程中，您将检查针对给定的表存在哪些公共统计信息和正在审核的统计信息、了解如何在不发布的情况下收集统计信息，最后您将了解如何发布正在审核的统计信息。此外，您将在使正在审核的统计信息变为公共统计信息之前对其进行测试，以确保它们可被接受。

返回主题列表

前提条件

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

1. 安装 Oracle 数据库 11g 并确保已解除对 SH 用户的锁定。
2. 将 stats.zip 文件下载并解压缩到一个工作目录。
3.

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

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

返回主题列表

更改全局和表统计信息首选项

在本主题中，您将更改针对统计信息收集的默认首选项或参数设置。您可能关心的一个参数是 STALE_PERCENT。STALE_PERCENT 决定在表中的统计信息被认为过时并应该重新收集之前该表中需要更改的行的百分比。执行以下步骤：

1.

您首先要检查默认的设置。打开一个终端窗口，执行以下命令： 

cd <sql_files_directory>
sqlplus /nolog
set echo on
@check_sales_pref

check_sales_pref.sql 代码如下所示：

connect sh/sh

select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent 
from dual;
2.

SALES 表的默认值为 10%。要更改整个数据库的参数或首选项，您需要拥有 dba 权限。以 sysdba 身份连接后，您可以将整个数据库的 STALE_PERCENT 设置为 13%。然后，再次检查过时百分比。从终端窗口中，执行以下 SQL 脚本： 

@change_global_pref
@check_sales_pref

change_global_pref.sql 和 check_sales_pref.sql 代码如下所示：

connect / as sysdba
execute dbms_stats.set_global_prefs('STALE_PERCENT', '13');

connect sh/sh
select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent 
from dual;

3.

现在，SALES 表的 STALE_PERCENT 为 13%，该值也是全局值。如果您只想更改单个表的 STALE_PERCENT，该怎样操作？您可以使用 set_table_prefs 过程。将 SALES 表的 STALE_PERCENT 设置为 65%。由于您只更改属于该模式的一个表，因此不需要以 sysdba 身份登录。然后，再次检查过时百分比。从终端窗口中，执行以下 SQL 脚本： 

@change_table_pref
@check_sales_pref

change_table_pref.sql 和 check_sales_pref.sql 代码如下所示：

execute dbms_stats.set_table_prefs('SH', 'SALES', 'STALE_PERCENT', '65');

connect sh/sh
select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent 
from dual;
4.

sales 的 STALE_PERCENT 已更改为 65%，但模式中的所有其他表呢？检查 PRODUCTS 表的 STALE_PERCENT，它应该是新的全局默认值，即 13%。从终端窗口中，执行以下 SQL 脚本： 

@check_products_pref

check_products_pref.sql 代码如下所示：

select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'PRODUCTS') stale_percent 
from dual;
5.

现在，您可以将所有参数重置回默认值。您首先需要删除在 SALES 上设置的表首选项，使该表具有全局默认值。从终端窗口中，执行以下 SQL 脚本： 

@reset_table_prefs
@check_sales_pref

reset_table_prefs.sql 和 check_sales_pref.sql 代码如下所示：

execute dbms_stats.delete_table_prefs('SH', 'SALES', 'STALE_PERCENT');

connect sh/sh
select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent 
from dual;
6.

现在，您需要以 sysdba 身份重新连接，重置全局默认值。您无需记住默认值。您只需将首选项的值设置为 null，这将恢复“出厂”默认值。从终端窗口中，执行以下 SQL 脚本： 

@reset_global_prefs
@check_sales_pref

reset_table_prefs.sql 和 check_sales_pref.sql 代码如下所示：

connect / as sysdba
execute dbms_stats.set_global_prefs('STALE_PERCENT', null);

connect sh/sh
select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent 
from dual;

返回主题列表

收集正在审核的统计信息

在该主题中，您将检查针对给定的表存在哪些公共统计信息和正在审核的统计信息、了解如何在不发布的情况下收集统计信息。执行以下步骤：

1.

您首先要更改数据格式，并重置 CUSTOMERS_OBE 表的统计信息。在终端窗口中，执行以下脚本： 

@reset_table_stats

reset_table_stats.sql 代码如下所示：

connect sh/sh
alter session set nls_date_format='mm/dd hh24:mi:ss';

-- delete statistics
exec dbms_stats.delete_table_stats('SH', 'CUSTOMERS_OBE');
2.

现在，您可以查看 CUSTOMERS_OBE 表的公共统计信息。从终端窗口中，执行以下命令： 

@show_public_stats CUSTOMERS_OBE

show_public_stats.sql 代码如下所示：

-- tables
select table_name, last_analyzed analyze_time, num_rows, blocks, avg_row_len
from user_tables
where table_name = '&1';

-- indexes
select index_name, last_analyzed ANALYZE_TIME, num_rows, 
       leaf_blocks, distinct_keys
from user_indexes
where table_name = '&1'
order by index_name;

-- columns
select column_name, last_analyzed ANALYZE_TIME, num_distinct, 
       num_nulls, density
from user_tab_columns
where table_name = '&1'
order by column_name;
3.

然后，查看 CUSTOMERS_OBE 表的正在审核的统计信息。从终端窗口中，执行以下命令： 

@show_pending_stats CUSTOMERS_OBE

show_pending_stats.sql 代码如下所示：

-- tables
select table_name, last_analyzed "analyze time", num_rows, blocks, avg_row_len
from user_tab_pending_stats
where table_name = '&1' and partition_name is null;

-- indexes
select index_name, last_analyzed "analyze time", num_rows, 
       leaf_blocks, distinct_keys
from user_ind_pending_stats
where table_name = '&1' and partition_name is null
order by index_name;

-- columns
select column_name, last_analyzed "analyze time", num_distinct, 
       num_nulls, density
from user_col_pending_stats
where table_name = '&1' and partition_name is null
order by column_name;
4.

当前，CUSTOMERS_OBE 表中不存在任何公共或正在审核的统计信息。收集完成后，Oracle 数据库 11g 中的默认行为与统计信息发布之前相同。您可以通过从终端窗口执行以下脚本检查首选项：

@check_publish_prefs

check_publish_prefs.sql 代码如下所示：

select dbms_stats.get_prefs('PUBLISH') publish from dual;
5.

您还可以在表级别检查或更改发布模式。您希望检查 CUSTOMERS_OBE 表的发布首选项值。它应该与全局默认值相同。从终端窗口中，执行以下 SQL 脚本： 

@check_table_publish_prefs

check_table_publish_prefs.sql 代码如下所示：

select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;
6.

将 CUSTOMERS_OBE 表的发布值设置为 false。这意味着，任何从现在开始收集的统计信息不会自动发布。从终端窗口中，执行以下 SQL 脚本： 

@set_table_publish_prefs_false

set_table_publish_prefs_false.sql 代码如下所示：

exec dbms_stats.set_table_prefs('SH', 'CUSTOMERS_OBE', 'PUBLISH', 'false');
7.

执行以下脚本，确认更改是否生效：

@check_table_publish_prefs

check_table_publish_prefs.sql 代码如下所示：

select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;
8.

现在，您可以执行以下脚本来收集 CUSTOMERS_OBE 表中的统计信息：

@gather_table_stats

gather_table_stats.sql 代码如下所示：

execute dbms_stats.gather_table_stats('SH', 'CUSTOMERS_OBE');
9.

由于 CUSTOMERS_OBE 表将其发布首选项设置为 false，在收集统计信息任务完成后，该表不应该有任何公共统计信息。在终端窗口中，执行以下脚本： 

@show_public_stats CUSTOMERS_OBE

show_public_stats.sql 代码如下所示：

-- tables
select table_name, last_analyzed analyze_time, num_rows, blocks, avg_row_len
from user_tables
where table_name = '&1';

-- indexes
select index_name, last_analyzed ANALYZE_TIME, num_rows, 
       leaf_blocks, distinct_keys
from user_indexes
where table_name = '&1'
order by index_name;

-- columns
select column_name, last_analyzed ANALYZE_TIME, num_distinct, 
       num_nulls, density
from user_tab_columns
where table_name = '&1'
order by column_name;
10.

现在，检查您是否有正在审核的统计信息。在终端窗口中，执行以下脚本： 

@show_pending_stats CUSTOMERS_OBE

show_pending_stats.sql 代码如下所示：

-- tables
select table_name, last_analyzed "analyze time", num_rows, blocks, avg_row_len
from user_tab_pending_stats
where table_name = '&1' and partition_name is null;

-- indexes
select index_name, last_analyzed "analyze time", num_rows, 
       leaf_blocks, distinct_keys
from user_ind_pending_stats
where table_name = '&1' and partition_name is null
order by index_name;

-- columns
select column_name, last_analyzed "analyze time", num_distinct, 
       num_nulls, density
from user_col_pending_stats
where table_name = '&1' and partition_name is null
order by column_name;

返回主题列表

测试正在审核的统计信息

CUSTOMERS_OBE 表上的所有统计信息都正在审核。如果要测试新的统计信息，您可以从正在审核的统计信息表导出信息，然后将它们导入一个测试系统。如果发现它们是不可接受的信息，您只需将它们从正在审核的统计信息表中删除，不会对生产造成任何影响。执行以下步骤：

1.

您需要执行以下脚本来关闭对正在审核的统计信息的使用： 

@set_pending_stats_off

set_pending_stats_off.sql 代码如下所示：

alter session set optimizer_use_pending_statistics = false;
alter session set optimizer_dynamic_sampling = 0;
2.

现在，您可以获得并显示您的查询的解释计划。在终端窗口中，执行以下脚本： 

@get_execplan

get_execplan.sql 代码如下所示：

explain plan for 
select * from customers_obe where CUST_CREDIT_LIMIT=1500;

select plan_table_output plan from table(dbms_xplan.display('plan_table',null,'serial'));

这不是一个很理想的计划，因为它为 CUST_CREDIT_LIMIT=1500 的每行重新查询表的一个索引查找和一个单行访问。表中 20% 以上的行的 CUST_CREDIT_LIMIT 值为 1500。随着 CUSTOMERS_OBE 表不断增长，该计划会随着与查询匹配的行数增加而变得越来越慢。

3.

要查看优化器使用正在审核的统计信息表中的统计信息时是否性能更好，您需要运行以下脚本将 optimizer_use_pending_statistics 设置为 true： 

@set_optimizer_pending_stats_true

set_optimizer_pending_stats_true.sql 代码如下所示：

alter session set optimizer_use_pending_statistics = true;
4.

现在，重新运行查询的解释计划。

@get_execplan

get_execplan.sql 代码如下所示：

explain plan for 
select * from customers_obe where CUST_CREDIT_LIMIT=1500;

select plan_table_output plan from table(dbms_xplan.display('plan_table',null,'serial'));

利用正在审核的统计信息时，该计划得到很大改善。您现在进行的是一次全表扫描，这样只访问一次即可检索所有匹配的行。这是您要在生产中运行的计划。

返回主题列表

发布正在审核的统计信息

在该主题中，您可以发布正在审核的统计信息。执行以下步骤：

1.

如果统计信息被证实是可以接受的，您可以执行以下脚本将它们变为公共统计信息： 

@publish_pending_stats

publish_pending_stats.sql 代码如下所示：

exec dbms_stats.publish_pending_stats(null, null);
2.

现在，如果您要检查 CUSTOMERS_OBE 表的公共统计信息，您应该查看以前收集的所有统计信息。在终端窗口中，执行以下脚本： 

@show_public_stats CUSTOMERS_OBE

show_public_stats.sql 代码如下所示：

-- tables
select table_name, last_analyzed analyze_time, num_rows, blocks, avg_row_len
from user_tables
where table_name = '&1';

-- indexes
select index_name, last_analyzed ANALYZE_TIME, num_rows, 
       leaf_blocks, distinct_keys
from user_indexes
where table_name = '&1'
order by index_name;

-- columns
select column_name, last_analyzed ANALYZE_TIME, num_distinct, 
       num_nulls, density
from user_tab_columns
where table_name = '&1'
order by column_name;
3.

然后，检查 CUSTOMERS_OBE 表的正在审核的统计信息。在终端窗口中，执行以下脚本： 

@show_pending_stats CUSTOMERS_OBE

show_pending_stats.sql 代码如下所示：

-- tables
select table_name, last_analyzed "analyze time", num_rows, blocks, avg_row_len
from user_tab_pending_stats
where table_name = '&1' and partition_name is null;

-- indexes
select index_name, last_analyzed "analyze time", num_rows, 
       leaf_blocks, distinct_keys
from user_ind_pending_stats
where table_name = '&1' and partition_name is null
order by index_name;

-- columns
select column_name, last_analyzed "analyze time", num_distinct, 
       num_nulls, density
from user_col_pending_stats
where table_name = '&1' and partition_name is null
order by column_name;

注意，现在不存在正在审核的统计信息，因为它们已经发布。

返回主题列表

重置统计信息和首选项

要重置该 OBE 教程的统计信息和首选项，执行以下步骤：

1.

您首先需要删除表首选项。在终端窗口中，执行以下脚本： 

@delete_table_stats

delete_table_stats.sql 代码如下所示：

exec dbms_stats.delete_table_stats('SH', 'CUSTOMERS_OBE');
2.

然后，您需要将 CUSTOMERS_OBE 表的全局值设置为 PUBLISH。从终端窗口中，执行以下命令： 

@set_global_publish

set_global_publish.sql 代码如下所示：

select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;

返回主题列表

总结

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

更改全局和表统计信息首选项
收集正在审核的统计信息
测试正在审核的统计信息
发布正在审核的统计信息
重置统计信息和首选项

返回主题列表

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