Oracle 19C 管理已发布和待处理的统计信息

原创 Asher.HU 2021-02-04

527

本节说明如何使用DBMS_STATS程序单元来更改优化器统计信息的发布行为，以及如何导出和删除这些统计信息。

假设条件

本教程假定以下内容：

您想要更改sh.customers和sh.sales表的首选项，以使新收集的统计信息具有待处理状态。
您希望当前会话使用挂起的统计信息。
您要在sh.customers表上收集和发布未决统计信息。
您在sh.sales表上收集了挂起的统计信息，但是决定删除它们而不发布它们。
您想要更改sh.customers和sh.sales表的首选项，以便发布新收集的统计信息。

要管理已发布和未决的统计信息：

启动SQL * Plus并以user身份连接到数据库sh。
查询全局优化器统计信息发布设置。
运行以下查询（包括示例输出）：
```
sh@PROD> SELECT DBMS_STATS.GET_PREFS('PUBLISH') PUBLISH FROM DUAL;

PUBLISH
-------
TRUE
```
该值true指示数据库在收集统计信息时将其发布。除非已设置特定的表首选项，否则每个表都使用此值。
使用时GET_PREFS，还可以指定架构和表名称。如果已设置，该函数将返回表首选项。否则，该函数将返回全局首选项。
查询挂起的统计信息。
例如，运行以下查询（包括示例输出）：
```
sh@PROD> SELECT * FROM USER_TAB_PENDING_STATS;
 
no rows selected
```
此示例显示数据库当前不存储该sh模式的任何挂起统计信息。
更改sh.customers表的发布首选项。
例如，执行以下过程，以便将统计信息标记为待处理：
```
BEGIN
  DBMS_STATS.SET_TABLE_PREFS('sh', 'customers', 'publish', 'false');
END;
/
```
随后，当您在customers表上收集统计信息时，收集工作完成后，数据库不会自动发布统计信息。而是，数据库将新收集的统计信息存储在USER_TAB_PENDING_STATS表中。

收集的统计信息sh.customers。

例如，运行以下程序：

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS('sh','customers');
END;
/

查询挂起的统计信息。

例如，运行以下查询（包括示例输出）：

sh@PROD> SELECT TABLE_NAME, NUM_ROWS FROM USER_TAB_PENDING_STATS;
 
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
CUSTOMERS                           55500

此示例显示数据库现在存储该sh.customers表的未决统计信息。

指示优化器在此会话中使用挂起的统计信息。
将初始化参数OPTIMIZER_USE_PENDING_STATISTICS设置true为，如下所示：
```
ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS = true;
```
运行工作负载。
下面的示例更改所有名为Bruce Chalmers的客户的电子邮件地址：
```
UPDATE  sh.customers 
  SET   cust_email='ChalmersB@company.example.icom' 
  WHERE cust_first_name = 'Bruce' 
  AND   cust_last_name = 'Chalmers';
COMMIT;
```
在此会话中编译所有SQL语句时，优化器将使用挂起的统计信息而不是已发布的统计信息。
发布的待处理统计信息sh.customers。
例如，执行以下程序：
```
BEGIN
  DBMS_STATS.PUBLISH_PENDING_STATS('SH','CUSTOMERS');
END;
/
```
更改sh.sales表的发布首选项。
例如，执行以下程序：
```
BEGIN
  DBMS_STATS.SET_TABLE_PREFS('sh', 'sales', 'publish', 'false');
END;
/
```
随后，当您在sh.sales表上收集统计信息时，收集工作完成后，数据库不会自动发布统计信息。而是，数据库将统计信息存储在USER_TAB_PENDING_STATS表中。

收集的统计信息sh.sales。

例如，运行以下程序：

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS('sh','sales');
END;
/

删除的待处理统计信息sh.sales。
假设您改变主意，现在想删除的待处理统计信息sh.sales。运行以下程序：
```
BEGIN
  DBMS_STATS.DELETE_PENDING_STATS('sh','sales');
END;
/
```

将sh.customers和sh.sales表的发布首选项更改回其默认设置。

例如，执行以下程序：

BEGIN
  DBMS_STATS.SET_TABLE_PREFS('sh', 'customers', 'publish', null);
  DBMS_STATS.SET_TABLE_PREFS('sh', 'sales', 'publish', null);
END;
/

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle 19C 管理已发布和待处理的统计信息

评论