cursor: pin S wait on X等待實驗二

我的ORACLE版本11.1.0.7

構造一個查詢，查詢的物件列表400個。可以根據all_tables去構造。主要目的是讓語句的解析時間足夠長。

select count(*) from a,b,c.....（400個） where 1=0;

在session1和session2分別執行上面的語句。觀察兩個session的等待事件：

等待事件的語句如下：

col event for a30
select event,total_waits,time_waited from
v$session_event
where sid=(select sid from v$mystat where rownum=1)
order by 3 desc;

session1的等待事件：

EVENT                          TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
SQL*Net message from client             25        7149
SQL*Net message to client               26           0

session2的等待事件：

EVENT                          TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
SQL*Net message from client             24       33047
cursor: pin S wait on X                810         792
SQL*Net message to client               25           0

可以看到session2的等待事件比session1只多了cursor: pin S wait on X等待。這個等待是由於硬解析導致的，session1硬解析期間需要獲得cursor的x鎖。session2執行同樣的SQL，需要獲得cursor的S鎖，兩種鎖不相容，導致出現cursor: pin S wait on X 。

再看看10G的情況。

構造一個查詢，查詢的物件列表400個。可以根據all_tables去構造。主要目的是讓語句的解析時間足夠長。

select count(*) from a,b,c.....（400個） where 1=0;

在session1和session2分別執行上面的語句。觀察兩個session的等待事件：

session1的等待事件：

EVENT                          TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
SQL*Net message from client             17        1509
db file sequential read                 53          47
SQL*Net more data from client            2           0
SQL*Net message to client               18           0

session2的等待事件：

EVENT                          TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
SQL*Net message from client             17        1490
library cache pin                        5        1254
SQL*Net more data from client            2           0
SQL*Net message to client               18           0

明顯看到了11G與10G的不同。10G 的等待事件出現的是library cache pin ，而不是cursor: pin S wait on X 。10G的時候，編譯SQL的時候，需要在handler上獲得S模式的library cache lock,然後獲得物件HEAP上的x模式的library cache pin，解析的時候，如果有同樣的SQL需要執行，需要獲得S模式的library cache lock,由於都是請求的S模式，這個是可以獲得。繼而需要獲得s模式的library cache pin，由於跟之前的x模式是互斥的，於是產生等待library cache pin。

11G以後，採用了mutex機制，對於cursor上的pin等待，用cursor: pin S wait on X 來代替了。對於mutex機制，還沒看到什麼比較詳盡的文件。

再看看錶修改（DDL）與select是否會產生cursor: pin S wait on X。

10G的情況：

session1執行如下DDL;

begin
   for idx in 1 .. 100000 loop
execute immediate 'alter table test enable all triggers';
end loop;
end;
/

session2執行如下查詢：

declare
v_value number;
begin
for idx in 1 .. 50000 loop
select 1 into v_value from test where rownum=1;
end loop;
end;
/

session1的等待：

EVENT                          TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
SQL*Net message from client             22       37319
log file sync                        12387        4344
library cache lock                   10051        1528
latch: library cache                   239           3

session2的等待：

EVENT                          TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
SQL*Net message from client             20        5077
library cache lock                    5902         351
db file scattered read                5390         301
db file sequential read                377          15
SQL*Net message to client               21           0
latch: shared pool                      16           0
latch: library cache                    57           0

我們都看到了library cache lock ，DDL會獲得handler上X模式的library cache lock  ，有查詢請求的時候，需要獲得s模式的library cache lock  ,兩者不能共享，因此出現library cache lock  。反過來也一樣。

11G又會如何：

session1的等待：

EVENT                          TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
SQL*Net message from client             20        5285
library cache load lock               9685         531
library cache lock                      10           5
latch: shared pool                      31           1
events in waitclass Other                2           0
library cache: mutex X                   9           0
SQL*Net message to client               21           0
log file sync                            1           0

session2 的等待：

EVENT                          TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
SQL*Net message from client             20        7131
library cache lock                    4968         477
library cache load lock                 94           9
events in waitclass Other                1           0
library cache: mutex X                  26           0
SQL*Net message to client               21           0
latch: row cache objects                 1           0
latch: shared pool                      17           0

也看到了library cache lock， 知道競爭都是handler上的。跟10G差不多。仔細觀察，還多了library cache: mutex X   事件，這個事件其實就是10G的latch: library cache   。還多了library cache load lock  ，不是很清楚做什麼用的。

看來11G，透過cursor: pin S wait on X 取代了10G之前的cursor上的library cache pin等待事件。透過library cache: mutex X 取代了10G之前的latch: library cache（沒詳細證明，有事件做個實驗證明下）。

還有需要說明的是，這種library cache lock 爭用是發生在table的headler上的，而不是發生在cursor的headler上。

select  kglnaobj from x$kglob where kglhdadr='0000000069CBB7E0';

KGLNAOBJ
------------------------------
TEST