统计信息被锁定引发的血案

By banping On 2010/03/19 · Leave a Comment · In Oracle

在一个测试环境进行统计信息收集，结果提示被锁定：

SQL> exec dbms_stats.gather_table_stats('erp','balance');
BEGIN dbms_stats.gather_table_stats('cnderp','balance'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 13056
ORA-06512: at "SYS.DBMS_STATS", line 13076
ORA-06512: at line 1
Elapsed: 00:00:00.87

可以用以下命令解除锁定：

SQL> exec dbms_stats.unlock_table_stats('erp','balance');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.47
SQL> exec dbms_stats.gather_table_stats('erp','balance');

PL/SQL procedure successfully completed.

Elapsed: 00:04:37.31

解除都整个schema对象的锁定

SQL> EXEC DBMS_STATS.unlock_schema_stats(ownname => 'erp');

PL/SQL procedure successfully completed.

统计信息被锁定的原因有多种：

Symptoms
---------
Either of the following two error messages are signaled:
1. ORA-38029: object statistics are locked
2. ORA-20005: object statistics are locked (stattype = ALL)

Cause
---------
Possible Cause 1:
DBMS_STATS.LOCK_[SCHEMA|TABLE]_STATS has been used to lock statistics on the table.

Possible Cause 2:
Using import (imp) or data pump import (impdp) to import a table without data results in the table's statistics being locked in 10gR2.

Possible Cause 3:
After an IMPORT is finished for which ROWS=N, the statistics for all tables imported will be locked.
Part Number B14233-04 Database Readme 10g Release 2 (10.2) (39.5 Original Export/Import)

Possible Cause 4: If the table is a queue table then the statistics are intended to be empty and locked so that dynamic sampling will be used due to the table's volatility. During an upgrade to 10gR2 statistics on queue tables are deleted and then locked. In 10gR2 when a queue table is created statistics are locked while still empty.

Solution
---------
If the table is a queue table then the statistics should remain empty and locked so that dynamic sampling is used due to the volatility of queue tables. If the table is not a queue table, unlock the statistics using DBMS_STATS.UNLOCK_[SCHEMA|TABLE]_STATS or gather statistics on the table using DBMS_STATS.GATHER_[SCHEMA|TABLE|INDEX]_STATS and the force=>true parameter.

To prevent import (imp) from locking the table's statistics when importing a table without therows (rows=n), use statistics=none. To prevent data pump import (impdp) from locking the table's statistics when importing a table without the rows (content=metadata_only), use exclude=(table_statistics,index_statistics).

我这里统计信息被锁定的原因是：这些数据文件是从一个测试库copy过来的，而这些数据最初imp到测试库的时候没有导入约束，然后我在当前环境通过imp加rows=n参数导入了约束信息。

但是奇怪的事情是，在完成了统计信息收集后，通过autotrace查看sql语句的统计信息时，统计信息显示的都是0：

SQL> connect sys/sys as sysdba
Connected.
SQL>
SQL> shutdown immediate
SQL> startup
ORACLE instance started.
Database opened.
SQL> connect erp/erp
Connected.

SQL> show user
USER is "ERP"

SQL> set timing on
SQL> set autotrace on statistics
SQL> select count(*) from balance;

COUNT(*)
----------
19548622

Elapsed: 00:00:01.31

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

尝试了很多种办法也没用：

SQL> drop table plan_table
2 /

Table dropped.

Elapsed: 00:00:02.83
SQL> @?/rdbms/admin/utlxplan.sql

Table created.

Elapsed: 00:00:00.26

SQL> analyze table balance estimate statistics;

Table analyzed.

开来这个问题有待进一步研究：

SQL> show user
USER is "SYS"

SQL> connect erp/erp@erpoptm
Connected.

SQL> set autotrace on statistics
SQL> set timing on
SQL> select count(*) from balance;

COUNT(*)
----------
19548622

Elapsed: 00:00:00.99

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> exit

[oracle@erpdevdb install]$ sqlplus "cnderp/cnderp@erpoptm"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 22 11:38:34 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> set autotrace on statistics
SQL> select count(*) from balance;

COUNT(*)
----------
19548622

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
43615 consistent gets
0 physical reads
0 redo size
518 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@erpdevdb install]$ sqlplus " / as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 22 11:39:13 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> connect erp/erp@erpoptm
Connected.
SQL> set autotrace on statistics
SQL> select count(*) from balance;

COUNT(*)
----------
19548622

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
43615 consistent gets
0 physical reads
0 redo size
518 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@erpdevdb install]$ sqlplus " / as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 22 11:40:28 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> set autotrace on statistics
SQL> select count(*) from erp.balance;

COUNT(*)
----------
19548622

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
43615 consistent gets
0 physical reads
0 redo size
518 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> show user
USER is "SYS"