lock table stats
dbms_stats包的10g新功能。可以lock住某个表和其索引的统计信息，让这个表的统计信息不受dbms_stats,analyze,compute statistics等行为的影响。
Statistics for a table or schema can be locked. Once statistics are locked,no modifications can be made to those statistics until the statistics have been unlocked. These locking procedures are useful in a static environment in which you want to guarantee that the statistics never change.
The LOCK_* procedures either freeze the current set of the statistics or to keep the statistics empty (uncollected).
When statistics on a table are locked, all the statistics depending on the table, including table statistics,column statistics, histograms and statistics on all dependent indexes, are considered to be locked.
看了看文档，总结如下:
1) how to see if a table or a index or a object’s statistics is lokced.
2) see if index, columns statistics is cascaded lock while statistics of base table is lock.
3) see if exchange partitions, rebuild index, move tables etc DDL’s effect on locking statistics
4) see if how partition level DDL affect locking statistics
5) The SET_*, DELETE_*, IMPORT_*, GATHER_* procedures that modify statistics in the dictionary of an individual table, index or column will raise an error if statistics of the object is locked.
6)This procedure either freezes the current set of the statistics or keeps the statistics empty (uncollected) to use Dynamic Sampling.
7)The locked or unlocked state is not exported along with the table statistics when using EXPORT_*_STATS procedures.
8)a little internal.
9) bug list
1) how to see if a table or a index or a object’s statistics is lokced.
You may determine if a table’s statistics are locked by querying the STATTYPE_LOCKED column in the
{USER | ALL | DBA}_TAB_STATISTICS view
SQL> create table binzhang_test tablespace cr_data as select * from test where 1=0;
Table created.
SQL> create unique index binzhang_Pk on binzhang_test(event_type,event_id) tablespace cr_data;
Index created.
Index structure of binzhang_test:
----------------------------------
OWNER U PAR TYPE Index Name Column Name
--------------- - --- ------ ------------------------------- ------------------------- ---
HALFCATY_USER Y NO BINZHANG_PK EVENT_TYPE 1
HALFCATY_USER NO EVENT_ID 2
OWNER TABLE_NAME TS_NAME PAR NUM_ROWS BLOCKS SAMPLE
--------------- -------------------- --------------- --- --------------- --------------- ---------------
HALFCATY_USER BINZHANG_TEST CR_DATA NO
OWNER INDEX_NAME PAR LEAF_BLOCKS BLEVEL DISTINCT_KEYS NUM_ROWS CLUSTERING_FACTOR SAMPLE
--------------- ------------------------------ --- --------------- --------------- --------------- --------------- ----------------- ---------------
HALFCATY_USER BINZHANG_PK NO
SQL> exec dbms_stats.lock_table_stats(user,'binzhang_test');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.lock_table_stats(user,'binzhang_test');
PL/SQL procedure successfully completed
SQL> SELECT STATTYPE_LOCKED FROM DBA_TAB_STATISTICS WHERE TABLE_NAME='BINZHANG_TEST';
STATT
-----
ALL
SQL> select index_name,STATTYPE_LOCKED from dba_ind_statistics where TABLE_NAME='BINZHANG_TEST';
INDEX_NAME STATT
------------------------------ -----
BINZHANG_PK ALL
SQL> analyze table binzhang_test compute statistics;
analyze table binzhang_test compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked
SQL> analyze index binzhang_pk compute statistics;
analyze index binzhang_pk compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked
SQL$> !oerr ora 38029
38029, 00000, "object statistics are locked"
// *Cause: An attept was made to modify optimizer statistics of the object.
// *Action: Unlock statistics with the DBMS_STATS.UNLOCK_TABLE_STATS procedure
// on base table(s). Retry the operation if it is okay to update statistics.
SQL> exec dbms_stats.gather_table_stats(user,'binzhang_test');
BEGIN dbms_stats.gather_table_stats(user,'binzhang_test'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 13182
ORA-06512: at "SYS.DBMS_STATS", line 13202
ORA-06512: at line 1
SQL> alter index binzhang_pk rebuild;
Index altered.
SQL> alter table binzhang_test move;
Table altered.
SQL> create index binzhang_ix2 on binzhang_test(event_id) tablespace cr_data compute statistics;
create index binzhang_ix2 on binzhang_test(event_id) tablespace cr_data compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked
SQL> create index binzhang_ix2 on binzhang_test(event_id) tablespace cr_data;
Index created.
Index structure of binzhang_test:
----------------------------------
OWNER U PAR TYPE Index Name Column Name
--------------- - --- ------ ------------------------------- ------------------------- ---
HALFCATY_USER Y NO BINZHANG_PK EVENT_TYPE 1
HALFCATY_USER NO EVENT_ID 2
OWNER TABLE_NAME TS_NAME PAR NUM_ROWS BLOCKS SAMPLE
--------------- -------------------- --------------- --- --------------- --------------- ---------------
HALFCATY_USER BINZHANG_TEST CR_DATA NO
OWNER INDEX_NAME PAR LEAF_BLOCKS BLEVEL DISTINCT_KEYS NUM_ROWS CLUSTERING_FACTOR SAMPLE
--------------- ------------------------------ --- --------------- --------------- --------------- --------------- ----------------- ---------------
HALFCATY_USER BINZHANG_PK NO
HALFCATY_USER BINZHANG_IX2 NO
可见，对于analyze,dbms_stats,compute staitstics等字眼，lock stats都会抛出错误信息。
2) see if index, columns statistics is cascaded lock while statistics of base table is lock.
如上例子也说明了。
3) see if exchange partitions, rebuild index, move tables etc DDL’s effect on locking statistics
如上例子: rebuild index, move tables等DDL并不受到影响。
并且对于交换分区来说，也不受lock stats影响，且统计信息随着数据段的交换，也被交换了。这表示统计信息是捆绑在segment上的，而lock stats这个功能只有在表level上监控。见如下测试。
SQL> create table binzhang_hash tablespace cr_data partition by hash (event_id)
partitions 2 as select * from test where rownum<=1000;
Table created.
SQL> create unique index binzhang_hash_pk on binzhang_hash(event_type,event_id) local;
Index created.
SQL> exec dbms_stats.gather_table_stats(user,’binzhang_hash’);
PL/SQL procedure successfully completed.
SQL> !ora idxdesc binzhang_hash
OWNER U PAR TYPE Index Name Column Name
————— - — —— ——————————- ————————- —
HALFCATY_USER Y YES BINZHANG_HASH_PK EVENT_TYPE 1
HALFCATY_USER YES EVENT_ID 2
OWNER TABLE_NAME TS_NAME PAR NUM_ROWS BLOCKS SAMPLE LAST_ANALYZED
————— ——————– ————— — ————— ————— ————— ——————-
HALFCATY_USER BINZHANG_HASH YES 1000 40 99 2008/10/12 23:49:18
OWNER INDEX_NAME PAR LEAF_BLOCKS BLEVEL DISTINCT_KEYS NUM_ROWS CLUSTERING_FACTOR SAMPLE
————— —————————— — ————— ————— ————— ————— —————– —————
HALFCATY_USER BINZHANG_HASH_PK YES 6 1 1000 1000 82 99
SQL> alter table binzhang_hash exchange partition SYS_P341 with table binzhang_test including indexes without validation;
Table altered.
SQL> !ora idxdesc binzhang_test
OWNER U PAR TYPE Index Name Column Name
————— - — —— ——————————- ————————- —
HALFCATY_USER Y NO BINZHANG_PK EVENT_TYPE 1
HALFCATY_USER NO EVENT_ID 2
OWNER TABLE_NAME TS_NAME PAR NUM_ROWS BLOCKS SAMPLE LAST_ANALYZED
————— ——————– ————— — ————— ————— ————— ——————-
HALFCATY_USER BINZHANG_TEST CR_DATA NO 497 20 99 2008/10/12 23:49:17
OWNER INDEX_NAME PAR LEAF_BLOCKS BLEVEL DISTINCT_KEYS NUM_ROWS CLUSTERING_FACTOR SAMPLE
————— —————————— — ————— ————— ————— ————— —————– —————
HALFCATY_USER BINZHANG_PK NO 3 1 497 497 41 99
SQL> analyze table binzhang_test compute statistics;
analyze table binzhang_test compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked
4) see if how partition level DDL affect locking statistics
效果同表一样。
SQL> exec dbms_stats.lock_table_stats(user,'binzhang_hash');
PL/SQL procedure successfully completed.
SQL> SELECT STATTYPE_LOCKED FROM DBA_TAB_STATISTICS WHERE TABLE_NAME='BINZHANG_HASH';
STATT
-----
ALL
ALL
ALL
SQL> select table_name,STATTYPE_LOCKED FROM DBA_TAB_STATISTICS WHERE TABLE_NAME='BINZHANG_HASH';
TABLE_NAME STATT
------------------------------ -----
BINZHANG_HASH ALL
BINZHANG_HASH ALL
BINZHANG_HASH ALL
SQL> select table_name,partition_name ,STATTYPE_LOCKED FROM DBA_TAB_STATISTICS WHERE TABLE_NAME='BINZHANG_HASH';
TABLE_NAME PARTITION_NAME STATT
------------------------------ ------------------------------ -----
BINZHANG_HASH ALL
BINZHANG_HASH SYS_P341 ALL
BINZHANG_HASH SYS_P342 ALL
SQL> alter table BINZHANG_HASH move partition SYS_P341;
Table altered.
SQL> alter table BINZHANG_HASH move partition SYS_P342;
Table altered.
SQL> alter index BINZHANG_HASH_PK rebuild partition SYS_P341;
Index altered.
SQL> alter index BINZHANG_HASH_PK rebuild partition SYS_P342;
Index altered.
SQL> exec dbms_stats.gather_index_stats(user,'BINZHANG_HASH_PK','SYS_P342');
BEGIN dbms_stats.gather_index_stats(user,'BINZHANG_HASH_PK','SYS_P342'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 10640
ORA-06512: at "SYS.DBMS_STATS", line 10664
ORA-06512: at line 1
5) The SET_*, DELETE_*, IMPORT_*, GATHER_* procedures that modify statistics in the dictionary of an individual table,
index or column will raise an error if statistics of the object is locked.
BEGIN
dbms_stats.import_table_stats ( 'halfcaty_user', 'binzhang_test', NULL , 'stat_test');
END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 1607
ORA-06512: at "SYS.DBMS_STATS", line 2117
ORA-06512: at "SYS.DBMS_STATS", line 7250
ORA-06512: at line 1
6)This procedure either freezes the current set of the statistics or keeps the statistics empty (uncollected) to use Dynamic Sampling.
7)The locked or unlocked state is not exported along with the table statistics when using EXPORT_*_STATS procedures.
8)a little internal.
oracle judge locking status by tab$.trigflag. Probably there is an implicit system wide DDL trigger.
select text from dba_views where view_name='DBA_IND_STATISTICS';
decode(bitand(t.trigflag, 67108864) + bitand(t.trigflag, 134217728),
0, NULL, 67108864, 'DATA', 134217728, 'CACHE', 'ALL'),
SQL> select TRIGFLAG,count(*) from tab$ group by TRIGFLAG;
TRIGFLAG COUNT(*)
---------- ----------
0 1196
2097152 29
201326592 10
9) bug list
附加一个小bug.4921917 :DBA_TAB_STATISTICS DOES NOT SHOW IF SINGLE PARTTIONS STATISTICS ARE LOCKED
SQL> exec dbms_stats.unlock_table_stats(user,'binzhang_hash');
PL/SQL procedure successfully completed.
SQL> select table_name,partition_name ,STATTYPE_LOCKED FROM DBA_TAB_STATISTICS WHERE TABLE_NAME='BINZHANG_HASH';
TABLE_NAME PARTITION_NAME STATT
------------------------------ ------------------------------ -----
BINZHANG_HASH
BINZHANG_HASH SYS_P341
BINZHANG_HASH SYS_P342
SQL> exec dbms_stats.unlock_partition_stats(user,'binzhang_hash','SYS_P341');
PL/SQL procedure successfully completed.
SQL> select table_name,partition_name ,STATTYPE_LOCKED FROM DBA_TAB_STATISTICS WHERE TABLE_NAME='BINZHANG_HASH';
TABLE_NAME PARTITION_NAME STATT
------------------------------ ------------------------------ -----
BINZHANG_HASH
BINZHANG_HASH SYS_P341
BINZHANG_HASH SYS_P342
.text:''):(d.getSelection?d.getSelection():'');void(keyit=window.open('http://my.poco.cn/fav/storeIt.php?t='+escape(d.title)+'&u='+escape(d.location.href)+'&c='+escape(t)+'&img=http://www.h-strong.com/blog/logo.gif','keyit','scrollbars=no,width=475,height=575,status=no,resizable=yes'));keyit.focus(); "添加到 POCO 网摘"){kind=logo}
