ITPub博客

首页 > 数据库 > Oracle > 统计信息锁定相关

统计信息锁定相关

原创 Oracle 作者:to_be_dba 时间:2016-01-20 17:37:13 0 删除 编辑
实验步骤:
(1)在scott用户下创建表t1、t2、t3,分别插入数据,收集统计信息
(2)锁定scott.t1的统计信息
(3)expdp导出
(4)impdp导入到t1、t2到terry用户下,使用CONTENT=METADATA_ONLY选项将t3用impdp导入到terry用户下
(5)查看统计信息,并尝试重新收集
(6)导入数据时,在使用content=metadata_only的同时,使用exclude=TABLE_STATISTICS去掉表统计信息的导入
(7)导入数据时指定查询条件,统计信息会被导入,但不再准确
(8)手工设置统计信息
(9)解锁统计信息


环境:11.2.0.1.0
=======================
(1)在scott用户下创建表t1、t2、t3,分别插入数据,收集统计信息
drop table scott.t1;
drop table scott.t2;
drop table scott.t3;
create table scott.t1 as select * from dba_tables where rownum<1000;
create table scott.t2 as select * from dba_tables where rownum<1000;
create table scott.t3 as select * from dba_tables where rownum<1000;


exec dbms_stats.gather_table_stats('SCOTT','T1',cascade=>true);
exec dbms_stats.gather_table_stats('SCOTT','T2',cascade=>true);
exec dbms_stats.gather_table_stats('SCOTT','T3',cascade=>true);


(2)锁定scott.t1的统计信息
exec dbms_stats.lock_table_stats(ownname => 'SCOTT',tabname => 'T1');
set lines 200
select owner,table_name,num_rows,blocks,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss'),user_stats,stattype_locked 
from dba_tab_statistics
where table_name in ('T1','T2','T3');




SQL>set lines 200
SQL>select owner,table_name,num_rows,blocks,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss'),user_stats,stattype_locked 
from dba_tab_statistics
where table_name in ('T1','T2','T3');


