Useful SQL

I was observing high DFS lock handle wait during Siebel CRM performance testing  and i found below SQL very useful to understand the root cause of Watis.

Output of Query 1 will input to Query 2.

Query1:

SELECT CHR(to_char(bitand(p1,-16777216))/16777215)||CHR(to_char(bitand(p1, 16711680))/65535) "Lock Type",  to_char( bitand(p1, 65535) )    "Lock Mode"

FROM v$session_wait

WHERE event = 'DFS lock handle'

/

Lock Mode
------------------
SV

Query 2:

column name format a20

column type format a5

column DESCRIPTION  format a40

select type,name , description from V$lock_type where type='SV'

/

TYPE  NAME                 DESCRIPTION

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

SV    Sequence Ordering    Lock to ensure ordered sequence allocati

                                          on in RAC mode

when Query the V$lock_type we can clearly see type SV mean database having ordered sequence allocation issue in RAC mode.