使用DBMS_STATS， 可以指定优化器何时以及如何使用统计信息。
锁定和解锁优化器统计信息
统计信息被锁定之后，不能对统计信息进行修改，如果想保证统计信息和执行计划永远不变时，锁定在静态环境中很有用。锁定后，所有相关统计信息都将被锁定，如表统计、列统计、直方图及相关索引统计。要想覆盖统计信息，可以通过f在dbms_stats过程中设置force。
锁定举例
--锁定对象
BEGIN
DBMS_STATS.LOCK_TABLE_STATS('OE','ORDERS');
END;
/
--锁定用户
BEGIN
DBMS_STATS.LOCK_SCHEMA_STATS('HR');
END;
/
解锁举例：
BEGIN
DBMS_STATS.UNLOCK_TABLE_STATS('OE','ORDERS');
END;
/
--or
BEGIN
DBMS_STATS.UNLOCK_SCHEMA_STATS('HR');
END;
/
发布待定的优化器统计信息
默认情况下，优化器使用已发布的统计信息。可以通过将OPTIMIZER_USE_PENDING_STATISTICS初始化参数设置为true（默认为false）来更改默认行为。
与发布相关的DBMS_STATS程序单元
| 程序 | 描述 |
|---|---|
| GET_PREFS | 检查统计信息是否在DBMS_STATS收集后立即自动发布。对于参数PUBLISH，true表示在数据库收集统计信息时必须发布统计信息，而false表示数据库必须保持统计信息挂起。 |
| SET_TABLE_PREFS | 将PUBLISH设置设置为true或false在表级别 |
| SET_SCHEMA_PREFS | 将PUBLISH设置设置为true或false在架构级别。 |
| PUBLISH_PENDING_STATS | 发布所有对象或仅指定对象的有效挂起统计信息 |
| DELETE_PENDING_STATS | 删除待处理的统计信息 |
| EXPORT_PENDING_STATS | 导出待处理的统计信息 |
与统计信息相关的视图：
| 视图 | 描述 |
|---|---|
| USER_TAB_STATISTICS | 显示当前用户可访问的表的优化器统计信息。 |
| USER_TAB_COL_STATISTICS | 显示从 中提取的列统计信息和直方图信息ALL_TAB_COLUMNS。 |
| USER_PART_COL_STATISTICS | 显示当前用户拥有的表分区的列统计信息和直方图信息。 |
| USER_SUBPART_COL_STATISTICS | 描述当前用户拥有的分区对象的子分区的列统计信息和直方图信息 |
| USER_IND_STATISTICS | 显示当前用户可访问的索引的优化器统计信息。 |
| USER_TAB_PENDING_STATS | 描述当前用户可访问的表、分区和子分区的挂起统计信息 |
| USER_COL_PENDING_STATS | 描述当前用户可访问的列的挂起统计信息。 |
| USER_IND_PENDING_STATS | 描述使用DBMS_STATS包收集的当前用户可访问的表、分区和子分区的挂起统计信息 |
管理统计信息
本教程假设如下：
- 想要更改sh.customers和sh.sales表的首选项，以便新收集的统计信息处于挂起状态。
- 希望当前会话使用挂起的统计信息。
- 想收集和发布sh.customers表上的待处理统计信息。
- 收集sh.sales表上的待处理统计信息，但决定删除它们而不发布它们。
- 想要更改sh.customers和sh.sales表的首选项，以便发布新收集的统计信息。
--查询全局优化器统计发布设置
sh@PROD> SELECT DBMS_STATS.GET_PREFS('PUBLISH') PUBLISH FROM DUAL;
PUBLISH
-------
TRUE
--查询待处理的统计信息
sh@PROD> SELECT * FROM USER_TAB_PENDING_STATS;
no rows selected
--更改sh.customers表格的发布首选项
BEGIN
DBMS_STATS.SET_TABLE_PREFS('sh', 'customers', 'publish', 'false');
END;
/
--收集统计信息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
--指示优化器在此会话中使用挂起的统计信息
ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS = true;
--运行工作负载
UPDATE sh.customers
SET cust_email='ChalmersB@company.example.icom'
WHERE cust_first_name = 'Bruce'
AND cust_last_name = 'Chalmers';
COMMIT;
--发布 的待定统计信息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
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('sh','sales');
END;
/
--删除 的待定统计信息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;
/
参考
更多信息参考：