[20190328]簡單探究sql語句相關mutexes.txt

--//摘要:

雖然Mutex中文翻譯為互斥鎖，但為了和OS mutex充分的區別，所以我們在本文裡稱Oracle Mutex為Mutex。

Oracle中的mutex，類似於Latch，是一種低階的序列機制，用以控制對SGA中部分共享資料結構的訪問控制。

Oracle中的序列機制有不少，引入它們的目的是避免一個物件出現下述現象：

當某些程式在訪問該物件時，該資源被重新分配

當某些程式在修改它時，被其他程式讀取

當某些程式在修改它時，被其他程式修改

當某些程式在讀取它時，被其他程式修改

不同於Latch，Mutex的使用更靈活，用途更多，例如:

哪些需要被mutex保護的共享資料結構可以有自己獨立的mutex，即一個物件擁有自己獨立的mutex，不像Latch往往一個需要保護大量

物件，舉例來說，每一個父遊標有其對應的mutex，而每一個子遊標也有其對應的mutex

每一個資料結構可能有一個或多個mutex保護，每一個mutex負責保護其結構的不同部分

當然一個mutex也可以用來保護多於一個的資料結構

理論上mutex即可以存放在其保護的結構本身中(其實是嵌入在結構裡)，也可以存放在其他地方。一般情況下Mutex是在資料結構需要被

保護時動態建立出來的。如是嵌在需要保護結構體內的mutex，則當所依附的資料結構被清理時該mutex也將被摧毀。

Mutex帶來的好處

雖然mutex和latch都是Oracle中的序列機制，但是mutex具有一些latch沒有的好處

更輕量級且更快

Mutex作為Latch的替代品，具有更快速獲得，更小等優勢。獲取一個mutex進需要大約30~35個指令，而Latch則需要150~200個指令。一

個mutex結構的大小大約為16 bytes，而在10.2版本中一個latch需要112個bytes，在更早的版本中是200個bytes。從200個bytes 精簡到

112個是透過減少不必要的統計指標 SLEEP1~SLEEP11、WAITERS_WOKEN, WAITS_HOLDING_LATCH等從而實現的。今後我們將看到更多關於

Latch的程式碼最佳化。

減少偽爭用

典型情況下一個Latch保護多個物件。當一個Latch保護多個熱物件時，並行地對這些物件的頻繁訪問讓latch本身變成效能的序列點。

這也就是我們此處說的偽爭用點，因為爭用是發生在這個序列保護的機制上，而不是程式去訪問的物件本身。與latch不同，使用mutex

的情況下Oracle開發人員可以為每一個要保護的資料結構建立一個獨立的mutex。這意味著Latch的那種偽爭用將大大減少，因為每一個

物件均被自己獨立擁有的mutex保護

--//我想透過測試說明問題.

1.環境:

--//session 1:

SCOTT@book> @ ver1

PORT_STRING VERSION BANNER

------------------- -------------- --------------------------------------------------------------------------------

x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

$ cat m2.txt

set verify off

host sleep $(echo &&3/50| bc -l )

insert into job_times values ( sys_context ('userenv', 'sid') ,dbms_utility.get_time ,'&&2') ;

commit ;

declare

v_id number;

v_d date;

begin

for i in 1 .. &&1 loop

--select 1 into v_id from dual ;

--select sysdate into v_d from dual ;

select deptno into v_id from dept where deptno=10;

end loop;

end ;

/

update job_times set time_ela = dbms_utility.get_time - time_ela where sid=sys_context ('userenv', 'sid') and method='&&2';

commit;

quit

$ seq 150 | xargs -I {} -P 150 bash -c "sqlplus -s -l scott/book @m2.txt 1e6 C2_150 {} >/dev/null"

--//執行以上命令後,由於大量的sql執行語句相同,出現cursor: pin S.可以執行多次,避免語句從共享池刷出.

--//注意oracle圍繞mutex的檢視很少,僅僅V$MUTEX_SLEEP,V$MUTEX_SLEEP_HISTORY,指令少結構體小,這樣記錄的診斷資訊少.

2.首先看看mutex在哪裡?

--//首先確定mutex_addr在那裡.

--//sesson 1:

SCOTT@book> column LOCATION format a40

SCOTT@book> select * from V$MUTEX_SLEEP order by 3 ;

MUTEX_TYPE LOCATION SLEEPS WAIT_TIME

-------------------- -------------------------- -------- ----------

Library Cache kglget1 1 1 0

Library Cache kglini1 32 1 0

Library Cache kgldtin1 42 1 0

Library Cache kglati1 45 2 0

Library Cache kglnti1 46 3 0

Library Cache kglllal1 109 6 0

Library Cache kgllldl2 112 7 0

Library Cache kglllal3 111 10 0

Library Cache kglpnal1 90 17 0

Library Cache kgllkdl1 85 18 0

Library Cache kglpndl1 95 27 0

Library Cache kglget2 2 28 0

Library Cache kgllkc1 57 28 0

Library Cache kglpin1 4 29 0

