ORACLE中如何鎖住/解鎖統計信息？

從Oracle 10g 開始，Oracle提供了鎖定/解鎖表統計信息功能，它的目的是阻止資料庫自動收集統計信息，防止可能會產生/出現的糟糕的計劃。它對於數據頻繁更改的Volatile Tables最有用，因為Volatile Tables的某些數據集可能會生成糟糕的計劃。

官方的介紹如下：

Prevent automatic statistic gathering where this may otherwise produce poor plans. As such it is most useful for volatile tables whose data changes frequently and for whom poor plans may be generated with certain data sets.

註：什麼是Volatile Tables呢？官方解釋：Volatile tables are being deleted or truncated, and then rebuilt during the day. 還有一種解釋指：Volatile Tables 是指變化比較大的表，即頻繁進行Insert、Delete、Update 多種操作的表。

另外，資料庫自動收集表的統計信息，大部分情況下，這種行為對於資料庫的性能是有利的。但是也有一些情況，我們不想資料庫自動收集某個表的統計信息，例如：

• 1：自動收集統計信息作業數據採樣的比例過低，尤其對於一些大表，準確來說是對於一些數據不怎麼變化的大表，我們想手工收集集統計信息（指定較高的採樣比例）。這樣有利於相關SQL生成正確的執行計劃。
• 2：自動統計信息收集作業運行過後或運行期間，由於一些作業或業務邏輯出現了大量的DML，此時收集的的統計信息可能是不准確，也是就說自動統計信息收集對於這種表沒有什麼意義，反而浪費了大量資源，我們需要手工或設置相關作業去收集統計信息。那麼我們就可以通過鎖定表的統計信息，阻止資料庫的自動收集統計信息作業去採集相關統計信息。

如果一些對象沒有統計信息，而你又鎖定了統計信息，那麼此時資料庫在執行SQL時，就會使用動態採樣。這個也是鎖定統計信息的另外一個功能。

When you set the statistics of a volatile object to null, Oracle Database dynamically gathers the necessary statistics during optimization using dynamic statistics. The OPTIMIZER_DYNAMIC_SAMPLING initialization parameter controls this feature.

This is useful when you wish to use dynamic sampling on a volatile table. You may also lock statistics on a volatile table at a point when it is fully populated, so that the table statistics are more representative of the table population, so as to use those statistics to generate plans, whatever happens to the table data.

統計信息鎖定

當不需要對某個表做收集的時候，可以採用鎖定統計信息的方法，把不需要收集的表排除在外，這樣可以使得此表上的統計信息不變，Oracle提供三種粒度的鎖定統計信息的方法，如下所示

--鎖定表的統計信息

EXEC DBMS_STATS.LOCK_TABLE_STATS();


參數：
PROCEDURE LOCK_TABLE_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNNAME                        VARCHAR2                IN
 TABNAME                        VARCHAR2                IN
 STATTYPE                       VARCHAR2                IN     DEFAULT

--鎖定表的分區統計信息

EXEC DBMS_STATS.LOCK_PARTITION_STATS(); 


參數
PROCEDURE LOCK_PARTITION_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNNAME                        VARCHAR2                IN
 TABNAME                        VARCHAR2                IN
 PARTNAME                       VARCHAR2                IN

--鎖定用戶統計信息

EXEC DBMS_STATS.LOCK_SCHEMA_STATS();


參數：
PROCEDURE LOCK_SCHEMA_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNNAME                        VARCHAR2                IN
 STATTYPE                       VARCHAR2                IN     DEFAULT

案例，如何鎖定表scott.emp的統計信息,如下所示：

SQL> exec dbms_stats.lock_table_stats('scott', 'emp');

PL/SQL procedure successfully completed.

如果在鎖定條件下收集統計信息，那麼會出現如下報錯：

