Hot Blocks
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Hot Blocks

  1. #1
    Join Date
    Dec 2001
    Posts
    337

    Hot Blocks

    Hello all,

    We have severe lock contention in one of our tables when it is being updated. I suspect contention for a specific row in the database. Is there anyway i can detect this hot block?

    Thanks in advance,
    Nikesh.

  2. #2
    Join Date
    Jan 2003
    Posts
    78
    This script will report the SQL text of some of the locks currently being held in the database:

    set pagesize 60
    set linesize 132
    select s.username username,
    a.sid sid,
    a.owner||'.'||a.object object,
    s.lockwait,
    t.sql_text SQL
    from v$sqltext t,
    v$session s,
    v$access a
    where t.address=s.sql_address
    and t.hash_value=s.sql_hash_value
    and s.sid = a.sid
    and a.owner != 'SYS'
    and upper(substr(a.object,1,2)) != 'V$'
    /
    HTH.
    Shripad Godbole
    OCP DBA (8,8i,9i)

    "Let's document it and call it a feature."

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