Cursor Pin kkslce [KKSCHLPIN2] 130 0

Library Cache kglhdgn2 106 475 0

Cursor Pin kksfbc [KKSCHLFSP2] 649446 0

Cursor Pin kksLockDelete [KKSCHLPIN6] 1109496 0

18 rows selected.

--//僅僅知道MUTEX_TYPE=Cursor Pin ,LOCATION=kksLockDelete [KKSCHLPIN6],kksfbc [KKSCHLFSP2]的sleep很高,具體在那裡,那個語

--//句引起的問題,明顯診斷資訊不足,

--//而僅僅mutex出現阻塞sleep後,oracle才會在檢視V$MUTEX_SLEEP_HISTORY留下資訊,不然很難定位.

--//session 2:

SYS@book> @ mutexy 3

old 21: ORDER BY sum_sleeps DESC ) where rownum<= &1

new 21: ORDER BY sum_sleeps DESC ) where rownum<= 3

HASH SUM_SLEEPS SUM_GETS LOCATION MUTEX_TYPE MUTEX_ADDR SQLID KGLNAOWN C100

---------- ---------- ---------- ------------------------------ -------------------- ---------------- ------------- -------- ---------------------------------------

1692266099 770212 1.8476E+10 kksLockDelete [KKSCHLPIN6] Cursor Pin 000000007DFACAB8 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 408706 9792073012 kksfbc [KKSCHLFSP2] Cursor Pin 000000007DFACAB8 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

991821498 115 16076 kglhdgn2 106 Library Cache 000000007C638348 RECO.ORACLE.COM

--//MUTEX_ADDR = 000000007DFACAB8

SYS@book> @ fcha 000000007DFACAB8

Find in which heap (UGA, PGA or Shared Pool) the memory address 000000007DFACAB8 resides...

WARNING!!! This script will query X$KSMSP, which will cause heavy shared pool latch contention

in systems under load and with large shared pool. This may even completely hang

your instance until the query has finished! You probably do not want to run this in production!

Press ENTER to continue, CTRL+C to cancel...

old 14: to_number(substr('&1', instr(lower('&1'), 'x')+1) ,'XXXXXXXXXXXXXXXX')

new 14: to_number(substr('000000007DFACAB8', instr(lower('000000007DFACAB8'), 'x')+1) ,'XXXXXXXXXXXXXXXX')

old 32: to_number(substr('&1', instr(lower('&1'), 'x')+1) ,'XXXXXXXXXXXXXXXX')

new 32: to_number(substr('000000007DFACAB8', instr(lower('000000007DFACAB8'), 'x')+1) ,'XXXXXXXXXXXXXXXX')

old 50: to_number(substr('&1', instr(lower('&1'), 'x')+1) ,'XXXXXXXXXXXXXXXX')

new 50: to_number(substr('000000007DFACAB8', instr(lower('000000007DFACAB8'), 'x')+1) ,'XXXXXXXXXXXXXXXX')

LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR

--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------

SGA 000000007DFAC3F0 1 1 KGLH0^64ddee73 4096 recr 4095 000000007E0AE4B0

--//KSMCHCOM=KGLH0^64ddee73 ,KSMCHCOM有資訊KGLH0,可以猜測在sql語句的某個父或者子游標的堆0中.

SYS@book> @ sharepool/shp4 a31kd5tkdvvmm 0

old 20: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2

new 20: WHERE kglobt03 = 'a31kd5tkdvvmm' or kglhdpar='a31kd5tkdvvmm' or kglhdadr='a31kd5tkdvvmm' or KGLNAHSH= 0

TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09

-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------

子游標控制程式碼地址 000000007E1B07D8 000000007E3B7830 SELECT DEPTNO FROM DEPT WHERE DEPTNO=10 1 2 0 000000007E23F080 000000007DFACB60 4528 8088 3072 15688 15688 1692266099 a31kd5tkdvvmm 0

父遊標控制程式碼地址 000000007E3B7830 000000007E3B7830 SELECT DEPTNO FROM DEPT WHERE DEPTNO=10 1 0 0 000000007E0AE4B0 00 4720 0 0 4720 4720 1692266099 a31kd5tkdvvmm 65535

--//與父遊標控制程式碼地址的KGLOBHD0='000000007E0AE4B0',注意這個地址是堆描述符地址.也可以理解為指向堆0的指標,

--//也就是這個mutex結構體在父遊標的堆0裡面.

SYS@book> SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000000007E0AE4B0', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR,'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1;

