Monday, June 2, 2014

RDBMS-- Allocating latches manually

In this post, I will demostrate a latch allocation using oradebug utility..
The latches are allocating and released by Oracle processes.. This is the design to supply the concurreny in memory.
In this demo, we will see the effects of not releasing a latch :)

First, we find the memory address of the latch that we want to acquire;

sqlplus "/as sysdba"
SQL>select addr from v$latch where name like '%session allocation%';
ADDR
---------------
000000006000A160

Then  we acquire it using oradebug utility
SQL> oradebug setmypid
Statement processed.
SQL> oradebug call kslgetl 0X00000006000A160 1 2 3
Function returned 1

Now we have the session allocation latch.. I have choosed this latch to show you to hang state clearly..
Session allocation latch is acquired when creating a new session. It is used to protect the structures externalized with v$session and v$process during a session creation.

So after getting this latch, I open another sqlplus and it hangs as we expected :)

 sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 2 15:23:59 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

--hangs in here..

Basically, a session can not be created because we dont release the session allocation latch..
We see that, misses and sleep counts for the relevant latches increase during the hang.
We also see that, the hung sqlplus sessions connects to the database immediately just after  we release the session allocation latch.

To release the latch we use kslfre as follows;

 oradebug call kslfre 0X00000006000A160 0

Lastly, I will give some information about the functions that contribute latch interface of oracle:

kslgetl(laddr, wait, why, where) - get exclusive latch
kslg2c(l1,l2,trc,why, where) - get two excl. child latches
kslgetsl(laddr,wait,why,where,mode) - get shared latch
11g -ksl_get_shared_latch
kslg2cs(l1,l2,mode,trc,why, where)) - get two shr. child latches
kslgpl(laddr,comment,why,where) - get parent and all childs
kslfre(laddr) - free the latch

laddres– address of latch in SGA
wait– flag for no-wait (0) or wait (1) latch acquisition
where– integer code for location from where the latch is acquired.
mode– requesting state for shared lathes. – 8 SHARED mode, 16 EXCLUSIVE mode

No comments :

Post a Comment