大家好，我是 JiekeXu,江湖人称“强哥”，青学会 MOP 技术社区主席，荣获 Oracle ACE Pro 称号，墨天轮 MVP，墨天轮年度“墨力之星”，拥有 Oracle OCP/OCM 认证,MySQL 5.7/8.0 OCP 认证以及 PCA、PCTA、OBCA、OGCA、金仓KCA、KCP 等众多国产数据库认证证书,今天和大家一起来看看Oracle 锁定统计信息解决办法,欢迎关注我的微信公众号“JiekeXu DBA之路”，然后点击右上方三个点“设为星标”置顶，更多干货文章才能第一时间推送，谢谢！
前 言
锁定统计信息虽然可以防止统计信息被意外修改，但也可能带来统计信息过时、优化器决策不准确等问题。因此，在使用锁定统计信息时，需要结合具体业务场景，并做好监控和维护工作。
正 文
Oracle 收集统计信息时报错 ORA-20005 统计信息已锁定。不知什么时候什么人，这个表的统计信息被锁定了，锁定统计信息后，统计信息将不再更新，即使表中的数据发生了显著变化（如大量插入、更新或删除操作），过时的统计信息可能导致优化器选择错误的执行计划，进而影响查询性能，一般在数仓环境下经常 truncate 表时会锁定统计信息，以保证 SQL 执行计划不发生改变。
如果一些对象没有统计信息，而恰巧该对象又锁定了统计信息，那么此时数据库在执行 SQL 时，就会使用动态采样。这个也是锁定统计信息的另外一个功能。
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Mar 10 16:46:28 2025
Version 19.15.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP1');
BEGIN DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP1'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 40799
ORA-06512: at "SYS.DBMS_STATS", line 40083
ORA-06512: at "SYS.DBMS_STATS", line 9393
ORA-06512: at "SYS.DBMS_STATS", line 10317
ORA-06512: at "SYS.DBMS_STATS", line 39372
ORA-06512: at "SYS.DBMS_STATS", line 40231
ORA-06512: at "SYS.DBMS_STATS", line 40780
ORA-06512: at line 1
查看视图 dba_tab_statistics 发现确实是锁定状态。STATTYPE_LOCKED 不为空代表统计信息锁定。
SQL> select OWNER,TABLE_NAME,STATTYPE_LOCKED,NUM_ROWS,LAST_ANALYZED
2 from dba_tab_statistics where STATTYPE_LOCKED is not null and OWNER='SCOTT';
OWNER TABLE_NAME STATT NUM_ROWS LAST_ANALYZED
------------------------------ ----------------------------------- ----- ---------- -------------------
SCOTT EMP ALL 12 2023-06-06 14:23:41
SCOTT EMP1 ALL 3159 2025-01-23 20:46:22
强制收集统计信息，我们使用 DBMS_STATS 包的 GATHER_TABLE_STATS 选项。语法如下：
--https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_STATS.html#GUID-CA6A56B9-0540-45E9-B1D7-D78769B7714C
DBMS_STATS.GATHER_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
stattype VARCHAR2 DEFAULT 'DATA',
force BOOLEAN DEFAULT FALSE,
context DBMS_STATS.CCONTEXT DEFAULT NULL, -- non operative
options VARCHAR2 DEFAULT get_param('OPTIONS'));
force：Gather statistics of table even if it is locked 我们使用 force 参数值设置为 True，强制收集统计信息，只不过下次自动收集时还是会报错。锁定表的统计信息时，所有从属统计信息都会被锁定。锁定的统计信息包括表统计信息、列统计信息、直方图和从属索引统计信息。要在锁定统计信息的情况下覆盖它们，可以将各种 DBMS_STATS 存储过程（例如 DELETE_STATS 和 RESTORE_STATS）中的 FORCE 参数值设置为 true。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP1',FORCE => TRUE);
PL/SQL procedure successfully completed.
SQL> select OWNER,TABLE_NAME,STATTYPE_LOCKED,NUM_ROWS,LAST_ANALYZED
2 from dba_tab_statistics where STATTYPE_LOCKED is not null and OWNER='SCOTT';
OWNER TABLE_NAME STATT NUM_ROWS LAST_ANALYZED
------------------------------ ----------------------------------- ----- ---------- -------------------
SCOTT EMP ALL 12 2023-06-06 14:23:41
SCOTT EMP1 ALL 3159 2025-03-10 15:33:42
当然我们也可以使用 UNLOCK 解锁统计信息，再次查看已经没有锁定的统计信息了，直接收集统计信息也不会报错了。
SQL> EXEC DBMS_STATS.UNLOCK_TABLE_STATS('SCOTT','EMP1');
PL/SQL procedure successfully completed.
SQL> select OWNER,TABLE_NAME,STATTYPE_LOCKED,NUM_ROWS,LAST_ANALYZED from dba_tab_statistics
2 where STATTYPE_LOCKED is not null and owner='SCOTT';
no rows selected
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP1');
PL/SQL procedure successfully completed.
如下是 11204 版本下的测试，和我 19c 环境没有什么区别。
另外其他 SQL 语句：
--锁定分区统计信息
EXEC DBMS_STATS.LOCK_PARTITION_STATS();
--锁定用户统计信息
EXEC DBMS_STATS.LOCK_SCHEMA_STATS();
--查看统计信息锁定的对象
SET LINESIZE 345
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,D.STATTYPE_LOCKED
FROM DBA_IND_STATISTICS D
WHERE STATTYPE_LOCKED is not null and D.OWNER='SCOTT'
UNION ALL
SELECT '---',
'---',
D.OWNER,
D.TABLE_NAME,
D.PARTITION_NAME,
D.SUBPARTITION_NAME,
D.OBJECT_TYPE,D.STATTYPE_LOCKED
FROM DBA_TAB_STATISTICS D
WHERE STATTYPE_LOCKED is not null and D.OWNER='SCOTT';
参考链接
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/controlling-the-use-of-optimizer-statistics.html#GUID-6D85C813-5FBF-4655-ACE5-1E50DB4F8E60
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/gathering-optimizer-statistics.html#GUID-A91DCC46-394D-47F6-8D8E-A9E6F56E432B
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_STATS.html#GUID-CA6A56B9-0540-45E9-B1D7-D78769B7714C
全文完，希望可以帮到正在阅读的你，如果觉得有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~~
❤️ 欢迎关注我的公众号【JiekeXu DBA之路】，一起学习新知识！
——————————————————————————
公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
ITPUB：https://blog.itpub.net/69968215
腾讯云：https://cloud.tencent.com/developer/user/5645107
——————————————————————————
