Archive for the ‘Oracle Tuning’ Category

Oracle 11g and Oracle 10g articles

December 27, 2010

Hi friends,

Excellent resource of Oracle 11g technical articles, Oracle 10g technical articles, scripts is available on our website of dbametrix.com

There are lot of remote dba tips, job searching tips also available in same site in Oracle DBA tips link.

Kindly check daily because daily we are adding new article in same resource.

Thanks and regards,

Gitesh

Remote DBA support

J3BJMVFQXSFA

tuning of latch free wait event

March 31, 2010

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