一张表上几乎没有dml操作,在系统联机情况下ddl操作(增加varchar2列的长度)有风险吗?

初步分析,此DDL操作需要获取表上的独占锁,之需要修改数据字典,会很快完成,看起来没什么风险.
进一步分析,此DDL操作会导致存在依赖关系的存储过程状态无效，而重新编译存储过程需要获取library cache pin，如果存储过程运行时间较长或比较频繁，将导致大量会话被阻塞。
测试如下：

--测试环境准备：
create table test(x int,y varchar2(10));
insert into test values(1,'1');
insert into test values(2,'2');
commit;

set serveroutput on
create or replace procedure prc
as
l_cnt int;
begin
   select count(*) into l_cnt from test;
   dbms_lock.sleep(300);
   dbms_output.put_line('rows:'||l_cnt);
end;
/

--session 1:sid=816
--session 2:sid=850
--session 3:sid=680

1,如果某个存储过程引用该表,而该存储过程执行时间较长或很频繁,是否会对系统造成较大影响?
--session 1:
exec prc;
--没返回

--session x:
select sid,event,blocking_session,wait_class from v$session where sid in(816,850,680);
       SID EVENT                               BLOCKING_SESSION WAIT_CLASS
---------- ----------------------------------- ---------------- ------------------------------
       680 SQL*Net message from client                          Idle
       816 PL/SQL lock timer                                    Idle
       850 SQL*Net message from client                          Idle
==>会话1在运行，event：“PL/SQL lock timer”

--session 2:
SQL> set timing on
SQL> alter table test modify(y varchar2(50));

Table altered.

Elapsed: 00:00:00.04
==>很快返回

--session x:

select * from v$lock where sid in(816,850,680);
no rows selected

select sid,event,blocking_session,wait_class from v$session where sid in(816,850,680);
       SID EVENT                               BLOCKING_SESSION WAIT_CLASS
---------- ----------------------------------- ---------------- ----------------------------
       680 SQL*Net message from client                          Idle
       816 PL/SQL lock timer                                    Idle
       850 SQL*Net message from client                          Idle
==>会话1在运行，event：“PL/SQL lock timer”

--session 3:
exec prc;
--没返回

--session x:

select * from v$lock where sid in(816,850,680);
no rows selected

select sid,event,blocking_session,wait_class from v$session where sid in(816,850,680);
       SID EVENT                               BLOCKING_SESSION WAIT_CLASS
---------- ----------------------------------- ---------------- -----------------------------
       680 library cache pin                                816 Concurrency
       816 PL/SQL lock timer                                    Idle
       850 SQL*Net message from client                          Idle
==>会话1在运行，event：“PL/SQL lock timer”
==>会话3被会话1阻塞:由于会话2修改表结构，会话3运行存储过程时需重新编译存储过程，
   而会话1运行状态导致无法对存储过程重新编译，从而阻塞了会话3；等待时间会持续到会话1结束。

--等待事件变化：
1* select sid,event,blocking_session,wait_class from v$session where sid in(816,850,680)

       SID EVENT                               BLOCKING_SESSION WAIT_CLASS
---------- ----------------------------------- ---------------- -----------------------------
       680 library cache pin                                816 Concurrency
       816 PL/SQL lock timer                                    Idle
       850 SQL*Net message from client                          Idle

SQL> r
1* select sid,event,blocking_session,wait_class from v$session where sid in(816,850,680)

       SID EVENT                               BLOCKING_SESSION WAIT_CLASS
---------- ----------------------------------- ---------------- -----------------------------
       680 PL/SQL lock timer                                    Idle
       816 SQL*Net message from client                          Idle
       850 SQL*Net message from client                          Idle

SQL> r
1* select sid,event,blocking_session,wait_class from v$session where sid in(816,850,680)

       SID EVENT                               BLOCKING_SESSION WAIT_CLASS
---------- ----------------------------------- ---------------- -----------------------------
       680 SQL*Net message from client                          Idle
       816 SQL*Net message from client                          Idle
       850 SQL*Net message from client                          Idle

2,作为对比测试，如果存储过程不引用该表，情况相同吗?
==>不会出现重新编译导致的library cache pin等待了。

存储过程修改为：
set serveroutput on
create or replace procedure prc
as
l_cnt int;
begin
dbms_lock.sleep(300);
dbms_output.put_line('rows:'||l_cnt);
end;
/

--session 1:
exec prc;
--没返回

--session x:
select sid,event,blocking_session,wait_class from v$session where sid in(816,850,680);
       SID EVENT                               BLOCKING_SESSION WAIT_CLASS
