oracle 锁定、对比统计信息



转载▼

 

锁定schema统计
execute dbms_statS.unlock_schema_stats(ownname=>'SCOTT');

查看表锁定情况
SQL> select owner,table_name,STATTYPE_LOCKED from dba_tab_statistics where owner='SCOTT';

OWNER TABLE_NAME STATTYPE_L
------------------------------ ------------------------------ ----------
SCOTT BIN$ywjNKrPMSEygN6Kvb2yz7Q==$0
SCOTT BONUS ALL
SCOTT DEPT ALL
SCOTT EMP ALL
SCOTT SALGRADE ALL
SCOTT T ALL

解锁
SQL> execute dbms_statS.unlock_schema_stats(ownname=>'SCOTT');

SQL> select owner,table_name,STATTYPE_LOCKED from dba_tab_statistics where owner='SCOTT';

OWNER TABLE_NAME STATTYPE_L
------------------------------ ------------------------------ ----------
SCOTT BIN$ywjNKrPMSEygN6Kvb2yz7Q==$0
SCOTT BONUS
SCOTT DEPT
SCOTT EMP
SCOTT SALGRADE
SCOTT T

锁定表统计
execute dbms_stats.lock_table_stats(ownname=>'SCOTT',tabname=>'T');

收集统计信息将报错
execute dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T');

SQL> execute dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T');
BEGIN dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T'); END;

*
第 1 行出现错误:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: 在 "SYS.DBMS_STATS", line 20337
ORA-06512: 在 "SYS.DBMS_STATS", line 20360
ORA-06512: 在 line 1

可以添加force强制覆盖
execute dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T',force=>true);

SQL> create index iid_idx on t(iid) compute statistics;
create index iid_idx on t(iid) compute statistics
*
第 1 行出现错误:
ORA-38029: 对象统计信息已锁定

默认没有搜集统计信息，有点疑惑
SQL> create index iid_idx on t(iid) ;

execute dbms_statS.unlock_TABLE_stats(ownname=>'SCOTT',TABNAME=>'T');

比较对象统计信息。
SELECT *
FROM table(dbms_stats.diff_table_stats_in_pending(
ownname => 'SCOTT',
tabname => 'T',
time_stamp => NULL,
pctthreshold => 10));

SELECT *
FROM table(dbms_stats.diff_table_stats_in_pending(
ownname => 'SCOTT',
tabname => 'T',
time_stamp => NULL,
pctthreshold => 0));

SELECT *
FROM table(dbms_stats.diff_table_stats_in_stattab(
ownname => 'SCOTT',
tabname => 'T',
stattab1 => 'MYSTATS',
statid1 => 'SET1',
stattab1own => 'SCOTT',
pctthreshold => 0));

SELECT *
FROM table(dbms_stats.diff_table_stats_in_history(
ownname => 'SCOTT',
tabname => 'T',
time1 => systimestamp - to_dsinterval('0 00:00:15'),
time2 => NULL,
pctthreshold => 0));