oracle统计信息被锁定处理

基本现象

ORA-20005: object statistics are locked (stattype = ALL)
在进行SQL时，发现一个表的统计信息过旧，想要收集一个表的统计信息
然而信息报错如下：
SQL> begin
2 dbms_stats.gather_table_stats(ownname => ‘DB’, tabname => ‘tbhrc0’,cascade => true);
3 end;
4 /
begin
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at “SYS.DBMS_STATS”, line 24281
ORA-06512: at “SYS.DBMS_STATS”, line 24332
ORA-06512: at line 2

分析

此表的统计信息被锁定

解锁统计信息：
SQL> exec dbms_stats.unlock_table_stats(‘db’,‘tb0’);

重新收集：

重新收集统计信息：
SQL> begin
2 dbms_stats.gather_table_stats(ownname => ‘DB’, tabname => ‘tbh0’,cascade => true);
3 end;
4 /

PL/SQL procedure successfully completed.

再次查看表的统计 信息：
SQL> select table_name,t.LAST_ANALYZED,t.STATTYPE_LOCKED from dba_tab_statistics t where t.table_name = ‘TBHRC0’;

TABLE_NAME LAST_ANALYZE STATT

---

TB0 31-JUL-19

至此处理正常

总结

自动统计信息收集

oracle 11g会自动收集统计信息
自动收集的频率为：

SQL> select t1.window_name, t1.repeat_interval, t1.duration
  2    from dba_scheduler_windows t1, dba_scheduler_wingroup_members t2
  3   where t1.window_name = t2.window_name
  4     and t2.window_group_name in
  5         ('MAINTENANCE_WINDOW_GROUP', 'BSLN_MAINTAIN_STATS_SCHED');

WINDOW_NAME                    REPEAT_INTERVAL
------------------------------ --------------------------------------------------------------------------------
DURATION
---------------------------------------------------------------------------
MONDAY_WINDOW                  freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0
+000 04:00:00

TUESDAY_WINDOW                 freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0
+000 04:00:00

WEDNESDAY_WINDOW               freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0
+000 04:00:00

THURSDAY_WINDOW                freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0
+000 04:00:00

FRIDAY_WINDOW                  freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0
+000 04:00:00

SATURDAY_WINDOW                freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0
+000 20:00:00

SUNDAY_WINDOW                  freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0
+000 20:00:00


7 rows selected.

周一到周五，每天22开始，持续时间为4h，所以
周六至周日，每天6点

查看开启自动统计信息收集

SQL> select client_name,status from dba_autotask_client;

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection                                  ENABLED
auto space advisor                                               ENABLED
sql tuning advisor                                               ENABLED

auto optimizer stats collection 是自动统计信息收集任务，当前为开户状态

禁止和启动自动统计信息收集
禁止：

exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

启用：

 exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

统计信息管理

解锁一个用户的统计信息
DBMS_STATS.UNLOCK_schema_STATS(user);

解锁单个对象的统计信息
1)被锁定统计信息的表
select table_name from user_tab_statistics where stattype_locked is not null;
然后再解锁对象
exec dbms_stats.unlock_table_stats(user,‘表名’);
2)直接生成sql脚本批量执行
select ‘exec dbms_stats.unlock_table_stats(’’’||user||’’’,’’’||table_name||’’’);’ from user_tab_statistics where stattype_locked is not null;
这里不在生成的sql中用动态的user是为了让执行者明确知道到底是解锁哪个schema下的表，防止误操作

当统计信息收集不准确以及执行计划走偏的时候，有时候类似的需求，我们需要固定统计信息，在这种场景下，需要锁定统计信息的。
敷衍dbms_stats.lock_table_stats来锁定统计信息

删除统计信息
exec dbms_stats.delete_table_stats(‘user’,‘t1’,cascade => TRUE);

锁定统计信息
exec dbms_stats.lock_table_stats(‘user’,‘t1’,cascade=>TRUE);

改变统计信息的估算百分比
exec dbms_stats.set_table_stats(‘TEST’,‘T1’,numrows=>10);

收集表和索引统计信息
exec dbms_stats.gather_table_stats(‘DB’, ‘T1’);
exec dbms_stats.gather_index_stats(‘DB’, ‘T1_IDX1’);

统计信息表
ALL_TAB_STATISTICS displays optimizer statistics for the tables accessible to the current user.
DBA_TAB_STATISTICS displays optimizer statistics for all tables in the database.
USER_TAB_STATISTICS displays optimizer statistics for the tables owned by the current user. This view does not display the OWNER column.

可以根据不同的schema粒度选择对应的schema来访问