Latch Free event in library Cache

Printable View

11-14-2002, 06:53 AM
anandkl

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
11-14-2002, 06:59 AM
pando

use bind variables

you can set the number of latch for library cache? sounds new to me :D
11-14-2002, 07:09 AM
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
11-14-2002, 07:12 AM
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 :o
11-14-2002, 08:15 AM
anandkl

Ok...:o

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

regards
anandkl
11-14-2002, 08:21 AM
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
11-14-2002, 09:05 AM
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
11-14-2002, 09:29 AM
Sameer

Quote:

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...
11-14-2002, 09:57 AM
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
11-14-2002, 10:21 AM
Sameer

Quote:

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
11-14-2002, 10:29 AM
pando

Quote:

Originally posted by 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

it´s not a bug, its side effects, with bind variables Oracle cant make good use of histograms, but if your queries query columns with uniform data distributions then you dont have to use histograms in that case you dont have to worry about this side effect