lock contention please
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: lock contention please

  1. #1
    Join Date
    Dec 2001
    Location
    Hyderabad
    Posts
    17

    Question

    hi all,
    can anybody please tell me how to find out lock contention? Are there any scripts available to know this? i'll be very thankful to any advices regarding this.
    venu!
    Venu G. (OCP 8i & 9i)

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    OPS locking problems or normal Oracle?

    If it is OPS then it is not that simpe to explain. For a single env, run this for example:

    set linesize 132 pagesize 66
    break on Kill on username on terminal
    column Kill heading 'Kill String' format a13
    column res heading 'Resource Type' format 999
    column id1 format 9999990
    column id2 format 9999990
    column lmode heading 'Lock Held' format a20
    column request heading 'Lock Requested' format a20
    column serial# format 99999
    column username format a10 heading "Username"
    column terminal heading Term format a6
    column tab format a35 heading "Table Name"
    column owner format a9
    column Address format a18
    select nvl(S.USERNAME,'Internal') username,
    nvl(S.TERMINAL,'None') terminal,
    L.SID||','||S.SERIAL# Kill,
    U1.NAME||'.'||substr(T1.NAME,1,20) tab,
    decode(L.LMODE,1,'No Lock',
    2,'Row Share',
    3,'Row Exclusive',
    4,'Share',
    5,'Share Row Exclusive',
    6,'Exclusive',null) lmode,
    decode(L.REQUEST,1,'No Lock',
    2,'Row Share',
    3,'Row Exclusive',
    4,'Share',
    5,'Share Row Exclusive',
    6,'Exclusive',null) request
    from V$LOCK L,
    V$SESSION S,
    SYS.USER$ U1,
    SYS.OBJ$ T1
    where L.SID = S.SID
    and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)
    and U1.USER# = T1.OWNER#
    and S.TYPE != 'BACKGROUND'
    order by 1,2,5
    /

  3. #3
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Check the following views
    http://technet.oracle.com/docs/produ...ch380.htm#5397
    http://technet.oracle.com/docs/produ...h383.htm#46175

    Also refer utllockt.sql script under $ORACLE_HOME/rdbms/admin.


    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  4. #4
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530
    Hi,
    The following is the script to find out lock contention.There are only 3 locks which a DBA can tune i.e redo copy,redo allocation and redo writing.The ratio of all this 3 types of locks ie.(misses/gets)*100 and (immediate_misses/immediate_gets)*100 should be less than < 1%.

    select substr(c.name,1,30),a.gets,a.misses,a.sleeps, a.immediate_gets,a.immediate_misses,b.pid
    from v$latch a, v$latchholder b, v$latchname c
    where a.addr = b.laddr(+)
    and a.latch# = c.latch#
    and (c.name like 'redo%' or c.name like 'row%' )
    order by a.latch#

    In case of any help please be free to ask me at rohitsn@altavista.com

    Regards,
    Rohit Nirkhe,Oracle DBA,OCP 8i
    rohitsn@altavista.com

  5. #5
    Join Date
    Dec 2001
    Location
    Hyderabad
    Posts
    17

    it's non OPS

    thank you julian and thank you sam, i was referring to a non-OPS database. but i think the scrpit julian has provided will serve the purpose. thanks a lot ppl.
    Venu G. (OCP 8i & 9i)

  6. #6
    Join Date
    Dec 2001
    Location
    Hyderabad
    Posts
    17

    thank you rohit!

    nice script rohit! thanks a lot!
    Venu G. (OCP 8i & 9i)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width