957
技術社區[雲棲]
深入內核：Oracle數據庫裏SELECT操作Hang解析
崔華，網名 dbsnake
Oracle ACE Director，ACOUG 核心專家
編輯手記：感謝崔華授權我們獨家轉載其精品文章，也歡迎大家向“Oracle”社區投稿。
我們都知道在 Oracle 數據庫裏是“讀不阻塞寫，寫不阻塞讀”，那麼是否可以認為在正常情況下，select 操作是怎樣都能執行，始終不會被 hang 住的呢？注意這裏提到的是正常情況下，不包括那些由於 latch 被 hold 住、或者 bug 等相關異常導致的 select 操作 hang 住的情況。
答案是：不可以這樣認為的。
我們來舉一個反例。
首先我們來分析一下在 sql 硬解析時在相關表對象上 library cache lock 的持有情況。這裏我用到了10049事件，用10049事件，最重要的就是要知道如何設置它所對應的 level 值。
10049的level值可能會有如下一些組合：
這裏因為我要跟蹤 sql 硬解析時相關表對象的 library cache lock 的持有情況，所以這裏level 值取0x0210=0x0200|0x0010，即這裏 level 值取528。
SQL> select to_number(‘210′,’XXXX’)
from dual;
先在11.2.0.1裏使用一下10049事件：
C:\Documents and Settings\cuihua>sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 6月 27 21:39:37 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> conn / as sysdba;
已連接。
SQL> oradebug setmypid
已處理的語句
SQL> oradebug event 10049 trace
name context forever,level 528
已處理的語句
SQL> select count(*) from scott.emp;
COUNT(*)
———-
14
SQL> oradebug tracefile_name
c:\app\cuihua\diag\rdbms\cuihua112\
cuihua112\trace\cuihua112_ora_2292.trc
這個TRACE文件沒有任何內容，看起來似乎是10049事件對11gR2無效或者 Oracle 改變了10049事件在11gR2中的 level 的定義（這個我不確定）。
我們換一個10gR2的版本：
SQL> select * from v$version;
SQL> oradebug setmypid
已處理的語句
SQL> oradebug event 10049 trace
name context forever,level 528
已處理的語句
SQL> select count(*) from scott.emp;
COUNT(*)
———-
13
SQL> oradebug tracefile_name
d:\oracle\admin\cuihua\udump\cuihua_ora_5012.trc
從上述 trace 文件（d:\oracle\admin\cuihua\udump\cuihua_ora_5012.trc）中從前到後可以看到如下內容：
即針對上述 cursor 是以 NULL 模式持有 library cache lock，
針對表 scott.emp 是以 share 模式持有 library cache lock。
也就是說，隻要我事先以 exclusive 模式在表 scott.emp上持有 library cache lock，那麼後續的以硬解析方式執行的針對該表的所有sql（包括 select 語句）都將被 hang 住。
現在我們來測一下對一個表增加一個主鍵時的 library cache lock 的持有情況。
SQL> create table t2 as select * from emp;
Table created
SQL> select count(*) from t2;
COUNT(*)
———-
13
SQL> conn / as sysdba;
已連接。
SQL> oradebug setmypid
已處理的語句
SQL> oradebug event 10049 trace name
context forever,level 528
已處理的語句
SQL> alter table scott.t2 add constraint PK_T2
primary key (EMPNO);
表已更改。
SQL> oradebug tracefile_name
d:\oracle\admin\cuihua\udump\cuihua_ora_6120.trc
從這個trace文件（d:\oracle\admin\cuihua\udump\cuihua_ora_6120.trc）中我們可以看出對表t2的 library cache lock 的先後持有模式為:
即大部分時間 library cache lock 的持有模式都是N，隻有在一頭一尾的時候才是X。
但請注意這種情況下 select 操作是會被hang住的。
因為一開頭的X是 kglget，結尾才 kgllkdl（kgllkdl大致是 kgl lock delete 的意思，表示釋放相應的 library cache lock），並且它們的 KGL Lock addr 相同：
這也就意味著在添加主鍵的整個過程中，Oracle始終會以 exclusive 模式在表 scott.t2 上持有 library cache lock，直到最後主鍵添加完畢了才釋放。
所以在 win32上的10.2.0.1中，在添加主鍵的過程中會一直阻塞查詢（select）操作。
我們來測一下，同時開3個session。
Session 1:
SQL> create table t3(id number);
Table created
SQL> declare
2 i number;
3 begin
4 for i in 1..3000000 loop
5 insert into t3 values (i);
6 end loop i;
7 commit;
8 end;
9 /
PL/SQL procedure successfully completed
Session 2:
SQL> select * from v$mystat
where rownum<2;
在 session 1中開始執行添加主鍵操作：
Session 1:
SQL> alter table scott.t3 add constraint PK_T3 primary key (id);
……開始執行
轉到 session 2執行查詢操作：
Session 2：
SQL> select * from t3
where rownum<10;
……這裏 hang 住了
轉到 session 3並執行對 session2的等待事件的查詢：
Session 3：
SQL> select t.event,t.state,t.seconds_in_wait
from v$session t
where sid=138;
從中可以看到 session 2在等待 library cache lock，同時它的STATE為waiting，SECONDS_IN_WAIT的值在遞增。
這就驗證了我們的結論：在 win32上的10.2.0.1中，在對表增加主鍵的過程中會一直阻塞對這個表的查詢（select）操作。
現在我們再問一個問題：是不是所有對表的DDL操作，在DDL操作的執行過程中都會阻塞對這個表的select操作？
答案是：不是這樣的。
我們來舉一個反例。
現在我們來測一下對表 drop一個column 時 library cache lock 的持有情況：
SQL> desc t1;
SQL> select count(*) from t1;
同時開兩個session。
在session 1中打開10049事件後drop表t1的列object_type:
Session 1：
SQL> conn / as sysdba;
已連接。
SQL> oradebug setmypid
已處理的語句
SQL> oradebug event 10049 trace
name context forever,level 528
已處理的語句
SQL> alter table scott.t1 drop
column OBJECT_TYPE;
表已更改。
SQL> oradebug tracefile_name
d:\oracle\admin\cuihua\udump\
cuihua_ora_5020.trc
session 2在 session 1執行 drop column 操作的同時查詢表t1，結果是 select 操作並沒有被 hang 住，且能看到正在被 drop 的列 object_type:
Session 2：
SQL> select owner,object_name,object_type
from t1
where rownum<10;
從 session 1所產生的 trace 文件
（d:\oracle\admin\cuihua\udump\cuihua_ora_5020.trc）中我們可以看出對表t1的 library cache lock 的先後持有模式為：
即大部分時間對表 scott.t1 的 library cache lock 的持有模式都是S，最後才是X，所以這就可以解釋為什麼在對表 scott.t1 執行 drop column 操作的時候對它的select語句能夠同時執行。
從 trace 文件來看，drop column 並不是不會阻塞 select 操作，隻是阻塞的時間點要恰好是Oracle以X模式持有library cache lock時。
最後我們來測一下對一個表增加一個 unique constraint時library cache lock的持有情況
SQL> conn / as sysdba;
已連接。
SQL> oradebug setmypid
已處理的語句
SQL> oradebug event 10049 trace
name context forever,
level 528
已處理的語句
SQL> alter table scott.t2 add constraint UK_T2_EMPNO
unique (EMPNO, ENAME);
表已更改。
SQL> oradebug tracefile_name
d:\oracle\admin\cuihua\udump\cuihua_ora_5240.trc
從這個trace文件中我們可以看出對表 scott.t2 的 library cache lock 的先後持有模式為:
即大部分時間都是N，一頭一尾才是X，這個和添加主鍵操作一樣，在此不再贅述。
結論：不要隨便在生產環境對大表執行DDL操作（如添加唯一性約束等），可能會導致針對這個表的所有 sql（包括select操作）在執行DDL操作的時間段都 hang 住。
本文出自數據和雲公眾號，原文鏈接
最後更新：2017-07-17 18:04:14