ORACLE中鎖住/解鎖統(tǒng)計信息的操作代碼
從Oracle 10g 開始，Oracle提供了鎖定/解鎖表統(tǒng)計信息功能，它的目的是阻止數(shù)據(jù)庫自動收集統(tǒng)計信息，防止可能會產(chǎn)生/出現(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)計信息（指定較高的采樣比例）。這樣有利于相關(guān)SQL生成正確的執(zhí)行計劃。
2：自動統(tǒng)計信息收集作業(yè)運行過后或運行期間，由于一些作業(yè)或業(yè)務(wù)邏輯出現(xiàn)了大量的DML，此時收集的的統(tǒng)計信息可能是不準確，也是就說自動統(tǒng)計信息收集對于這種表沒有什么意義，反而浪費了大量資源，我們需要手工或設(shè)置相關(guān)作業(yè)去收集統(tǒng)計信息。那么我們就可以通過鎖定表的統(tǒng)計信息，阻止數(shù)據(jù)庫的自動收集統(tǒng)計信息作業(yè)去采集相關(guān)統(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)建索引時，不會采集生成索引的相關(guān)統(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)
到此這篇關(guān)于ORACLE中鎖住/解鎖統(tǒng)計信息的操作代碼的文章就介紹到這了,更多相關(guān)oracle鎖住/解鎖統(tǒng)計信息內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
Oracle觸發(fā)器表發(fā)生了變化 觸發(fā)器不能讀它的解決方法(必看)
下面小編就為大家?guī)硪黄狾racle觸發(fā)器表發(fā)生了變化 觸發(fā)器不能讀它的解決方法(必看)。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-04-04
ORA-00349|激活 ADG 備庫時遇到的問題及處理方法
這篇文章主要介紹了ORA-00349|激活 ADG 備庫時遇到的問題及處理方法,本文給大家介紹的非常詳細，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-03-03
PL/SQL是Oracle數(shù)據(jù)庫的編程語言,用于編寫存儲過程、觸發(fā)器、函數(shù)等,今天用plsql想查看表的屬性,看看各個字段的注釋,可是打開一看,居然是亂碼的,所以接下來本文給大家介紹了使用PLSQL查看表屬性亂碼的解決方法,需要的朋友可以參考下2024-03-03
oracle中l(wèi)pad函數(shù)的用法詳解，有需求的朋友可以參考下2012-11-11
CDB和PDB是ORACLE 12C一個很亮的新特性,由于他們的引入導(dǎo)致傳統(tǒng)的ORACLE數(shù)據(jù)庫管理理念不少發(fā)生了改變,這里列舉了部分最基本的cdb和pdb管理方式2016-09-09
本篇文章是對oracle select執(zhí)行順序進行了詳細的分析介紹，需要的朋友參考下2013-05-05
這篇文章主要介紹了Oracle中字符串截取常用方法總結(jié)，非常不錯，具有一定的參考借鑒價值,需要的朋友可以參考下2018-11-11
Oracle中幾種常見的數(shù)據(jù)庫錯誤類型及處理方法
處理常見的數(shù)據(jù)庫錯誤是數(shù)據(jù)庫管理的重要組成部分,以下是幾種常見的數(shù)據(jù)庫錯誤類型及其處理方法,結(jié)合具體代碼示例,以幫助你更好地解決這些問題,感興趣的小伙伴跟著小編一起來看看吧2024-09-09
Oracle語言環(huán)境的描述包括三部分：language、territory、characterset(語言、地域、字符集)，這篇文章主要介紹了Oracle數(shù)據(jù)庫字符集概述及修改方式,需要的朋友可以參考下2023-08-08
Oracle數(shù)據(jù)庫TNS常見錯誤的解決方法匯總
這篇文章主要介紹了Oracle數(shù)據(jù)庫TNS常見錯誤的解決方法,需要的朋友可以參考下2014-07-07