使用DBMS_STATS， 可以指定优化器何时以及如何使用统计信息。 #### 锁定和解锁优化器统计信息 统计信息被锁定之后，不能对统计信息进行修改，如果想保证统计信息和执行计划永远不变时，锁定在静态环境中很有用。锁定后，所有相关统计信息都将被锁定，如表统计、列统计、直方图及相关索引统计。要想覆盖统计信息，可以通过f在dbms_stats过程中设置force。 锁定举例 ``` sql --锁定对象 BEGIN DBMS_STATS.LOCK_TABLE_STATS('OE','ORDERS'); END; / --锁定用户 BEGIN DBMS_STATS.LOCK_SCHEMA_STATS('HR'); END; / ``` 解锁举例： ``` sql 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表的首选项，以便发布新收集的统计信息。 ``` sql --查询全局优化器统计发布设置 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; / ``` #### 参考 更多信息参考： - https://docs.oracle.com/en/database/oracle/oracle-database/21/tgsql/controlling-the-use-of-optimizer-statistics.html#GUID-D73DB2F4-46C1-4C5B-94DD-DE1F1DD5BBCC