Help! Latch Waits are out of control
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Help! Latch Waits are out of control

  1. #1
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    We have a database that has started to experience SEVERE spikes in Latch Waits (library cache). These spikes are in the range of 100K - 1M ms waited/sec. When the spikes occur, our users are almost completely locked-up until the latches come down.

    Most of the documentation on Library Cache Latches tells you to tune your SQL. Unfortunately, this is a purchased application, and we have no ability to change the sql.

    A couple of notes on our database:
    -The database has ~80 Gb of data
    -We have ~800 users on a typical day... using the same application for the most part.
    -Our system is 8.1.6.3 on AIX 4.3.3
    -We have 16 Gb of Memory
    -Our SGA is ~3 Gig
    -We are using MTS.

    Does anyone have any idea what we can do to fix this problem?

    Any help would be appreciated!

    Thanks!
    Jodie

  2. #2
    Join Date
    Sep 2001
    Location
    Fort Smith
    Posts
    184
    look into metalink doc 100964.1
    also calculate the sga
    8.0.X

    To approximate size of the SGA (Shared Global Area), use the following
    formula:

    ((db_block_buffers * block size) +
    (shared_pool_size + large_pool_size + log_buffers) + 1MB


    8.1.X

    To approximate size of the SGA (Shared Global Area), use the following
    formula:

    ((db_block_buffers * block size) +
    (shared_pool_size + large_pool_size + java_pool_size + log_buffers) + 1MB

    sonofsita
    http://www.ordba.net

  3. #3
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    The size of the SGA was not our problem. We have done a lot of work there, and our SGA appears to be the appropriate size. But thanks for the help.

    In case anyone is interested, we did solve our problem. We had a query that was doing a FULL table scan of a 9.5Gig table over and over. We forced the use of the index, and our problem went away!

    Thanks!
    Jodie

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    We had a query that was doing a FULL table scan of a 9.5Gig table over and over. We forced the use of the index, and our problem went away!
    9.5G? Wow :-)


  5. #5
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    Most of the documentation on Library Cache Latches tells you to tune your SQL. Unfortunately, this is a purchased application, and we have no ability to change the sql.
    seems like you did end up tuning the SQL anyway :-)

    - Rajeev
    Rajeev Suri

  6. #6
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    Actually, the Vendor came back with an "update" since the query appeared to not be using the index.



    I agree SQL is what needed to be tuned, but Mgmt wanted a quick fix from the DBA's.

    Oh Well!

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    next time if you cannot modify the query and you need to change or force a specific execution plan use stored outlines

  8. #8
    Join Date
    Aug 2001
    Posts
    111
    A simple or materialised view would also do the trick.
    Performance... Push the envelope!

  9. #9
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    Thanks for all your help!!

    Jodie

  10. #10
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    next time if you cannot modify the query and you need to change or force a specific execution plan use stored outlines
    Pando,
    what is "stored outlines" ?

    Thanks,
    - Rajeev
    Rajeev Suri

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