欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

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

 更新時間:2023年09月01日 14:29:01   作者:瀟湘隱者  
從Oracle 10g 開始,Oracle提供了鎖定/解鎖表統(tǒng)計信息功能,它的目的是阻止數(shù)據(jù)庫自動收集統(tǒng)計信息,防止可能會產(chǎn)生/出現(xiàn)的糟糕的計劃,這篇文章主要介紹了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)文章

最新評論