Latch Free event in library Cache
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Latch Free event in library Cache

  1. #1
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840

    Latch Free event in library Cache

    Hi All,
    oracle version=8.1.6
    os=sun 2.7
    database =production

    I have very serious problem with my production database.Daily every morning there is a huge wait for Latch free wait event in library cache and the database cannot take this wait and goes down.

    I know if i increase the size of the shared_pool i would benifit,but this is temporary solution.And also i have set the latch no. to the max. allowed.

    Can you please suggest me a permanenet solution for this problem.

    regards
    anandkl
    anandkl

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    use bind variables

    you can set the number of latch for library cache? sounds new to me

  3. #3
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Hi Pando,
    What i meant to say was the no of db_block_lru_latches.I am sorry for not mentioning this in my previous mail.

    Do you think there is any other way of solving this issue.Because i have already told my Engg team that we need to use bind variables.

    Regards
    anandkl
    anandkl

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    db_block_lru_latches has nothing to do with library cache, it sets number of latches for cache buffer chain lru latch which is another latch to manage LRU lists in data block buffer

    and library cache is in shared pool

  5. #5
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840

    Red face

    Ok...

    Can you please help me solving this issue.Other can using bind variable.

    regards
    anandkl
    anandkl

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well if you are using 8i try use cursor_sharing = force and see if it helps

    it may have side effects though, for example may be index scans become full table scans or vice-versa

  7. #7
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    So this a bug..it might a very serious effect on the database..
    SO you suggest that using bind variable is the only safe solution.

    Please suggest.

    regards
    anandkl
    anandkl

  8. #8
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Originally posted by anandkl
    SO you suggest that using bind variable is the only safe solution.
    +

    Check for session high parse time in v$sesstat or also look in v$sqlarea/v$sql for high ratio of parse calls to execution, high number of reloads or invalidations...

  9. #9
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840

    Unhappy

    I have gone through all this process and found out that the sql are getting invalidated and resulting in 'HARD PARSE', so i want a solution to avoid this 'HARD PARSE'

    regards
    anandkl
    anandkl

  10. #10
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Originally posted by anandkl
    I have gone through all this process and found out that the sql are getting invalidated and resulting in 'HARD PARSE', so i want a solution to avoid this 'HARD PARSE'

    regards
    anandkl
    then solution is as suggested by Pando.. Use bind variables, use procedures,... pin frequently used objects

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