[20170923]模擬session allocation latch.txt

--//oracle 從11g開始(也許10g開始)使用mutex代替latch,但是還是保留大量的使用latch,除了cbc latch外,還有其他latch.
--//手工模擬session allocation latch.

1.環境:
--//session 1:
SCOTT@book> @ &r/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

SCOTT@book> select * from v$latch where name like '%session allocation%';
ADDR LATCH# LEVEL# NAME HASH GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES WAITERS_WOKEN WAITS_HOLDING_LATCH SPIN_GETS SLEEP1 SLEEP2 SLEEP3 SLEEP4 SLEEP5 SLEEP6 SLEEP7 SLEEP8 SLEEP9 SLEEP10 SLEEP11 WAIT_TIME
---------------- ---------- ---------- -------------------- ---------- ---------- ---------- ---------- -------------- ---------------- ------------- ------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
000000006000A170 11 5 session allocation 896287525 20410 0 0 13431 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

--//確定session allocation latch的地址=000000006000A170
--//做1次會話登入看看.

SCOTT@book> select * from v$latch where name like '%session allocation%';
ADDR LATCH# LEVEL# NAME HASH GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES WAITERS_WOKEN WAITS_HOLDING_LATCH SPIN_GETS SLEEP1 SLEEP2 SLEEP3 SLEEP4 SLEEP5 SLEEP6 SLEEP7 SLEEP8 SLEEP9 SLEEP10 SLEEP11 WAIT_TIME
---------------- ---------- ---------- -------------------- ---------- ---------- ---------- ---------- -------------- ---------------- ------------- ------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
000000006000A170 11 5 session allocation 896287525 20418 0 0 13436 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

--//增加8次.

2.手工加鎖:
--//開啟另外回話2:
--//session 2:
SYS@book> oradebug setmypid
Statement processed.
SYS@book> oradebug call kslgetl 0X000000006000A170 1
Function returned 1

--//在啟動一個新的session 3,可以發現hang在那裡,無法登陸.回到session 1檢視等待事件:
$ rlsql scott/book
--//掛起!!

SCOTT@book> @ &r/wait
no rows selected

--//奇怪看不到等待事件....wait.sql指令碼如下:
select p1raw,p2raw,p3raw,p1,p2,p3,sid,serial#,seq#,event,state,wait_time_micro,seconds_in_wait from v$session where wait_class<>'Idle'
and sid not in (select sid from v$mystat where rownum=1)
order by event ;

3.解除加鎖:
--//session 2:
SYS@book> oradebug call kslfre 0X000000006000A170 0
Function returned 0

--//再檢查session 3,提示如下:
$ rlsql scott/book
SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 25 10:48:32 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-03113: end-of-file on communication channel
Process ID: 0
Session ID: 0 Serial number: 0
Enter user-name: scott
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

--//重新登入ok.
4.至於前面為什麼有8次主要問題是我在vim下呼叫的緣故.
--http://blog.itpub.net/267265/viewspace-2140936/=> [20170617]vim中呼叫sqlplus.txt

SCOTT@book> select ADDR,LATCH#,LEVEL#,NAME,HASH,GETS,MISSES,SLEEPS,IMMEDIATE_GETS from v$latch where name like 'session allocation';
ADDR LATCH# LEVEL# NAME HASH GETS MISSES SLEEPS IMMEDIATE_GETS
---------------- ---------- ---------- -------------------- ---------- ---------- ---------- ---------- --------------
000000006000A170 11 5 session allocation 896287525 20540 0 0 13520

SCOTT@book> select ADDR,LATCH#,LEVEL#,NAME,HASH,GETS,MISSES,SLEEPS,IMMEDIATE_GETS from v$latch where name like 'session allocation';
ADDR LATCH# LEVEL# NAME HASH GETS MISSES SLEEPS IMMEDIATE_GETS
---------------- ---------- ---------- -------------------- ---------- ---------- ---------- ---------- --------------
000000006000A170 11 5 session allocation 896287525 20541 0 0 13520

SCOTT@book> select ADDR,LATCH#,LEVEL#,NAME,HASH,GETS,MISSES,SLEEPS,IMMEDIATE_GETS from v$latch where name like 'session allocation';
ADDR LATCH# LEVEL# NAME HASH GETS MISSES SLEEPS IMMEDIATE_GETS
---------------- ---------- ---------- -------------------- ---------- ---------- ---------- ---------- --------------
000000006000A170 11 5 session allocation 896287525 20542 0 0 13520

SCOTT@book> select ADDR,LATCH#,LEVEL#,NAME,HASH,GETS,MISSES,SLEEPS,IMMEDIATE_GETS from v$latch where name like 'session allocation';
ADDR LATCH# LEVEL# NAME HASH GETS MISSES SLEEPS IMMEDIATE_GETS
---------------- ---------- ---------- -------------------- ---------- ---------- ---------- ---------- --------------
000000006000A170 11 5 session allocation 896287525 20543 0 0 13520

--//我發現我每次查詢這個檢視,都會增加.這點可以透過如下驗證:

--//session 2,執行:
SYS@book> oradebug call kslgetl 0X000000006000A170 1
Function returned 1

--//session 3:執行:
SCOTT@book> select ADDR,LATCH#,LEVEL#,NAME,HASH,GETS,MISSES,SLEEPS,IMMEDIATE_GETS from v$latch where name like 'session allocation';

--//掛起..執行如下ok:
SCOTT@book> @ &r/wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ------------------- --------------- ---------------
000000006000A170 000000000000000B 00 1610654064 11 0 144 1471 120 latch: session allocation WAITING 1456628 1
--//這樣能看到latch: session allocation事件.

SYS@book> oradebug call kslfre 0X000000006000A170 0
Function returned 0

--//session 3:執行執行其他sql語句沒有問題在oradebug call kslgetl 0X000000006000A170 1情況下.

[20170923]模擬session allocation latch

相關文章