OraFAQ Forum: Server Administration » log file sync and LGWR wait on LNS Event - ADDM

Home » RDBMS Server » Server Administration » log file sync and LGWR wait on LNS Event - ADDM (Oracle 10.2.0.4.0, AIX)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

log file sync and LGWR wait on LNS Event - ADDM [message #502757]

Fri, 08 April 2011 19:49

pchandna1
Messages: 24
Registered: March 2011
Location: USA

Junior Member

Hi Folks,

The attached ADDM report shows LGWR wait on LNS and
log file sync as Top 5 events on AWR and ADDM report for couple of days.

I'm suspecting the possible cause of these event might be
"LGWR is unable to post the processes fast enough, due to excessive commits" or Disk I/O performance to log files is not good enough.

Please advise.

Thanks
Parikshit

Attachment: AWR Rpt - udas2p1 Snap 20619 thru 20621.txt
(Size: 27.70KB, Downloaded 2497 times)

Report message to a moderator

Re: log file sync and LGWR wait on LNS Event - ADDM [message #502758 is a reply to message #502757]

Fri, 08 April 2011 20:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>"LGWR is unable to post the processes fast enough, due to excessive commits" or Disk I/O performance to log files is not good enough.
What Line# in posted file does above appear?

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: log file sync and LGWR wait on LNS Event - ADDM [message #502761 is a reply to message #502758]

Fri, 08 April 2011 22:32

pchandna1
Messages: 24
Registered: March 2011
Location: USA

Junior Member

The above posted lines do not appear in file. This my view on
events -
a)LGWR wait on LNS
b) log file sync

The attached file shows the above two listed events.

Thanks

Report message to a moderator

Re: log file sync and LGWR wait on LNS Event - ADDM [message #502762 is a reply to message #502761]

Fri, 08 April 2011 22:40

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post results from follow sql

select TO_CHAR(first_time,'YYYY-MM-DD HH24:MI') from v$log order by 1

Report message to a moderator

Re: log file sync and LGWR wait on LNS Event - ADDM [message #502770 is a reply to message #502757]

Sat, 09 April 2011 01:51

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

Can you post the AWR report, please? It is generally more use than the ADDM report.

Report message to a moderator

Re: log file sync and LGWR wait on LNS Event - ADDM [message #502783 is a reply to message #502770]

Sat, 09 April 2011 11:35

pchandna1
Messages: 24
Registered: March 2011
Location: USA

Junior Member

Unable to upload .html files. so uploaded in .txt format. Please save as in .html for better readability.

Thanks

Attachment: awr_217_20789.txt
(Size: 571.71KB, Downloaded 2575 times)

Report message to a moderator

Re: log file sync and LGWR wait on LNS Event - ADDM [message #502784 is a reply to message #502762]

Sat, 09 April 2011 11:36

pchandna1
Messages: 24
Registered: March 2011
Location: USA

Junior Member

awr file attached.

Thanks

Attachment: awr_329_20789.txt
(Size: 555.33KB, Downloaded 3048 times)

Report message to a moderator

Re: log file sync and LGWR wait on LNS Event - ADDM [message #502785 is a reply to message #502784]

Sat, 09 April 2011 11:36

pchandna1
Messages: 24
Registered: March 2011
Location: USA

Junior Member

awr file attached.

Thanks

Attachment: awr_429_20789.txt
(Size: 555.33KB, Downloaded 2431 times)

Report message to a moderator

Re: log file sync and LGWR wait on LNS Event - ADDM [message #502786 is a reply to message #502785]

Sat, 09 April 2011 11:51

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Top 5 Timed Events show nothing about LGWR or LNS

You should have explicitly mentioned that this was RAC environment.

post SQL & results that provide evidence a problem exists that needs to be solved.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: log file sync and LGWR wait on LNS Event - ADDM [message #502793 is a reply to message #502785]

Sat, 09 April 2011 13:55

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

I don't think ADDM was any use for RAC back with 10g.

Those global cache buffer busy and current block busy wait events are the ones I would look at. They are giving you an awful CPU time to DB time ratio. Looking at the "SQL ordered by cluster wait time" section you can see the code that is hitting the problem: those insert statements, which are giving you the block contention between the instances. I would look at the segments involved. Can you reverse key the indexes? Or hash partition the tables? And of course, are you using automatic segment space management?

Report message to a moderator

Re: log file sync and LGWR wait on LNS Event - ADDM [message #502821 is a reply to message #502793]

Sun, 10 April 2011 10:40

pchandna1
Messages: 24
Registered: March 2011
Location: USA

Junior Member

Thanks

Yes, We are using Locally managed Table sapce and ASSM. Looking at the 'Sql ordered by cluster wait time
' and both 'Segments by Global Cache Buffer Busy Waits', "Segments By Current Blocks Received' points
some tables and indexes which were hid hardest and travel most acorss the interconnect. The impacted indexes
are concatenated indexes and there is no primary index based on sequence.

One more thing that I observed that 'Cluster wait time and Elapsed Time' for those queries are almost
same.

Most of the impacted tables are not partitioned and have good volume of data except one which is List-partitioned but none
of the index is local-prefixed or non-prefixed on these tables

1) I feel changing them to partition and creating local-prefixed or non-prefixed might help to reduce above events
2) Increase the SGA

Report message to a moderator

Re: log file sync and LGWR wait on LNS Event - ADDM [message #502822 is a reply to message #502821]

Sun, 10 April 2011 10:46

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>2) Increase the SGA
based only upon the belief that BIGGER is always BETTER?
randomly changing parameters is not a good approach to DB tuning.
How will you determine when SGA is properly sized?

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: log file sync and LGWR wait on LNS Event - ADDM [message #502823 is a reply to message #502822]

Sun, 10 April 2011 11:02

pchandna1
Messages: 24
Registered: March 2011
Location: USA

Junior Member

Bigger is always better that's not the belief. Running the ADDM report suggesting to increase SGA from x size to x+y.

Thanks

Report message to a moderator

Re: log file sync and LGWR wait on LNS Event - ADDM [message #502825 is a reply to message #502823]

Sun, 10 April 2011 11:18

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Bigger is always better that's not the belief.

You are wrong and you will learn it at your cost sooner or later.

Regards
Michel

Report message to a moderator

Re: log file sync and LGWR wait on LNS Event - ADDM [message #502826 is a reply to message #502825]

Sun, 10 April 2011 11:24

pchandna1
Messages: 24
Registered: March 2011
Location: USA

Junior Member

What you experts suggest; I should try focus on point#1 at this moment without tweaking the SGA.

Appreciate your help.

Thanks

Report message to a moderator

Re: log file sync and LGWR wait on LNS Event - ADDM [message #502835 is a reply to message #502826]

Sun, 10 April 2011 14:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Since I have no clear understanding what is wrong or needs to be fixed, therefore I am unable to offer any "solution".

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: log file sync and LGWR wait on LNS Event - ADDM [message #502837 is a reply to message #502826]

Sun, 10 April 2011 14:32

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

Hash partitioning your tables and indexes might reduce the contention, if concurrent inserts will then go into different partitions. You'll need to find a partitioning algorithm that will result in some partitions having an affinity to each instance. But you have to be absolutely certain of what will happen. A partitioning strategy that works for your inserts might be a disaster for your queries.
As for memory, well, I always do what the advisors tell me to do.

Report message to a moderator

Previous Topic:	Getting error while applying PSU Patch on windows
Next Topic:	Error in startup mount after shutdown immediate

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 06:35:12 CST 2024