[20170923]模拟session allocation latch-CSDN博客

[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次会话登录看看.

--//增加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

--//我发现我每次查询这个视图,都会增加.这点可以通过如下验证:

--//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情况下.