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来访问