tuning of latch free wait event


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


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.


Tune SQL if found more consuming blocks and less executions found in shared pool.

All the best,

Gitesh Trivedi


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.