空閒表上的聯機ddl操作有風險嗎
一張表上幾乎沒有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;
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;
/
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
--session 2:sid=850
--session 3:sid=680
1,如果某個儲存過程引用該表,而該儲存過程執行時間較長或很頻繁,是否會對系統造成較大影響?
--session 1:
exec prc;
--沒返回
--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”
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
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”
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;
--沒返回
exec prc;
--沒返回
--session x:
select * from v$lock where sid in(816,850,680);
no rows selected
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結束。
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
---------- ----------------------------------- ---------------- -----------------------------
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
---------- ----------------------------------- ---------------- -----------------------------
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)
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
---------- ----------------------------------- ---------------- -----------------------------
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等待了。
==>不會出現重新編譯導致的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;
/
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”
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
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”
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;
--沒返回
exec prc;
--沒返回
--session x:
select * from v$lock where sid in(816,850,680);
no rows selected
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”
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
---------- ----------------------------------- ---------------- ----------------------------
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)
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
---------- ----------------------------------- ---------------- -----------------------------
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;
/
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”
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
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”
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;
--沒返回
exec prc;
--沒返回
--session x:
select * from v$lock where sid in(816,850,680);
no rows selected
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
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阻塞
==>會話3被會話1阻塞
--等待事件變化：
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/18922393/viewspace-756069/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 04 MySQL 表的基本操作-DDLMySql
- 分析表空間空閒率並收縮表空間
- Oracle 表空間 的操作Oracle
- 在物聯網這個舞臺上RFID還有機會嗎？
- logmnr挖掘中間有DDL的操作示例-對於執行DDL前的操作無法挖掘
- 查詢表空間已使用空間和空閒空間的簡單檢視
- 表空間常見的操作
- oracle表空間操作Oracle
- 表空間基本操作
- 使用代理伺服器有風險嗎？伺服器
- 刪使用者刪表空間的操作還能flashback回來嗎？
- 蘋果手機app試玩賺錢是真的嗎？有風險嗎？能賺多少錢？蘋果APP
- 使用聯機SQL執行表空間還原(一)SQL
- 榜單一月一換血，休閒、超休閒遊戲出海美國還有機會嗎？遊戲
- 表空間的建立修改等操作
- undo表空間中常用的操作
- 自簽名SSL證書有風險嗎？
- MySQL 執行 Online DDL 操作報錯空間不足？MySql
- DDL操作的自動提交
- 如何檢視Oracle資料庫表空間大小(空閒、已使用),是否要增加表空間的資料檔案...Oracle資料庫
- 傳輸表空間操作-OracleOracle
- oracle表空間日常操作管理Oracle
- Oracle表空間操作詳解Oracle
- Oracle表空間相關操作Oracle
- 限制DDL操作（四）
- 限制DDL操作（三）
- 限制DDL操作（二）
- 限制DDL操作（一）
- 【指令碼】快速排除空閒等待事件得到敏感的非空閒等待事件指令碼事件
- 達夢使用聯機SQL執行表空間還原(二)SQL
- 有關UNDO表空間的學習：
- 禁止使用者的DDL操作
- 閒聊oracle SQL*Net相關的空閒等待事件OracleSQL事件
- Oracle SQL 基本操作之 表空間OracleSQL
- 臨時表空間操作總結
- 【原創】表空間相關操作
- oracle 10g表空間操作Oracle 10g
- 獲取資料庫空閒空間的SQL資料庫SQL