使用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

【DBMS_STATUS】Oracle控制優化器統計資訊的使用

相關文章