oracle ddl操作很慢,Oracle11g新特性：在线操作功能增强-可等待DDL操作

最新推荐文章于 2024-05-16 21:47:14 发布

weixin_39908462

最新推荐文章于 2024-05-16 21:47:14 发布

阅读量493

点赞数

文章标签： oracle ddl操作很慢

Oracle 11g 引入了在线DDL处理功能，允许在数据操作期间修改表结构等，通过DDL_LOCK_TIMEOUT参数设置等待时间来避免资源冲突。当该参数为0时，DDL操作会立即报错；设置为非0值后，系统会在指定时间等待锁释放。如果在等待时间内会话提交，DDL操作则能继续，否则超时后报错。需要注意的是，过大的等待时间可能导致其他DML操作被长时间锁定。

摘要由CSDN通过智能技术生成

11g中在线处理功能得到了很大增强，其中包括在线修改表结构，在线创建或重建索引，建立不可见索引，表增加非空字段，在线DDL以及对象依赖性细化等。

在11g以前，DDL操作都要获取对象的排他锁，如果当时无法获取，则会马上报错：

SQL> conn yangtk/yangtk@ytk102已连接。

SQL> create table t (id number);

表已创建。

SQL> insert into t values (1);

已创建 1 行。

在当前会话不提交，保持对t表的锁。然后在另外的session登陆，尝试对t表进行ddl操作：

SQL

conn yangtk

yangtk

@ytk102已连接

。 SQL

set

sqlp

SQL2>

SQL2

truncate

table

truncate

table

第

行出现错误: ORA

00054

: 资源正忙, 但指定以 NOWAIT 方式获取资源 SQL2

drop

table

drop

table

第

行出现错误: ORA

00054

: 资源正忙, 但指定以 NOWAIT 方式获取资源 SQL2

alter

table

add

name

varchar2

(

);

alter

table

add

name

varchar2

(

)

第

行出现错误: ORA

00054

: 资源正忙, 但指定以 NOWAIT 方式获取资源 SQL2

select

from

v$version; BANNER

--------------------------------------------------------------

Oracle

Database

10g Enterprise Edition Release

10.2

0.1

Prod PL

SQL Release

10.2

0.1

Production CORE

10.2

0.1

Production TNS

for

bit

Windows: Version

10.2

0.1

Production NLSRTL Version

10.2

0.1

Production

这就是11g以前的DDL处理方式，在11g中，用户可以为DDL设置一个等待时间，这样DDL在获取不到排他锁时，会根据设置等待相应的时间，如果到时仍然获取不到锁，那么才会报错。登陆11g进行同样的处理： SQL> conn yangtk/yangtk@ora11g已连接。 SQL> create table t (id number); 表已创建。 SQL> insert into t values (1); 已创建 1 行。登陆第二个会话，发出DDL操作：SQL2> conn yangtk/yangtk@ora11g已连接。 SQL2> set timing on SQL2> truncate table t; truncate table t *第 1 行出现错误: ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效已用时间: 00: 00: 00.07 DDL仍然马上报错，这是由于控制DDL等待时间的初始化参数DDL_LOCK_TIMEOUT默认值是0，下面修改这个默认值： SQL2> alter session set ddl_lock_timeout = 5; 会话已更改。已用时间: 00: 00: 00.06 SQL2> truncate table t; truncate table t *第 1 行出现错误: ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效已用时间: 00: 00: 05.07 如果在等待时间内第一个会话进行提交，那么DDL就可以获取到排他锁，从而开始操作。 SQL2> alter session set ddl_lock_timeout = 60; 会话已更改。已用时间: 00: 00: 00.04 SQL2> truncate table t; 返回会话1进行提交： SQL> commit; 提交完成。会话2在会话1提交后，得到T表的锁，开始TRUNCATE操作：表被截断。已用时间: 00: 00: 13.03 SQL2> select * from v$version; BANNER -------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production PL/SQL Release 11.1.0.6.0 - Production CORE 11.1.0.6.0 Production TNS for Linux: Version 11.1.0.6.0 - Production NLSRTL Version 11.1.0.6.0 - Production 已用时间: 00: 00: 00.21 注意，将DDL_LOCK_TIMEOUT参数设置过大也可能造成潜在的问题，虽然DDL还没有获取到排他锁，不能进行操作，但是它已经在T上增加了锁信息，这会导致后续的DML都会被锁住，直到DDL等待超时或锁住DDL的会话提交或回滚使得DDL顺利完成。这对于繁忙的系统可能造成很严重的问题，设置之前需要谨慎考虑。