统计信息锁定的一种场景

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 统计信息锁定的一种场景

统计信息锁定的一种场景

这篇文章介绍统计信息锁定,《》,当时提出猜测,就是原库用了lock锁定统计信息,因此需要解锁,但是有位朋友很认真,提出了另一种可能的场景,

统计信息锁定的一种场景

测试了下,果然如此,

  1. 创建测试表数据,

SQL create table a (id number); Table created.

SQL insert into a values(1); 1 row created.

SQL commit; Commit complete.

SQL select * from a;         ID ----------          1

  1. 导出数据,

$ exp system/oracle file=/home/ora11g/a.dmp schema=bisal

  1. 场景一:仅导入表结构,

$ imp system/oracle file=/home/ora11g/a.dmp fromuser=bisal touser=phibisal rows=n

Import: Release 11.2.0.4.0 - Production on Mon Feb 5 14:49:30 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V11.02.00 via conventional path import done in AL32UTF8 character set and AL16UTF16 NCHAR character set . importing BISAL's objects into PHIBISAL Import terminated successfully without warnings.

检索dba_tab_statistics,STATTYPE_LOCKED值为ALL,

SQL exec print_table('select owner, table_name, num_rows, blocks, sample_size, last_analyzed, stattype_locked from dba_tab_statistics where owner=''PHIBISAL'''); OWNER: PHIBISAL TABLE_NAME: A NUM_ROWS: BLOCKS: SAMPLE_SIZE: LAST_ANALYZED: STATTYPE_LOCKED: ALL ----------------- PL/SQL procedure successfully completed.

执行统计信息的收集,出现ORA-20005错误,

SQL dbms_stats.gather_table_stats('PHIBISAL','A',cascade=true); SP2-0734: unknown command beginning "dbms_stats..." - rest of line ignored. SQL exec dbms_stats.gather_table_stats('PHIBISAL','A',cascade=true); BEGIN dbms_stats.gather_table_stats('PHIBISAL','A',cascade=true); END; * 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 1

  1. 场景二:导入数据,

imp system/oracle file=/home/ora11g/a.dmp fromuser=bisal touser=phibisal

Import: Release 11.2.0.4.0 - Production on Mon Feb 5 14:52:02 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V11.02.00 via conventional path import done in AL32UTF8 character set and AL16UTF16 NCHAR character set . importing BISAL's objects into PHIBISAL . . importing table                            "A"          1 rows imported Import terminated successfully without warnings.

检索dba_tab_statistics,STATTYPE_LOCKED值为“”,

SQL exec print_table('select owner, table_name, num_rows, blocks, sample_size, last_analyzed, stattype_locked from dba_tab_statistics where owner=''PHIBISAL''');   OWNER: PHIBISAL TABLE_NAME: A NUM_ROWS: BLOCKS: SAMPLE_SIZE: LAST_ANALYZED: STATTYPE_LOCKED: -----------------

PL/SQL procedure successfully completed.

此时可以采集统计信息,

SQL exec dbms_stats.gather_table_stats('PHIBISAL','A',cascade=true); PL/SQL procedure successfully completed.

因此除了使用dbms_stats.lock_schema_stats/dbms_stats.lock_table_stats会所统计信息,执行imp外加rows=n只导入表结构,同样会锁定统计信息,需要执行解锁的操作。

谢谢captain朋友的提醒,对于imp导入,需要注意这些小问题,如果有其他问题,欢迎朋友们提出。

如果您觉得本文有帮助,欢迎关注转发:bisal的个人杂货铺,

统计信息锁定的一种场景 统计信息锁定的一种场景
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 统计信息锁定的一种场景


 上一篇
MySQL 主从同步架构中你不知道的“坑”(完结篇) MySQL 主从同步架构中你不知道的“坑”(完结篇)
前言导读 之前写出一篇文章也是关于这个主从同步架构的“坑”,具体链接如下 此篇算是接上一篇文章的后续介绍,介绍下在指定同步库的情况一些所遇到的“坑” 指定同步库情况 1、binlog_format= ROW模式‍ mysql use te
下一篇 
EDB是什么数据库? EDB是什么数据库?
最近某一个尘封多年的产品,需要重新启动,原来用的数据库就是EDB,可能有些朋友听起来,觉得有些新鲜,EDB数据库?可能国内听说的比较少,但说到PG即PostgreSQL,可能朋友们就比较熟悉了,作为一款开源数据库,PG的流行程度