Latch Hit Contention Questions
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Latch Hit Contention Questions

  1. #1
    Join Date
    Feb 2001
    Posts
    44

    Question

    A quick question....

    Which V$ will give me the information about latch hit contention?

    v$sysstat or v$waitstat or v$system_event or v$session_event or v$latch????

    Somebody help!!!!!

  2. #2
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    solution

    hi, 25th May 2001 21:00 hrs chennai

    Sorry my system net gone down .I could not catch up

    SELECT ln.name,misses/gets "MISSES/GETS',
    (immediate_misses/immediate_gets+immediate_misses)'I_MISSES/I_GETS'
    FROM V$LATCH l ,V$LATCHNAME ln
    where ln.name IN('redo allocation','redo copy')
    and ln.latch#=l.latch#;

    You can also use

    Performance Manager==>Latch==>Redo Allocation Hit%

    Cheers

    Padmam
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

  3. #3
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316

    Thumbs up

    Here is a handy little view to find the v$ views that you are looking for:

    select * from dba_catalog where table_name like 'V$%LATCH%' and owner = 'PUBLIC';

  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Buffer Cache Latch:
    ======================

    select name, gets, misses, sleeps, immediate_gets, immediate_misses
    from v$latch
    where name like 'cache %'
    order by name;



    Redolog buffer latches:
    ========================

    select name,
    sum(gets) "Gets",
    sum(misses) "Misses",
    sum(immediate_gets) "IMGets",
    sum(immediate_misses') "IMMisses"
    from v$latch
    where name like '%redo%'
    order by name;


    List the latch statistics:
    ===========================

    select name,
    gets,
    misses,
    immediate_gets,
    immediate_misses
    from v$latch
    where gets > 0
    order by name;


    Identify the source of contention:
    ===================================
    select vs.sid, vsw.p1, vsw.p2, vsq.sql_text
    from v$sql vsq,
    v$session vs,
    v$session_wait vsw
    where vsw.sid = vs.sid
    and vs.sql_hash_value = vsq.hash_value
    and vsw.event = 'latch free'
    and vsw.wait_time = 0;



    Hope these would help you and others who are interested in.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


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