OWNER                          TABLE_NAME                       NUM_ROWS     BLOCKS TO_CHAR(LAST_ANALYZ USE STATT
------------------------------ ------------------------------ ---------- ---------- ------------------- --- -----
SCOTT                          T3                                    999         41 2016-01-10 20:01:06 NO
SCOTT                          T2                                    999         41 2016-01-10 20:01:05 NO
SCOTT                          T1                                    999         41 2016-01-10 20:01:04 NO  ALL


(3)expdp导出
[oracle@ogg ~]$ expdp scott/scott directory=ORA_HOME dumpfile=testexp.dmp logfile=testexp.log tables=T1,T2,T3


Export: Release 11.2.0.1.0 - Production on Sun Jan 10 20:13:29 2016


Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=ORA_HOME dumpfile=testexp.dmp logfile=testexp.log tables=T1,T2,T3 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.125 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."T1"                                269.0 KB     999 rows
. . exported "SCOTT"."T2"                                269.0 KB     999 rows
. . exported "SCOTT"."T3"                                269.0 KB     999 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /home/oracle/testexp.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 20:13:39


(4)impdp导入到t1、t2到terry用户下,使用CONTENT=METADATA_ONLY选项将t3用impdp导入到terry用户下
[oracle@ogg ~]$  impdp terry/terry remap_schema=scott:terry directory=ORA_HOME  dumpfile=testexp.dmp logfile=impdp_testexp.log tables=scott.t1,scott.t2


Import: Release 11.2.0.1.0 - Production on Sun Jan 10 20:29:33 2016


Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TERRY"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TERRY"."SYS_IMPORT_TABLE_01":  terry/******** remap_schema=scott:terry directory=ORA_HOME dumpfile=testexp.dmp logfile=impdp_testexp.log tables=scott.t1,scott.t2 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TERRY"."T1"                                269.0 KB     999 rows
. . imported "TERRY"."T2"                                269.0 KB     999 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "TERRY"."SYS_IMPORT_TABLE_01" successfully completed at 20:29:39






[oracle@ogg ~]$ impdp terry/terry remap_schema=scott:terry directory=ORA_HOME  dumpfile=testexp.dmp logfile=impdp_testexp.log tables=scott.t3 CONTENT=METADATA_ONLY


Import: Release 11.2.0.1.0 - Production on Sun Jan 10 20:30:36 2016


Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TERRY"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TERRY"."SYS_IMPORT_TABLE_01":  terry/******** remap_schema=scott:terry directory=ORA_HOME dumpfile=testexp.dmp logfile=impdp_testexp.log tables=scott.t3 CONTENT=METADATA_ONLY 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "TERRY"."SYS_IMPORT_TABLE_01" successfully completed at 20:30:42




(5)查看统计信息,并尝试重新收集
查看统计信息:
SQL> set lines 200
select owner,table_name,num_rows,blocks,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss'),user_stats,stattype_locked 
  2  from dba_tab_statistics
  3  where table_name in ('T1','T2','T3');


OWNER                          TABLE_NAME                       NUM_ROWS     BLOCKS TO_CHAR(LAST_ANALYZ USE STATT
------------------------------ ------------------------------ ---------- ---------- ------------------- --- -----
SCOTT                          T3                                    999         41 2016-01-10 20:01:06 NO
SCOTT                          T2                                    999         41 2016-01-10 20:01:05 NO
SCOTT                          T1                                    999         41 2016-01-10 20:01:04 NO  ALL
TERRY                          T3                                    999         41 2016-01-10 20:01:06 NO  ALL
TERRY                          T2                                    999         41 2016-01-10 20:01:05 NO
TERRY                          T1                                    999         41 2016-01-10 20:01:04 NO  ALL


6 rows selected.
可以看到:原统计信息锁定的scott.t1,导入后terry.t1仍是锁定的;
scott.t3由于导入时指定了content=metadata_only,统计信息也被锁定了。


三个表中分别插入数据:
insert into terry.t1 select * from terry.t1;
insert into terry.t2 select * from terry.t1;
insert into terry.t3 select * from terry.t1;
commit;


收集统计信息:
SQL> exec dbms_stats.gather_table_stats('TERRY','T1',cascade=>true);
BEGIN dbms_stats.gather_table_stats('TERRY','T1',cascade=>true); END;


*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 20337
ORA-06512: at "SYS.DBMS_STATS", line 20360
ORA-06512: at line 1




SQL> exec dbms_stats.gather_table_stats('TERRY','T2',cascade=>true);


PL/SQL procedure successfully completed.


SQL> exec dbms_stats.gather_table_stats('TERRY','T3',cascade=>true);
BEGIN dbms_stats.gather_table_stats('TERRY','T3',cascade=>true); END;


*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 20337
ORA-06512: at "SYS.DBMS_STATS", line 20360
ORA-06512: at line 1


锁定统计信息后,手工收集统计信息报错。




(6)导入数据时,在使用content=metadata_only的同时,使用exclude=TABLE_STATISTICS去掉表统计信息的导入


[oracle@ogg ~]$ impdp terry/terry remap_schema=scott:terry directory=ORA_HOME  dumpfile=testexp.dmp logfile=impdp_testexp.log remap_table=t3:t4 CONTENT=METADATA_ONLY EXCLUDE=TABLE_STATISTICS 


Import: Release 11.2.0.1.0 - Production on Sun Jan 10 20:33:56 2016


Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TERRY"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TERRY"."SYS_IMPORT_FULL_01":  terry/******** remap_schema=scott:terry directory=ORA_HOME dumpfile=testexp.dmp logfile=impdp_testexp.log remap_table=t3:t4 CONTENT=METADATA_ONLY EXCLUDE=TABLE_STATISTICS 
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "TERRY"."T1" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "TERRY"."T2" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Job "TERRY"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 20:34:01


[oracle@ogg ~]$ 
[oracle@ogg ~]$ 
[oracle@ogg ~]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 10 20:34:16 2016


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




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> set lines 200
select owner,table_name,num_rows,blocks,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss'),user_stats,stattype_locked 
from dba_tab_statistics
  3  where table_name in ('T1','T2','T3','T4');


OWNER                          TABLE_NAME                       NUM_ROWS     BLOCKS TO_CHAR(LAST_ANALYZ USE STATT
------------------------------ ------------------------------ ---------- ---------- ------------------- --- -----
SCOTT                          T3                                    999         41 2016-01-10 20:01:06 NO
SCOTT                          T2                                    999         41 2016-01-10 20:01:05 NO
SCOTT                          T1                                    999         41 2016-01-10 20:01:04 NO  ALL
TERRY                          T4                                                                       NO
TERRY                          T3                                    999         41 2016-01-10 20:01:06 NO  ALL
TERRY                          T2                                   2997        115 2016-01-10 20:32:18 NO
TERRY                          T1                                    999         41 2016-01-10 20:01:04 NO  ALL


7 rows selected.


从上面查询看到terry.t4的统计信息是空的。


(7)导入数据时指定查询条件,统计信息会被导入,但不再准确
删除terry.t3表,重新导入,导入时指定查询条件:
SQL> drop table terry.t3;


Table dropped.




[oracle@ogg ~]$ impdp terry/terry remap_schema=scott:terry directory=ORA_HOME  dumpfile=testexp.dmp logfile=impdp_testexp.log remap_table=t3:t3  QUERY=t3:\"WHERE rownum\< 100\"


Import: Release 11.2.0.1.0 - Production on Sun Jan 10 20:54:16 2016


Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TERRY"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TERRY"."SYS_IMPORT_FULL_01":  terry/******** remap_schema=scott:terry directory=ORA_HOME dumpfile=testexp.dmp logfile=impdp_testexp.log remap_table=t3:t3 QUERY=t3:"WHERE rownum< 100" 
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "TERRY"."T1" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "TERRY"."T2" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TERRY"."T3"                                269.0 KB      99 out of 999 rows  --根据此处的显示,确实导入了99条数据
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "TERRY"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 20:54:23


[oracle@ogg ~]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 10 20:54:29 2016


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




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> select count(1) from terry.t3;


  COUNT(1)
----------
        99
        
set lines 200
select owner,table_name,num_rows,blocks,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss'),user_stats,stattype_locked 
from dba_tab_statistics
  3  where table_name in ('T1','T2','T3','T4');


OWNER                          TABLE_NAME                       NUM_ROWS     BLOCKS TO_CHAR(LAST_ANALYZ USE STATT
------------------------------ ------------------------------ ---------- ---------- ------------------- --- -----
SCOTT                          T4                                      0          4 2015-11-06 22:01:03 NO
SCOTT                          T3                                    999         41 2016-01-10 20:01:06 NO
SCOTT                          T2                                    999         41 2016-01-10 20:01:05 NO
SCOTT                          T1                                    999         41 2016-01-10 20:01:04 NO  ALL
TERRY                          T4                                                                       NO
TERRY                          T3                                    999         41 2016-01-10 20:01:06 NO
TERRY                          T2                                   2997        115 2016-01-10 20:32:18 NO
TERRY                          T1                                    999         41 2016-01-10 20:01:04 NO  ALL


8 rows selected.


注意到上面显示结果中terry.t3统计信息仍然是999条。


(8)手工设置统计信息


SQL> exec dbms_stats.set_table_stats(ownname => 'TERRY',tabname => 'T4',numrows => 1000,numblks => 1000);


PL/SQL procedure successfully completed.


set lines 200
select owner,table_name,num_rows,blocks,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss'),user_stats,stattype_locked 
from dba_tab_statistics
where table_name in ('T1','T2','T3','T4');


OWNER                          TABLE_NAME                       NUM_ROWS     BLOCKS TO_CHAR(LAST_ANALYZ USE STATT
------------------------------ ------------------------------ ---------- ---------- ------------------- --- -----
SCOTT                          T4                                      0          4 2015-11-06 22:01:03 NO
SCOTT                          T3                                    999         41 2016-01-10 20:01:06 NO
SCOTT                          T2                                    999         41 2016-01-10 20:01:05 NO
SCOTT                          T1                                    999         41 2016-01-10 20:01:04 NO  ALL
TERRY                          T4                                   1000       1000 2016-01-10 21:30:01 YES
TERRY                          T3                                    999         41 2016-01-10 20:01:06 NO
TERRY                          T2                                   2997        115 2016-01-10 20:32:18 NO
TERRY                          T1                                    999         41 2016-01-10 20:01:04 NO  ALL


8 rows selected.


注意到terry.t4的user_stats字段变为YES,即手工指定了统计信息。


再次收集统计信息,该字段恢复:


SQL>  exec dbms_stats.gather_table_stats('TERRY','T4',cascade=>true);


PL/SQL procedure successfully completed.


SQL> set lines 200
SQL> select owner,table_name,num_rows,blocks,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss'),user_stats,stattype_locked 
  2  from dba_tab_statistics
  3  where table_name in ('T1','T2','T3','T4');


OWNER                          TABLE_NAME                       NUM_ROWS     BLOCKS TO_CHAR(LAST_ANALYZ USE STATT
------------------------------ ------------------------------ ---------- ---------- ------------------- --- -----
SCOTT                          T4                                      0          4 2015-11-06 22:01:03 NO
SCOTT                          T3                                    999         41 2016-01-10 20:01:06 NO
SCOTT                          T2                                    999         41 2016-01-10 20:01:05 NO
SCOTT                          T1                                    999         41 2016-01-10 20:01:04 NO  ALL
TERRY                          T4                                      0          0 2016-01-10 21:32:21 NO
TERRY                          T3                                    999         41 2016-01-10 20:01:06 NO
TERRY                          T2                                   2997        115 2016-01-10 20:32:18 NO
TERRY                          T1                                    999         41 2016-01-10 20:01:04 NO  ALL


8 rows selected.




(9)解锁统计信息
解锁的语句为
execute DBMS_STATS.UNLOCK_TABLE_STATS ('TERRY', 'T1');


还可以在收集统计信息时指定force选项,这样可以修改统计信息,但统计信息仍是锁定状态
exec dbms_stats.gather_table_stats('SCOTT','T1',cascade=>true,force=>true);


查询结果如下:
SQL> set lines 200
SQL> from dba_tab_statistics
select owner,table_name,num_rows,blocks,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss'),user_stats,stattype_locked 
from dba_tab_statistics
  3  where table_name in ('T1','T2','T3','T4');


OWNER                          TABLE_NAME                       NUM_ROWS     BLOCKS TO_CHAR(LAST_ANALYZ USE STATT
------------------------------ ------------------------------ ---------- ---------- ------------------- --- -----
SCOTT                          T4                                      0          4 2015-11-06 22:01:03 NO
SCOTT                          T3                                    999         41 2016-01-10 20:01:06 NO
SCOTT                          T2                                    999         41 2016-01-10 20:01:05 NO
SCOTT                          T1                                   1998         78 2016-01-10 21:36:44 NO  ALL
TERRY                          T4                                      0          0 2016-01-10 21:32:21 NO
TERRY                          T3                                    999         41 2016-01-10 20:01:06 NO
TERRY                          T2                                   2997        115 2016-01-10 20:32:18 NO
TERRY                          T1                                    999         41 2016-01-10 20:01:04 NO


8 rows selected.


综上,impdp导入时如果使用了content=metadata_only,则统计信息被自动锁定,可以通过参数exclude=table_statistics去掉统计信息,或者导入后手工解锁;
如果导入数据时指定了查询条件(其实只要导出和导入,统计信息就不再准确),应根据需要重新收集统计信息。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26451536/viewspace-1979722/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2011-11-23

  • 博文量
    148
  • 访问量
    391983