---------- ----------------------------------- ---------------- --------------------------
       680 SQL*Net message from client                          Idle
       816 PL/SQL lock timer                                    Idle
       850 SQL*Net message from client                          Idle
==>会话1在运行，event：“PL/SQL lock timer”

--session 2:
SQL> set timing on
SQL> alter table test modify(y varchar2(80));

Table altered.

Elapsed: 00:00:00.04
==>很快返回

--session x:

select * from v$lock where sid in(816,850,680);
no rows selected

select sid,event,blocking_session,wait_class from v$session where sid in(816,850,680);
      SID EVENT                               BLOCKING_SESSION WAIT_CLASS
---------- ----------------------------------- ---------------- ---------------------------
       680 SQL*Net message from client                          Idle
       816 PL/SQL lock timer                                    Idle
       850 SQL*Net message from client                          Idle
==>会话1在运行，event：“PL/SQL lock timer”

--session 3:
exec prc;
--没返回

--session x:

select * from v$lock where sid in(816,850,680);
no rows selected

select sid,event,blocking_session,wait_class from v$session where sid in(816,850,680);
      SID EVENT                               BLOCKING_SESSION WAIT_CLASS
---------- ----------------------------------- ---------------- -----------------------------
       680 PL/SQL lock timer                                    Idle
       816 PL/SQL lock timer                                    Idle
       850 SQL*Net message from client                          Idle
==>会话1在运行，event：“PL/SQL lock timer”
==>会话3在运行，event：“PL/SQL lock timer”

--等待事件变化：
15:43:58 SQL> r
1* select sid,event,blocking_session,wait_class from v$session where sid in(816,850,680)

       SID EVENT                               BLOCKING_SESSION WAIT_CLASS
---------- ----------------------------------- ---------------- ----------------------------
       680 PL/SQL lock timer                                    Idle
       816 PL/SQL lock timer                                    Idle
       850 SQL*Net message from client                          Idle

15:46:12 SQL> r
1* select sid,event,blocking_session,wait_class from v$session where sid in(816,850,680)

       SID EVENT                               BLOCKING_SESSION WAIT_CLASS
---------- ----------------------------------- ---------------- -----------------------------
       680 SQL*Net message from client                          Idle
       816 SQL*Net message from client                          Idle
       850 SQL*Net message from client                          Idle

3,作为对比测试，如果DDL操作变为表上增加字段，情况相同吗?
==>

存储过程恢复为：
set serveroutput on
create or replace procedure prc
as
l_cnt int;
begin
   select count(*) into l_cnt from test;
   dbms_lock.sleep(300);
   dbms_output.put_line('rows:'||l_cnt);
end;
/

--session 1:
exec prc;
--没返回

--session x:
select sid,event,blocking_session,wait_class from v$session where sid in(816,850,680);
       SID EVENT                               BLOCKING_SESSION WAIT_CLASS
---------- ----------------------------------- ---------------- -------------------------
       680 SQL*Net message from client                          Idle
       816 PL/SQL lock timer                                    Idle
       850 SQL*Net message from client                          Idle
==>会话1在运行，event：“PL/SQL lock timer”

--session 2:
SQL> set timing on
SQL> alter table test add (z varchar2(80));

Table altered.

Elapsed: 00:00:00.04
==>很快返回

--session x:

select * from v$lock where sid in(816,850,680);
no rows selected

select sid,event,blocking_session,wait_class from v$session where sid in(816,850,680);
       SID EVENT                               BLOCKING_SESSION WAIT_CLASS
---------- ----------------------------------- ---------------- -----------------------
       680 SQL*Net message from client                          Idle
       816 PL/SQL lock timer                                    Idle
       850 SQL*Net message from client                          Idle
==>会话1在运行，event：“PL/SQL lock timer”

--session 3:
exec prc;
--没返回

--session x:

select * from v$lock where sid in(816,850,680);
no rows selected

select sid,event,blocking_session,wait_class from v$session where sid in(816,850,680);
       SID EVENT                               BLOCKING_SESSION WAIT_CLASS
---------- ----------------------------------- ---------------- ---------------------------
       680 library cache pin                                816 Concurrency
       816 PL/SQL lock timer                                    Idle
       850 SQL*Net message from client                          Idle

==>会话1在运行，event：“PL/SQL lock timer”
==>会话3被会话1阻塞

--等待事件变化：

ITPub博客

空闲表上的联机ddl操作有风险吗

最新文章