Hi,
Today We will try to see how to tune LATCH FREE wait event?
Today I have checked statspack report of one of my client. In Top 5 Wait event as follows in statspack report.
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (s) Wt Time
——————————————– ———— ———– ——-
control file sequential read 43 1 48.89
latch free 88 1 43.73
control file parallel write 43 0 2.33
db file parallel write 2 0 2.25
db file scattered read 4 0 1.32
————————————————————-
After that I have seen other statspack reports and found that “latch free” wait event was consuming more CPU than other wait events.
After I have checked latch statistics and found there were problem in shared pool because there were no free space available in shared_pool_size and no bind variables were used.
Generally Latch free wait event is increasing due to session has to wait for getting latch which is being held by another process. Latch contention occurs due to this problem.
Heavy misses are being reflected in shared pool and library cache statistics reflects Hard parsing problem and bind variable usage.
How to tune Latch free wait event:
Check hard parse and soft parse. Also check execute to parse ratio. If hard parsing is more then check the root cause of problem. use bind variable instead of static.
Check free space in shared pool if you find out free space then increase following parameter of cursor
session_cached_cursors=50
Now monitor cached cursor usage. Increase appropriate value of this parameter for tuning of hard parsing if you have free space in shared_pool.
Monitor usage of session_cached_cursors using following query.
SQL> select max(value) from v$sesstat
where statistic# in (select statistic# from v$statname where name=’session cursor cache count’);
We can use HINT as SESSION_CACHE in our SQL statement for cursor caching (Oracle 10g) as follows.
SQL>select /*+ session_cache */ * from emp;
Increase size of shared pool for providing more room for new cursors and pl/sql blocks.May be cursors are being aged out due to lack of free space available to make room for new cursors in shared pool.
Shared_pool_size
Tune SQL if found more consuming blocks and less executions found in shared pool.
All the best,
Gitesh Trivedi
Myths-Mysteries-Memorable 