SQL> exec dbms_stats.lock_table_stats('TEST', 'TEST');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('TEST', 'TEST');
BEGIN dbms_stats.gather_table_stats('TEST', 'TEST'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 40751
ORA-06512: at "SYS.DBMS_STATS", line 40035
ORA-06512: at "SYS.DBMS_STATS", line 9393
ORA-06512: at "SYS.DBMS_STATS", line 10317
ORA-06512: at "SYS.DBMS_STATS", line 39324
ORA-06512: at "SYS.DBMS_STATS", line 40183
ORA-06512: at "SYS.DBMS_STATS", line 40732
ORA-06512: at line 1

還有一個要註意的是，如果當表的統計信息被鎖定時，此表上創建索引時，不會採集生成索引的相關統計信息，如下所示：

SQL> drop table test.test;

Table dropped.

SQL> create table test.test
  2  as select * from dba_objects;

Table created.

SQL> exec dbms_stats.lock_table_stats('TEST', 'TEST');

PL/SQL procedure successfully completed.

SQL>  create index test.idx_test_n1 on test.test(object_id, object_name);

Index created.

SQL> select num_rows, last_analyzed from dba_ind_statistics where index_name =upper('idx_test_n1');

  NUM_ROWS LAST_ANALYZED
---------- -------------------


1 row selected.

如果表的統計信息鎖定的時候，我們可以使用下麵腳本來收集統計信息：

--使用參數force,強制收集統計信息
exec dbms_stats.gather_index_stats('TEST', 'idx_test_n1',force=>true);

SQL> exec dbms_stats.gather_index_stats('TEST', 'idx_test_n1',force=>true);

PL/SQL procedure successfully completed.

SQL> select num_rows, last_analyzed from dba_ind_statistics where index_name =upper('idx_test_n1');

  NUM_ROWS LAST_ANALYZED
---------- -------------------
     72502 2023-08-31 13:55:01

1 row selected.

如果要收集表的統計信息，使用下麵SQL

exec dbms_stats.gather_table_stats('TEST', 'TEST',force=>true);

當然還有一種方法就是，先給表解鎖統計信息，收集統計信息，然後鎖定表的統計信息，如下所示：

exec dbms_stats.unlock_table_stats('TEST','TEST');
exec dbms_stats.gather_table_stats(ownname =>'TEST', tabname =>'TEST',cascade => true,method_opt=>'for all indexed columns size');
exec dbms_stats.lock_table_stats('TEST','TEST');

查看統計信息鎖定的對象

我們可以使用如下的SQL查詢資料庫中哪些表或索引的統計信息被鎖定了：

SET LINESIZE 680;
COL OWNER FOR A16
COL INDEX_NAME FOR A30
COL TABLE_OWNER FOR A16
COL TABLE_NAME FOR A30
COL PARTITION_NAME FOR A30
COL SUBPARTITION_NAME FOR A30
SELECT D.OWNER,
       D.INDEX_NAME,
       D.TABLE_OWNER,
       D.TABLE_NAME,
       D.PARTITION_NAME,
       D.SUBPARTITION_NAME,
       D.OBJECT_TYPE
FROM DBA_IND_STATISTICS D
 WHERE STATTYPE_LOCKED IN('ALL', 'DATA', 'CACHE')
UNION ALL
SELECT '---',
       '---',
       D.OWNER,
       D.TABLE_NAME,
       D.PARTITION_NAME,
       D.SUBPARTITION_NAME,
       D.OBJECT_TYPE
 FROM DBA_TAB_STATISTICS D
  WHERE STATTYPE_LOCKED  IN('ALL', 'DATA', 'CACHE');

STATTYPE_LOCKED為空代表統計信息未鎖定。

查看統計信息鎖定的表，可以使用下麵SQL語句查詢獲取。

SET LINESIZE 680;
COL OWNER FOR A16
COL TABLE_OWNER FOR A16
COL TABLE_NAME FOR A30
COL PARTITION_NAME FOR A30
COL SUBPARTITION_NAME FOR A30
SELECT 
       D.OWNER,
       D.TABLE_NAME,
       D.PARTITION_NAME,
       D.SUBPARTITION_NAME,
       D.OBJECT_TYPE
 FROM DBA_TAB_STATISTICS D
  WHERE STATTYPE_LOCKED  IN('ALL', 'DATA', 'CACHE');

解鎖統計信息鎖定

語法：

PROCEDURE UNLOCK_TABLE_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNNAME                        VARCHAR2                IN
 TABNAME                        VARCHAR2                IN
 STATTYPE                       VARCHAR2                IN     DEFAULT

解鎖單個表的統計信息

EXEC DBMS_STATS.UNLOCK_TABLE_STATS('scott','emp');

--解鎖用戶統計信息

EXEC DBMS_STATS.UNLOCK_SCHEMA_STATS('xxx');

解鎖某個用戶下(例如，scott用戶）的表的統計信息

EXEC DBMS_STATS.UNLOCK_SCHEMA_STATS('scott');

參考資料：

https://cloud.tencent.com/developer/article/1515990

Preserving Statistics using DBMS_STATS.LOCK_TABLE_STATS (Doc ID 283890.1)