Home » RDBMS Server » Performance Tuning » LNS wait on SENDREQ+ARCH wait on SENDREQ on ASYNC dataguard at primary database (11g, 11.2.0.3, solaris10)
LNS wait on SENDREQ+ARCH wait on SENDREQ on ASYNC dataguard at primary database [message #630655] Fri, 02 January 2015 00:01 Go to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear All,

Today's AWR pointed out wait event as " log file sync" in OLTP.

Top 5 Timed Foreground Events

Event	Waits	Time(s)	Avg wait (ms)	% DB time	Wait Class
DB CPU	 	3,294	 	74.83	 
direct path read	75,429	459	6	10.42	User I/O
db file sequential read	158,485	368	2	8.35	User I/O
log file sync	122,798	232	2	5.27	Commit
TCP Socket (KGAS)	14	30	2113	0.67	Network



Below are the configuration -

log_archive_config	DG_CONFIG=(TAGICPRD, TAGICODS, TGDROLTP, TGDRODS)	  
log_archive_dest_1	location=+OLTP_DATA2/arc_oltp/ VALID_FOR=(all_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=TAGICPRD	  
log_archive_dest_2	SERVICE=TAGICODS LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=TAGICODS	  
log_archive_dest_3	LOCATION=+OLTP_DATA2/arc_logicalods VALID_FOR=(STANDBY_LOGFILES, STANDBY_ROLE) DB_UNIQUE_NAME=TAGICPRD	  
log_archive_dest_4	SERVICE=TGDROLTP LGWR ASYNC NET_TIMEOUT=180 VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=TGDROLTP	  
log_archive_dest_5	SERVICE=TGDRODS LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=TGDRODS


Why the below events are appearing, though we have LGWR ASYNC?

Wait Event Histogram Detail (4 min to 1 hr)

    Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
    Units for % of Total Waits: m is 64*1024 milliseconds (approximately 67 seconds or 1.1 minutes) h is 4096*1024 milliseconds (approximately 70 minutes or 1.17 hours)
    % of Total Waits: total waits for all wait classes, including Idle
    % of Total Waits: value of .0 indicates value was <.05%; value of null is truly 0
    Ordered by Event (only non-idle events are displayed)

 	 	% of Total Waits
Event	Waits 4m to 1h	<2m	<4m	<8m	<1/4h	<1/2h	< 1h	>=1h
ARCH wait on SENDREQ	1	99.5	 	.5	 	 	 	 
LNS wait on SENDREQ	1	100.0	.0	 	 	 	 


In our DR location, there is network issue. It was confirmed by oracle support team. So is that would be the issue, the above wait events appearing on primary OLTP?

We have implemented cascading data guard concept.

Waiting for your kind reply on the same...

Regards,
Ashish Kumar Mahanta
Re: LNS wait on SENDREQ+ARCH wait on SENDREQ on ASYNC dataguard at primary database [message #630658 is a reply to message #630655] Fri, 02 January 2015 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why do you care about 1 (ONE) wait?

Please align the columns, this is unreadable.

[Updated on: Fri, 02 January 2015 01:48]

Report message to a moderator

Re: LNS wait on SENDREQ+ARCH wait on SENDREQ on ASYNC dataguard at primary database [message #630659 is a reply to message #630658] Fri, 02 January 2015 04:08 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear Michel,

Top 5 Timed Foreground Events

Event			Waits	Time(s)	Avg wait (ms)	% DB time	Wait Class
DB CPU	 		3,294	 	74.83	 
direct path read	75,429	459	6		10.42		User I/O
db file sequential read	158,485	368	2		8.35		User I/O
log file sync		122,798	232	2		5.27		Commit
TCP Socket (KGAS)	14	30	2113		0.67		Network


Wait Event Histogram Detail (4 min to 1 hr)

    Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
    Units for % of Total Waits: m is 64*1024 milliseconds (approximately 67 seconds or 1.1 minutes) h is 4096*1024 milliseconds (approximately 70 minutes or 1.17 hours)
    % of Total Waits: total waits for all wait classes, including Idle
    % of Total Waits: value of .0 indicates value was <.05%; value of null is truly 0
    Ordered by Event (only non-idle events are displayed)

 	 	% of Total Waits

Event			Waits 4m to 1h	<2m	<4m	<8m	<1/4h	<1/2h	< 1h	>=1h
ARCH wait on SENDREQ	1		99.5	 	.5	 	 	 	 
LNS wait on SENDREQ	1		100.0	.0	 	 	 	 	



Other Wait events are conveyed to application team for tuning of sqls. This is the wait event whose root cause to find out.

Is it because, primary database is sending archive logs to standby and due to network issue at other end, generates wait?

Regards,
Ashish
Re: LNS wait on SENDREQ+ARCH wait on SENDREQ on ASYNC dataguard at primary database [message #630676 is a reply to message #630659] Fri, 02 January 2015 10:43 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Once again, why do you care about ONE ONE ONE wait?
What do you expect from this?

[Updated on: Fri, 02 January 2015 10:43]

Report message to a moderator

Previous Topic: Optimisation of query sql
Next Topic: Optimizing Dynamic SQL Usage
Goto Forum:
  


Current Time: Thu Mar 28 10:46:42 CDT 2024