空閒表上的聯機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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL DDL操作表MySql
- 獲取表空間DDL
- 04 MySQL 表的基本操作-DDLMySql
- 使用代理伺服器有風險嗎？伺服器
- 自簽名SSL證書有風險嗎？
- MySQL5.7 InnoDB線上DDL操作MySql
- MySQL 執行 Online DDL 操作報錯空間不足？MySql
- 蘋果手機app試玩賺錢是真的嗎？有風險嗎？能賺多少錢？蘋果APP
- 刪使用者刪表空間的操作還能flashback回來嗎？
- 使用聯機SQL執行表空間還原(一)SQL
- [20181026]12c增強索引線上DDL操作.txt索引
- 2021年全球風險展望：明確存在的危險，0-2年短期風險（附原資料表）
- MySQL的DDL和DML操作語法MySql
- 《2021網路空間測繪年報》解讀|物聯網資產與風險篇
- mysql DDL時鎖表的排查MySql
- 達夢使用聯機SQL執行表空間還原(二)SQL
- mysql 原生 線上DDL 的bug .MySql
- 投資有風險入市需謹慎
- 新研究揭示避孕藥與自殺風險的關聯
- 安聯保險：2021年度海洋運輸風險報告
- 12C grid CDB異機恢復+歸檔(DDL操作)
- 2021年全球風險展望：存在的威脅，5-10年長期風險（附原資料表）
- 榜單一月一換血，休閒、超休閒遊戲出海美國還有機會嗎？遊戲
- 資料庫操作語言DDL資料庫
- DDL、DML、DCL、DQL相關操作
- 機器學習之過擬合的風險機器學習
- 物聯網火爆背後隱藏的巨大安全風險
- 股市回暖網上炒股安全風險驟增
- python中的風險Python
- 等級保護和風險評估分別是什麼意思？有什麼聯絡？
- 6大知名休閒遊戲發行談如何低風險立項遊戲
- 使用WiFi真的有那麼危險嗎？WiFi
- 遊戲海外發行有哪些法律風險？遊戲
- 當使用者無限制使用表空間配額且表空間有足夠空間時出現超出表空間的空間限額
- PostgreSQL 建立主鍵自增表的 DDLSQL
- MySQL（十三）DDL之庫和表的管理MySql
- mysql 大表drop和truncate 技術風險點MySql
- Oracle切換undo表空間操作步驟Oracle
- 在“高收益”面前，“風險可控”的承諾淪為一句空話