ORACLE中鎖住/解鎖統(tǒng)計(jì)信息的操作代碼

更新時(shí)間：2023年09月01日 14:29:01 作者：瀟湘隱者

從Oracle 10g 開(kāi)始,Oracle提供了鎖定/解鎖表統(tǒng)計(jì)信息功能,它的目的是阻止數(shù)據(jù)庫(kù)自動(dòng)收集統(tǒng)計(jì)信息,防止可能會(huì)產(chǎn)生/出現(xiàn)的糟糕的計(jì)劃,這篇文章主要介紹了ORACLE中鎖住/解鎖統(tǒng)計(jì)信息的操作代碼,需要的朋友可以參考下

從Oracle 10g 開(kāi)始，Oracle提供了鎖定/解鎖表統(tǒng)計(jì)信息功能，它的目的是阻止數(shù)據(jù)庫(kù)自動(dòng)收集統(tǒng)計(jì)信息，防止可能會(huì)產(chǎn)生/出現(xiàn)的糟糕的計(jì)劃。它對(duì)于數(shù)據(jù)頻繁更改的Volatile Tables最有用，因?yàn)閂olatile Tables的某些數(shù)據(jù)集可能會(huì)生成糟糕的計(jì)劃。

官方的介紹如下：

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呢？官方解釋?zhuān)篤olatile tables are being deleted or truncated, and then rebuilt during the day. 還有一種解釋指：Volatile Tables 是指變化比較大的表，即頻繁進(jìn)行Insert、Delete、Update 多種操作的表。

另外，數(shù)據(jù)庫(kù)自動(dòng)收集表的統(tǒng)計(jì)信息，大部分情況下，這種行為對(duì)于數(shù)據(jù)庫(kù)的性能是有利的。但是也有一些情況，我們不想數(shù)據(jù)庫(kù)自動(dòng)收集某個(gè)表的統(tǒng)計(jì)信息，例如：

1：自動(dòng)收集統(tǒng)計(jì)信息作業(yè)數(shù)據(jù)采樣的比例過(guò)低，尤其對(duì)于一些大表，準(zhǔn)確來(lái)說(shuō)是對(duì)于一些數(shù)據(jù)不怎么變化的大表，我們想手工收集集統(tǒng)計(jì)信息（指定較高的采樣比例）。這樣有利于相關(guān)SQL生成正確的執(zhí)行計(jì)劃。
2：自動(dòng)統(tǒng)計(jì)信息收集作業(yè)運(yùn)行過(guò)后或運(yùn)行期間，由于一些作業(yè)或業(yè)務(wù)邏輯出現(xiàn)了大量的DML，此時(shí)收集的的統(tǒng)計(jì)信息可能是不準(zhǔn)確，也是就說(shuō)自動(dòng)統(tǒng)計(jì)信息收集對(duì)于這種表沒(méi)有什么意義，反而浪費(fèi)了大量資源，我們需要手工或設(shè)置相關(guān)作業(yè)去收集統(tǒng)計(jì)信息。那么我們就可以通過(guò)鎖定表的統(tǒng)計(jì)信息，阻止數(shù)據(jù)庫(kù)的自動(dòng)收集統(tǒng)計(jì)信息作業(yè)去采集相關(guān)統(tǒng)計(jì)信息。

如果一些對(duì)象沒(méi)有統(tǒng)計(jì)信息，而你又鎖定了統(tǒng)計(jì)信息，那么此時(shí)數(shù)據(jù)庫(kù)在執(zhí)行SQL時(shí)，就會(huì)使用動(dòng)態(tài)采樣。這個(gè)也是鎖定統(tǒng)計(jì)信息的另外一個(gè)功能。

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.

統(tǒng)計(jì)信息鎖定

當(dāng)不需要對(duì)某個(gè)表做收集的時(shí)候，可以采用鎖定統(tǒng)計(jì)信息的方法，把不需要收集的表排除在外，這樣可以使得此表上的統(tǒng)計(jì)信息不變，Oracle提供三種粒度的鎖定統(tǒng)計(jì)信息的方法，如下所示

--鎖定表的統(tǒng)計(jì)信息

EXEC?DBMS_STATS.LOCK_TABLE_STATS();
參數(shù)：
PROCEDURE?LOCK_TABLE_STATS
?Argument?Name??????????????????Type????????????????????In/Out?Default?
?------------------------------?-----------------------?------?--------
?OWNNAME????????????????????????VARCHAR2????????????????IN
?TABNAME????????????????????????VARCHAR2????????????????IN
?STATTYPE???????????????????????VARCHAR2????????????????IN?????DEFAULT

--鎖定表的分區(qū)統(tǒng)計(jì)信息

EXEC?DBMS_STATS.LOCK_PARTITION_STATS();?
參數(shù)
PROCEDURE?LOCK_PARTITION_STATS
?Argument?Name??????????????????Type????????????????????In/Out?Default?
?------------------------------?-----------------------?------?--------
?OWNNAME????????????????????????VARCHAR2????????????????IN
?TABNAME????????????????????????VARCHAR2????????????????IN
?PARTNAME???????????????????????VARCHAR2????????????????IN

--鎖定用戶(hù)統(tǒng)計(jì)信息

EXEC?DBMS_STATS.LOCK_SCHEMA_STATS();
參數(shù)：
PROCEDURE?LOCK_SCHEMA_STATS
?Argument?Name??????????????????Type????????????????????In/Out?Default?
?------------------------------?-----------------------?------?--------
?OWNNAME????????????????????????VARCHAR2????????????????IN
?STATTYPE???????????????????????VARCHAR2????????????????IN?????DEFAULT

案例，如何鎖定表scott.emp的統(tǒng)計(jì)信息,如下所示：

SQL>?exec?dbms_stats.lock_table_stats('scott',?'emp');
PL/SQL?procedure?successfully?completed.

如果在鎖定條件下收集統(tǒng)計(jì)信息，那么會(huì)出現(xiàn)如下報(bào)錯(cuò)：

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

還有一個(gè)要注意的是，如果當(dāng)表的統(tǒng)計(jì)信息被鎖定時(shí)，此表上創(chuàng)建索引時(shí)，不會(huì)采集生成索引的相關(guān)統(tǒng)計(jì)信息，如下所示：

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.

如果表的統(tǒng)計(jì)信息鎖定的時(shí)候，我們可以使用下面腳本來(lái)收集統(tǒng)計(jì)信息：

--使用參數(shù)force,強(qiáng)制收集統(tǒng)計(jì)信息
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.

如果要收集表的統(tǒng)計(jì)信息，使用下面SQL

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

當(dāng)然還有一種方法就是，先給表解鎖統(tǒng)計(jì)信息，收集統(tǒng)計(jì)信息，然后鎖定表的統(tǒng)計(jì)信息，如下所示：

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');

查看統(tǒng)計(jì)信息鎖定的對(duì)象

我們可以使用如下的SQL查詢(xún)數(shù)據(jù)庫(kù)中哪些表或索引的統(tǒng)計(jì)信息被鎖定了：

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為空代表統(tǒng)計(jì)信息未鎖定。

查看統(tǒng)計(jì)信息鎖定的表，可以使用下面SQL語(yǔ)句查詢(xún)獲取。

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');

解鎖統(tǒng)計(jì)信息鎖定

語(yǔ)法：
PROCEDURE?UNLOCK_TABLE_STATS
?Argument?Name??????????????????Type????????????????????In/Out?Default?
?------------------------------?-----------------------?------?--------
?OWNNAME????????????????????????VARCHAR2????????????????IN
?TABNAME????????????????????????VARCHAR2????????????????IN
?STATTYPE???????????????????????VARCHAR2????????????????IN?????DEFAULT

解鎖單個(gè)表的統(tǒng)計(jì)信息

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

--解鎖用戶(hù)統(tǒng)計(jì)信息

EXEC?DBMS_STATS.UNLOCK_SCHEMA_STATS('xxx');

解鎖某個(gè)用戶(hù)下(例如，scott用戶(hù)）的表的統(tǒng)計(jì)信息

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)

到此這篇關(guān)于ORACLE中鎖住/解鎖統(tǒng)計(jì)信息的操作代碼的文章就介紹到這了,更多相關(guān)oracle鎖住/解鎖統(tǒng)計(jì)信息內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: