Home > Performance Tuning > Change in LGWR behavior , living with beast or best?

Change in LGWR behavior , living with beast or best?

If you are experiencing the log file sync wait after an upgrade to 11.2.0.3 and most importantly that as there are no other symptoms of issues with I/O or in other areas, the problem could be with excessive switching between post/wait and polling wait methods.

While writing previous post, came across the another interesting note and reference post that there is a change in the LGWR behavior appears from 11.2.0.3,

Background:- LGWR posts/poll to the user session (acknowledgement) that receives requests or send acknowledgement to the foreground process or to other process like checkpoint.

But  due to kernel scheduling delays (due to CPU starvation, memory starvation) LGWR can also be victim waiting to post/poll or another angle could be user sessions may not receive the acknowledgement in time which can drastically increase the log file sync events.

According to metalink note, Adaptive Switching Between Log Write Methods can Cause ‘log file sync’ Waits [ID 1462942.1] , On a system with 11.2.0.3 (solaris) log file sync can be appear , even if the other contributors (read here) are taking less wait time which revealing in your troubleshooting.

This is because of change in the lgwr post or polling (kernel calls to post to foreground processes) behavior with an underscore parameter, called _use_adaptive_log_file_sync (default true)

Description of _use_adaptive_log_file_sync

With " _use_adaptive_log_file_sync " = true:
Adaptive switching between post/wait and polling for log file sync is implemented.
When polling is adaptively selected, the polling interval is also dynamically adjusted.
If post/wait is selected and the foreground processes fail to receive a post from LGWR, an incident is recorded, diagnostic traces are performed, and polling is used instead of post/wait.

If you dont want to live with beast, then Set the parameter _use_adaptive_log_file_sync = false and restart the database:

SQL> ALTER SYSTEM SET "_use_adaptive_log_file_sync" = FALSE;

As this parameter by default true, you may need to consider to set it false if you are seeing higher log file sync waits

Along with this there are other underscore parameters for lgwr behavior which were discussed in the reference link below. Thanks to Pythian Team and its blog.

References:-

http://www.pythian.com/news/36791/adaptive-log-file-sync-oracle-please-dont-do-that-again/

Adaptive Switching Between Log Write Methods can Cause ‘log file sync’ Waits [ID 1462942.1]

-Thanks

Suresh

About these ads
  1. No comments yet.
  1. No trackbacks yet.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

BigData - Oracle-info.com

Sureshgandhi's Blog : Its time to learn BigData

Oracle Alchemist

Steve Karam's Oracle Blog

ishudba

The King's .... "Joy of Life"..

the dba diary !!!

Naga's Blog : Oracle DBA experiences

ORACLE-INFO

Sureshgandhi's Blog: It's all about Oracle..Just Learn,Apply & Share.

SureshGandhi

All about Oracle.......

All about Oracle.......

DB Optimizer

database performance, SQL tuning and data visualizatoin

jarneil

The thoughts of Jason Arneil

All Things Oracle

All about Oracle.......

Dion Cho - Oracle Performance Storyteller

We are natural born scientists

All about Oracle.......

All about Oracle.......

All about Oracle.......

Miladin Modrakovic's Blog: Oraclue

Oracle internals, debugging and undocumented features

Coskan's Approach to Oracle

What I learned about Oracle

The Arup Nanda Blog

All about Oracle.......

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

Martins Blog

Trying to explain complex things in simple terms

All about Oracle.......

Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

All about Oracle.......

Kevin Closson's Blog: Platforms, Databases and Storage

Platform, Database and Storage Topics

Iordan Iotzov's DBA blog

The opinions here may not necessarily reflect that of my employers - both past and present. The comments left by the reviewers are theirs alone and may not reflect my opinion. None of the advice is warranted to be free of errors and omissions. Please use at your own risk and after thorough testing in your environment.

Doug's Oracle Blog

All about Oracle.......

Oracle related stuff

All about Oracle.......

All about Oracle.......

Charles Hooper's Oracle Notes

Miscellaneous Random Oracle Topics: Stop, Think, ... Understand

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Oracle E-Business Suite Technology

All about Oracle.......

Tanel Poder's blog: Responsible data management

Linux, Oracle, Exadata and Hadoop.

Oracle DBA - Tips and Techniques

Knowledge Is Power - Share IT ....

Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

Laurent Schneider

All about Oracle.......

Kerry Osborne's Oracle Blog

All about Oracle.......

Hemant's Oracle DBA Blog

All about Oracle.......

Oracle Enterprise Manager

All about Oracle.......

Structured Data

Thoughts on: Big Data, Hadoop, Databases, Platform, Performance & Scalability

Oracle Scratchpad

Just another Oracle weblog

Karl Arao's Blog

Just another weblog about Oracle,Linux,Troubleshooting,Performance,etc..etc..

The Data Warehouse Insider

All about Oracle.......

Online Apps DBA

One Stop Shop for Apps DBA's

All about Oracle.......

Microsoft SQL Server

All about Oracle.......

Follow

Get every new post delivered to your Inbox.

Join 83 other followers

%d bloggers like this: