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

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

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

從Oracle 10g 開始，Oracle提供了鎖定/解鎖表統(tǒng)計信息功能，它的目的是阻止數(shù)據(jù)庫自動收集統(tǒng)計信息，防止可能會產生/出現(xiàn)的糟糕的計劃。它對于數(shù)據(jù)頻繁更改的Volatile Tables最有用，因為Volatile Tables的某些數(shù)據(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呢？官方解釋：Volatile tables are being deleted or truncated, and then rebuilt during the day. 還有一種解釋指：Volatile Tables 是指變化比較大的表，即頻繁進行Insert、Delete、Update 多種操作的表。

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

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

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

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)計信息鎖定

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

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

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)計信息

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

--鎖定用戶統(tǒng)計信息

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)計信息,如下所示：

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

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

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

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

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)計信息鎖定的時候，我們可以使用下面腳本來收集統(tǒng)計信息：

--使用參數(shù)force,強制收集統(tǒng)計信息
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)計信息，使用下面SQL

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

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

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)計信息鎖定的對象

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

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)計信息未鎖定。

查看統(tǒng)計信息鎖定的表，可以使用下面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');

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

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

解鎖單個表的統(tǒng)計信息

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

--解鎖用戶統(tǒng)計信息

EXEC?DBMS_STATS.UNLOCK_SCHEMA_STATS('xxx');

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

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)

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

您可能感興趣的文章:

Oracle date 和 timestamp 區(qū)別詳解
這篇文章主要介紹了Oracle date 和 timestamp 區(qū)別詳解的相關資料,需要的朋友可以參考下
2017-03-03
Oracle(90)數(shù)據(jù)庫如何創(chuàng)建用戶(User)
這篇文章主要介紹了在Oracle數(shù)據(jù)庫中創(chuàng)建用戶的過程,包括連接到數(shù)據(jù)庫、創(chuàng)建用戶、分配權限、分配表空間和設置賬戶狀態(tài),提供了詳細的步驟和代碼示例,文中通過代碼介紹的非常詳細,需要的朋友可以參考下
2024-12-12
oracle查詢語句大全(oracle 基本命令大全一)
這篇文章主要介紹了oracle查詢語句，有助于新手迅速熟悉ORACLE基本語法有助于新手迅速熟悉ORACLE基本語法，需要的朋友可以收藏下
2013-08-08
Oracle 11g Client客戶端安裝教程
這篇文章主要為大家詳細介紹了Oracle 11g Client客戶端安裝教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下
2017-01-01
[Oracle] Data Guard 之淺析Switchover與Failover
以下是對Oracle中Switchover與Failover的使用進行了詳細的分析介紹，需要的朋友參考下
2013-07-07
Oracle數(shù)據(jù)庫查詢執(zhí)行過的SQL語句方法匯總
這篇文章主要介紹了Oracle數(shù)據(jù)庫查詢執(zhí)行過的SQL語句方法的相關資料,并提供了按時間、操作類型、用戶或權限過濾的示例,同時還強調了查詢的注意事項,文中給出了詳細的代碼示例,需要的朋友可以參考下
2025-05-05
ORACLE批量導入圖片到BLOB字段代碼示例
這篇文章主要介紹了ORACLE批量導入圖片到BLOB字段代碼示例，此代碼示例是文章作者的項目源碼，具有一定參考價值，需要的朋友可以了解下。
2017-10-10
catalog方式的rman備份與恢復示例
這篇文章主要介紹了catalog方式的rman備份與恢復,需要的朋友可以參考下
2014-03-03
Oracle模糊查詢的幾種方法匯總【最后一種最優(yōu)】
這篇文章主要介紹了Oracle模糊查詢的幾種方法匯總,針對每種方法小編給大家做了解析說明，在這推薦第三種，需要的朋友可以參考下
2023-07-07
Oracle修改seuqnce當前值的三種方法
本文介紹了三種調整Oracle數(shù)據(jù)庫序列當前值的方法,增加、減少序列當前值；直接使用SQL調整序列當前值；以及刪除并重建序列,還討論了序列越界的問題及其解決方法,以及不能修改序列初始值的注意事項,感興趣的朋友跟隨小編一起看看吧
2026-03-03