Latch Free event in library Cache

[anandkl]

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

[pando]

use bind variables

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

[anandkl]

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

[pando]

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

[anandkl]

Ok...

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

regards
anandkl

[pando]

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

[anandkl]

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

[Sameer]

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...

[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

[Sameer]

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