old 1: SELECT * FROM x$ksmsp WHERE TO_NUMBER ('&&1', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ

new 1: SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000000007E0AE4B0', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ

ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR

---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------

00007F24581306C8 3192 1 1 1 KGLDA 000000007E0AE448 240 freeabl 0 00

3.人為加鎖看看:

--//session 2:

SYS@book> @ pt 'select * from V$MUTEX_SLEEP_HISTORY where MUTEX_IDENTIFIER=1692266099 and rownum=1'

old 10: passing xmltype(cursor( &1 ))

new 10: passing xmltype(cursor( select * from V$MUTEX_SLEEP_HISTORY where MUTEX_IDENTIFIER=1692266099 and rownum=1 ))

ROW_NUM COL_NAME COL_VALUE

------- ------------------ --------------------------

1 MUTEX_IDENTIFIER 1692266099

SLEEP_TIMESTAMP 2019-03-28 15:36:51.649428

MUTEX_TYPE Cursor Pin

GETS 599013225

SLEEPS 25361

REQUESTING_SESSION 7

BLOCKING_SESSION 35

LOCATION kksLockDelete [KKSCHLPIN6]

MUTEX_VALUE 000000230000001E

P1 1

P1RAW 00

P2 0

P3 0

P4 0

14 rows selected.

--//MUTEX_VALUE=000000230000001E.0x00000023=35,表示阻塞的SESSION的sid.

--//session 1:

SCOTT@book> @ spid

SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50

---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------

295 7 11295 DEDICATED 11296 21 4 alter system kill session '295,7' immediate;

--//sid=295 , 295=0x127.

SYS@book> oradebug setmypid

Statement processed.

SYS@book> oradebug poke 0x000000007DFACAB8 8 0x0000012700000127

BEFORE: [07DFACAB8, 07DFACAC0) = 00000000 00000000

AFTER: [07DFACAB8, 07DFACAC0) = 00000127 00000127

SCOTT@book> @ m2.txt 1 c1 0

1 row created.

Commit complete.

declare

*

ERROR at line 1:

ORA-04024: self-deadlock detected while trying to mutex pin cursor 0x07E1B07D8

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ORA-06512: at line 8

2 rows updated.

Commit complete.

--//注意看下劃線,這是自鎖.oracle設計夠嚴謹的,什麼情況下會出現ORA-04024錯誤.

--//這裡的0x07E1B07D8是子游標控制程式碼地址.

--//session 2:

SYS@book> @ spid

SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50

---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------

1 31 11248 DEDICATED 11249 24 9 alter system kill session '1,31' immediate;

--//sid=1.

SYS@book> oradebug poke 0x000000007DFACAB8 8 0x0000000100000127

BEFORE: [07DFACAB8, 07DFACAC0) = 00000127 00000127

AFTER: [07DFACAB8, 07DFACAC0) = 00000127 00000001

--//注意一個細節,intel cpu系列的大小頭問題. 4個4個位元組顛倒的.

SYS@book> oradebug peek 0x000000007DFACAB8 8

[07DFACAB8, 07DFACAC0) = 00000127 00000001

--//session 1:

SCOTT@book> @ m2.txt 1 c1 0

1 row created.

Commit complete.

--//掛起!!

--//說明:一般測試環境沒有使用者登入的情況下,下次登入sid不會變化.

--//session 2:

SYS@book> @ wait

P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATUS STATE WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS

---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ------------- -------- ------- --------------- --------------- -----------

0000000064DDEE73 0000000100000127 0000000500000000 1692266099 4294967591 2.1475E+10 295 13 30 cursor: pin S ACTIVE WAITING 50465005 50 Concurrency

--//注意P2RAW.

SYS@book> oradebug poke 0x000000007DFACAB8 8 0x000000000000000

BEFORE: [07DFACAB8, 07DFACAC0) = 00000127 00000001

AFTER: [07DFACAB8, 07DFACAC0) = 00000000 00000000

--//session 1:

--//執行完成退出.

4.看看一些細節:

--//session 1:

SCOTT@book> @ spid

SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50

---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------

295 17 11477 DEDICATED 11478 21 9 alter system kill session '295,17' immediate;

--//SPID=1147811358

--//session 2:

SYS@book> oradebug poke 0x000000007DFACAB8 8 0x0000000100000127

BEFORE: [07DFACAB8, 07DFACAC0) = 00000000 00000000

AFTER: [07DFACAB8, 07DFACAC0) = 00000127 00000001

--//開啟新的終端執行如下命令:

$ strace -fTtt -p 11478 2>&1 | tee /tmp/m.txt

--//session 1:

SCOTT@book> @ m2.txt 1 c1 0

1 row created.

Commit complete.

--//再次掛起.等1分鐘以上.

--//session 2:

SYS@book> oradebug poke 0x000000007DFACAB8 8 0x000000000000000

BEFORE: [07DFACAB8, 07DFACAC0) = 00000127 00000001

AFTER: [07DFACAB8, 07DFACAC0) = 00000000 00000000

--//分析/tmp/m.txt.

$ awk '{print $2}' /tmp/m.txt | uniq -c | egrep "semtimedop| getrusag"

...

1 getrusage(RUSAGE_SELF,

2 getrusage(RUSAGE_SELF,

7 getrusage(RUSAGE_SELF,

1 getrusage(RUSAGE_SELF,

117 semtimedop(309166080,

2 getrusage(RUSAGE_SELF,

181 semtimedop(309166080,

2 getrusage(RUSAGE_SELF,

182 semtimedop(309166080,

2 getrusage(RUSAGE_SELF,

182 semtimedop(309166080,

2 getrusage(RUSAGE_SELF,

....

182 semtimedop(309166080,

2 getrusage(RUSAGE_SELF,

167 semtimedop(309166080,

1 getrusage(RUSAGE_SELF,

4 getrusage(RUSAGE_SELF,

6 getrusage(RUSAGE_SELF,

2 getrusage(RUSAGE_SELF,

1 getrusage(RUSAGE_SELF,

2 getrusage(RUSAGE_SELF,

1 getrusage(RUSAGE_SELF,

2 getrusage(RUSAGE_SELF,

3 getrusage(RUSAGE_SELF,

1 getrusage(RUSAGE_SELF,

2 getrusage(RUSAGE_SELF,

--//大約呼叫semtimedop 182次後(大約2秒),呼叫2次getrusage.擷取其中1段.

16:04:56.410571 getrusage(RUSAGE_SELF, {ru_utime={0, 33994}, ru_stime={0, 33994}, ...}) = 0 <0.000020>

16:04:56.410676 getrusage(RUSAGE_SELF, {ru_utime={0, 33994}, ru_stime={0, 33994}, ...}) = 0 <0.000017>

16:04:56.410785 semtimedop(309166080, 0x7fff83068fd0, 1, {0, 10000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.010684>

16:04:56.421565 semtimedop(309166080, 0x7fff83068fd0, 1, {0, 10000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.010886>

16:04:56.432559 semtimedop(309166080, 0x7fff83068fd0, 1, {0, 10000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.010894>

...

16:04:58.368564 semtimedop(309166080, 0x7fff83068fd0, 1, {0, 10000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.010889>

16:04:58.379561 semtimedop(309166080, 0x7fff83068fd0, 1, {0, 10000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.010888>

16:04:58.390557 semtimedop(309166080, 0x7fff83068fd0, 1, {0, 10000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.010905>

16:04:58.401570 semtimedop(309166080, 0x7fff83068fd0, 1, {0, 10000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.010883>

16:04:58.412548 getrusage(RUSAGE_SELF, {ru_utime={0, 39993}, ru_stime={0, 38994}, ...}) = 0 <0.000019>

16:04:58.412648 getrusage(RUSAGE_SELF, {ru_utime={0, 39993}, ru_stime={0, 38994}, ...}) = 0 <0.000017>

--//大家可以man semtimedop或者man getrusage表示什麼意思.摘要一段:

$ man semtimedop

NAME

semop, semtimedop - semaphore operations

int semtimedop(int semid, struct sembuf *sops, unsigned nsops, struct timespec *timeout);

semtimedop() behaves identically to semop() except that in those cases were the calling process would sleep, the

duration of that sleep is limited by the amount of elapsed time specified by the timespec structure whose address is

passed in the timeout parameter. If the specified time limit has been reached, semtimedop() fails with errno set to

EAGAIN (and none of the operations in sops is performed). If the timeout parameter is NULL, then semtimedop() behaves

exactly like semop().

--//翻譯如下:

semtimedop()的行為與semop相同()，但在這些情況下，呼叫程式將休眠，睡眠的持續時間受時間規格結構指定的經過時間的限制,其地址

是在超時引數中傳遞。如果已達到指定的時間限制，則semtimedop()失敗，errno設定為再次（不執行sop中的操作）。如果超時引數為空

，則semtimedop()行為就像semop()。

16:04:56.410785 semtimedop(309166080, 0x7fff83068fd0, 1, {0, 10000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.010684>

--//{0, 10000000} 是 timespec. 前面單位是秒,後面單位是納秒(毫微秒) 1秒=10^9納秒, 10000000/10^9 = .01.

--//這樣每次呼叫semtimedop需要0.010xXX秒.

--//我的理解相當於不斷spin,檢查這個資源是否可用.2秒後呼叫getrusage.

--//session 2:

SYS@book> @ hide mutex

NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE

------------------ ----------------- ------------- ------------- ------------

_mutex_spin_count Mutex spin count TRUE 255 255

_mutex_wait_scheme Mutex wait scheme TRUE 2 2

_mutex_wait_time Mutex wait time TRUE 1 1

5.修改這些隱含引數看看:

--//session 2:

SYS@book> alter system set "_mutex_wait_time"=100 scope=memory;

System altered.

--//session 1:

SCOTT@book> @ spid

SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50

---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------

37 79 11990 DEDICATED 11991 26 19 alter system kill session '37,79' immediate

--//SID=37 ,37=0x25.

--//session 2:

SYS@book> oradebug poke 0x000000007DFACAB8 8 0x0000000100000025

BEFORE: [07DFACAB8, 07DFACAC0) = 00000000 00000000

AFTER: [07DFACAB8, 07DFACAC0) = 00000025 00000001

--//開啟新的終端執行如下命令:

$ strace -fttT -p 11956 2>&1 | tee /tmp/m1.txt

16:45:21.034782 getrusage(RUSAGE_SELF, {ru_utime={0, 300954}, ru_stime={0, 31995}, ...}) = 0 <0.000034>

16:45:21.034936 semtimedop(309166080, 0x7fff3961cd50, 1, {1, 0}) = -1 EAGAIN (Resource temporarily unavailable) <1.000684>

16:45:22.035733 semtimedop(309166080, 0x7fff3961cd50, 1, {1, 0}) = -1 EAGAIN (Resource temporarily unavailable) <1.000669>

16:45:23.036517 getrusage(RUSAGE_SELF, {ru_utime={0, 300954}, ru_stime={0, 31995}, ...}) = 0 <0.000033>

16:45:23.036668 getrusage(RUSAGE_SELF, {ru_utime={0, 300954}, ru_stime={0, 31995}, ...}) = 0 <0.000031>

16:45:23.036819 semtimedop(309166080, 0x7fff3961cd50, 1, {1, 0}) = -1 EAGAIN (Resource temporarily unavailable) <1.000693>

16:45:24.037629 semtimedop(309166080, 0x7fff3961cd50, 1, {1, 0}) = -1 EAGAIN (Resource temporarily unavailable) <1.000844>

16:45:25.038587 getrusage(RUSAGE_SELF, {ru_utime={0, 301954}, ru_stime={0, 31995}, ...}) = 0 <0.000032>

16:45:25.038734 getrusage(RUSAGE_SELF, {ru_utime={0, 301954}, ru_stime={0, 31995}, ...}) = 0 <0.000035>

16:45:25.038883 semtimedop(309166080, 0x7fff3961cd50, 1, {1, 0}) = -1 EAGAIN (Resource temporarily unavailable) <1.000709>

16:45:26.039702 semtimedop(309166080, 0x7fff3961cd50, 1, {1, 0}) = -1 EAGAIN (Resource temporarily unavailable) <1.000822>

16:45:27.040680 getrusage(RUSAGE_SELF, {ru_utime={0, 301954}, ru_stime={0, 31995}, ...}) = 0 <0.000029>

16:45:27.040805 getrusage(RUSAGE_SELF, {ru_utime={0, 301954}, ru_stime={0, 31995}, ...}) = 0 <0.000017>

16:45:27.040909 semtimedop(309166080, 0x7fff3961cd50, 1, {1, 0}) = -1 EAGAIN (Resource temporarily unavailable) <1.000680>

16:45:28.041693 semtimedop(309166080, 0x7fff3961cd50, 1, {1, 0}) = -1 EAGAIN (Resource temporarily unavailable) <1.000819>

16:45:29.042627 getrusage(RUSAGE_SELF, {ru_utime={0, 301954}, ru_stime={0, 31995}, ...}) = 0 <0.000033>

16:45:29.042780 getrusage(RUSAGE_SELF, {ru_utime={0, 301954}, ru_stime={0, 31995}, ...}) = 0 <0.000034>

--//現在是間隔1秒呼叫semtimedop. 也就是改變_mutex_wait_time等待時間(單位cs).我的理解在spin 255次不成功sleep 1秒.

--//注:semtimedop , spin次數來源這裡(_mutex_spin_count=255),然後sleep,再次喚醒.

--//session 2:

SYS@book> alter system set "_mutex_wait_time"=10 scope=memory;

System altered.

--//重新測試:

16:49:32.057570 getrusage(RUSAGE_SELF, {ru_utime={0, 29995}, ru_stime={0, 18997}, ...}) = 0 <0.000019>

16:49:32.057682 getrusage(RUSAGE_SELF, {ru_utime={0, 29995}, ru_stime={0, 18997}, ...}) = 0 <0.000019>

16:49:32.057796 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100747>

16:49:32.158640 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100834>

16:49:32.259582 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100928>

16:49:32.360618 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100856>

16:49:32.461584 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100890>

16:49:32.562583 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100924>

16:49:32.663615 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100858>

16:49:32.764582 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100928>

16:49:32.865619 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100855>

16:49:32.966582 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100909>

16:49:33.067606 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100853>

16:49:33.168602 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100809>

16:49:33.269536 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100879>

16:49:33.370541 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100854>

16:49:33.471520 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100872>

16:49:33.572516 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100915>

16:49:33.673559 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100849>

16:49:33.774533 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100899>

16:49:33.875556 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100842>

16:49:33.976519 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100887>

16:49:34.077528 getrusage(RUSAGE_SELF, {ru_utime={0, 30995}, ru_stime={0, 18997}, ...}) = 0 <0.000031>

16:49:34.077679 getrusage(RUSAGE_SELF, {ru_utime={0, 30995}, ru_stime={0, 18997}, ...}) = 0 <0.000031>

16:49:34.077827 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100601>

16:49:34.178538 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100878>

16:49:34.279542 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100854>

16:49:34.380520 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100891>

16:49:34.481551 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100866>

16:49:34.582536 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100886>

16:49:34.683545 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100869>

16:49:34.784537 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100874>

16:49:34.885552 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100868>

16:49:34.986539 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100874>

16:49:35.087538 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100855>

16:49:35.188519 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100890>

16:49:35.289535 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100901>

16:49:35.390564 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100880>

16:49:35.491562 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100836>

16:49:35.592519 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100912>

16:49:35.693555 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100861>

16:49:35.794537 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100892>

16:49:35.895553 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100843>

16:49:35.996518 semtimedop(309166080, 0x7fffbe3108a0, 1, {0, 100000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.100876>

16:49:36.097508 getrusage(RUSAGE_SELF, {ru_utime={0, 31995}, ru_stime={0, 19996}, ...}) = 0 <0.000030>

16:49:36.097635 getrusage(RUSAGE_SELF, {ru_utime={0, 31995}, ru_stime={0, 19996}, ...}) = 0 <0.000027>

--//變成了0.1秒.

--//注意一個細節,如果減去_mutex_wait_time時間,可以發現後面的時間都在0.0006xx-0.00090x之間.

--//取一段計算看看

0.100601+0.100878+0.100854+0.100891+0.100866+0.100886+0.100869+0.100874+0.100868+0.100874+0.100855+0.100890

+0.100901+0.100880+0.100836+0.100912+0.100861+0.100892+0.100843+0.100876= 2.017207

2.017207/20 = .10086035000000000000

--//取平均0.000860秒=860微秒.如果spin=255次的話.每次0.000860/255 = 0.0000034秒

--//另外我修改_mutex_spin_count引數,好像呼叫semtimedop時間不會變化,不知道我理解錯誤在哪裡?

--//session 2:

SYS@book> alter system set "_mutex_wait_time"=1 scope=memory;

System altered.

SYS@book> alter system set "_mutex_wait_scheme"=0 scope=memory;

System altered.

$ awk '{print $2}' /tmp/m2.txt | uniq -c |egrep "sched_yield|select"|head

99 sched_yield()

1 select(0,

99 sched_yield()

1 select(0,

99 sched_yield()

1 select(0,

99 sched_yield()

1 select(0,

99 sched_yield()

1 select(0,

--//呼叫99次sched_yield,然後1次seelct.

--//擷取一段

16:51:53.763611 sched_yield() = 0 <0.000025>

16:51:53.763710 sched_yield() = 0 <0.000020>

16:51:53.763797 sched_yield() = 0 <0.000025>

16:51:53.763896 sched_yield() = 0 <0.000023>

16:51:53.763993 sched_yield() = 0 <0.000023>

16:51:53.764089 sched_yield() = 0 <0.000023>

16:51:53.764206 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.001109>

16:51:53.765424 sched_yield() = 0 <0.000024>

16:51:53.765530 sched_yield() = 0 <0.000023>

16:51:53.765624 sched_yield() = 0 <0.000022>

--//如果是_mutex_wait_scheme=0,呼叫是sched_yield 99次,然後1次select.

$ man sched_yield

SCHED_YIELD(2) Linux Programmer's Manual SCHED_YIELD(2)

NAME

sched_yield - yield the processor

SYNOPSIS

#include <sched.h>

int sched_yield(void);

DESCRIPTION

A process can relinquish the processor voluntarily without blocking by calling sched_yield(). The process will

then be moved to the end of the queue for its static priority and a new process gets to run.

Note: If the current process is the only process in the highest priority list at that time, this process will

continue to run after a call to sched_yield().

POSIX systems on which sched_yield() is available define _POSIX_PRIORITY_SCHEDULING in <unistd.h>.

--//對於select函式不是很理解.不過裡面的時間單位是秒,微妙.

$ man select

NAME

select, pselect, FD_CLR, FD_ISSET, FD_SET, FD_ZERO - synchronous I/O multiplexing

DESCRIPTION

select() and pselect() allow a program to monitor multiple file descriptors, waiting until one or more of the

file descriptors become "ready" for some class of I/O operation (e.g., input possible). A file descriptor is

considered ready

if it is possible to perform the corresponding I/O operation (e.g., read(2)) without blocking.

The operation of select() and pselect() is identical, with three differences:

(i) select() uses a timeout that is a struct timeval (with seconds and microseconds), while pselect() uses a

struct timespec (with seconds and nanoseconds).

(ii) select() may update the timeout argument to indicate how much time was left. pselect() does not change

this argument.

(iii) select() has no sigmask argument, and behaves as pselect() called with NULL sigmask.

Three independent sets of file descriptors are watched. Those listed in readfds will be watched to see if

characters become available for reading (more precisely, to see if a read will not block; in particular, a file

descriptor is also ready on end-of-file), those in writefds will be watched to see if a write will not block,

and those in exceptfds will be watched for exceptions. On exit, the sets are modified in place to indicate which

file descriptors actually changed status. Each of the three file descriptor sets may be specified as NULL if no

file descriptors are to be watched for the corresponding class of events.

Four macros are provided to manipulate the sets. FD_ZERO() clears a set. FD_SET() and FD_CLR() respectively add

and remove a given file descriptor from a set. FD_ISSET() tests to see if a file descriptor is part of the set;

this is useful after select() returns.

nfds is the highest-numbered file descriptor in any of the three sets, plus 1.

timeout is an upper bound on the amount of time elapsed before select() returns. It may be zero, causing select()

to return immediately. (This is useful for polling.) If timeout is NULL (no timeout), select() can block

indefinitely.

sigmask is a pointer to a signal mask (see sigprocmask(2)); if it is not NULL, then pselect() first replaces the

current signal mask by the one pointed to by sigmask, then does the 'select' function, and then restores the

original signal mask.

...

The timeout

The time structures involved are defined in <sys/time.h> and look like

struct timeval {

long tv_sec; /* seconds */

long tv_usec; /* microseconds */

};

--//修改_mutex_wait_scheme=1看看.

--//session 2:

SYS@book> alter system set "_mutex_wait_scheme"=1 scope=memory;

System altered.

$ awk '{print $2}' /tmp/m3.txt | uniq -c

$ awk '{print $2}' /tmp/m3.txt | uniq -c | egrep "select|getrusage"

...

2 getrusage(RUSAGE_SELF,

7 getrusage(RUSAGE_SELF,

1 getrusage(RUSAGE_SELF,

1387 select(0,

2 getrusage(RUSAGE_SELF,

1684 select(0,

2 getrusage(RUSAGE_SELF,

1675 select(0,

2 getrusage(RUSAGE_SELF,

1635 select(0,

2 getrusage(RUSAGE_SELF,

1615 select(0,

2 getrusage(RUSAGE_SELF,

1617 select(0,

2 getrusage(RUSAGE_SELF,

1616 select(0,

2 getrusage(RUSAGE_SELF,

1615 select(0,

2 getrusage(RUSAGE_SELF,

1616 select(0,

2 getrusage(RUSAGE_SELF,

1619 select(0,

2 getrusage(RUSAGE_SELF,

1611 select(0,

2 getrusage(RUSAGE_SELF,

766 select(0,

1 getrusage(RUSAGE_SELF,

4 getrusage(RUSAGE_SELF,

--//擷取一段:

17:06:45.648350 sched_yield() = 0 <0.000037>

17:06:45.648570 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.001082>

17:06:45.649750 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.001115>

17:06:45.650979 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.001111>

...

17:06:47.290475 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.001099>

17:06:47.291645 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.001138>

17:06:47.292862 getrusage(RUSAGE_SELF, {ru_utime={0, 61990}, ru_stime={0, 67989}, ...}) = 0 <0.000021>

17:06:47.292968 getrusage(RUSAGE_SELF, {ru_utime={0, 61990}, ru_stime={0, 67989}, ...}) = 0 <0.000018>

17:06:47.293111 select(0, [], [], [], {0, 1000}) = 0 (Timeout) <0.001095>

--//還原:

--//session 2:

SYS@book> alter system set "_mutex_wait_scheme"=2 scope=memory;

System altered.

--//視乎_mutex_wait_scheme引數調整mutex的方式.

6.總結:

--//不小心寫的太長.對於os,oracle等許多概念不是很清楚,做這探究難度太大...

--//網上找了一段資料:

* _mutex_spin_count (Integer)

- This sets the number of times to spin before yielding/waiting.

* _mutex_wait_scheme (Integer)

- In 11.2 this controls which wait scheme to use. It can be set to one

of the three wait schemes described above thus:

_mutex_wait_scheme = 0 – Always YIELD

_mutex_wait_scheme = 1 & _mutex_wait_time = t – Always SLEEP for t milli-seconds (default)

_mutex_wait_scheme = 2 & _mutex_wait_time = t – EXP BACKOFF with maximum sleep

--//注:_mutex_wait_scheme = 2,我並沒有看到指數回退-每次迭代我們都會睡得更多的時間的情況.

7.附上測試的指令碼:

$ cat mutexy.sql

column kglnaown format a20

column MUTEX_TYPE format a20

column kglnaobj format a100

column LOCATION format a30

select * from (

SELECT kglnahsh hash

,SUM (sleeps) sum_sleeps

,SUM (gets) sum_gets

,location

,mutex_type

,MUTEX_ADDR

,kglobt03 sqlid

,kglnaown

,replace(kglnaobj,chr(13)) c100

--,SUBSTR (kglnaobj, 1, 140) object

FROM x$kglob, x$mutex_sleep_history

WHERE kglnahsh = mutex_identifier

GROUP BY kglnaobj

,kglobt03

,kglnaown

,kglnahsh

,location

,mutex_type

,MUTEX_ADDR

ORDER BY sum_sleeps DESC ) where rownum<= &1;

--//查詢x$mutex_sleep_history資訊,主要v$mutex_sleep_history沒有MUTEX_ADDR欄位資訊.

$ cat fcha.sql

--------------------------------------------------------------------------------

--

-- File name: fcha.sql (Find CHunk Address) v0.2

-- Purpose: Find in which heap (UGA, PGA or Shared Pool) a memory address resides

--

-- Author: Tanel Poder

-- Copyright: (c) http://blog.tanelpoder.com | @tanelpoder

--

-- Usage: @fcha <addr_hex>

-- @fcha F6A14448

--

-- Other: This would only report an UGA/PGA chunk address if it belongs

-- to *your* process/session (x$ksmup and x$ksmpp do not see other

-- session/process memory)

--

--------------------------------------------------------------------------------

prompt Find in which heap (UGA, PGA or Shared Pool) the memory address &1 resides...

prompt

prompt WARNING!!! This script will query X$KSMSP, which will cause heavy shared pool latch contention

prompt in systems under load and with large shared pool. This may even completely hang

prompt your instance until the query has finished! You probably do not want to run this in production!

prompt

pause Press ENTER to continue, CTRL+C to cancel...

select

'SGA' LOC,

KSMCHPTR,

KSMCHIDX,

KSMCHDUR,

KSMCHCOM,

KSMCHSIZ,

KSMCHCLS,

KSMCHTYP,

KSMCHPAR

from

x$ksmsp

where

to_number(substr('&1', instr(lower('&1'), 'x')+1) ,'XXXXXXXXXXXXXXXX')

between

to_number(ksmchptr,'XXXXXXXXXXXXXXXX')

and to_number(ksmchptr,'XXXXXXXXXXXXXXXX') + ksmchsiz - 1

union all

select

'UGA',

KSMCHPTR,

null,

KSMCHCOM,

KSMCHSIZ,

KSMCHCLS,

KSMCHTYP,

KSMCHPAR

from

x$ksmup

where

to_number(substr('&1', instr(lower('&1'), 'x')+1) ,'XXXXXXXXXXXXXXXX')

between

to_number(ksmchptr,'XXXXXXXXXXXXXXXX')

and to_number(ksmchptr,'XXXXXXXXXXXXXXXX') + ksmchsiz - 1

union all

select

'PGA',

KSMCHPTR,

null,

KSMCHCOM,

KSMCHSIZ,

KSMCHCLS,

KSMCHTYP,

KSMCHPAR

from

x$ksmpp

where

to_number(substr('&1', instr(lower('&1'), 'x')+1) ,'XXXXXXXXXXXXXXXX')

between

to_number(ksmchptr,'XXXXXXXXXXXXXXXX')

and to_number(ksmchptr,'XXXXXXXXXXXXXXXX') + ksmchsiz - 1

/

$ cat shp4.sql

column N0_6_16 format 99999999

SELECT DECODE (kglhdadr,

kglhdpar, '父遊標控制程式碼地址',

'子游標控制程式碼地址')

text,

kglhdadr,

kglhdpar,

substr(kglnaobj,1,40) c40,

KGLHDLMD,

KGLHDPMD,

kglhdivc,

kglobhd0,

kglobhd6,

kglobhs0,kglobhs6,kglobt16,

kglobhs0+kglobhs6+kglobt16 N0_6_16,

kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,

kglnahsh,

kglobt03 ,

kglobt09

FROM x$kglob

WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2;

$ cat spid.sql

column sid new_value newsid

column serial# new_value newserial

column spid new_value newspid

set verify off

/* Formatted on 2019/3/28 17:19:16 (QP5 v5.252.13127.32867) */

SELECT s.sid

,s.serial#

,s.process

,s.server

,p.spid

,p.pid

,p.serial# p_serial#

, 'alter system kill session '''

|| s.sid

|| ','

|| s.serial#

|| ''''

|| ' immediate;'

c50

FROM v$session s, v$process p

WHERE s.sid IN (SELECT sid FROM v$mystat WHERE ROWNUM = 1)

AND s.paddr = p.addr;

$ cat hide.sql

col name format a40

col description format a66

col session_value format a22

col default_value format a22

col system_value format a22

select

a.ksppinm name,

a.ksppdesc DESCRIPTION,

b.ksppstdf DEFAULT_VALUE,

b.ksppstvl SESSION_VALUE,

c.ksppstvl SYSTEM_VALUE

from x$ksppi a, x$ksppcv b, x$ksppsv c

where a.indx = b.indx

and a.indx = c.indx

and lower(a.ksppinm) like lower('%&1%')

order by 1;

$ cat pt.sql

--

-- show output

set termout on

-- but without echo

set echo off

-- without newpage on start:

set embedded on

-- scrolling control

set pause on

-- two lines between rows:

set newpage 2

-- text for prompt after each page:

set pause "Press Enter to view next row..."

-- new page on new "row_num"

break on row_num skip page

-- main query:

select *

from

xmltable( 'for $a at $i in /ROWSET/ROW

,$r in $a/*

return element ROW{

element ROW_NUM{$i}

,element COL_NAME{$r/name()}

,element COL_VALUE{$r/text()}

}'

passing xmltype(cursor( &1 ))

columns

row_num int

-- ,col_name varchar2(30)

,col_name varchar2(30)

,col_value varchar2(100)

);

-- disabling pause and breaks:

set pause off

clear breaks

[20190328]簡單探究sql語句相關mutexes.txt

相關文章